Checking table compatibility in sqlalchemy

Question

I have several declared tables representing remote databases.

I want to check if my table definitions match the remote databases to which I connect.

I have the following function:

def verify_db_tables(conn, metadata):
    """checks that the tables declared in metadata are actually in the db"""
    for table in metadata.tables.values():
        check = sqlalchemy.MetaData()
        check.reflect(conn, table.schema, True, (table.name,))
        check = check.tables[table.key]
        for column in table.c:
            if column.name not in check.c:
                raise Exception("table %s does not contain column %s" %
                        (table.key, column.name))
            check_column = check.c[column.name]
            if check_column.type != column.type:
                raise Exception("column %s.%s is %s but expected %s" %
                        (table.key, column.name, check_column.type, column.type))

I don't care if there are additional columns in the tables, and it doesn't matter if there are additional tables.

However, when I run this code, I get the following error:

Exception: column dx.mail_data.message_id is INTEGER but expected INTEGER

How do I verify that a column in a mirrored table is of the same type as my definition?

Answer 1

SQLite uses the heiarchy class, so this will work beautifully

    if not instance(check_column.type, column.type.__class__):