1

I have a complex SQL Server query that I would like to execute from Python and return the results as a Pandas DataFrame.

My database is read only so I don't have a lot of options like other answers say for making less complex queries.

This answer was helpful, but I keep getting TypeError: 'NoneType' object is not iterable

SQL Example

This is not the real query - just to demonstrate I have temporary tables. Using global temporary tables because my queries failed previously using local temp tables: See this question

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF OBJECT_ID('tempdb..##temptable') IS NOT NULL DROP TABLE ##temptable
IF OBJECT_ID('tempdb..##results') IS NOT NULL DROP TABLE ##results

DECLARE @closing_period int = 0, @starting_period int = 0

Select col1, col2, col3 into ##temptable from readonlytables

Select * into ##results from ##temptable

Select * from ##results

Execute query with pyodbc and pandas

conn = pyodbc.connect('db connection details')

sql = open('myquery.sql', 'r')
df = read_sql_query(sql.read(), conn)
sql.close()
conn.close()

Results - Full Stack Trace

ypeError                                 Traceback (most recent call last)
<ipython-input-38-4fcfe4123667> in <module>
      5 
      6 sql = open('sql/month_end_close_hp.sql', 'r')
----> 7 df = pd.read_sql_query(sql.read(), conn)
      8 #sql.close()
      9 

C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\sql.py in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize)
    330         coerce_float=coerce_float,
    331         parse_dates=parse_dates,
--> 332         chunksize=chunksize,
    333     )
    334 

C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\sql.py in read_query(self, sql, index_col, coerce_float, params, parse_dates, chunksize)
   1632         args = _convert_params(sql, params)
   1633         cursor = self.execute(*args)
-> 1634         columns = [col_desc[0] for col_desc in cursor.description]
   1635 
   1636         if chunksize is not None:

TypeError: 'NoneType' object is not iterable

When I run the query in my database I get the expected results. If I pass the query in as a string I also get the expected results:

Query as String

conn = pyodbc.connect('db connection details')

sql = '''
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF OBJECT_ID('tempdb..##temptable') IS NOT NULL DROP TABLE ##temptable
IF OBJECT_ID('tempdb..##results') IS NOT NULL DROP TABLE ##results

DECLARE @closing_period int = 0, @starting_period int = 0

Select col1, col2, col3 into ##temptable from readonlytables

Select * into ##results from ##temptable

Select * from ##results
'''

df = read_sql(sql, conn)

conn.close()

I think it might have something to do with the single quotes inside my query?

terrah27
  • 21
  • 1
  • 7
  • 1
    Out of interesting, what is `Select * into ##results from ##temptable` there to achieve, other than to duplicate the data? Also why global temporary tables? – Thom A Aug 18 '20 at 11:26
  • can you post the full stack trace? – Umar.H Aug 18 '20 at 11:50
  • After `sql = open('myquery.sql', 'r')`, try `df = read_sql_query(sql.read(), conn)` – Gord Thompson Aug 18 '20 at 14:26
  • @Larnu - I am trying to provide examples of whats in my query without actually posting the query itself. Local temp tables didn't work when I passed queries as strings so I changed to global (per this answer)[https://stackoverflow.com/questions/37863125/sql-server-temp-table-not-available-in-pyodbc-code] – terrah27 Aug 18 '20 at 18:15
  • @Manakin Added full stack trace – terrah27 Aug 18 '20 at 18:17
  • @GordThompson Thank you for catching this - I had in my code but omitted in the question. Unfortunately this doesn't solve the problem. – terrah27 Aug 18 '20 at 18:17
  • Line 7 in your stack trace does not match your sample code. The `read()` is still missing. – Gord Thompson Aug 18 '20 at 18:54
  • I am unable to reproduce your issue. [This code](https://pastebin.com/xc11BGES) works fine for me. – Gord Thompson Aug 19 '20 at 14:42

1 Answers1

1

I got it working.

I had to use global variables by replacing @ with @@ I was able to get the query working as expected.

DECLARE @@closing_period int = 0, @@starting_period int = 0

Update: My ODBC driver was very outdated - after updating to the latest version, I no longer needed global temp tables or variables - and the query ran significantly faster.

terrah27
  • 21
  • 1
  • 7