Issue 1:
Hello.

Thank you for SQLAlchemy! It makes working with DB in Python a dream!

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 before_create 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.

Searches online and through the issues have turned up nothing for me.

This was tested on SQLAlchemy 1.3.13, psycopg2 2.8.4, and postgresql 11.2.

Thank you for your help.

Below is a full example and trace.

Anders.

--

"""
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

I get the following error:

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 <module>
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')]

Issue 2:
Migrated issue, originally created by Michael Bayer (@zzzeek)

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()