SQLAlchemy: how to conditionally select a type for a column, depending on its backend

Question

I want to use the HSTORE type for a column if it uses PostgreSQL as its backend or PickleType otherwise. The problem is that we cannot determine which backend will be used when defining the schema (in Python). How can I determine this and conditionally select the data type when the table is actually created in the backend database?

Answer 1

You can do something similar with TypeEngine.with_variant :

 from sqlalchemy.types import PickleType from sqlalchemy.dialects import postgresql HybridType = PickleType() HybridType = HybridType.with_variant(postgresql.HSTORE(), 'postgresql') 

This creates a new type of HybridType that you can use like any other type, with the caveat that it will generate an HSTORE column in Postgres and PickleType everywhere.