Schema replacement is not working in SQLalchemy

I have the following Python code which uses SQLalchemy

    stmt = select(Lclzone.sequence).where(Lclzone.refno == cReference) 
    _sequence = self.session.execute(stmt).scalar()

It generates the error;

Unable to process fmb73 error: (psycopg2.errors.UndefinedTable) relation "public.lclzone" does not exist
LINE 2: FROM public.lclzone
         ^

[SQL: SELECT public.lclzone.sequence
FROM public.lclzone
WHERE public.lclzone.refno = %(refno_1)s]

This is application has multiple schemas. I need it to execute against the "route_optix" schema

from sqlalchemy.orm import Mapped, mapped_column
from models_client import Client_Base
class Lclzone(Client_Base):
    __tablename__ = "lclzone"
    __table_args__ = {'schema': 'per_client'}
    refno: Mapped[str] = mapped_column(default=None, primary_key=True)
    sequence: Mapped[int] = mapped_column(default=None)

At the time of running if I run

print(self.session.bind.get_execution_options())

the result is

immutabledict({'schema_translate_map': {None: 'public', 'per_client': 'route_optix'}})

The .execute(..) does not seem to be honoring the schema_translate_map.

When I use

print(stmt.compile(self.session.bind))

the result is

SELECT per_client.lclzone.sequence 
FROM per_client.lclzone 
WHERE per_client.lclzone.refno = %(refno_1)s

If I look at self.session.bind I see that the _execute_options are set to the translations map, but the _schema_translate_map is set to None

Why isn’t the translation map being used?

You need to sign in to view this answers

Related Post