Question Cyril Grosjean · Jan 2, 2024

Access to a RecordMap with SQLAlchemy

Hi,

I'm trying to access to my datas stored in a RecordMap from SQLAlchemy, and I need to access to any tables already created before using SQLAlchemy.

Here is some part of my code :

TestBase:

class TestBase(DeclarativeBase):
    CreatedAt: Mapped[int] = mapped_column(TIMESTAMP, default=func.now())
    UpdatedAt: Mapped[int] = mapped_column(TIMESTAMP, default=func.now(), onupdate=func.current_timestamp())

 

Engine creation and entities binding :

bases = {
    "TEST": TestBase.metadata.create_all,
}

def create_engine_and_session(namespace: str) -> Session:
    engine: Engine = create_engine(f"iris://_SYSTEM:SYS@localhost:1972/{namespace}") # create_engine(f'iris+emb:///{namespace}')
    metadata = MetaData()
    metadata.reflect(engine)
    bases[namespace](engine)
    OrmSession = sessionmaker(bind=engine)
    sessions[namespace] = OrmSession()
    return sessions[namespace]

 

My RecordMap entity :

class BastideRecord(TestBase):
    __tablename__ = "User_BastideRecord.Record"
    __table_args__ = {"extend_existing": True}
    __versioned__ = {}

    ID1: Mapped[int] = mapped_column(Integer, primary_key=True)
    Statut: Mapped[str] = mapped_column(String(999))
    Id: Mapped[str] = mapped_column(String(50))

Each part of my code are in different files, the "User_BastideRecord.Record" RecordMap is already created but when I create my SQLAlchemy engine, it will try to create the RecordMap table. Here is the error log I have :

: ERROR #5002: ObjectScript error: OnInit+4 ^Grongier.PEX.BusinessProcess.1 ^8^ do ..%class."_dispatch_on_init"($this) *<class 'sqlalchemy.exc.DatabaseError'>: (intersystems_iris.dbapi._DBAPI.DatabaseError) [SQLCODE: <-1>:<Invalid SQL statement>]
[Location: <Prepare>]
[%msg: < Delimited identifier expected, delimited identifier containing invalid character '.'
found ^ CREATE TABLE "User_BastideRecord.Record">]

I'm using SQLAlchemy 2.0 and I need to have entities for other tables already created because I will need to make some updates.

 

Thanks !

 

Best regards,

Cyril

 

UPDATE : I just noticed that when I display the metada that I reflect in my engine (when I'm creating the SQLAlchemy engine), I see each tables except the RecordMap table. Is there any solution to get datas from RecordMap ?

Product version: IRIS 2023.1
$ZV: IRIS for UNIX (Ubuntu Server LTS for x86-64 Containers) 2023.1.1 (Build 380U) Fri Jul 7 2023 23:53:46 EDT [Health:5.1.0-1.m1]

Comments

DC AI Bot · May 30 Bot

The problem is that your mapped class definition is being included in TestBase.metadata.create_all, so SQLAlchemy tries to issue CREATE TABLE "User_BastideRecord.Record". That fails because the projected name contains a dot, as shown by your error: Delimited identifier expected, delimited identifier containing invalid character '.' found ^ CREATE TABLE "User_BastideRecord.Record" [1]

Your reflected MetaData() is separate from the declarative metadata used by TestBase. In your code, metadata.reflect(https://community.intersystems.com/post/engine) only reflects into the local metadata object, but bases[namespace](https://community.intersystems.com/post/engine) calls TestBase.metadata.create_all, which still uses the ORM class definitions and attempts to create any mapped tables it knows about, including BastideRecord [1]

A practical fix is to avoid calling create_all() for classes that correspond to already existing objects such as your RecordMap projection, and use the existing table/class only for querying or updating instead of trying to create it [1]

You also noted that reflection shows all tables except the RecordMap table. Based on the retrieved material, there is no documented solution here showing how to make SQLAlchemy reflection discover a RecordMap table automatically, nor a confirmed method in these sources for accessing RecordMap data through SQLAlchemy reflection [1]

So from the available information, the supported conclusion is:

  • do not let TestBase.metadata.create_all include the RecordMap-mapped class if that RecordMap already exists
  • keep reflected/existing objects separate from ORM classes meant to be created
  • the source provided does not confirm a way for SQLAlchemy reflection to expose the RecordMap table automatically [1]

If you need, you can restructure so only your normal application tables are attached to the metadata passed to create_all, while the RecordMap entity is declared for use against an already existing projection and excluded from table creation [1]


Sources:

Tell us more:
Don't like the style
Not factually correct
Didn't fully follow instructions
Refused when it shouldn't have
Being lazy
More...
Submit
0 0