CODEWITHSUNDEEP
pythonsql-serversqlalchemy
Damakshn
Damakshn

Reputation: 31

Reflection in SQLAlchemy doesn't work for MS SQL Server system tables?

I'm trying to reflect system table in MS SQL Server database:

from sqlalchemy import engine, create_engine, MetaData, Table 

meta = MetaData() 

url = engine.url.URL(
    "mssql+pyodbc",
    username=credentials["username"],
    password=credentials["pswd"],
    host=credentials["host"],
    database=credentials["db"],
    query=dict(driver="ODBC Driver 13 for SQL Server")
)

e = create_engine(url)
conn = e.connect()
tt = Table("objects", meta, autoload=True, autoload_with=e, schema="sys")
for c in tt.columns:
    print(c.name)

At the end I get NoSuchTable error. I tried to reflect other system tables (sys.triggers, sys.sql_modules) - same results. With ordinary tables this code works normally, I can list columns and make other queries. Login which I use in my application has "db_owner" role, so it has enough permissions, and if I write something like this 

for item in conn.execute("select * from sys.triggers"):
    print(item)

it works fine.

What am I doing wrong? Is there any other way to work with data from system tables, besides executing raw sql and wrapping results in dataclasses etc.?

Upvotes: 2

Views: 1504

Answers (2)

Victor Nobel
Victor Nobel

Reputation: 15

When you add echo='debug' to your engine you can see the steps it's going through when talking to the database. In my case it sends out a query consisting of a left join of all_col_comments on all_tab_cols. In this query you'll see it uses owner = <schema value>.

I found that the system tables are owned by 'SYS' so by setting the schema to 'SYS' it will be able to find the system table just fine. A small code example to clarify where to set the schema:

table = db.Table('USER_SOURCE', metadata, schema='SYS', autoload=True, autoload_with=engine)

Upvotes: 0

xXxSniper666xXx
xXxSniper666xXx

Reputation: 41

I was trying to reflect system views in MS SQL Server database. After adding echo='debug' to engine, I realized that, SQL Alchemy searches table and view metadata from INFORMATION_SCHEMA in MSSQL.

The system tables and views are not listed in INFORMATION_SCHEMA.TABLES OR INFORMATION_SCHEMA.VIEWS.

(I'm using SQLAlchemy version 1.3.5.)

Upvotes: 1

Related Questions