not the same number of columns in the 2 queries of the recursive request

22 views
Skip to first unread message

Olivier SAINT-EVE

unread,
Jul 7, 2019, 9:29:19 AM7/7/19
to sqlalchemy

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

Mike Bayer

unread,
Jul 7, 2019, 9:53:15 AM7/7/19
to noreply-spamdigest via sqlalchemy
Hi, and thanks for the complete information to reproduce.

When you get an error like this, go into pdb and print out the SQL being emitted:

(Pdb) print(sub_territories.element)
SELECT territoire_1.id, territoire_1.name, territoire_1.scale
FROM territoire AS territoire_1
WHERE territoire_1.id = :id_1 AND territoire_1.scale != :scale_1


(Pdb) print(corps)
WITH RECURSIVE sub_territories(id, name, scale) AS
(SELECT territoire_2.id AS id, territoire_2.name AS name, territoire_2.scale AS scale
FROM territoire AS territoire_2
WHERE territoire_2.id = :id_1 AND territoire_2.scale != :scale_1)
SELECT territoire_1.id AS territoire_1_id, territoire_1.name AS territoire_1_name, territoire_1.scale AS territoire_1_scale, parent_child_1.child_id AS parent_child_1_child_id
FROM parent_child AS parent_child_1 JOIN territoire AS territoire_1 ON territoire_1.id = parent_child_1.child_id JOIN sub_territories AS anon_1 ON parent_child_1.parent_id = anon_1.id
WHERE anon_1.scale != :scale_2



Above, you can see the first query has only three columns, even though you've placed a three-column entity plus a separate column into the Query.  Why is this?  Because SELECT statements in SQLAlchemy always SELECT distinct names, and by placing territorie_1.id in the query twice, it is deduped:

(Pdb) print(sub_territories.columns)
['sub_territories.id', 'sub_territories.name', 'sub_territories.scale']

we can see it like this also:

(Pdb) c1, c2 = column('c1'), column('c2')
(Pdb) print(select([c1, c2, c1]))
SELECT c1, c2

Since we are in the middle of changing lots of things for 2.0, I will see if this can be adjusted because it is inconsistent that a Query(a, b, a) will return a three-column tuple regardless, but select([a, b, a]) will return a two-tuple: https://github.com/sqlalchemy/sqlalchemy/issues/4753


the solution for now is therefore to apply a label to the extra column:

sub_territories = (
    session.query(element0, element0.id.label("child_id"))
    .filter(element0.id == 1)
    .filter(element0.scale != "Region")
    .cte(name="sub_territories", recursive=True)
)



thank you


--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Olivier SAINT-EVE

unread,
Jul 7, 2019, 4:02:34 PM7/7/19
to sqlalchemy
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

Mike Bayer

unread,
Jul 8, 2019, 10:09:59 AM7/8/19
to noreply-spamdigest via sqlalchemy
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.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages