[Python] SQLAlchemy add a table to an automapped Base results in `object has no attribute...

I'm mirroring a remote MS SQL into a local SQLite db.

Here is the relevant code:

eng_str = rf"mssql+pymssql://{user_domain}\{username}:{password}@{hostip}/{dbname}"
engine_remote = create_engine(eng_str, echo=False)

dbfp = Path("../../data/mydb.sqlite3")
engine_local = create_engine(f"sqlite:///{dbfp}", echo=False)

Base = automap_base()

# See ORM documentation on intercepting column definitions: https://docs.sqlalchemy.org/en/20/orm/extensions/automap.html#intercepting-column-definitions
@event.listens_for(Base.metadata, "column_reflect")
def genericize_datatypes(inspector, tablename, column_dict):
# Convert dialect specific column types to SQLAlchemy agnostic types
# See: https://stackoverflow.com/questions/79496414/convert-tinyint-to-int-when-mirroring-microsoft-sql-server-to-local-sqlite-with
# See Core documentation on reflecting with database-agnostic types: https://docs.sqlalchemy.org/en/20/core/reflection.html#reflecting-with-database-agnostic-types
old_type = column_dict['type']
column_dict["type"] = column_dict["type"].as_generic()
# We have to remove collation when mirroring a Microsoft SQL server into SQLite
# See: https://stackoverflow.com/a/59328211/1719931
if getattr(column_dict["type"], "collation", None) is not None:
column_dict["type"].collation = None

# Load Base with remote DB metadata
Base.prepare(autoload_with=engine_remote)


I need however to add a table to my db, so I did:

class MissingApl(Base):
__tablename__ = "missing_apl"
id: Mapped[int] = Column(Integer, primary_key=True)
appln_nr: Mapped[int] = Column(Integer)


and then I'm creating the metadata on the local db:

Base.metadata.create_all(engine_local)


However doing for instance:

testobj=MissingApl(appln_nr=9999)
print(testobj)
with Session(engine_local) as session:
session.add(testobj)
session.commit()


results in:

---------------------------------------------------------------------------
AttributeError Traceback (most recent call last)
File \Python\Python313\Lib\site-packages\sqlalchemy\orm\session.py:3477, in Session.add(self, instance, _warn)
3476 try:
-> 3477 state = attributes.instance_state(instance)
3478 except exc.NO_STATE as err:

AttributeError: 'MissingApl' object has no attribute '_sa_instance_state'

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

UnmappedInstanceError Traceback (most recent call last)
Cell In[26], line 4
2 print(testobj)
3 with Session(engine_local) as session:
----> 4 session.add(testobj)
5 session.commit()

File \Python\Python313\Lib\site-packages\sqlalchemy\orm\session.py:3479, in Session.add(self, instance, _warn)
3477 state = attributes.instance_state(instance)
3478 except exc.NO_STATE as err:
-> 3479 raise exc.UnmappedInstanceError(instance) from err
3481 self._save_or_update_state(state)

UnmappedInstanceError: Class '__main__.MissingApl' is not mapped
```

Why it does give me this erro and how can I fix it?

Continue reading...