Adam Morris Adam Morris - 1 month ago 7
Python Question

Easy convert betwen SQLAlchemy columns and data types?

I'm looking for an easy python way to compare column types from SQLAlchemy to base types. For example, if my column type is a VARCHAR of any length, I want to read it as a string.

I can read the column type okay, but I'm not sure an easy way to verify it's basic type... it would be nice if I could use something like "if isinstance(mycolumn, int)" - but I'm new to python and not sure how this would work.

Here's what I have so far:

from sqlalchemy import MetaData
from sqlalchemy import create_engine, Column, Table
engine = create_engine('mysql+mysqldb://user:pass@localhost:3306/mydb', pool_recycle=3600)
meta = MetaData()
meta.bind = engine
meta.reflect()
datatable = meta.tables['my_data_table']
[c.type for c in datatable.columns]


Output:

[INTEGER(display_width=11), DATE(), VARCHAR(length=127), DOUBLE(precision=None, scale=None, asdecimal=True)]


My end purpose is twofold, first because I want to format the output based on the type when I load it into my jQuery jqGrid. The second, is I'm slowly converting non-normalized data tables into a normalized structure, and want to ensure that I keep my types consistent - (to make sure my numbers in the previous table are stored as numbers and not strings...)

Answer

One solution is to do the conversion manually - for example, this works:

def convert(self, saType):
    type = "Unknown"
    if isinstance(saType,sqlalchemy.types.INTEGER):
        type = "Integer"
    elif isinstance(saType,sqlalchemy.types.VARCHAR):
        type = "String"
    elif isinstance(saType,sqlalchemy.types.DATE):
        type = "Date"
    elif isinstance(saType,sqlalchemy.dialects.mysql.base._FloatType):
        type = "Double"
    return type

Not sure if this is a normal python way of doing things... I still think like a java programmer.