Attribute Error for compare_metadata with multiple schemas

Question

I'm trying to use the compare_metadata function with the following setup:

base.py

from sqlalchemy.orm import DeclarativeBase


class Base(DeclarativeBase):
    pass

workspace.py

from sqlalchemy import Column, Integer, String
from base import Base


class Workspace(Base):

    __table_args__ = {"schema": "workspace"}
    __tablename__ = 'workspace_table'

    id = Column(Integer, primary_key=True)
    name = Column(String(10))

host.py

from sqlalchemy import Column, Integer, String
from base import Base


class Host(Base):

    __table_args__ = {"schema": "host"}
    __tablename__ = 'host_table'

    id = Column(Integer, primary_key=True)
    ip = Column(String(10))

main.py

import sqlalchemy as sa
from alembic.autogenerate import compare_metadata
from alembic.migration import MigrationContext
from sqlalchemy.schema import CreateSchema

import host
import workspace
from base import Base


def main():
    conn_str = "mysql+mysqlconnector://root:<my_password>@localhost"
    
    engine = sa.create_engine(conn_str)
    connection = engine.connect()
    
    connection.execute(CreateSchema("workspace", if_not_exists=True))
    connection.execute(CreateSchema("host", if_not_exists=True))
    
    Base.metadata.drop_all(bind=engine)
    Base.metadata.create_all(bind=engine)
                             
    mc = MigrationContext.configure(engine.connect())
    
    diff_list = compare_metadata(mc, Base.metadata)
    
    print(diff_list)


if __name__ == "__main__":
    main()

However, running main.py results in the following error:

AttributeError: 'NoneType' object has no attribute 'replace'

Tracking this back it seems that SQLAlchemy can't find a schema.

Reading around I have tried:

mc = MigrationContext.configure(engine.connect(), opts={"include_schemas": True})

But same error in the same place.

Any ideas on where I'm going wrong?

Details:

• macOS Sonoma
• python 3.11
• SQLAlchemy 2.0.7
• alembic 1.13.1
• mysql-connector-python 8.2.0

Answer 1

https://github.com/sqlalchemy/sqlalchemy/issues/1909 this is an open issue, if you are in a position to pass a database name, then add it like so "mysql+mysqlconnector://root:<my_password>@localhost/database_name_you_want"

You can read the issue for more details, but long story short, logging into MySQL without a default database will cause this. Seems there is no "default" (other than internal dbs) database created in a fresh install of MySQL, so you will have to create one if you need one.