[sqlalchemy] finding if a table is already join in a query
Hi all,
We have a small function that helps us create a simple search query by
automatically joining on required relations if needed.
For example, consider an employee ORM that has a 1:M relationship with
addresses (for postal/physical). We can do something like:
query = Employee().search('streetname', [Employee.name,
Address.street1])
We have that working, but when we add a second search field on Address:
query = Employee.search('streetname', [Employee.name, Address.street1,
Address.street2])
our method fails with: table name address specified more than once
We need to be able to identify if the query already has a join on
'address'
I've tried getting details on the query object (it
has ._from, ._from_obj, ._from_alias and .from_statement) that looked
interesting, but they don't appear to give us what we need.
Here is a cut down sample implementation that will hopefully remove any
confusion... Note the TODO: in Employee.search()
---8---8---8
from sqlalchemy import create_engine, Column, ForeignKey, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship,
joinedload
from sqlalchemy.types import Integer, String, Text
from sqlalchemy.sql.expression import cast
engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base(bind=engine)
Session = scoped_session(sessionmaker(bind=engine))
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String)
def search(self, value, columns):
query = Session.query(Employee)
for i, column in enumerate(columns):
model = column.parententity.class_
if Employee is not model:
#TODO: Are we already joined from Employee onto model?
query = query.outerjoin(model)
args = [cast(c, Text).ilike('%%%s%%' % value) for c in columns]
return query.filter(or_(*args))
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey(Employee.id))
street1 = Column(String(50))
street2 = Column(String(50))
employee = relationship(Employee)
Base.metadata.create_all()
#e = Employee(name='Bob')
#a = Address(employee=e, street1='street1', street2='street2')
#Session.add(a)
#Session.commit()
q = Employee().search('stree', [Employee.name, Address.street1,
Address.street2])
print q
SELECT employee.id AS employee_id, employee.name AS employee_name
FROM employee LEFT OUTER JOIN address ON employee.id =
address.employee_id LEFT OUTER JOIN address ON employee.id =
address.employee_id
WHERE lower(CAST(employee.name AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street1 AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street2 AS TEXT)) LIKE lower(?)
---8---8---8
TIA
Jim
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] finding if a table is already join in a query
On Tue, 2010-11-30 at 11:52 -0500, Michael Bayer wrote:
On Nov 30, 2010, at 11:13 AM, James Neethling wrote:
Hi all,
We have a small function that helps us create a simple search query by
automatically joining on required relations if needed.
For example, consider an employee ORM that has a 1:M relationship with
addresses (for postal/physical). We can do something like:
query = Employee().search('streetname', [Employee.name,
Address.street1])
We have that working, but when we add a second search field on Address:
query = Employee.search('streetname', [Employee.name, Address.street1,
Address.street2])
our method fails with: table name address specified more than once
We need to be able to identify if the query already has a join on
'address'
I've tried getting details on the query object (it
has ._from, ._from_obj, ._from_alias and .from_statement) that looked
interesting, but they don't appear to give us what we need.
if you would like multiple references to Address to all work from the same
join, your routine needs to track which entities have already been joined as
a destination in a separate collection:
def search(columns):
already_joined = set()
...
if class_ not in already_joined:
q = q.join(destination)
already_joined.add(class_)
Hi Michael,
Thank you for the quick response.
Unfortunately we don't always know where this query comes from (my
example was a little contrived :( )
Is there any way to get the tables that are currently in the join for a
query?
Here is a cut down sample implementation that will hopefully remove any
confusion... Note the TODO: in Employee.search()
---8---8---8
from sqlalchemy import create_engine, Column, ForeignKey, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship,
joinedload
from sqlalchemy.types import Integer, String, Text
from sqlalchemy.sql.expression import cast
engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base(bind=engine)
Session = scoped_session(sessionmaker(bind=engine))
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String)
def search(self, value, columns):
query = Session.query(Employee)
for i, column in enumerate(columns):
model = column.parententity.class_
if Employee is not model:
#TODO: Are we already joined from Employee onto model?
query = query.outerjoin(model)
args = [cast(c, Text).ilike('%%%s%%' % value) for c in columns]
return query.filter(or_(*args))
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey(Employee.id))
street1 = Column(String(50))
street2 = Column(String(50))
employee = relationship(Employee)
Base.metadata.create_all()
#e = Employee(name='Bob')
#a = Address(employee=e, street1='street1', street2='street2')
#Session.add(a)
#Session.commit()
q = Employee().search('stree', [Employee.name, Address.street1,
Address.street2])
print q
SELECT employee.id AS employee_id, employee.name AS employee_name
FROM employee LEFT OUTER JOIN address ON employee.id =
address.employee_id LEFT OUTER JOIN address ON employee.id =
address.employee_id
WHERE lower(CAST(employee.name AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street1 AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street2 AS TEXT)) LIKE lower(?)
---8---8---8
TIA
Jim
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
James Neethling
Development Manager
XO Africa Safari
(t) +27 21 486 2700 (ext. 127)
(e) jam...@xoafrica.com
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http
Re: [sqlalchemy] session.execute(sql_statement) does not flush the session in a autoflush=True session ?
On Fri, 2010-11-26 at 15:41 -0500, Michael Bayer wrote: I wouldn't say its a bug since its intentional. But I'll grant the intention is up for debate. I've always considered usage of execute() to mean, you're going below the level of the ORM and would like to control the SQL interaction directly, not to mention with as minimal overhead as possible, which is why it works that way currently. It might be just as surprising to many users if execute() issued a whole series of insert/update statements as much as it was apparently surprising to you that it did not. I agree with the current behaviour. I've never actually thought of execute as doing anything other than *exactly* what I want it to do - and only that. Hi, In a session which has some dirty objects, doing session.execute(sql_statement) doesn't not flush the dirty objects to the database before executing the sql_statement query. The session was initialized with autoflush=True. Is it the expected behaviour ? Is it a bug ? Thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] multi Table FKs - ORM mapping
Hi All,
We're looking to add tags to a number of 'entities' within our
application. A simplified data structure would be:
Image:
id
file
title
description
Article:
id
text
Tag:
id
value
Entity_tags:
id
entity_type ('image' or 'article'
entity_id(PK of the image/article table)
tag_id
We've got the following (highly simplified) structure:
image_table = Table('image', meta.metadata,
Column('id', types.Integer, primary_key=True), )
article_table = Table('article', meta.metadata,
Column('id', types.Integer, primary_key=True), )
tag_table = Table('tag', meta.metadata,
Column('id', types.Integer, primary_key=True),
Column('name', types.Unicode(50), unique=True), )
entity_tag_table = Table('entity_tag', meta.metadata,
Column('tag_id', types.Integer, ForeignKey(tag_table.c.id)),
Column('entity_type', types.String, nullable=False),
Column('entity_id', types.Integer, nullable=False),
)
# And the ORM Mappings:
class Image(object): pass
class Article(object): pass
class Tag(object): pass
class EntityTag(object): pass
orm.mapper(Image, image_table, properties={
'tags': orm.relation(Tag, secondary=entity_tag_table,
primaryjoin=and_(image_table.c.id==EntityTag.entity_id,
EntityTag.entity_type=='image'),
secondaryjoin=EntityTag.tag_id==Tag.id,
foreignkeys=[EntityTag.entity_id, EntityTag.tag_id], ),
})
orm.mapper(Article, article_table, properties={
'tags': orm.relation(Tag, secondary=entity_tag_table,
primaryjoin=and_(article_table.c.id==EntityTag.entity_id,
EntityTag.entity_type=='article'),
secondaryjoin=EntityTag.tag_id==Tag.id,
foreignkeys=[EntityTag.entity_id, EntityTag.tag_id], ),
})
When we append to the image.tags collection, the entity_tag table needs
to know that the entity_type is 'image', but we can't seem to set that.
What is the standard way of dealing with this problem?
Is there the concept of a generic foreign key in SQLAlchemy?
Does anyone know if this database pattern has a formal name?
TIA,
Jim
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
