NoneType object is not iterable error in pandas

Question

I am trying to pull some data from a stored proc on a sql server using python.

Here is my code:

import datetime as dt
import pyodbc
import pandas as pd

conn = pyodbc.connect('Trusted_Connection=yes', driver = '{SQL Server Native client 11.0}',server = '*****, database = '**')

pd.read_sql("EXEC ******** '20140528'",conn)

I get the error: TypeError: 'NoneType' object is not iterable

I suspect this is because I have a cell in the sql table with value NULL but not sure if that's the true reason why I am getting the error. I have run many sql statements using the same code without any errors.

Here's the traceback:

In[39]: pd.read_sql("EXEC [dbo].[] '20140528'",conn)
Traceback (most recent call last):
  File "C:*", line 3032, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-39-68fb1c956dd7>", line 1, in <module>
    pd.read_sql("EXEC [dbo].[] '20140528'",conn)
  File "C:*", line 467, in read_sql
    chunksize=chunksize
  File "c:***", line 1404, in read_query
    columns = [col_desc[0] for col_desc in cursor.description]
TypeError: 'NoneType' object is not iterable

Solution 1

Your sproc needs

SET NOCOUNT ON;

Without this sql will return the rowcount for the call, which will come back without a column name, causing the NoneType error.

Solution 2

pd.read_sql() expects to have output to return, and tries to iterate through the output; that's where the TypeError is coming from. Instead, execute with a cursor object:

import datetime as dt
import pyodbc
import pandas as pd

conn = pyodbc.connect('Trusted_Connection=yes', driver = '{SQL Server Native client 11.0}',server = '*****', database = '**')
cur = conn.cursor()
cur.execute("EXEC ******** '20140528'")

You won't receive any output, but since none is expected, your code should run without error.

Comments

[ZdaR]

I can see a lot of unmatched closing quotes for strings ", '?

[Andrew L]

I've also encountered this issue using temp tables in my queries. This method will also fix.

[Stev]

Yes, this worked for me. I didn't need to use it for pymssql but needed it for pyodbc.