Hi group,
my question is about Query.select_entity_from().
I have a table that contains a small subset of rows copied from a pretty
large table.
The two tables have the same definition.
The large table is mapped on a Python class, say it Item().
I would like to query the small table the same way I can query the large
one, receving Item() objects as result.
Is this possible or is my expectation wrong?
I had success with where clauses, but not with joins on relations defined
on the large table (say it, the "original" one).
An example of what I did follows.
I work with PostgreSQL 9.6 and SqlAlchemy 1.2.10.
Thank you very much for any clarification,
Sara
# Given:
engine = sqlalchemy.create_engine('postgresql+psycopg2://.....')
session = ....
# Suppose that the following are the production tables:
# t_items is the pretty large table
# t_items has a relation to t_groups
metadata = sqlalchemy.MetaData()
t_groups = sqlalchemy.Table('*t_groups*', metadata,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key = True),
sqlalchemy.Column('code', sqlalchemy.String(50), nullable = False),
)
t_items = sqlalchemy.Table('*t_items*', metadata,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key = True),
sqlalchemy.Column('group_id', sqlalchemy.Integer, sqlalchemy.
*ForeignKey*('t_groups.id'), nullable = False),
)
metadata.drop_all(bind=engine)
metadata.create_all(bind=engine)
class Group(object):
pass
class Item(object):
pass
sqlalchemy.orm.mapper(Group, t_groups)
sqlalchemy.orm.mapper(Item, t_items,
properties = {
'group' : sqlalchemy.orm.relationship(Group),
})
# Some data into the production tables:
group = Group()
group.id = 1
group.code = u'group code'
session.add(group)
session.flush()
item1 = Item()
item1.id = 100
item1.group = group
item2 = Item()
item2.id = 101
item2.group = group
session.add_all([item1, item2])
session.flush()
# Now create the subset table (used only to speed up some computations);
same definition of t_items
stmtDrop = u"DROP TABLE IF EXISTS t_items_subset"
session.execute(stmtDrop)
session.commit()
stmtCreate = u"""
CREATE TABLE t_items_subset
WITH OIDS
AS
SELECT *
FROM t_items
WITH NO DATA
"""
session.execute(stmtCreate)
session.commit()
t_items_subset = sqlalchemy.Table('t_items_subset', metadata,
*autoload_with*=engine)
# Now try to query the t_items_subset table receiving Item() objects as
result
itemAlias = sqlalchemy.orm.aliased(Item, t_items_subset,
adapt_on_names=True)
query = session.query(Item).\
*select_entity_from*(itemAlias)
print query
#SELECT t_items_subset.id AS t_items_subset_id, t_items_subset.group_id AS
t_items_subset_group_id
#FROM t_items_subset
query = query.filter(Item.id==100)
print query.statement
#SELECT t_items_subset.id, t_items_subset.group_id
#FROM t_items_subset
#WHERE t_items_subset.id = :id_1
query = query.join('group').\
filter(Group.code=='group code')
print query.statement
# ##################################
# *Here SQL does NOT appears as (I) expected*: the ON clause of JOIN refers
t_items instead of t_items_subset
# ##################################
#SELECT t_items_subset.id, t_items_subset.group_id
#FROM t_items_subset JOIN t_groups ON t_groups.id = t_items.group_id
#WHERE t_items_subset.id = :id_1 AND t_groups.code = :code_1
--
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.
