schema_translate_map is ignored on refresh in MSSQL

[pellejacobs]

Describe the bug

Schema mappings defined in schema_translate_map are ignored on instance refresh using the MSSQL dialect. This persist when SQLAlchemy does an implicit refresh when an instance is referenced after create.

This behaviour only persists when session.commit() is used to add new rows. When autocommit is turned on instead, everything works as expected.

Expected behavior

For the effective schema to be mapped in a refresh, similar to how other actions are mapped.

To Reproduce

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, sessionmaker

connection_string = "mssql+pyodbc://<username>:<password>@<dsnname>"
engine = create_engine(connection_string, echo="debug")
Base = declarative_base()


class Order(Base):
    __tablename__ = "Order"

    id = Column(Integer, primary_key=True, autoincrement=True)
    Status = Column(String)


EngineSession = sessionmaker(bind=engine)
session: Session = EngineSession()
session.connection(execution_options={"schema_translate_map": {None: "1"}})


order = Order(Status="CREATED")
session.add(order)
session.commit()
""" Executes:
INSERT INTO [1].[Order] ([Status]) OUTPUT inserted.id VALUES (?)
"""
session.refresh(order)
""" Executes:
SELECT [Order].id AS [Order_id], [Order].[Status] AS [Order_Status]
FROM [Order]
WHERE [Order].id = ?

While expected:
SELECT [Order].id AS [Order_id], [Order].[Status] AS [Order_Status]
FROM [1].[Order]
WHERE [Order].id = ?
"""

Versions

• OS: macOS Catalina 10.15.7
• Python: 3.7.9
• SQLAlchemy: 1.3.23
• Database: MSSQL
• DBAPI: pyodbc

[zzzeek]

hey there -

the connection is returned to the connection pool when you call commit(). to use the method that you're using, you would need to call session.connection() after each commit:

session: Session = EngineSession()
session.connection(execution_options={"schema_translate_map": {None: "test_schema"}})


order = Order(Status="CREATED")
session.add(order)
session.commit()
session.connection(execution_options={"schema_translate_map": {None: "test_schema"}})
session.refresh(order)

instead, create an engine with the options you need:

EngineSession = sessionmaker(bind=engine.execution_options(schema_translate_map={None: "test_schema"}))
session: Session = EngineSession()

order = Order(Status="CREATED")
session.add(order)
session.commit()


session.refresh(order)

let me know if there's a document you saw that was misleading in this regard.

[pellejacobs]

Thanks Mike, that makes sense.

I was using the second code example from the docs https://docs.sqlalchemy.org/en/13/core/connections.html#translation-of-schema-names

connection = engine.connect().execution_options(
    schema_translate_map={None: "user_schema_one"})

result = connection.execute(user_table.select())

while being unaware of the exact implications of calling commit.

[zzzeek]

ah, well it applies to Session.commit(), not connection.commit() itself. the Session is a transaction / connection manager and uses a new connecton for each transaction.

[zzzeek]

so perhaps an ORM-related example there. historyically these two subjects weren't mixed in the docs but in 1.4 I'm just doing it.