Accessing Global Temporary Table through pyodbc

Question

I am operating a python 3 notebook in Azure Data Studio (similar to jupyter notebook) and trying to access an existing global temp table (##TEMP1) via pyodbc.

Here is my code:

import pandas as pd
import pyodbc

con = pyodbc.connect(
    Trusted_Connection="yes",
    driver="{ODBC Driver 17 for SQL Server}",
    host="SERVERNAME",
    database=db,
    trustServerCertificate="yes",
    multisubnetfailover="yes",
    applicationIntent="ReadOnly",
)

sql = """
select * from ##TEMP1
"""

data = pd.read_sql(sql, con)

con.close()
data.head()

In Azure Data Studio, when I switch the kernel to sql and simply query select * from ##TEMP1, it returns results, however when I try to run via the python code above via pyodbc, it's returning the following error.

DatabaseError: Execution failed on sql ' select * from ##TEMP1 ': ('####', "[####] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name '##TEMP1'. (###) (SQLExecDirectW)")

Please help, those much smarter than I! :)

EcSync · Answer 1 · 2019-10-17T15:00:38.343

I think you want something like this:

I use pyodbc to make the connection and query the database. As you can see I create a curser with pyodbc and execute the curser with the SQL query string as an argument. Then use fetchall() to return the result for the curser

import pandas as pd
import pyodbc
con = pyodbc.connect(Trusted_Connection='yes', driver = '{ODBC Driver 17 for SQL Server}',host = 'SERVERNAME', database = db,trustServerCertificate='yes',multisubnetfailover='yes',applicationIntent='ReadOnly')

crsr = conn.cursor()

sql = '''
select * from ##TEMP1
'''

crsr.execute(sql)

results = crsr.fetchall()

con.close()
data.head()

Accessing Global Temporary Table through pyodbc

1 Answers1