CODEWITHSUNDEEP
pythonsql-serverpandassqlalchemypyodbc
Kevin Maguire
Kevin Maguire

Reputation: 93

SQLAlchemy cannot autoload an mssql temporary table

I'm not able to connect to temporary tables created on an SQL server using SQLAlchemy.

I connect to the server:

engine = create_engine(URL, poolclass=StaticPool)

I fill a temporary table with data from a pandas dataframe:

df_tmp.to_sql('#table_test', con=engine)

The table exists on the server:

res = engine.execute('SELECT * FROM tempdb..#table_test')
print(res)

which returns a list of tuples of my data. But then when I try to make an SQLAlchemy table it fails with a NoSuchTableError:

from sqlalchemy import create_engine, MetaData, Table
metadata = MetaData(engine)
metadata.create_all()
table = Table('#table_test', metadata, autoload=True, autoload_with=engine)

I also tried this, which gives the same error:

table = Table('tempdb..#table_test', metadata, autoload=True, autoload_with=engine)

And I also tried creating a blank table with an SQL command, which gives the same error when I try to read it with SQLAlchemy:

engine.execute('CREATE TABLE #table_test (id_number INT, name TEXT)')

Does SQLAlchemy support temporary tables? If so what is going wrong here? I'd like to have the temporary table as an sqlalchemy.schema.Table object if possible, as then it fits with all my other code.

Upvotes: 5

Views: 1602

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123474

(re: comments to the question)

Actually, it is a limitation of the current mechanism by which SQLAlchemy's mssql dialect checks for the existence of a table. It queries INFORMATION_SCHEMA.TABLES for the current catalog (database), and #temp tables do not appear in that view. They do appear — after a fashion, and in a not-particularly-helpful way — if we USE tempdb and then query INFORMATION_SCHEMA.TABLES from there.

For now, I have created a GitHub issue here to see if we can improve on this.

Update 2020-09-01

The changes for the above GitHub issue have been merged into SQLAlchemy's master branch and will be included in version 1.4. If you want to take advantage of this feature before 1.4 is officially released you can install SQLAlchemy via

pip install git+https://github.com/sqlalchemy/sqlalchemy.git

Upvotes: 5

Related Questions