didn't look close yet but you wouldn't want to have foreign() on a column that is a primary key column. should be on the opposite side of whatever refers to a primary key.
On Wed, Jan 3, 2018 at 6:28 PM, Jonathan Vanasco <[email protected]> wrote: > 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. -- 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.
