are you able to set echo="debug" on your create_engine(), view the SQL being
emitted as well as the results being returned, and then ensure the SQL
statement and results are what you are looking for? once you have that we can
make sure the ORM interprets these results correctly.
On Sun, Jul 7, 2019, at 4:02 PM, Olivier SAINT-EVE wrote:
> hello,
>
> thank you a lot for your answer.
>
> I modified my request and now it compiles, but I do not get the expected
> result...
> the method should stop when it encounters a 'Territoire' which is 'region',
> but it lists all the elements, even those of scale (type) 'ville' (I mean
> town) which shhould be removed from the result.
>
> here is a small code reproducing the error:
>
> from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
> from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import relationship, sessionmaker, aliased
> from sqlalchemy.schema import Table
>
> Base = declarative_base()
>
> parent_child = Table('parent_child', Base.metadata,
> Column('parent_id', ForeignKey('territoire.id'), primary_key=True),
> Column('child_id', ForeignKey('territoire.id'), primary_key=True))
>
>
> class Territoire(Base):
> __tablename__ = 'territoire'
> id = Column(Integer, primary_key=True)
> name = Column(String)
> scale = Column(String)
>
> children = relationship(
> 'Territoire',
> secondary=parent_child,
> back_populates='parents',
> primaryjoin=parent_child.c.parent_id == id,
> secondaryjoin=parent_child.c.child_id == id,
>
> )
>
> parents = relationship(
> 'Territoire',
> secondary=parent_child,
> primaryjoin=parent_child.c.child_id==id,
> secondaryjoin=parent_child.c.parent_id==id,
> back_populates='children'
> )
>
> def __repr__(self):
> chaine1= "<Territoire(%s // %s // %s)>" % (self.id, self.name, self.scale)
> return chaine1
>
> def __init__(self, name, scale):
> self.name = name
> self.scale = scale
> self.parents=[]
> self.children=[]
>
>
> if __name__ == "__main__":
> engine = create_engine('postgresql://postgres:admin@localhost/territoires2',
> echo=True)
> # Base = declarative_base()
> Session = sessionmaker(bind=engine)
> Session.configure(bind=engine)
> Base.metadata.create_all(engine)
> session = Session()
>
> # print("territoire table="+Territoire.__table__)
> france = Territoire(name="france", scale="pays")
> metz = Territoire(name='metz', scale='ville')
> grand_est = Territoire(name='Grand Est', scale='region')
>
> metz.parents.append(grand_est)
> grand_est.parents.append(france)
> var = Territoire('Var', 'region')
> france.children.append(var)
> sete = Territoire('sete', 'ville')
> var.children.append(sete)
> session.add_all([france, metz, grand_est])
> session.add_all([var,sete])
>
> # +++++++++++++++++++++++
>
> # **********************************************
> session.commit()
> # **********************************************
>
> # for t in session.query(Territoire):
> # print(t)
>
> element0 = aliased(Territoire)
> sub_territories = session.query(element0.id,element0.name,element0.scale). \
> filter(element0.id == 1). \
> filter(element0.scale != 'region'). \
> cte(name='sub_territories', recursive=True)
>
> # st_alias = aliased(sub_territories,name="sa")
> relation = aliased(parent_child, name="rel")
> porteur = aliased(Territoire, name="p")
> rec=aliased(sub_territories,name="rec")
>
> sub_territories = sub_territories.union(
> session.query(porteur.id,porteur.name,porteur.scale). \
> # filter(porteur.id == relation.c.child_id). \
> filter(porteur.id == relation.c.child_id). \
> filter(relation.c.parent_id == sub_territories.c.id). \
> filter(sub_territories.c.scale != 'region'))
>
> # sub_territories = sub_territories.union(corps)
>
> print(sub_territories)
>
> q = session.query(Territoire).select_from(sub_territories)
> print('solution =' + str(q.all()))
>
> my problem seems to be logical, I will try to solve it, but maybe someone
> could help me to go faster!
>
> thank you
>
>
>
>
>
> Le dimanche 7 juillet 2019 15:29:19 UTC+2, Olivier SAINT-EVE a écrit :
>> I created this recursive query:
>> `element0 = aliased(Territoire)
sub_territories = session.query(element0, element0.id). \
filter(element0.id == 1). \
filter(element0.scale != 'Region'). \
cte(name='sub_territories', recursive=True)
st_alias = aliased(sub_territories)
relation = aliased(parent_child)
porteur = aliased(Territoire)
corps = session.query(porteur, relation.c.child_id). \
join(porteur, porteur.id == relation.c.child_id). \
join(st_alias, relation.c.parent_id == st_alias.c.id). \
filter(st_alias.c.scale != 'Region')
sub_territories = sub_territories.union(corps)`
>> which should give all the children of element0 recursively, stopping at the
>> 'Region' level(items are linked by a many-to-many relation), each item
>> having a level which can be : world, country, region, city...
>> here is some code that could help you:
>> `class Territoire(Base):
__tablename__ = 'territoire'
id = Column(Integer, primary_key=True)
name = Column(String)
scale = Column(String)
children = relationship(
'Territoire',
secondary=parent_child,
back_populates='parents',
primaryjoin=parent_child.c.parent_id == id,
secondaryjoin=parent_child.c.child_id == id,
)
parents = relationship(
'Territoire',
secondary=parent_child,
primaryjoin=parent_child.c.child_id==id,
secondaryjoin=parent_child.c.parent_id==id,
back_populates='children'
)`
>> and the table:
>> `parent_child = Table('parent_child', Base.metadata,
Column('parent_id', ForeignKey('territoire.id'),
primary_key=True),
Column('child_id', ForeignKey('territoire.id'),
primary_key=True))`
>> the error message is :
>>> sqlalchemy.exc.ArgumentError: All selectables passed to CompoundSelect must
>>> have identical numbers of columns; select #1 has 3 columns, select #2 has 4
>> I don't understand this error, since the 2 CTE requests have for 1st element
>> a Territoire object and for 2nd element an int (id).
>> thank you
>
> --
> 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.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/abac3ac5-fac3-42f9-aa4f-1f4268b202c2%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/abac3ac5-fac3-42f9-aa4f-1f4268b202c2%40googlegroups.com?utm_medium=email&utm_source=footer>.
> 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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/dc801abf-bf97-4b8d-8806-d2813c1e87a0%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.
