Friday 1 January 2021

How to get generic data type from dialect specific type in sqlalchemy?

I want to create a function that maps dialect-specific DB types to generic sqlalchemy DB types.

For example: I'm trying to get data types from the OracleDB table and then create a table in MySql DB(or another) using received metadata. I'm getting dialect-specific column types.

Here is a minimal code to create OracleDB table:

create table test_test(id int, num_int number(10), num_float number(10,4), dt date, ts timestamp, str varchar2(100), fl float);

insert into test_test(id) values(1);

To receive DB table column types

from pprint import pprint
from sqlalchemy import sessionmaker, create_engine

engine = create_engine(...)
session = sessionmaker()
session.configure(bind=engine)
session = session()
res = session.execute('select * from test_test where 1 = 2')
columns = res.cursor.description
pprint(columns)

Output

[('ID', <cx_Oracle.DbType DB_TYPE_NUMBER>, 39, None, 38, 0, 1),
 ('NUM_INT', <cx_Oracle.DbType DB_TYPE_NUMBER>, 11, None, 10, 0, 1),
 ('NUM_FLOAT', <cx_Oracle.DbType DB_TYPE_NUMBER>, 16, None, 10, 4, 1),
 ('DT', <cx_Oracle.DbType DB_TYPE_DATE>, 23, None, None, None, 1),
 ('TS', <cx_Oracle.DbType DB_TYPE_TIMESTAMP>, 23, None, 0, 6, 1),
 ('STR', <cx_Oracle.DbType DB_TYPE_VARCHAR>, 100, 100, None, None, 1),
 ('FL', <cx_Oracle.DbType DB_TYPE_NUMBER>, 127, None, 126, -127, 1)]

How can I get generic sqlalchemy column types (String, Integer, etc) instead of dialect-specific ones?



from How to get generic data type from dialect specific type in sqlalchemy?

No comments:

Post a Comment