How to get metadata of an SQL query?

user_5741205 0
Viewed 1420

I know how to read metadata for existing DB table using SQL Alchemy:

meta = MetaData(bind=engine)
meta.reflect(schema="schema_name", views=True, resolve_fks=False)
res = meta.tables["table_name"]

Question:

How can I get metadata or column types for an SQL query (select * from tab1 join tab2 on tab1.id = tab2.id)?

metadata sqlalchemy python
2 Answers

It's a bit of a hack, but one approach would be to create a temporary table using the query and then reflect the table. Example for mssql:

with engine.begin() as con:
    original_query = """\
    SELECT c.name as firstname, p.lastname 
    FROM parent p INNER JOIN child c
        ON c.parent_id = p.id
    """
    con.execute(
        sa.text(f"SELECT TOP 1 q.* INTO #tmp FROM ({original_query}) q")
    )
    con.commit()
    tmp = sa.Table("#tmp", sa.MetaData(), autoload_with=engine)
    for col in tmp.columns:
        print(repr(col))
    """console output:
    Column('firstname', NVARCHAR(length=100), table=<#tmp>, nullable=False)
    Column('lastname', VARCHAR(length=50, collation='SQL_Latin1_General_CP1_CI_AS'), table=<#tmp>)
    """
user_21443900

When you execute an query using sqlalchemy Core (not ORM), you will will be returned an instance of ResultProxy object.

ResultProxy is a wrapper over the underlying DBAPI cursor, and if the driver to the RDBMS your code uses is implemented in compliance with PEP 249 -- Python Database API Specification v2.0 or PEP 248 -- Python Database API Specification v1.0, you should be access the underlying ResultProxy.cursor object, which in turn should have .description property.

Therefore, code similar to below should just work:

res = session.execute(my_sql_statement)
columns = res.cursor.description
for col in columns:
    print(col.name, col.type_code, col.display_size, col.internal_size, col.precision, col.scale, col.null_ok)

For small query like SELECT "user".id AS user_id, "user".name AS user_name, "group".id AS group_id, "group".name AS group_name FROM "user" JOIN user_group AS user_group_1 ON "user".id = user_group_1.user_id JOIN "group" ON "group".id = user_group_1.group_id, here is the output I see for PostgreSQL database:

user_id 23 None 4 None None None
user_name 1043 None 250 None None None
group_id 23 None 4 None None None
group_name 1043 None 250 None None None
user_995940
Related