python - SQLAlchemy add a table to an automapped Base results in `object has no attribute '_sa_instance_state'`

admin管理员组

文章数量:1403468

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: .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: 
    # See Core documentation on reflecting with database-agnostic types: .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: 
    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)
    sessionmit()

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

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 error and how can I fix it?

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./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/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./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/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)
    sessionmit()

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

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 error and how can I fix it?

• python
• sqlalchemy

Share Improve this question Follow edited Mar 20 at 23:40 robertspierre asked Mar 20 at 19:15 robertspierrerobertspierre 4,48233 gold badges4141 silver badges6363 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Inspecting the model

from sqlalchemy import inspect
...
inspect(MissingApi)

generates this error

sqlalchemy.orm.exc.UnmappedClassError: Class __main__.MissingApi is a subclass of AutomapBase. Mappings are not produced until the .prepare() method is called on the class hierarchy.

So Base.prepare() must be called after the new model class has been defined. Calling .prepare() twice, before and after the model definition seems to work but I don't know if it would have any side-effects.

本文标签：