How to read a table from INFORMATION_SCHEMA using SQL Alchemy?

Question

I have table: TAX_CREDIT and I am trying to access some metadata of that table from the table: INFORMATION_SCHEMA.COLUMNS. My database is SQLServer. I am trying to get certain column names and check the datatypes of it for some other function. To do that, I wrote the below code to access the table: INFORMATION_SCHEMA.COLUMNS

import sqlalchemy as sa
from sqlalchemy import select, MetaData, Table, Column, String
from sqlalchemy.engine.reflection import Inspector

def prep_ddl(table_name: str):
    engine = sa.create_engine("mssql+pyodbc://user:pwd@server/dbname?driver=SQL+Server")
    insp = Inspector.from_engine(engine)
    metadata = MetaData()
    table = Table(table_name, metadata, Column('column_name', String), Column('data_type', String), Column('character_maximum_length', String), Column('numeric_precision', String), Column('table_name', String), autoload=True, autoload_with=engine)
    conn = engine.connect()
    s = select([table.c.column_name, table.c.data_type, table.c.character_maximum_length, table.c.numeric_precision]).where(table.c.table_name == 'TAX_CREDITED')
    result = conn.execute(s)
    for row in result:
        print(row)
    pass

if __name__ == '__main__':
    prep_ddl('INFORMATION_SCHEMA.COLUMNS')

If I run the above code, I see an error message:

Traceback (most recent call last):
  File "C:/Users/USERNAME/PycharmProjects/PrimaryUnique/main.py", line 23, in <module>
    prep_ddl('INFORMATION_SCHEMA.COLUMNS')
  File "C:/Users/USERNAME/PycharmProjects/PrimaryUnique/main.py", line 10, in prep_ddl
    table = Table(table_name, metadata, Column('column_name', String), Column('data_type', String), Column('character_maximum_length', String), Column('numeric_precision', String), Column('table_name', String), autoload=True, autoload_with=engine)
  File "C:\Users\USERNAME\PycharmProjects\PrimaryUnique\venv\lib\site-packages\sqlalchemy\sql\schema.py", line 555, in __new__
    table._init(name, metadata, *args, **kw)
  File "C:\Users\USERNAME\PycharmProjects\PrimaryUnique\venv\lib\site-packages\sqlalchemy\sql\schema.py", line 644, in _init
    self._autoload(
  File "C:\Users\USERNAME\PycharmProjects\PrimaryUnique\venv\lib\site-packages\sqlalchemy\sql\schema.py", line 667, in _autoload
    autoload_with.run_callable(
  File "C:\Users\USERNAME\PycharmProjects\PrimaryUnique\venv\lib\site-packages\sqlalchemy\engine\base.py", line 2214, in run_callable
    return conn.run_callable(callable_, *args, **kwargs)
  File "C:\Users\USERNAME\PycharmProjects\PrimaryUnique\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1653, in run_callable
    return callable_(self, *args, **kwargs)
  File "C:\Users\USERNAME\PycharmProjects\PrimaryUnique\venv\lib\site-packages\sqlalchemy\engine\default.py", line 469, in reflecttable
    return insp.reflecttable(
  File "C:\Users\USERNAME\PycharmProjects\PrimaryUnique\venv\lib\site-packages\sqlalchemy\engine\reflection.py", line 679, in reflecttable
    raise exc.NoSuchTableError(table.name)
sqlalchemy.exc.NoSuchTableError: INFORMATION_SCHEMA.COLUMNS

The error message says: no such table found for INFORMATION_SCHEMA.COLUMNS table. In INFORMATION_SCHEMA.COLUMNS, the schema is INFORMATION_SCHEMA and table is COLUMNS. If I run a query on the same table it runs.

select TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='some_table_name'

Edit 1: I also tried to give schema name in table and pass just 'COLUMNS' as below:

table = Table(table_name, metadata, Column('column_name', String), Column('data_type', String),
                      Column('character_maximum_length', String), Column('numeric_precision', String),
                      Column('table_name', String), autoload=True, autoload_with=engine, schema='INFORMATION_SCHEMA')

Edit 2:

I tried a bit different in my third attempt:

table = Table(table_name, metadata,Column('column_name', String),Column('data_type', String),Column('character_maximum_length', String),Column('numeric_precision', String), Column('table_name', String))

s = select([table.columns.column_name, table.columns.data_type, table.columns.character_maximum_length, table.columns.numeric_precision]) .where(table.columns.table_name == 'TAX_CREDITED')

But Still see the same error.

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42S02', "[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'INFORMATION_SCHEMA.COLUMNS'. (208) (SQLExecDirectW); [42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)")
[SQL: SELECT [INFORMATION_SCHEMA.COLUMNS].column_name, [INFORMATION_SCHEMA.COLUMNS].data_type, [INFORMATION_SCHEMA.COLUMNS].character_maximum_length, [INFORMATION_SCHEMA.COLUMNS].numeric_precision 
FROM [INFORMATION_SCHEMA.COLUMNS] 
WHERE [INFORMATION_SCHEMA.COLUMNS].table_name = ?]
[parameters: ('TAX_CREDITED',)]

Am I specifying the table name wrongly anywhere ? Could anyone let me know how can I fix the issue ? Any help is much appreciated.

Answer 1

The "mssql" dialect of SQLAlchemy uses INFORMATION_SCHEMA.COLUMNS itself when performing reflection on a table, and the INFORMATION_SCHEMA.COLUMNS system view does not return information about any system views (not even itself) so SQLAlchemy cannot autoload it.

If you really want to pull information from the INFORMATION_SCHEMA.COLUMNS view then you will need to use textual SQL:

sql = sa.text(
    "SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH "
    "FROM INFORMATION_SCHEMA.COLUMNS "
    "WHERE TABLE_NAME = :tbl_name"
)
with engine.begin() as conn:
    result = conn.execute(sql, {"tbl_name": "team"}).fetchall()
    pprint(result)
    """console output:
    [('id', 'int', None),
     ('prov', 'nvarchar', 2),
     ('city', 'nvarchar', 50),
     ('team_name', 'nvarchar', 50)]
    """