Get table description from Python sqlalchemy connection object and table name as a string

Starting from a sqlalchemy connection object in Python and a table name as a string how do I get table properties, eg column names and datatypes.

For example, connect to a database in sqlalchemy

from sqlalchemy import create_engine
conn = create_engine('mssql+pyodbc://...driver=ODBC+Driver+17+for+SQL+Server').connect()

Then conn is a sqlalchemy connection object

In [1]: conn
Out[1]: <sqlalchemy.engine.base.Connection at 0x7feb6efef070>

How do I get table properties based on a table name as a string, eg table = '...'?

This should work but instead creates an empty DataFrame

from sqlalchemy import text
import pandas as pd
query = f"""SELECT * FROM information_schema.columns WHERE table_name='{table}'"""
df = pd.read_sql_query(text(query), conn)

In [2]: df
Out[2]:
Empty DataFrame
Columns: [TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME]
Index: []

versions:
sqlalchemy - 2.0.4
pandas - 1.5.3

Solution

You wrote

conn = create_engine(...)

Prefer to phrase it as

engine = create_engine(...)

Load details dynamically from the RDBMS (with sqlalchemy 1.4):

from pprint import pp
import sqlachemy as sa

engine = ...
meta = sa.MetaData(bind=engine)
my_table = sa.Table('my_table', meta, autoload=True)

for column in my_table.c:
    print(column)

Take a look at pp(dir(my_table)) to see lots of other details that are available.
Also help(my_table).

Additionally, the resulting table object is a very convenient starting place for session SELECT queries, INSERTs, and so on.

EDIT

SqlAlchemy 2.0 is a breaking change. The docs explain that you should use this alternate keyword:

meta = sa.MetaData()
my_table = sa.Table('my_table', meta, autoload_with=engine)