0

I came across the below post, which is my exact situation. I have a large SQL query which has the below format:

SET NOCOUNT ON

CREATE TABLE x
INSERT INTO x

CREATE TABLE y
INSERT INTO y
.
.
.

SELECT * FROM x join y
where a

UNION

SELECT * FROM x join y
where b

The query works perfectly fine, returning over 15k rows as intended. When I try and read this into a dataframe in python, I get the 'NoneType' object is not iterable error, as described in the below link. I see the original poster converted his query into a stored procedure. I am wondering if there is another way to resolve this error, without writing a stored procedure.

Python Pandas read_sql_query “'NoneType' object is not iterable” error

UPDATE - inserting python code

server = 'server'
query = 'test.sql'
db  ='db'       
conn = pyodbc.connect(r'Driver={SQL Server};Server=' + dbserver + ';Database=' + database + ';Trusted_Connection=yes;')
fd = open(query, 'r')
df = pd.read_sql_query(fd.read(),conn)
fd.close()
conn.close()
JSmth
  • 31
  • 1
  • 9
  • Hi, can you also put your python code? – Nina Sep 24 '18 at 15:20
  • `read_sql` is essentially for reading the table/sql and fetching the rows. You can run those create statements separately and keep the select statements with union alone in the query. Another way is of course to create a stored procedure. – mad_ Sep 24 '18 at 15:28
  • @mad_ I tried what you mentioned. I basically split the create statements into one SQL, and wrote to dataframe using read_sql_query. Then I did the same with the select statement, which i wrote to a separate dataframe using read_sql_query. This resulted in the same error. – JSmth Sep 24 '18 at 15:33
  • can you post how you are executing those statements? – mad_ Sep 24 '18 at 15:37
  • `server = 'server' query = 'test.sql' db ='db' conn = pyodbc.connect(r'Driver={SQL Server};Server=' + dbserver + ';Database=' + database + ';Trusted_Connection=yes;') sql_1 = "..." df = pd.read_sql_query(sql_1,conn) sql_2 = "..." df_2 = pd.read_sql_query(sql_2,conn)` – JSmth Sep 24 '18 at 15:38
  • Once had the same problem and figured out on PL/SQL query hitting enter was ok in the editor but when sending it as a query I had to add semicolons. Would you also check if this works? – Nina Sep 24 '18 at 15:48
  • 1
    `;` will basically separate each query from one another in most of the databases. http://pandas.pydata.org/pandas-docs/version/0.22/generated/pandas.read_sql_query.html docs shows the read_sql is good for reading the tables in pandas dataframes and not great for sql execution. you have `pyodbc` driver which you can use to run your create statements. Look at this https://stackoverflow.com/questions/7744742/cant-create-tables-in-access-with-pyodbc. I think you can run series of queries using pyodbc execute statements. – mad_ Sep 24 '18 at 15:53

1 Answers1

0

This might be the issue as this was once the problem with my query. When sending the query, make sure semicolons aren't missed. On the editor, hitting enter works just fine, but when you're sending it, either as a string or from your files, you'd have to explicitly add it at the end of each statement.

Nina
  • 148
  • 2
  • 16