Get SQL data type from sqlalchemy model

Question

I am trying to generate documentation for a database created with sqlalchemy, and want to include column SQL data types, which are dependent on the current engine. Is there any way to extract this from sqlalchemy? I tried following the metadata.create_all(engine) rabbit hole but quickly got lost.

Essentially what I'm after is a mapping:

{
    'Integer': 'INT',
    'String': 'VARCHAR',
    ...
}

which is specific to the current engine and takes a sqlalchemy type to a SQL data type.

Do you mean you're using the generic types and want to know what they actually compile to? — Ilja Everilä
– Ilja Everilä, Commented Jun 26, 2018 at 9:36

Ilja Everilä · Accepted Answer · 2018-06-26 11:22:48Z

3

You can create/use a dialect instance and its type compiler:

In [18]: engine = create_engine('postgresql://sdf:sdf@localhost/sdf')

In [19]: types.Unicode().compile(dialect=engine.dialect)
Out[19]: 'VARCHAR'

Note that you can create relevant Dialect subclass instances without an engine as well:

In [20]: from sqlalchemy.dialects import mssql

In [21]: d = mssql.dialect()

In [22]: types.Unicode(32).compile(dialect=d)
Out[22]: 'NVARCHAR(32)'

edited Jun 26, 2018 at 11:22

answered Jun 26, 2018 at 11:09

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

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

1 Comment

badgerm Over a year ago

Perfect! This is both exactly what I was after and far simpler than I'd hoped. :)

Collectives™ on Stack Overflow

Get SQL data type from sqlalchemy model

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related