SQL Server temp table not available in pyodbc code

Question

I'm running a series of complex sql queries in python and it involves temp tables. My auto-commit method doesn't seem to be working to retrieve the data from the temp table. The code snippet I'm using below and this is the output I'm getting:

testQuery="""
    Select top 10 *
    INTO #Temp1
    FROM Table1 t1
    JOIN Table2 t2
    on t1.key=t2.key
"""
    cnxn=pyodbc.connect(r'DRIVER={SQL Server Native Client 11.0};SERVER=server;DATABASE=DB;UID=UID;PWD=PWD')
    cnxn.autocommit=True
    cursor=cnxn.cursor()
    cursor.execute(testQuery)
    cursor.execute("""Select top 10 * from #Temp1""")
    <pyodbc.Cursor at 0x8f78930>


cnxn=pyodbc.connect(r'DRIVER={SQL Server Native Client 11.0};SERVER=server;DATABASE=DB;UID=UID;PWD=PWD')
cnxn.autocommit=True
cursor=cnxn.cursor()
cursor.execute(testQuery)
cursor.execute("""Select top 10 * from #Temp1""")

Solution 1

Even though this question has a "solution", i.e., using global temp table instead of a local temp table, future readers might benefit from understanding why the problem happened in the first place.

A temporary table is automatically dropped when the last connection using said table is closed. The difference between a local temp table (#Temp1) and a global temp table (##Temp1) is that the local temp table is only visible to the connection that created it, while an existing global temp table is available to any connection.

So the following code using a local temp table will fail ...

conn = pyodbc.connect(conn_str, autocommit=True)
crsr = conn.cursor()

sql = """\
SELECT 1 AS foo, 2 AS bar INTO #Temp1
"""
crsr.execute(sql)

conn = pyodbc.connect(conn_str, autocommit=True)
crsr = conn.cursor()

sql = """\
SELECT foo, bar FROM #Temp1
"""
crsr.execute(sql)
row = crsr.fetchone()
print(row)

... while the exact same code using a global temp table will succeed ...

conn = pyodbc.connect(conn_str, autocommit=True)
crsr = conn.cursor()

sql = """\
SELECT 1 AS foo, 2 AS bar INTO ##Temp1
"""
crsr.execute(sql)

conn = pyodbc.connect(conn_str, autocommit=True)
crsr = conn.cursor()

sql = """\
SELECT foo, bar FROM ##Temp1
"""
crsr.execute(sql)
row = crsr.fetchone()
print(row)

... because the second pyodbc.connect call opens a separate second connection to the SQL Server without closing the first one.

The second connection cannot see the local temp table created by the first connection. Note that the local temp table still exists because the first connection was never closed, but the second connection cannot see it.

However, the second connection can see the global temp table because the first connection was never closed and therefore the global temp table continued to exist.

This type of behaviour has implications for ORMs and other mechanisms that may implicitly open and close connections to the server for each SQL statement that it executes.

Solution 2

I asked a colleague about this live and his suggestions worked. So I went and changed the testQuery to create a global temp table instead of a local (##Temp1 instead of #Temp1). And went to sql server to test whether the temp table was actually being created-it was. So I isolated that the problem was the second cursor.execute statement. I modified the code to use pandas read_sql_query instead and it all worked out! Below is the code I used:

testQuery="""
    Select top 10 *
    INTO ##Temp1
    FROM Table1 t1
    JOIN Table2 t2
    on t1.key=t2.key
"""
    cnxn=pyodbc.connect(r'DRIVER={SQL Server Native Client 11.0};SERVER=server;DATABASE=DB;UID=UID;PWD=PWD')
    cnxn.autocommit=True
    cursor=cnxn.cursor()
    cursor.execute(testQuery)
    cnxn.commit()
    query1="Select top 10 * from ##Temp1"
    data1=pd.read_sql_query(query1, cnxn)
    data1[:10]

Comments

[Alex]

Did you also figure out why global temp tables work while local ones don't? I also had a similar issue where I had to create multiple temp tables. The first temp table worked, but the subsequent ones did not. Using your method of creating global temp tables did the trick

[Auspex]

If that's true, there should really be some explanation of why it's so.