Hi,
I am working on a function takes an existing 'Query' object and extend it.
This is done via a join.
My problem is that either the result of the query is not an (table-) object
or the created query contains additional select-from-statements.
This is a little example that shows the problem.
The 'First Query' looks like
SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name
FROM (
SELECT table_one.id AS id, table_one.name AS name
FROM table_one) AS anon_1
JOIN (
SELECT table_two.id AS id, table_two.f_id AS f_id,
table_two.description AS description
FROM table_two) AS anon_2
ON anon_1.id = anon_2.f_id
That is what I am looking for but it returns a list with values and not
'TableOne' object.
The 'Second Query' return a list of 'TableOne' objects,
but the query contains additional stuff.
SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name
FROM (
SELECT anon_2.id AS id, anon_2.name AS name
FROM (
SELECT anon_3.id AS id, anon_3.name AS name
FROM (
SELECT table_one.id AS id, table_one.name AS name
FROM table_one) AS anon_3
JOIN (
SELECT table_two.id AS id, table_two.f_id AS f_id,
table_two.description AS description
FROM table_two) AS anon_4
ON anon_3.id = anon_4.f_id
) AS anon_2)
AS anon_1
Example code:
------------------------------------------------------------------------------------------------------------------------
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, String
Base = declarative_base()
class TableOne(Base):
__tablename__ = 'table_one'
id = Column(Integer, primary_key=True)
name = Column(String)
class TableTwo(Base):
__tablename__ = 'table_two'
id = Column(Integer, primary_key=True)
f_id = Column(Integer, ForeignKey(TableOne.id))
description = Column(String)
# initialization
engine = create_engine('sqlite://')
Base.metadata.create_all(bind=engine)
Session = sessionmaker(bind=engine)
session = Session()
session.add_all([
TableOne(id=1, name='some name'),
TableTwo(id=1, f_id=1, description='some description')])
# ----------- First Query --------------------
existing_query = session.query(TableOne).subquery()
additional_query = session.query(TableTwo).subquery()
combined_query = session.query(existing_query).join(additional_query)
print combined_query.all()[0]
# out: (1, u'some name')
# ----------- Secound Query -------------------
combined_query = session \
.query(TableOne) \
.select_from(combined_query.subquery().select())
print combined_query.all()[0]
# out: <__main__.TableOne object at 0x13224d0>
------------------------------------------------------------------------------------------------------------------------
Who can I combine the two queries and get list of table objects as result?
Thanks in advance.
Chris
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, String
Base = declarative_base()
class TableOne(Base):
__tablename__ = 'table_one'
id = Column(Integer, primary_key=True)
name = Column(String)
class TableTwo(Base):
__tablename__ = 'table_two'
id = Column(Integer, primary_key=True)
f_id = Column(Integer, ForeignKey(TableOne.id))
description = Column(String)
# initialization
engine = create_engine('sqlite://')
Base.metadata.create_all(bind=engine)
Session = sessionmaker(bind=engine)
session = Session()
session.add_all([
TableOne(id=1, name='some name'),
TableTwo(id=1, f_id=1, description='some description')])
# -------------------------------
existing_query = session.query(TableOne).subquery()
additional_query = session.query(TableTwo).subquery()
combined_query = session.query(existing_query).join(additional_query)
print combined_query.all()[0]
# out: (1, u'some name')
# -------------------------------
combined_query = session \
.query(TableOne) \
.select_from(combined_query.subquery().select())
result = combined_query.all()
print combined_query.all()[0]
print combined_query
# out: <__main__.TableOne object at 0x13224d0>
