Inspecting Postgres column types with SqlAlchemy

SQL Alchemy makes it easy to get types out of the database:

from sqlalchemy import *
engine = create_engine(
                "postgresql+pg8000://postgres:postgres@localhost/postgres",
                isolation_level="READ UNCOMMITTED"
            )
c = engine.connect()
 
meta = MetaData()
 
t = Table('table', meta, autoload=True, autoload_with=engine, schema='test')

columns = [col for col in t.columns]

And then from there, you can filter the column list down to things you want.

import sqlalchemy.sql.sqltypes

def useColumn(c):
  if (type(c.type) is TIMESTAMP):
    return False

  if (type(c.type) is VARCHAR):
    if (c.type.length == 24):
      return False

  if (type(c.type) is DATE):
    return False

  if (c.name.startswith("internal_")):
    return False

  if (c.name == "dont_use_me"): 
    return False

  return True

For certain columns, SQL Alchemy will give you an object that is a database specific type (e.g. there is a Postgres namespace), but you can find the exact class name:

columns[0].type.__class__

...sqlalchemy.sql.sqltypes.INTEGER