SQLAlchemy Automap not loading table

I am using SQLAlchemy version 2.0.19 (latest public release).

I am trying to map existing tables as documented in https://docs.sqlalchemy.org/en/20/orm/extensions/automap.html#basic-use

I created a SQLite table called user:

sqlite3 /tmp/mydatabase.db
SQLite version 3.39.5 2022-10-14 20:58:05
Enter ".help" for usage hints.
sqlite> 
sqlite> .schema user
CREATE TABLE user (id int, a int);
sqlite>

I am trying to automap this table using SQLAlchemy but the table is not available in Base.classes as documented:

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

Base = automap_base()

# engine
engine = create_engine("sqlite:////tmp/mydatabase.db")

# reflect the tables
Base.prepare(autoload_with=engine)

print(Base.classes.keys())

# mapped classes are now created with names by default
# matching that of the table name.
User = Base.classes.user

print(Base.classes.keys()) returns [].

User = Base.classes.user shows the table not mapped:

Traceback (most recent call last):
  File "/***/venv/lib/python3.10/site-packages/sqlalchemy/util/_collections.py", line 186, in __getattr__
    return self._data[key]
KeyError: 'user'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/***/test.py", line 17, in <module>
    User = Base.classes.user
  File "/***/venv/lib/python3.10/site-packages/sqlalchemy/util/_collections.py", line 188, in __getattr__
    raise AttributeError(key)
AttributeError: user

I am a little confused because the example is pretty straightforward and nonetheless it does not appear to work.

Solution

For SQLAlchemy's reflection to detect the table, the table needs to have a primary key.

CREATE TABLE user (
  id INT PRIMARY KEY, -- modify this!
  a INT
);

Running reflection with Automap:

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

engine = create_engine(DATABASE_URL) # note: 4 slashes for an absolute path "////"
Base = automap_base()
Base.prepare(autoload_with=engine)
print("keys", Base.classes.keys())

Will give us:

keys ['user']