i've got a handful of bad data from some edge cases and have been having
trouble querying the data with sqlalchemy. i attached a SSCCE below.
my cleanup script imports the model, extends `Main_Foo` with a new
relationship, and creates 2 aliases.
this selects the right rows (2 and 4) but the relationship is populated
with the "local side" data, not the "remote side" data
i'm sure I'm missing something obvious.
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -
# Standard imports
import sqlalchemy
import sqlalchemy.orm
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Integer, Column, Unicode
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -
# You probably don't need to overwrite this
Base = declarative_base()
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -
# Define some models that inherit from Base
class Main_Foo(Base):
__tablename__ = 'main_foo'
id = Column(Integer, primary_key=True)
id_foo_alt = Column(Integer)
name = Column(Unicode)
# need to define these separately as they're in a new script that
overwrites the model
Main_Foo.foo_alt = sqlalchemy.orm.relationship(
"Main_Foo",
primaryjoin="Main_Foo.id_foo_alt==remote(foreign(Main_Foo.id))",
uselist=False,
)
Foo = sqlalchemy.orm.aliased(Main_Foo, name='foo')
Foo_2 = sqlalchemy.orm.aliased(Main_Foo, name='foo2')
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -
# set the engine
engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -
# do a simple query to trigger the mapper error
sessionFactory = sqlalchemy.orm.sessionmaker(bind=engine)
s = sessionFactory()
rows = ((1, None, 'a (ab)'),
(2, 1, 'b (ab)'),
(3, None, 'c (cd)'),
(4, 3, 'd (cd)'),
(5, None, 'e'),
(6, 'x', 'f'),
(7, 'x', 'g'),
)
for row in rows:
f = Main_Foo()
f.id = row[0]
f.id_foo_alt = row[1]
f.name = row[2]
s.add(f)
s.flush()
s.commit()
qedge_case = s.query(Foo)\
.join(Foo_2,
Foo.id_foo_alt == Foo_2.id
)\
.filter(Foo_2.name.op('IS NOT')(None),
)\
.options(sqlalchemy.orm.contains_eager('foo_alt'))\
.order_by(Foo.id.asc())
for f in qedge_case.all():
print "---"
print f.id
print " %s %s" % (f.id, f.name)
print " > >"
print " %s %s" % (f.foo_alt.id, f.foo_alt.name)
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
