Error: 'NoneType' object is not iterable when read_sql

Question

have looked all over the internet for a solution.

This is the code:

import pyodbc
import pandas as pd
conn = pyodbc.connect("Driver={SQL Server};"
                         "Server=Server;"
                         "Trusted_Connection=yes;")
cursor = conn.cursor()
query = """
SET
   nocount 
   ON;
USE database;
SELECT
   * 
FROM
   (
      SELECT
         db.column1 AS col1,
         db.column2 AS col2,
         db.column3 AS col3,
         CASE
            WHEN
               db.column4 IS NULL 
            THEN
               0 
            ELSE
               1 
         END
         AS col4, 
         db.column5 AS col5 
      FROM
         TABLE AS tab 
         INNER JOIN
            data as db 
            ON ( operator ) 
   )
   AS sivola 
WHERE
   sivola.col2 > x 
ORDER BY
   sivola.col2 ASC
"""
df = pd.read_sql(query, conn)

Although I am using SET nocount ON; (as suggested in many posts), I am still receving the error TypeError: 'NoneType' object is not iterable

Any viable solution?

EDIT: Full traceback

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-56-f01b80801200> in <module>()
     82 logging.error('Starting query from BL \n')
     83 
---> 84 df = pd.read_sql(query, conn)

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)
    379             sql, index_col=index_col, params=params,
    380             coerce_float=coerce_float, parse_dates=parse_dates,
--> 381             chunksize=chunksize)
    382 
    383     try:

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\io\sql.py in read_query(self, sql, index_col, coerce_float, params, parse_dates, chunksize)
   1434         args = _convert_params(sql, params)
   1435         cursor = self.execute(*args)
-> 1436         columns = [col_desc[0] for col_desc in cursor.description]
   1437 
   1438         if chunksize is not None:

TypeError: 'NoneType' object is not iterable

The formatting is a mess, please fix that. Then give the full traceback — roganjosh, Nov 22 '19 at 14:05
I'm _guessing_ because I'm on a phone so can't test. You need to pass a _cursor_ not a _connection_ to `read_sql` — roganjosh, Nov 22 '19 at 14:19
The query returns 1999 rows on Microsoft SQL Server Management Studio — E. Faslo, Nov 22 '19 at 14:21
The query quite clearly isn't incorrect, it's how pandas handles it — roganjosh, Nov 22 '19 at 14:23

kubote · Accepted Answer · 2019-11-22T14:41:56.503

2

Could you remove the SET nocount ON; USE database;?

I think that way of getting a result from a connection is not right. Either you use the cursor and perform tasks without getting anything or you get stuff using just a select from a connection the way you did, but without those lines.

edited Nov 22 '19 at 14:41

answered Nov 22 '19 at 14:33

kubote

86
2
6

1

Indeed I read this post: https://stackoverflow.com/questions/48287404/python-pandas-read-sql-query-nonetype-object-is-not-iterable-error?noredirect=1&lq=1 where it's pointed out that if you have commands like SET pandas only runs that first line and the result is empty. I have tried to remove my SET, but still was not working. Now that I removed my SET and USE, the code runs perfectly! Thanks a lot – E. Faslo Nov 22 '19 at 14:44

Error: 'NoneType' object is not iterable when read_sql

1 Answers1