sqlalchemy: Query.get() in dictionary mode does not respect orm.synonym()

Describe the bug

When using a dictionary for specifying primary keys in Query.get(), synonyms for the primary key fields are not being respected.

To Reproduce

from sqlalchemy import create_engine
from sqlalchemy.orm import Session, declarative_base, synonym
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer

engine = create_engine("sqlite:///:memory:")
session = Session(engine)
Base = declarative_base()

class Parent(Base):
    __tablename__ = 'membership'
    child_id = Column(ForeignKey('child.id'), primary_key=True)
    synonym_child_id = synonym(child_id)


class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)

Base.metadata.create_all(engine)

session.query(Parent).get({
    'synonym_child_id': 0,
})

Error

Traceback (most recent call last):
  File "/Users/username/Library/Mobile Documents/com~apple~CloudDocs/Sites/bugs/sqlalchemy_synonym/venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2897, in _get_impl
    primary_key_identity = list(
  File "/Users/username/Library/Mobile Documents/com~apple~CloudDocs/Sites/bugs/sqlalchemy_synonym/venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2898, in <genexpr>
    primary_key_identity[prop.key]
KeyError: 'child_id'

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

Traceback (most recent call last):
  File "/Users/username/Library/Mobile Documents/com~apple~CloudDocs/Sites/bugs/sqlalchemy_synonym/example.py", line 22, in <module>
    session.query(Parent).get({
  File "<string>", line 2, in get
  File "/Users/username/Library/Mobile Documents/com~apple~CloudDocs/Sites/bugs/sqlalchemy_synonym/venv/lib/python3.9/site-packages/sqlalchemy/util/deprecations.py", line 402, in warned
    return fn(*args, **kwargs)
  File "/Users/username/Library/Mobile Documents/com~apple~CloudDocs/Sites/bugs/sqlalchemy_synonym/venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 947, in get
    return self._get_impl(ident, loading.load_on_pk_identity)
  File "/Users/username/Library/Mobile Documents/com~apple~CloudDocs/Sites/bugs/sqlalchemy_synonym/venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 951, in _get_impl
    return self.session._get_impl(
  File "/Users/username/Library/Mobile Documents/com~apple~CloudDocs/Sites/bugs/sqlalchemy_synonym/venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2903, in _get_impl
    util.raise_(
  File "/Users/username/Library/Mobile Documents/com~apple~CloudDocs/Sites/bugs/sqlalchemy_synonym/venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
sqlalchemy.exc.InvalidRequestError: Incorrect names of values in identifier to formulate primary key for session.get(); primary key attribute names are 'child_id'

Versions

  • OS: macOS 12.6
  • Python: 3.9.6
  • SQLAlchemy: 1.4.42
  • Database: sqlite
  • DBAPI (eg: psycopg, cx_oracle, mysqlclient): pysqlite

Additional context

No response

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Comments: 15 (8 by maintainers)

Commits related to this issue

Most upvoted comments

heya - are all your samples actual? synonym() requires a string argument, not a column object

Yes, I ran the code above - I was wondering about that too but it works just the same in this case at least

column objects in synonym aren’t supported, if you try to use that synonym in any meaningful context it will raise TypeError. the argument is not at the moment checked up front