<p dir="auto">Hello.</p>
<p dir="auto">Thank you for SQLAlchemy! It makes working with DB in Python a dream!</p>
<p dir="auto">I am wondering if I have run into a possible bug. I am getting a funny error when I have ENUMs in my tables in that the <code class="notranslate">before_create</code> statement seems to be ignored when the first CREATE statement is not CREATE TABLE, and thus failing to create the appropriate schema. Removing the ENUMs results in the expected behaviour working.</p>
<p dir="auto">Searches online and through the issues have turned up nothing for me.</p>
<p dir="auto">This was tested on SQLAlchemy 1.3.13, psycopg2 2.8.4, and postgresql 11.2.</p>
<p dir="auto">Thank you for your help.</p>
<p dir="auto">Below is a full example and trace.</p>
<p dir="auto">Anders.</p>
<p dir="auto">--</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="&quot;&quot;&quot;
Describe test case that fails in SQLAlchemy using PostgreSQL
&quot;&quot;&quot;

import enum
from sqlalchemy import Table, Column, Integer, String, Enum, create_engine, event, DDL
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from os import environ


Base = declarative_base()

class ReadTypesEnum(enum.Enum):
    read1 = 1
    read2 = 2

class Reads(Base):
    &quot;&quot;&quot;
    A table for reads. Each row corresponds to one FASTQ file.
    &quot;&quot;&quot;
    __tablename__ = &quot;reads&quot;
    __table_args__ = {'schema':'test'}

    id = Column(Integer, primary_key=True, unique=True, nullable=False)
    filename = Column(String(100), nullable=False)
    read_type = Column(Enum(ReadTypesEnum, inherit_schema=True))

if __name__ == &quot;__main__&quot;:

    # Create engine
    db_uri = environ.get('SQLALCHEMY_DATABASE_URI')
    engine = create_engine(db_uri, echo=True)

    # create schema
    event.listen(Base.metadata, 'before_create', DDL(&quot;CREATE SCHEMA IF NOT EXISTS test&quot;))

    # Create All Tables
    Base.metadata.create_all(engine"><pre class="notranslate"><code class="notranslate">"""
Describe test case that fails in SQLAlchemy using PostgreSQL
"""

import enum
from sqlalchemy import Table, Column, Integer, String, Enum, create_engine, event, DDL
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from os import environ


Base = declarative_base()

class ReadTypesEnum(enum.Enum):
    read1 = 1
    read2 = 2

class Reads(Base):
    """
    A table for reads. Each row corresponds to one FASTQ file.
    """
    __tablename__ = "reads"
    __table_args__ = {'schema':'test'}

    id = Column(Integer, primary_key=True, unique=True, nullable=False)
    filename = Column(String(100), nullable=False)
    read_type = Column(Enum(ReadTypesEnum, inherit_schema=True))

if __name__ == "__main__":

    # Create engine
    db_uri = environ.get('SQLALCHEMY_DATABASE_URI')
    engine = create_engine(db_uri, echo=True)

    # create schema
    event.listen(Base.metadata, 'before_create', DDL("CREATE SCHEMA IF NOT EXISTS test"))

    # Create All Tables
    Base.metadata.create_all(engine
</code></pre></div>
<p dir="auto">I get the following error:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="2020-02-12 19:03:29,230 INFO sqlalchemy.engine.base.Engine select version()
2020-02-12 19:03:29,231 INFO sqlalchemy.engine.base.Engine {}
2020-02-12 19:03:29,245 INFO sqlalchemy.engine.base.Engine select current_schema()
2020-02-12 19:03:29,245 INFO sqlalchemy.engine.base.Engine {}
2020-02-12 19:03:29,250 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2020-02-12 19:03:29,251 INFO sqlalchemy.engine.base.Engine {}
2020-02-12 19:03:29,256 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2020-02-12 19:03:29,257 INFO sqlalchemy.engine.base.Engine {}
2020-02-12 19:03:29,260 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2020-02-12 19:03:29,262 INFO sqlalchemy.engine.base.Engine {}
2020-02-12 19:03:29,268 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=%(schema)s and relname=%(name)s
2020-02-12 19:03:29,269 INFO sqlalchemy.engine.base.Engine {'schema': 'test', 'name': 'reads'}
2020-02-12 19:03:29,274 INFO sqlalchemy.engine.base.Engine 
            SELECT EXISTS (
                SELECT * FROM pg_catalog.pg_type t, pg_catalog.pg_namespace n
                WHERE t.typnamespace = n.oid
                AND t.typname = %(typname)s
                AND n.nspname = %(nspname)s
                )
                
2020-02-12 19:03:29,279 INFO sqlalchemy.engine.base.Engine {'typname': 'readtypesenum', 'nspname': 'test'}
2020-02-12 19:03:29,283 INFO sqlalchemy.engine.base.Engine CREATE TYPE test.readtypesenum AS ENUM ('read1', 'read2')
2020-02-12 19:03:29,285 INFO sqlalchemy.engine.base.Engine {}
2020-02-12 19:03:29,288 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py&quot;, line 1246, in _execute_context
    cursor, statement, parameters, context
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/default.py&quot;, line 588, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InvalidSchemaName: schema &quot;test&quot; does not exist


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File &quot;test.py&quot;, line 39, in &lt;module&gt;
    Base.metadata.create_all(engine)
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/sql/schema.py&quot;, line 4316, in create_all
    ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py&quot;, line 2049, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py&quot;, line 1618, in _run_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/sql/visitors.py&quot;, line 138, in traverse_single
    return meth(obj, **kw)
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/sql/ddl.py&quot;, line 769, in visit_metadata
    _ddl_runner=self,
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/event/attr.py&quot;, line 322, in __call__
    fn(*args, **kw)
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py&quot;, line 617, in __call__
    return getattr(self.target, self.name)(*arg, **kw)
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/sql/sqltypes.py&quot;, line 1165, in _on_metadata_create
    t._on_metadata_create(target, bind, **kw)
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/base.py&quot;, line 1505, in _on_metadata_create
    self.create(bind=bind, checkfirst=checkfirst)
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/base.py&quot;, line 1436, in create
    bind.execute(CreateEnumType(self))
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py&quot;, line 982, in execute
    return meth(self, multiparams, params)
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/sql/ddl.py&quot;, line 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py&quot;, line 1044, in _execute_ddl
    compiled,
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py&quot;, line 1250, in _execute_context
    e, statement, parameters, cursor, context
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py&quot;, line 1476, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/util/compat.py&quot;, line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/util/compat.py&quot;, line 152, in reraise
    raise value.with_traceback(tb)
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py&quot;, line 1246, in _execute_context
    cursor, statement, parameters, context
  File &quot;/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/default.py&quot;, line 588, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidSchemaName) schema &quot;test&quot; does not exist

[SQL: CREATE TYPE test.readtypesenum AS ENUM ('read1', 'read2')]"><pre class="notranslate"><code class="notranslate">2020-02-12 19:03:29,230 INFO sqlalchemy.engine.base.Engine select version()
2020-02-12 19:03:29,231 INFO sqlalchemy.engine.base.Engine {}
2020-02-12 19:03:29,245 INFO sqlalchemy.engine.base.Engine select current_schema()
2020-02-12 19:03:29,245 INFO sqlalchemy.engine.base.Engine {}
2020-02-12 19:03:29,250 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2020-02-12 19:03:29,251 INFO sqlalchemy.engine.base.Engine {}
2020-02-12 19:03:29,256 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2020-02-12 19:03:29,257 INFO sqlalchemy.engine.base.Engine {}
2020-02-12 19:03:29,260 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2020-02-12 19:03:29,262 INFO sqlalchemy.engine.base.Engine {}
2020-02-12 19:03:29,268 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=%(schema)s and relname=%(name)s
2020-02-12 19:03:29,269 INFO sqlalchemy.engine.base.Engine {'schema': 'test', 'name': 'reads'}
2020-02-12 19:03:29,274 INFO sqlalchemy.engine.base.Engine 
            SELECT EXISTS (
                SELECT * FROM pg_catalog.pg_type t, pg_catalog.pg_namespace n
                WHERE t.typnamespace = n.oid
                AND t.typname = %(typname)s
                AND n.nspname = %(nspname)s
                )
                
2020-02-12 19:03:29,279 INFO sqlalchemy.engine.base.Engine {'typname': 'readtypesenum', 'nspname': 'test'}
2020-02-12 19:03:29,283 INFO sqlalchemy.engine.base.Engine CREATE TYPE test.readtypesenum AS ENUM ('read1', 'read2')
2020-02-12 19:03:29,285 INFO sqlalchemy.engine.base.Engine {}
2020-02-12 19:03:29,288 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1246, in _execute_context
    cursor, statement, parameters, context
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 588, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InvalidSchemaName: schema "test" does not exist


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "test.py", line 39, in &lt;module&gt;
    Base.metadata.create_all(engine)
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/sql/schema.py", line 4316, in create_all
    ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2049, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1618, in _run_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/sql/visitors.py", line 138, in traverse_single
    return meth(obj, **kw)
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/sql/ddl.py", line 769, in visit_metadata
    _ddl_runner=self,
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/event/attr.py", line 322, in __call__
    fn(*args, **kw)
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 617, in __call__
    return getattr(self.target, self.name)(*arg, **kw)
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/sql/sqltypes.py", line 1165, in _on_metadata_create
    t._on_metadata_create(target, bind, **kw)
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/base.py", line 1505, in _on_metadata_create
    self.create(bind=bind, checkfirst=checkfirst)
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/base.py", line 1436, in create
    bind.execute(CreateEnumType(self))
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 982, in execute
    return meth(self, multiparams, params)
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1044, in _execute_ddl
    compiled,
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1250, in _execute_context
    e, statement, parameters, cursor, context
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1246, in _execute_context
    cursor, statement, parameters, context
  File "/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 588, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidSchemaName) schema "test" does not exist

[SQL: CREATE TYPE test.readtypesenum AS ENUM ('read1', 'read2')]
</code></pre></div>
<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from sqlalchemy import create_engine, event

eng = create_engine(&quot;mysql://scott:tiger@localhost/test&quot;, echo='debug')

@event.listens_for(eng, &quot;first_connect&quot;, insert=True)  # make sure we're the very first thing
@event.listens_for(eng, &quot;connect&quot;)
def connect(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute(&quot;SET sql_mode = 'STRICT_ALL_TABLES'&quot;)

conn = eng.connect()"><pre class="notranslate"><code class="notranslate">from sqlalchemy import create_engine, event

eng = create_engine("mysql://scott:tiger@localhost/test", echo='debug')

@event.listens_for(eng, "first_connect", insert=True)  # make sure we're the very first thing
@event.listens_for(eng, "connect")
def connect(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("SET sql_mode = 'STRICT_ALL_TABLES'")

conn = eng.connect()
</code></pre></div>