Closed
Description
If a foreign key column contains an empty string and there are no corresponding empty string key in the other tables primary key column, sqlalchemy fails with the confusing error KeyError: ('missing_key',):
Traceback (most recent call last):
File "test.py", line 49, in <module>
list(session.query(Bar))
File "/usr/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 105, in instances
util.raise_from_cause(err)
File "/usr/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/usr/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
raise value
File "/usr/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 93, in instances
post_load.invoke(context, path)
File "/usr/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 877, in invoke
loader(context, path, states, self.load_keys, *arg, **kw)
File "<string>", line 1, in <lambda>
File "/usr/lib/python3.7/site-packages/sqlalchemy/orm/strategies.py", line 2361, in _load_for_path
self._load_via_child(our_states, query_info, q, context)
File "/usr/lib/python3.7/site-packages/sqlalchemy/orm/strategies.py", line 2384, in _load_via_child
related_obj = data[key]
KeyError: ('missing_key',)
The following python code demonstrates the error:
from sqlalchemy import Column, String, create_engine, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Foo(Base):
__tablename__ = "Foo"
foo_id = Column(String(), primary_key=True)
class Bar(Base):
__tablename__ = "Bar"
bar_id = Column(String, primary_key=True)
foo_id = Column(ForeignKey(Foo.foo_id))
foo = relationship(Foo, lazy="selectin", uselist=False)
engine = create_engine("sqlite://")
Session = sessionmaker(bind=engine)
if __name__ == "__main__":
engine.execute('CREATE TABLE Bar (bar_id VARCHAR, foo_id VARCHAR, PRIMARY KEY (bar_id));')
engine.execute('CREATE TABLE Foo (foo_id VARCHAR, PRIMARY KEY (foo_id));')
engine.execute('INSERT INTO Foo (foo_id) VALUES ("one"), ("two");')
engine.execute('INSERT INTO Bar (bar_id, foo_id) VALUES ("1", "one"), ("2", "missing_key");')
session = Session()
list(session.query(Bar))Metadata
Metadata
Assignees
Labels
Type
Projects
Relationships
Development
No branches or pull requests
Participants
Activity
[-]Empty string in ForeignKey causes `KeyError('',)` if missing in table[/-][+]Missing ForeignKey causes `KeyError('missing_key',)` if missing in table[/+][-]Missing ForeignKey causes `KeyError('missing_key',)` if missing in table[/-][+]new selectinload does not tolerate failing primary join conditions[/+]zzzeek commentedon Jul 23, 2019
this is a regression new in 1.3.6 caused by #4775. please downgrade to 1.3.5 if this issue is problematic for now.
zzzeek commentedon Jul 23, 2019
oh you can also force the old logic like this:
ljos commentedon Jul 23, 2019
I also found that chaging the line at "/sqlalchemy/orm/strategies.py", line 2384, in
_load_via_childto:fixed the problem as well. I just couldn't figure out how to write a test for it.
sqla-tester commentedon Jul 23, 2019
Mike Bayer has proposed a fix for this issue in the master branch:
Don't assume m2o key is present in the dictionary https://gerrit.sqlalchemy.org/1373
sqla-tester commentedon Jul 23, 2019
Mike Bayer has proposed a fix for this issue in the rel_1_3 branch:
Don't assume m2o key is present in the dictionary https://gerrit.sqlalchemy.org/1374
Don't assume m2o key is present in the dictionary