Search code examples
pythonpostgresqlsqlalchemy

Getting SQLAlchemy to issue CREATE SCHEMA on create_all

I have a SqlAlchemy model with a schema argument like so:

Base = declarative_base()

class Road(Base):
  __tablename__ = "roads"
  __table_args__ = {'schema': 'my_schema'}
  id = Column(Integer, primary_key=True)

When I use Base.metadata.create_all(engine) it correctly issues a CREATE TABLE with the schema name on the front like so CREATE TABLE my_schema.roads ( but Postgresql rightly complains that the schema doesn't exist.

Am I missing a step to get SqlAlchemy to issue the CREATE SCHEMA my_schema or do I have to call this manually?

Solution

  • I ran into the same issue and believe the "cleanest" way of issuing the DDL is something like this:

    from sqlalchemy import event
from sqlalchemy.schema import CreateSchema

event.listen(Base.metadata, 'before_create', CreateSchema('my_schema'))

    This will ensure that before anything contained in the metadata of your base is created, you have the schema for it. This does, however, not check if the schema already exists.

    You can do CreateSchema('my_schema').execute_if(callback_=check_schema) if you can be bothered to write the check_schema callback ("Controlling DDL Sequences" on should_create in docs). Or, as an easy way out, just use DDL("CREATE SCHEMA IF NOT EXISTS my_schema") instead (for Postgres):

    from sqlalchemy import DDL

event.listen(Base.metadata, 'before_create', DDL("CREATE SCHEMA IF NOT EXISTS my_schema"))