SQLAlchemy column type comparison

Question

I'm doing a lot of database introspections across different database types and I would like to be able to compare two column types. For example, a field defined as Boolean type using the declarative_base() is then converted to a specific TINYINT for the MySQL dialact, so a check like this:

model_a.__table__.columns['col'].type == model_b.__table__.columns['col'].type

doesn't work, and neither this one:

(type_a == type_b) or issubclass(type_b, type_a)

How can I compare two columns for data type "affinity"? (By inspecting the code I saw that column types have a Comparator class attached but I'm not sure if it can be of any help and how to use it) Is also possible to force a column type in the SQLAlchemy configuration (by avoiding dialect-specific convertion)?

Answer 1

(Answering an old question because I've been looking into exactly this).

The quickest solution is to compare the str() of the types:

assert str(type_a) == (type_b)

That'll work for simple types, but if you have any Variant types or wrappers (for example, MutableDict wrapping Postgres JSON) then you'll have to handle a special case.

Answer 2

For example to check if a type is a BLOB. Use this code:

import sqlalchemy as db
some code..
if type(column.type) is db.types.BLOB:
    something..