Re: [sqlalchemy] trouble with a self-referential query and remote side population
On Thursday, January 4, 2018 at 10:58:49 AM UTC-5, Mike Bayer wrote:
>
> that and, when you use contains_eager you need to tell it what entity
> it's looking for when it considers columns as part of a relationship:
>
> .options(sqlalchemy.orm.contains_eager('foo_alt', alias=Foo_2))\
>
As always, THANK YOU SO MUCH, MIKE. That alias kwarg was the missing bit.
The `foreign` was left over from the original query and wasn't removed by
accident (the original doesn't fkey on a primary for the join).
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] trouble with a self-referential query and remote side population
On Wed, Jan 3, 2018 at 7:48 PM, Mike Bayer wrote:
> 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.
that and, when you use contains_eager you need to tell it what entity
it's looking for when it considers columns as part of a relationship:
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', alias=Foo_2))\
.order_by(Foo.id.asc())
>
> On Wed, Jan 3, 2018 at 6:28 PM, Jonathan Vanasco
> 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 sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/
Re: [sqlalchemy] trouble with a self-referential query and remote side population
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 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
