0

I am using pyodbc to connect to SQL Server.

I can run a single query that creates a temp table with

query = """select*
into #table0 
from 
(select
a.*
,ROW_NUMBER as 'Rank'
from 
lead..table a

select * from #table0"""

data = pd.read_sql(sql,conn)

data returns the expected result.

Once I try to make another temp table, based on the previously created table, I get the error

columns = [col_desc[0] for col_desc in cursor.description]
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
TypeError: 'NoneType' object is not iterable

The query that I use is:

query = """select*
into #table0 
from 
(select
a.*
,ROW_NUMBER  as 'Rank'
from 
lead..tableA a


select
a.col1,
b.col2,
c.col3
into #table1 
from #table0 a 
join Lead..tableB b on a.Id=b.ID
left join Lead..tableC c on b.Number=c.Number

select * from #table1
"""

When I run the query in SQL Server it produces the desired results. Any suggestions as to what this can imply?

frank
  • 3,036
  • 7
  • 33
  • 65
  • 1
    Temp tables don't persist beyond the current connection, so it's likely being lost between the two executions. The most common fix for this is to use a global temporary table. [This question and answer may be useful](https://stackoverflow.com/questions/37863125/sql-server-temp-table-not-available-in-pyodbc-code) – JNevill Jul 11 '23 at 15:39
  • In your particular case, try adding `SET NOCOUNT ON;` to the beginning of your SQL code block. – Gord Thompson Jul 11 '23 at 19:18

0 Answers0