SQLAlchemy Automap not generating Base.classes with existing MySQL tables

Question

I am using an existing mysql database which has pre existing tables. Everything works as expected when I manually create classes/ models using the declarative approach. Now, I want to use automap to avoid creating models manually. As the database is from work, I cannot edit it. Second, the primary keys are present already(Most posts I have read mention primary keys not being present on the table as the reason).
I have been looking around for a couple of days but have not found a solution. Example below :

from sqlalchemy.ext.automap import automap_base
from sqlalchemy import create_engine

Base = automap_base()
engine = create_engine('working-as-expected')
Base.prepare(autoload_with=engine)

print('Hello2 - base classes items')
print(Base.classes.items()) # output is []
print('Hello3 - metadata keys')
print(Base.metadata.tables.keys()) # output is []

example of an existing mysql table :

CREATE TABLE 'tbl_Xyz' (
'ID' int(11) NOT NULL,
'Name' varchar(45) NOT NULL,
PRIMARY KEY ('ID'))

What should I do differently ?

Answer 1

This is working for me, do you think you might not be referencing the correct database name? I used the mysql:8 docker container to run mysql.

In addition to all the logging from echo=True on the engine this script prints this: [('tbl_Xyz',)]

from sqlalchemy import (
    Integer,
    String,
    ForeignKey,
    UniqueConstraint,
)
from sqlalchemy.schema import (
    Column,
)
from sqlalchemy.orm import backref, relationship, declarative_base, Session
from sqlalchemy import create_engine, MetaData, Column, ForeignKey, Integer, String, text
from sqlalchemy.ext.automap import automap_base
engine = create_engine(f'mysql+pymysql://{DB_USER}:{DB_PASS}@{DB_IP}:{DB_PORT}/{DB_NAME}', echo=True)



with engine.connect() as conn, conn.begin():
    conn.execute(text('''
CREATE TABLE IF NOT EXISTS tbl_Xyz (
ID int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name varchar(45) NOT NULL)'''))
    print ([row for row in conn.execute(text('''
SHOW TABLES'''))])

Base = automap_base()

Base.prepare(autoload_with=engine)

assert len(Base.classes.items()) > 0
assert Base.classes.tbl_Xyz
assert Base.classes.tbl_Xyz.__table__ is not None
assert Base.classes.tbl_Xyz.__table__.c.ID is not None