CODEWITHSUNDEEP
pythonsql-serverpandaspyodbc
lampShadesDrifter
lampShadesDrifter

Reputation: 4149

Executing sequence of MS sql queries with temp tables in python

Currently trying to use pyodbc and pandas to read sequence of sql queries (from MS sql server) and examine as dataframe, getting the following error on the very first query in the sequence:

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-95-5eec1638614b> in <module>()
    285 for count, q in enumerate(sql_in):
    286     print count
--> 287     frame_in = pd.read_sql(q, cnxn)
    288 print frame_in.head(n=10)
    289 

/home/mapr/anaconda2/lib/python2.7/site-packages/pandas/io/sql.pyc in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)
    497             sql, index_col=index_col, params=params,
    498             coerce_float=coerce_float, parse_dates=parse_dates,
--> 499             chunksize=chunksize)
    500 
    501     try:

/home/mapr/anaconda2/lib/python2.7/site-packages/pandas/io/sql.pyc in read_query(self, sql, index_col, coerce_float, params, parse_dates, chunksize)
   1598         args = _convert_params(sql, params)
   1599         cursor = self.execute(*args)
-> 1600         columns = [col_desc[0] for col_desc in cursor.description]
   1601 
   1602         if chunksize is not None:

TypeError: 'NoneType' object is not iterable

The relevant code snippet looks like:

sql_in = """
SET NOCOUNT ON;
select <stuff> from <table1> into #<temptable1>
----SPLIT
SET NOCOUNT ON;
select <stuff> from <table2> into #<temptable2>
----SPLIT
SET NOCOUNT ON;
select <stuff> from <table3> into #<temptable3>
----SPLIT
SET NOCOUNT ON;
select <stuff> from <table4> <joined with temptables1-3> into #<temptable4>
""".split('----SPLIT')

for count, q in enumerate(sql_in):
    print count
    frame_in = pd.read_sql(q, cnxn)

Done this way because only interested in the final temp table (using split because of the advice posted here).

Can confirm that pyodbc is configured right by running:

cnxn = pyodbc.connect(cnxn_str)
cursor = cnxn.cursor()

#Sample select query
print 'Testing db connection...'
cursor.execute("SELECT @@version;") 
row = cursor.fetchone() 
while row: 
    print row[0] 
    row = cursor.fetchone()

And actually getting the server version.

Any ideas why this could be happening and how to fix? Thanks.

Upvotes: 0

Views: 1071

Answers (1)

scomes
scomes

Reputation: 1846

I believe it is because the first several queries don't return any data. Pandas read_sql expects data to come back. You should just use the pyodbc execute function to run those queries.

Upvotes: 1

Related Questions