Skip to content

Instantly share code, notes, and snippets.

@henribru

henribru/main.py

Last active April 6, 2022 19:19
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save henribru/24b8f20b11562049d3cdddabbeab62f3 to your computer and use it in GitHub Desktop.
Save henribru/24b8f20b11562049d3cdddabbeab62f3 to your computer and use it in GitHub Desktop.
Download ZIP
`selectinload` doesn't load all relationships when used with `with_polymorphic` and subclasses have relationships with the same name
Raw
main.py
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, inspect
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, with_polymorphic, selectinload
from sqlalchemy.orm import sessionmaker
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
Base = declarative_base()
class GenericParent(Base):
__tablename__ = "generic_parent"
id = Column(Integer, primary_key=True)
type = Column(String, nullable=False)
__mapper_args__ = {
'polymorphic_on': type,
'polymorphic_identity': 'generic_parent'
}
class ParentA(GenericParent):
__tablename__ = "parent_a"
id = Column(Integer, ForeignKey("generic_parent.id"), primary_key=True)
children = relationship("ChildA", back_populates="parent")
__mapper_args__ = {
'polymorphic_identity': 'parent_a'
}
class ParentB(GenericParent):
__tablename__ = "parent_b"
id = Column(Integer, ForeignKey("generic_parent.id"), primary_key=True)
children = relationship("ChildB", back_populates="parent")
__mapper_args__ = {
'polymorphic_identity': 'parent_b'
}
class ParentC(GenericParent):
__tablename__ = "parent_c"
id = Column(Integer, ForeignKey("generic_parent.id"), primary_key=True)
children_ = relationship("ChildC", back_populates="parent")
__mapper_args__ = {
'polymorphic_identity': 'parent_c'
}
class ChildA(Base):
__tablename__ = "child_a"
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey("parent_a.id"), nullable=False)
parent = relationship("ParentA", back_populates="children")
class ChildB(Base):
__tablename__ = "child_b"
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey("parent_b.id"), nullable=False)
parent = relationship("ParentB", back_populates="children")
class ChildC(Base):
__tablename__ = "child_c"
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey("parent_c.id"), nullable=False)
parent = relationship("ParentC", back_populates="children_")
Base.metadata.create_all(engine)
session = Session()
parent_a = ParentA()
parent_b = ParentB()
parent_c = ParentC()
for i in range(10):
parent_a.children.append(ChildA())
parent_b.children.append(ChildB())
parent_c.children_.append(ChildC())
session.add_all([parent_a, parent_b, parent_c])
session.commit()
session.expunge_all()
parent_types = with_polymorphic(GenericParent, [ParentA, ParentB, ParentC])
parents = session.query(parent_types).options(selectinload(parent_types.ParentA.children),
selectinload(parent_types.ParentB.children),
selectinload(parent_types.ParentC.children_)).all()
for parent in parents:
print(parent, inspect(parent).unloaded)
Raw
output.txt
2020-04-03 20:52:18,686 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("generic_parent")
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info("generic_parent")
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("parent_a")
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info("parent_a")
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("parent_b")
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info("parent_b")
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("parent_c")
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info("parent_c")
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("child_a")
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info("child_a")
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("child_b")
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info("child_b")
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("child_c")
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info("child_c")
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine
CREATE TABLE generic_parent (
id INTEGER NOT NULL,
type VARCHAR NOT NULL,
PRIMARY KEY (id)
)
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine COMMIT
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine
CREATE TABLE parent_a (
id INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(id) REFERENCES generic_parent (id)
)
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,691 INFO sqlalchemy.engine.base.Engine COMMIT
2020-04-03 20:52:18,696 INFO sqlalchemy.engine.base.Engine
CREATE TABLE parent_b (
id INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(id) REFERENCES generic_parent (id)
)
2020-04-03 20:52:18,696 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,696 INFO sqlalchemy.engine.base.Engine COMMIT
2020-04-03 20:52:18,696 INFO sqlalchemy.engine.base.Engine
CREATE TABLE parent_c (
id INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(id) REFERENCES generic_parent (id)
)
2020-04-03 20:52:18,696 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,696 INFO sqlalchemy.engine.base.Engine COMMIT
2020-04-03 20:52:18,696 INFO sqlalchemy.engine.base.Engine
CREATE TABLE child_a (
id INTEGER NOT NULL,
parent_id INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(parent_id) REFERENCES parent_a (id)
)
2020-04-03 20:52:18,696 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,696 INFO sqlalchemy.engine.base.Engine COMMIT
2020-04-03 20:52:18,701 INFO sqlalchemy.engine.base.Engine
CREATE TABLE child_b (
id INTEGER NOT NULL,
parent_id INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(parent_id) REFERENCES parent_b (id)
)
2020-04-03 20:52:18,701 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,701 INFO sqlalchemy.engine.base.Engine COMMIT
2020-04-03 20:52:18,701 INFO sqlalchemy.engine.base.Engine
CREATE TABLE child_c (
id INTEGER NOT NULL,
parent_id INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(parent_id) REFERENCES parent_c (id)
)
2020-04-03 20:52:18,701 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,701 INFO sqlalchemy.engine.base.Engine COMMIT
2020-04-03 20:52:18,701 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-04-03 20:52:18,701 INFO sqlalchemy.engine.base.Engine INSERT INTO generic_parent (type) VALUES (?)
2020-04-03 20:52:18,701 INFO sqlalchemy.engine.base.Engine ('parent_a',)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine INSERT INTO generic_parent (type) VALUES (?)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine ('parent_b',)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine INSERT INTO generic_parent (type) VALUES (?)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine ('parent_c',)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine INSERT INTO parent_a (id) VALUES (?)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine (1,)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine INSERT INTO parent_b (id) VALUES (?)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine (2,)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine INSERT INTO parent_c (id) VALUES (?)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine (3,)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine INSERT INTO child_a (parent_id) VALUES (?)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine (1,)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine INSERT INTO child_a (parent_id) VALUES (?)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine (1,)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine INSERT INTO child_a (parent_id) VALUES (?)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine (1,)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine INSERT INTO child_a (parent_id) VALUES (?)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine (1,)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine INSERT INTO child_a (parent_id) VALUES (?)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine (1,)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine INSERT INTO child_a (parent_id) VALUES (?)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine (1,)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine INSERT INTO child_a (parent_id) VALUES (?)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine (1,)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine INSERT INTO child_a (parent_id) VALUES (?)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine (1,)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine INSERT INTO child_a (parent_id) VALUES (?)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine (1,)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine INSERT INTO child_a (parent_id) VALUES (?)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine (1,)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine INSERT INTO child_b (parent_id) VALUES (?)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine (2,)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine INSERT INTO child_b (parent_id) VALUES (?)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine (2,)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine INSERT INTO child_b (parent_id) VALUES (?)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine (2,)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine INSERT INTO child_b (parent_id) VALUES (?)
2020-04-03 20:52:18,711 INFO sqlalchemy.engine.base.Engine (2,)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine INSERT INTO child_b (parent_id) VALUES (?)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine (2,)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine INSERT INTO child_b (parent_id) VALUES (?)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine (2,)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine INSERT INTO child_b (parent_id) VALUES (?)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine (2,)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine INSERT INTO child_b (parent_id) VALUES (?)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine (2,)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine INSERT INTO child_b (parent_id) VALUES (?)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine (2,)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine INSERT INTO child_b (parent_id) VALUES (?)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine (2,)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine INSERT INTO child_c (parent_id) VALUES (?)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine (3,)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine INSERT INTO child_c (parent_id) VALUES (?)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine (3,)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine INSERT INTO child_c (parent_id) VALUES (?)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine (3,)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine INSERT INTO child_c (parent_id) VALUES (?)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine (3,)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine INSERT INTO child_c (parent_id) VALUES (?)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine (3,)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine INSERT INTO child_c (parent_id) VALUES (?)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine (3,)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine INSERT INTO child_c (parent_id) VALUES (?)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine (3,)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine INSERT INTO child_c (parent_id) VALUES (?)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine (3,)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine INSERT INTO child_c (parent_id) VALUES (?)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine (3,)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine INSERT INTO child_c (parent_id) VALUES (?)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine (3,)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine COMMIT
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine SELECT generic_parent.id AS generic_parent_id, generic_parent.type AS generic_parent_type, parent_a.id AS parent_a_id, parent_b.id AS parent_b_id, parent_c.id AS parent_c_id
FROM generic_parent LEFT OUTER JOIN parent_a ON generic_parent.id = parent_a.id LEFT OUTER JOIN parent_b ON generic_parent.id = parent_b.id LEFT OUTER JOIN parent_c ON generic_parent.id = parent_c.id
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine ()
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine SELECT child_a.parent_id AS child_a_parent_id, child_a.id AS child_a_id
FROM child_a
WHERE child_a.parent_id IN (?) ORDER BY child_a.parent_id
2020-04-03 20:52:18,721 INFO sqlalchemy.engine.base.Engine (1,)
2020-04-03 20:52:18,731 INFO sqlalchemy.engine.base.Engine SELECT child_c.parent_id AS child_c_parent_id, child_c.id AS child_c_id
FROM child_c
WHERE child_c.parent_id IN (?) ORDER BY child_c.parent_id
2020-04-03 20:52:18,731 INFO sqlalchemy.engine.base.Engine (3,)
<__main__.ParentA object at 0x00000203EF87A080> set()
<__main__.ParentB object at 0x00000203EF87A2B0> {'children'}
<__main__.ParentC object at 0x00000203EF87A320> set()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment