SQLAlchemy does not return all table names in SQL Server database

Question

When I download this example database, AdventureWorksLT2012_Data, and try to access the table_names in sql alchemy, via

from sqlalchemy import create_engine
engine = create_engine("mssql+pyodbc://sa:PASSWORD_HERE@localhost:1433/AdventureWorksLT?driver=FreeTDS")
engine.table_names()

I get ['BuildVersion', 'ErrorLog']. However this is missing tables from the database.

For example, executing this query gives the expected table names...

rs = engine.execute("SELECT name FROM sys.Tables")
[row['name'] for row in rs]

I get

['BuildVersion', 'Address', 'Customer', 'CustomerAddress', 'Product', 'ProductCategory', 'ProductDescription', 'ProductModel', 'ProductModelProductDescription', 'SalesOrderDetail', 'SalesOrderHeader', 'ErrorLog']

Is this a bug, or have I overlooked something? If I create a new database, and tables with identical names as above, then engine.table_names() works as expected.

@Parfait, the db wasn't corrupted, issue can be reproduced by downloading from link. I didn't realize that engine.table_names might only look at a particular schema in the db. — machow
– machow, Commented Mar 14, 2017 at 14:12

Gord Thompson · Accepted Answer · 2017-03-14 00:06:24Z

8

Looking at the corresponding database creation script, the two tables you get from engine.table_names() are the ones that are created in the [dbo] schema. The others are created in the [SalesLT] schema.

So it appears that engine.table_names() is only listing tables for the default schema in effect at the time it is invoked, which in this case happens to be [dbo].

answered Mar 14, 2017 at 0:06

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

machow Over a year ago

Ah, that's it! Thanks for pointing to the database creation script--it's really useful. In case it's useful to anyone, I ended up listing the tables from the SalesLT schema using engine.table_names(schema = "SalesLT").

Collectives™ on Stack Overflow

SQLAlchemy does not return all table names in SQL Server database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related