SQLAlchemy getting column data types of query results

Question

from sqlalchemy import create_engine, MetaData, ForeignKey

engine = create_engine("mysql://user:passwd@localhost/shema", echo=False)
meta = MetaData(engine, True)
conn = engine.connect()

tb_list = meta.tables["tb_list"]
tb_data = meta.tables["tb_data"]

tb_list.c.i_data.append_foreign_key( ForeignKey(tb_data.c.i_id) )

q = tb_list.outerjoin(tb_data).select()

res = conn.execute(q)

And now, how can I get columns type of query result res

One of decisions:

res._key_cache[ col_name ][0]

Do you know something else ?

FYI there is a newer version of this question at stackoverflow.com/questions/64761911/…, though it seems SQL server specific? — dfrankow
– dfrankow, Commented Jun 30, 2024 at 21:17

zzzeek · Accepted Answer · 2010-02-14 02:54:11Z

21

you'd say:

types = [col.type for col in q.columns]

the (compiled) statement is on the result too if you feel like digging:

types = [col.type for col in res.context.compiled.statement.columns]

if you want the DBAPI version of the types, which is a little more varied based on DBAPI:

types = [elem[1] for elem in res.cursor.description]

maybe we'll look into adding this kind of metadata more directly to the ResultProxy.

answered Feb 14, 2010 at 2:54

zzzeek

75.8k24 gold badges204 silver badges190 bronze badges

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

3 Comments

z0r Over a year ago

Is it true that you need to do a vendor-specific lookup to get the actual data types? e.g. in Postgres, TYPES[res.context.cursor.description.type_code], where TYPES is a mapping of Postgres type OIDs?

zzzeek Over a year ago

@z0r in psycopg2, those values might be the objects themselves. psycopg2's objects I think evaluate as integers or something like that, try it out.

dfrankow Over a year ago

FYI: this answer is circa 2010, which would've been around sqlalchemy 0.6 or so.

Collectives™ on Stack Overflow

SQLAlchemy getting column data types of query results

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related