Re: [sqlalchemy] (OperationalError) no such column when child class uses different name for primary key in polymorphic relationship and a subselect issued
Thanks Michael.
On Saturday, 21 November 2015 22:35:14 UTC, Michael Bayer wrote:
>
>
>
> On 11/20/2015 12:20 PM, Martin Pengelly-Phillips wrote:
> > Hi there,
> >
> > Using SQLAlchemy 1.0.9
> >
> > I am dealing with some legacy code and came across the following issue.
> > It appears that in a polymorphic relationship SQLAlchemy is not able to
> > correctly determine what to load for a relationship when using a
> > subselect (due to limit on query) and child class has a different field
> > name for the primary key.
> >
> > Here is a reproducible test case to show the issue better:
> >
> > |
> > fromuuid importuuid1 asuuid
> >
> > importsqlalchemy
> > importsqlalchemy.orm
> > fromsqlalchemy import(
> > Column,Unicode,ForeignKey,CHAR,Boolean,UniqueConstraint
> > )
> > fromsqlalchemy.orm importrelationship,backref,synonym
> > fromsqlalchemy.ext.declarative importdeclarative_base,declared_attr
> >
> >
> > Base=declarative_base()
> >
> >
> > classContext(Base):
> > '''Represent a context.'''
> >
> > __tablename__ ='context'
> >
> > context_type =Column(Unicode(32),nullable=False)
> >
> > __mapper_args__ ={
> > 'polymorphic_on':context_type,
> > 'polymorphic_identity':'context'
> > }
> >
> > id =Column(CHAR(36),primary_key=True,default=lambda:str(uuid()))
> >
> >
> > classTask(Context):
> > '''Represent a task.'''
> >
> > __tablename__ ='task'
> >
> > __mapper_args__ ={
> > 'polymorphic_identity':'task'
> > }
> >
> > # Change this and references to it to 'id' to fix issue.
> > task_id =Column(
> > CHAR(36),
> > ForeignKey('context.id'),
> > primary_key=True
> > )
> >
> > scopes =relationship('Scope',secondary='task_scope')
> >
> >
> > classScope(Base):
> > '''Represent a Scope.'''
> >
> > __tablename__ ='scope'
> >
> > id =Column(CHAR(36),primary_key=True,default=lambda:str(uuid()))
> >
> >
> > classTaskScope(Base):
> > '''Represent a relation between a scope and a task.'''
> >
> > __tablename__ ='task_scope'
> >
> > id =Column(CHAR(36),primary_key=True,default=lambda:str(uuid()))
> >
> > scope_id =Column(CHAR(36),ForeignKey(Scope.id),nullable=False)
> >
> > task_id =Column(CHAR(36),ForeignKey(Task.task_id),nullable=False)
> >
> >
> > defmain():
> > '''Execute test.'''
> > engine =sqlalchemy.create_engine('sqlite://')
> > Base.metadata.create_all(engine)
> > Session=sqlalchemy.orm.sessionmaker(bind=engine)
> >
> > session =Session()
> >
> > task =Task()
> > scope =Scope()
> >
> > session.add(task)
> > session.add(scope)
> > session.commit()
> >
> > link =TaskScope(scope_id=scope.id,task_id=task.task_id)
> > session.add(link)
> > session.commit()
> >
> > query =session.query(Task).options(
> > sqlalchemy.orm.load_only('context_type'),
> > sqlalchemy.orm.joinedload('scopes').load_only()
> > ).limit(10)
> > printquery
> >
> > results =query.all()
> > printresults
> >
> >
> > if__name__ =='__main__':
> > main()
> > |
> >
> > Running the above gives:
> >
> > |
> > sqlalchemy.exc.OperationalError:(sqlite3.OperationalError)nosuch
> > column:task.task_id [SQL:u'SELECT anon_1.context_context_type AS
> > anon_1_context_context_type, anon_1.context_id AS anon_1_context_id,
> > anon_2.scope_1_id AS scope_1_id \nFROM (SELECT context.context_type AS
> > context_context_type, context.id AS context_id \nFROM context JOIN task
> > ON context.id = task.task_id\n LIMIT ? OFFSET ?) AS anon_1 LEFT OUTER
> > JOIN (SELECT task_scope_1.id AS task_scope_1_id, task_scope_1.scope_id
> > AS task_scope_1_scope_id, task_scope_1.task_id AS task_scope_1_task_id,
> > scope_1.id AS scope_1_id \nFROM task_scope AS task_scope_1 JOIN scope
> AS
> > scope_1 ON scope_1.id = task_scope_1.scope_id) AS anon_2 ON
> task.task_id
> > = anon_2.task_scope_1_task_id'][parameters:(10,0)]
> > |
> >
> >
> > If you change 'task_id' to 'id' (and references to it) then the query
> > will now work c
[sqlalchemy] (OperationalError) no such column when child class uses different name for primary key in polymorphic relationship and a subselect issued
Hi there,
Using SQLAlchemy 1.0.9
I am dealing with some legacy code and came across the following issue. It
appears that in a polymorphic relationship SQLAlchemy is not able to
correctly determine what to load for a relationship when using a subselect
(due to limit on query) and child class has a different field name for the
primary key.
Here is a reproducible test case to show the issue better:
from uuid import uuid1 as uuid
import sqlalchemy
import sqlalchemy.orm
from sqlalchemy import (
Column, Unicode, ForeignKey, CHAR, Boolean, UniqueConstraint
)
from sqlalchemy.orm import relationship, backref, synonym
from sqlalchemy.ext.declarative import declarative_base, declared_attr
Base = declarative_base()
class Context(Base):
'''Represent a context.'''
__tablename__ = 'context'
context_type = Column(Unicode(32), nullable=False)
__mapper_args__ = {
'polymorphic_on': context_type,
'polymorphic_identity': 'context'
}
id = Column(CHAR(36), primary_key=True, default=lambda: str(uuid()))
class Task(Context):
'''Represent a task.'''
__tablename__ = 'task'
__mapper_args__ = {
'polymorphic_identity': 'task'
}
# Change this and references to it to 'id' to fix issue.
task_id = Column(
CHAR(36),
ForeignKey('context.id'),
primary_key=True
)
scopes = relationship('Scope', secondary='task_scope')
class Scope(Base):
'''Represent a Scope.'''
__tablename__ = 'scope'
id = Column(CHAR(36), primary_key=True, default=lambda: str(uuid()))
class TaskScope(Base):
'''Represent a relation between a scope and a task.'''
__tablename__ = 'task_scope'
id = Column(CHAR(36), primary_key=True, default=lambda: str(uuid()))
scope_id = Column(CHAR(36), ForeignKey(Scope.id), nullable=False)
task_id = Column(CHAR(36), ForeignKey(Task.task_id), nullable=False)
def main():
'''Execute test.'''
engine = sqlalchemy.create_engine('sqlite://')
Base.metadata.create_all(engine)
Session = sqlalchemy.orm.sessionmaker(bind=engine)
session = Session()
task = Task()
scope = Scope()
session.add(task)
session.add(scope)
session.commit()
link = TaskScope(scope_id=scope.id, task_id=task.task_id)
session.add(link)
session.commit()
query = session.query(Task).options(
sqlalchemy.orm.load_only('context_type'),
sqlalchemy.orm.joinedload('scopes').load_only()
).limit(10)
print query
results = query.all()
print results
if __name__ == '__main__':
main()
Running the above gives:
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column:
task.task_id [SQL: u'SELECT anon_1.context_context_type AS
anon_1_context_context_type, anon_1.context_id AS anon_1_context_id,
anon_2.scope_1_id AS scope_1_id \nFROM (SELECT context.context_type AS
context_context_type, context.id AS context_id \nFROM context JOIN task ON
context.id = task.task_id\n LIMIT ? OFFSET ?) AS anon_1 LEFT OUTER JOIN
(SELECT task_scope_1.id AS task_scope_1_id, task_scope_1.scope_id AS
task_scope_1_scope_id, task_scope_1.task_id AS task_scope_1_task_id,
scope_1.id AS scope_1_id \nFROM task_scope AS task_scope_1 JOIN scope AS
scope_1 ON scope_1.id = task_scope_1.scope_id) AS anon_2 ON task.task_id =
anon_2.task_scope_1_task_id'] [parameters: (10, 0)]
If you change 'task_id' to 'id' (and references to it) then the query will
now work correctly.
As I mentioned this is legacy code so there are many references to task_id
throughout the code base unfortunately. I have found a workaround by using
a synonym, but wanted to report the issue to see if the failure is expected
and there is a requirement for the primary key field names to match or not.
cheers,
Martin
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Access foreign key value after collection modification before flush
Hello,
I'm curious about the behaviour of foreign keys when modifying collections
as I want to be able to get the value before flush for some business logic
validation.
The following is a cut down version of the association list example:
import uuid
from sqlalchemy import Column, ForeignKey, Integer, String, create_engine
from sqlalchemy.orm import Session, relationship, backref
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class TreeNode(Base):
__tablename__ = 'tree'
id = Column(String(36), primary_key=True)
parent_id = Column(Integer, ForeignKey(id))
children = relationship(
'TreeNode', backref=backref('parent', remote_side=id)
)
def __init__(self, id=None, *args, **kwargs):
if id is None:
id = str(uuid.uuid4())
self.id = id
super(TreeNode, self).__init__(*args, **kwargs)
if __name__ == '__main__':
engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
session = Session(engine)
node = TreeNode()
session.add(node)
print node.id # some uuid
child_node = TreeNode()
node.children.append(child_node)
# Question: Why is parent_id not set to node.id even though it was
available?
print child_node.parent_id
Note that the child_node.parent_id is None even though the referenced value
was present in the session. I know a flush will fix this, but I want to
check this value is not None for certain 'types' of node before flush if
possible.
Is there a way to configure that behaviour or should I be creating my own
collection listeners to handle this?
cheers,
Martin
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Custom Comparator with order_by
Hi there, Quick question - is it possible to get a custom comparator to work with order_by? At present my code based on SA/examples/vertical/dictlike- polymorphic.py raises a NotImplementedError when attempting to use the value in an order_by statement. ta, Martin --- q = (session.query(Animal). filter(Animal.facts.any(AnimalFact.value == 5)).order_by(AnimalFact.value)) print 'any animal with a .value of 5', q.all() raise NotImplementedError(%r % self) NotImplementedError: __main__.Comparator object at 0x12710b0 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Merge fails with ComparableProperty
Thanks as always for the quick response and fix. As a slight aside; I am using a MapperExtension to create an attribute on an object. When the object is stored, reloaded and then merged back into a session this attribute is no longer available. Any thoughts on why this might be? ta Martin Pseudo code: == class EntityMapperExtension(MapperExtension): def populate_instance(self, mapper, selectcontext, row, instance, **flags): if not hasattr(instance, 'property'): instance.property = PropertyDict(instance) return EXT_CONTINUE obj = Entity() obj.property['group'] [prop1, prop2] store[id] = obj Session.remove() ... session = Session() obj = session.merge(store[id]) obj.property['group'] AttributeError: 'Entity' object has no attribute 'property' --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Merge fails with ComparableProperty
Hi there,
In SA 0.4.6 I get the following failure when attempting to merge an
object that contains a ComparableProperty:
prop.merge(self, instance, merged, dont_load, _recursive)
TypeError: merge() takes exactly 4 arguments (6 given)
Is this something I am doing wrong or a bug?
ta,
Martin
---
import sqlalchemy
from sqlalchemy import Column, Table, types
from sqlalchemy.orm import relation, comparable_property
from sqlalchemy.orm.interfaces import PropComparator
engine = sqlalchemy.create_engine('sqlite:///', echo=False)
metadata = sqlalchemy.MetaData()
metadata.bind = engine
Session =
sqlalchemy.orm.scoped_session(sqlalchemy.orm.sessionmaker(autoflush=True,
transactional=True, bind=engine))
mapper = Session.mapper
class PolymorphicProperty(object):
class Comparator(PropComparator):
def __eq__(self, other):
return self._value == other._value
def __init__(self, key=None, value=None):
self.key = key
self.value = value
def _getValue(self):
return self._value
def _setValue(self, value):
setattr(self, '_value', value)
value = property(_getValue, _setValue)
def __str__(self):
return '%s:%s %s=%s' % (self.__class__.__name__, self.id,
self.key, self.value)
table = Table('polymorphicProperties', metadata,
Column('id', types.Integer, primary_key=True),
Column('key', types.String(20), nullable=False,
default='keyword'),
Column('_value', types.Text, default=None),
)
mapper(PolymorphicProperty,
table,
properties={
'value':
comparable_property(PolymorphicProperty.Comparator,
PolymorphicProperty.value),
}
)
metadata.create_all(engine)
sess = Session()
sess.begin()
prop = PolymorphicProperty(key='name', value='Bob')
sess.commit()
print prop
sess.expunge(prop)
Session.remove()
sess = Session()
# This Fails
sess.merge(prop)
Session.remove()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Merge fails with ComparableProperty
Upgraded to 0.5 trunk and all works fine now. Thank you again for your help. Martin On Jun 3, 5:23 pm, Michael Bayer [EMAIL PROTECTED] wrote: merge() makes a copy of the object in a similar manner as which it would be loaded from the DB for the first time, using __new__ instead of __init__ and assigning state according to the available MapperPropertys. Since your state relies upon extra steps in populate_instance, theres no corresponding hook within merge(). 0.5 has a better hook than populate_instance for post-load activities which is consistently called whenever an instance is reconsituted. On Jun 3, 2008, at 11:08 AM, Martin Pengelly-Phillips wrote: Thanks as always for the quick response and fix. As a slight aside; I am using a MapperExtension to create an attribute on an object. When the object is stored, reloaded and then merged back into a session this attribute is no longer available. Any thoughts on why this might be? ta Martin Pseudo code: == class EntityMapperExtension(MapperExtension): def populate_instance(self, mapper, selectcontext, row, instance, **flags): if not hasattr(instance, 'property'): instance.property = PropertyDict(instance) return EXT_CONTINUE obj = Entity() obj.property['group'] [prop1, prop2] store[id] = obj Session.remove() ... session = Session() obj = session.merge(store[id]) obj.property['group'] AttributeError: 'Entity' object has no attribute 'property' --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Merge fails with ComparableProperty
Hmmm...I read your last mail as populate_instance works better in 0.5, but looking again I see that wasn't what you meant. As it is I just left my code as is and after upgrading to 0.5 it just worked... On Jun 3, 7:51 pm, Michael Bayer [EMAIL PROTECTED] wrote: er, what's the reconsistute hook you're using ? we havent made a public API for it yet. we're discussing it on ticket #1070 since I'd like to get one in there for you. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Custom comparator with in_ (and subqueries)
Hello again, I have implemented the in_() method which works great for all the 'normal' fields, but throws recursion errors when trying to deal with the relation to another entity. Cutting it down to one line: # print session.query(PolymorphicProperty).filter(PolymorphicProperty.entity.in_([entity1])).all() ... File sqlalchemy/orm/properties.py, line 311, in operate return op(self, *other, **kwargs) File sqlalchemy/sql/operators.py, line 47, in in_op return a.in_(b) File sqlalchemy/sql/expression.py, line 1242, in in_ return self.operate(operators.in_op, other) File sqlalchemy/orm/properties.py, line 311, in operate return op(self, *other, **kwargs) RuntimeError: maximum recursion depth exceeded Obviously I could use # print session.query(PolymorphicProperty).filter(PolymorphicProperty.entity_id.in_([entity1.id])).all() but I thought it worth checking if the first case should work. ta, Martin On May 27, 9:09 pm, Michael Bayer [EMAIL PROTECTED] wrote: On May 27, 2008, at 3:11 PM, Martin Pengelly-Phillips wrote: Hi Michael, Thank you for the quick response. I had thought about using a straightforward OR statement - are you suggesting that this would form the body of the in_() method on the Comparator or were you referring more to just compiling the OR statements in the base query? i was saying the result of in_() would be a construct like: or_(*[col1.in_([x,y,z]), col2.in_([d, e, f]), ...]) Also, what is the correct expression for defining a subquery with the ORM interface (I gather my embedded session.query statement currently evaluates as a separate statement to return the list of candidates). any select() constituites a subquery when placed wihtin an enclosing select(). Often its a good idea to further enclose it in an Alias construct by saying select().alias(). Read through the SQL expression tutorial for examples. the subquery() method on Query wouldn't be used inside of a Comparator since theres no Query object available in those methods and its an overly heavy-handed approach at that level. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Custom comparator with in_ (and subqueries)
Ah, apologies Michael - I should have mentioned that I am using the svn 0.5 checkout. In 0.4 it does raise NotImplementedError. In 0.5 it causes a recursion error. I understand that the working copy will be more susceptible to bugs etc so please take this as a note rather than a major issue. In the meantime I am just checking against the id field instead. On May 28, 3:33 pm, Michael Bayer [EMAIL PROTECTED] wrote: in_() is not normally implemented for relation()s. I think the recursion overflow on in_() was a bug at some point which has since been fixed (and it raises NotImplementedError instead), but I'm not able to isolate at the moment if it was fixed for the 0.4 series or not. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Custom comparator with in_ (and subqueries)
Hi Michael,
Thank you for the quick response. I had thought about using a
straightforward OR statement - are you suggesting that this would form
the body of the in_() method on the Comparator or were you referring
more to just compiling the OR statements in the base query?
Also, what is the correct expression for defining a subquery with the
ORM interface (I gather my embedded session.query statement currently
evaluates as a separate statement to return the list of candidates).
ta,
Martin
On May 27, 7:33 pm, Michael Bayer [EMAIL PROTECTED] wrote:
at its most simplistic level, IN is just a bunch of X=Y joined
together via OR. Can that approach work here ? (seems like it would
be the best way considering its polymorphic scalar elements being
compared). At a higher level, seems like you'd want to group elements
of common types together, and produce a list of the form (type1 IN
(list of type 1s) OR type2 IN (list of type 2s) ).
On May 27, 2008, at 2:25 PM, Martin Pengelly-Phillips wrote:
Hi there,
Just came back to some code after a bit of a break and can't seem to
get my head around how to correctly use the in_ method with a custom
comparator and also the orm subquery functionality.
See attached file for an outline of my approach.
You will see that I am stuck on how to correctly form the last query:
Roughly: select all entities with property (key='association' and
value IN (subquery))
It looks like I need to define an in_() method on my custom
comparator and then possibly use the orm subquery() method for the
er...subquery.
It all feels like it should be straight forward, but the gears just
aren't turning at the moment so any pointers would be greatly
appreciated.
ta,
Martin
import sys
from datetime import datetime
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.orm.interfaces import *
# Database
engine = create_engine('sqlite:///')
metadata = MetaData()
Session = scoped_session(sessionmaker(autoflush=True,
transactional=False, bind=engine))
mapper = Session.mapper
# Class: Entity
#--
class Entity(object):
def __init__(self, properties=None):
self.properties = []
if properties is not None:
self.properties.extend(properties)
# Table: entities
#--
entityTable = Table('entities', metadata,
Column('id', Integer, primary_key=True),
Column('_entityType', String(30), nullable=False),
)
# Table: entitiesPolymorphicProperties
#--
entityPolymorphicPropertyTable =
Table('entitiesPolymorphicProperties', metadata,
Column('entity_id', Integer,
ForeignKey('entities.id')),
Column('polymorphicProperty_id', Integer,
ForeignKey('polymorphicProperties.id')),
)
# Class: PolymorphicProperty
#--
class PolymorphicProperty(object):
typeMap = {
str: ('text','text'),
int: ('integer','integer'),
float: ('float','float'),
datetime: ('datetime','datetime'),
Entity: ('entity','entity'),
type(None): (None, None)
}
def __init__(self, key=None, value=None):
self.key = key
self.value = value
def _getValue(self):
for discriminator, field in self.typeMap.values():
if self.type == discriminator:
return getattr(self, field)
return None
def _setValue(self, value):
# Check type of value can be handled
pyType = type(value)
if pyType not in self.typeMap:
if isinstance(value, Entity):
pyType = Entity
else:
raise TypeError(pyType)
# Set real values and current type of property
# Resets all real values no longer matching type to None
for fieldType in self.typeMap:
discriminator, field = self.typeMap[fieldType]
fieldValue = None
if pyType == fieldType:
self.type = discriminator
fieldValue = value
if field is not None:
setattr(self, field, fieldValue)
def _delValue(self, value):
self._set_value(None)
value = property(_getValue, _setValue, _delValue, doc='''Logical
value of this property''')
# Class: Comparator
#--
class Comparator(PropComparator):
castableMap = {
int: (Integer, Text, Float),
str: (Text,),
float: (Float, Text),
datetime: (DateTime, Text),
type(None
[sqlalchemy] Re: Custom comparator with in_ (and subqueries)
That makes sense - thanks again. Martin On May 27, 9:09 pm, Michael Bayer [EMAIL PROTECTED] wrote: On May 27, 2008, at 3:11 PM, Martin Pengelly-Phillips wrote: Hi Michael, Thank you for the quick response. I had thought about using a straightforward OR statement - are you suggesting that this would form the body of the in_() method on the Comparator or were you referring more to just compiling the OR statements in the base query? i was saying the result of in_() would be a construct like: or_(*[col1.in_([x,y,z]), col2.in_([d, e, f]), ...]) Also, what is the correct expression for defining a subquery with the ORM interface (I gather my embedded session.query statement currently evaluates as a separate statement to return the list of candidates). any select() constituites a subquery when placed wihtin an enclosing select(). Often its a good idea to further enclose it in an Alias construct by saying select().alias(). Read through the SQL expression tutorial for examples. the subquery() method on Query wouldn't be used inside of a Comparator since theres no Query object available in those methods and its an overly heavy-handed approach at that level. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Many to many self referential relationship.
Hi Michael, As expected the trunk works perfectly. I am also looking into the comparator example you pointed to as this looks like it will help with a couple of other implementation details as well. Thanks again, Martin On Feb 20, 10:25 pm, Martin Pengelly-Phillips [EMAIL PROTECTED] wrote: Thank you Michael - it's always good to get some validation when pushing your own knowledge of a system. I'll check out the trunk tomorrow, give it a whirl and report any findings then. Thanks again for the quick response, Martin On Feb 20, 6:31 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Feb 20, 2008, at 1:01 PM, Martin Pengelly-Phillips wrote: I have attempted to solve this by using the concept of 'forwardAssociations' with a backref of 'backwardAssociations' and then a custom property 'associations' that retrieves and sets the real attributes accordingly (let me know if you would like to see this code as well). However, even with this set up I find it hard to figure out how to then perform a query such as: # Get all entities that are in some way associated with entity 2 (without using entity2.associations property) session.query(Entity).filter(or_( Entity.forwardAssociations.any(Entity.id==2), Entity.backwardAssociations.any(Entity.id==2) )).all() The query this generates though uses the same id for both sides of the association table which cannot therefore result in a match: entities.id = entitiesEntities.entity1_id AND entitiesEntities.entity2_id = entities.id AND entities.id = ? well i think everything you're doing is on the right track. The any() operator and its friends have just been repaired in the latest trunk to recognize a self-referential relation and apply the appropriate aliasing to half of the join condition. I haven't tested it with a many-to-many self referential yet but I think it should work. give it a try and let me know what you get; I can add some m2m tests for it and ensure that its working if its not already. we also are working on getting better support for user-defined custom properties going such that they can be seamlessly used within Queries, so that you could also construct your query using your associations property, if you can define how comparison operations should be done. There is a way to do this right now using a slight bit of non-public API, where you can see an example of such inhttp://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/examples/vert... ; the comparable_property allows the injection of a Comparator object from where you can define things like __eq__(), any(), etc. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Many to many self referential relationship.
Thank you Michael - it's always good to get some validation when pushing your own knowledge of a system. I'll check out the trunk tomorrow, give it a whirl and report any findings then. Thanks again for the quick response, Martin On Feb 20, 6:31 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Feb 20, 2008, at 1:01 PM, Martin Pengelly-Phillips wrote: I have attempted to solve this by using the concept of 'forwardAssociations' with a backref of 'backwardAssociations' and then a custom property 'associations' that retrieves and sets the real attributes accordingly (let me know if you would like to see this code as well). However, even with this set up I find it hard to figure out how to then perform a query such as: # Get all entities that are in some way associated with entity 2 (without using entity2.associations property) session.query(Entity).filter(or_( Entity.forwardAssociations.any(Entity.id==2), Entity.backwardAssociations.any(Entity.id==2) )).all() The query this generates though uses the same id for both sides of the association table which cannot therefore result in a match: entities.id = entitiesEntities.entity1_id AND entitiesEntities.entity2_id = entities.id AND entities.id = ? well i think everything you're doing is on the right track. The any() operator and its friends have just been repaired in the latest trunk to recognize a self-referential relation and apply the appropriate aliasing to half of the join condition. I haven't tested it with a many-to-many self referential yet but I think it should work. give it a try and let me know what you get; I can add some m2m tests for it and ensure that its working if its not already. we also are working on getting better support for user-defined custom properties going such that they can be seamlessly used within Queries, so that you could also construct your query using your associations property, if you can define how comparison operations should be done. There is a way to do this right now using a slight bit of non-public API, where you can see an example of such inhttp://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/examples/vert... ; the comparable_property allows the injection of a Comparator object from where you can define things like __eq__(), any(), etc. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Slow relation based assignment.
hey mike, Just to confirm - trunk fixes problem with deletion. Additionally, I have removed the lazy loading condition and it maintains the speed of the query. Thanks again to the team, Martin On Dec 7, 4:14 pm, Michael Bayer [EMAIL PROTECTED] wrote: hey martin - this bug is fixed in trunk r3868, so if you use the svn trunk you can either keep using the dynamic or go back to the regular relation, you should be good in both cases. - mike --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Slow relation based assignment.
Hello again Michael,
Have read the documentation you referenced, but am still unsure how to
now delete a Tag without generating the following error: (Note - using
Postgres in production)
(IntegrityError) update or delete on tags violates foreign key
constraint employeesTags_tag_id_fkey on employeesTags
DETAIL: Key (id)=(3) is still referenced from table employeesTags.
'DELETE FROM tags WHERE tags.id = %(id)s' {'id': 3}
Without the lazy='dynamic' it works fine (correctly deletes entries
from employeesTags first).
The delete operation I am performing is:
session.begin()
entry = session.query(Tag).filter_by(id=3).first()
try:
session.delete(entry)
session.commit()
except Exception, error:
print error
session.rollback()
else:
print 'Deleted successfully'
Thanks again for all your help so far,
Martin
On Dec 5, 5:27 pm, Michael Bayer [EMAIL PROTECTED] wrote:
hi martin -
the issue is that each Tag object contains a collection of 1000
employees on it, and when you make an assignment in the forwards
direction (i.e. employee.tag.append(sometag)), the corresponding
reverse relation needs to be fully loaded and then updated according
to backref semantics. since you're using eager loading by default
between employees and tags, there is a load of 20,000 rows each time
an uninitialized tags.employees collection is touched.
To prevent the backref from being unnecessarily loaded, and since it
is a large collection, you should use a dynamic collection for the
reverse:
mapper(Employee, employees, properties={
'tags': relation(Tag,
secondary=employeesTags,backref=backref('employees', lazy='dynamic'),
lazy=False)
})
mapper(Tag, tags)
the employees collection on Tag is now a filterable Query object
which only queries when read from, and you'll see that the time goes
down to nothing. you can also append and delete from a dynamic
collection like a regular list.
large collection techniques are discussed
at:http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relatio...
we do have a ticket in trac to try improving upon backrefs to not load
unloaded collections in any case, this is ticket #871.
On Dec 5, 12:07 pm, Martin Pengelly-Phillips
[EMAIL PROTECTED] wrote:
Hello again,
I have recently noticed that a particular assignment seems to be
taking a relatively long time.
Not being a database expert I am confused as to whether the last
assignment 'person.tags = tags' should be so slow when referencing
existing tags that are used by other entities - it seems to try and
get a list of all employees that use the given tag and then spends the
time doing something with the resulting set, but why?
Test case below.
If the slow assignment is expected do you have any advice on how to
speed up such a statement?
Thank you in advance,
Martin
--
import os, datetime, time
from sqlalchemy import *
from sqlalchemy.orm import *
file = '/tmp/test.db'
if os.path.isfile(file): os.remove(file)
engine = create_engine('sqlite:///%s' % file, echo=True)
metadata = MetaData()
Session = scoped_session(sessionmaker(autoflush=True,
transactional=False, bind=engine))
mapper = Session.mapper
# Classes
#--
class Employee(object):
def __init__(self, name=None):
self.name = name
def __repr__(self):
return '%s:%s' % (self.id, self.name)
class Tag(object):
def __init__(self, label):
self.label = label
# Setup tables
#--
employees = Table('employees', metadata,
Column('id', Integer, primary_key=True),
Column('name', String, nullable=False,
default='bob'),
Column('dob', DateTime, nullable=False,
default=datetime.datetime.now),
)
tags = Table('tags', metadata,
Column('id', Integer, primary_key=True),
Column('label', String, nullable=False),
)
employeesTags = Table('employeesTags', metadata,
Column('employee_id', Integer,
ForeignKey('employees.id')),
Column('tag_id', Integer,
ForeignKey('tags.id')),
)
# Mappers
#--
mapper(Employee, employees, properties={
'tags': relation(Tag, secondary=employeesTags,
backref='employees', lazy=False)})
mapper(Tag, tags)
# Test
#--
metadata.create_all(engine)
session = Session()
session.begin()
tags = []
for i in xrange(20):
tag = Tag(str(datetime.datetime.now()))
tags.append(tag)
for i in xrange(1000):
p = Employee('john%d' % i)
p.tags = tags
session.commit()
session.clear()
session.begin
[sqlalchemy] Re: Slow relation based assignment.
hey Mike, Thanks for the update - I'll try it out tomorrow. Martin p.s. Have I mentioned you guys provide the best support I have encountered in a long time (including commercial products). On Dec 7, 4:14 pm, Michael Bayer [EMAIL PROTECTED] wrote: hey martin - this bug is fixed in trunk r3868, so if you use the svn trunk you can either keep using the dynamic or go back to the regular relation, you should be good in both cases. - mike --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Slow relation based assignment.
Thank you Michael - I had completely missed the backref full load.
On Dec 5, 5:27 pm, Michael Bayer [EMAIL PROTECTED] wrote:
hi martin -
the issue is that each Tag object contains a collection of 1000
employees on it, and when you make an assignment in the forwards
direction (i.e. employee.tag.append(sometag)), the corresponding
reverse relation needs to be fully loaded and then updated according
to backref semantics. since you're using eager loading by default
between employees and tags, there is a load of 20,000 rows each time
an uninitialized tags.employees collection is touched.
To prevent the backref from being unnecessarily loaded, and since it
is a large collection, you should use a dynamic collection for the
reverse:
mapper(Employee, employees, properties={
'tags': relation(Tag,
secondary=employeesTags,backref=backref('employees', lazy='dynamic'),
lazy=False)
})
mapper(Tag, tags)
the employees collection on Tag is now a filterable Query object
which only queries when read from, and you'll see that the time goes
down to nothing. you can also append and delete from a dynamic
collection like a regular list.
large collection techniques are discussed
at:http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relatio...
we do have a ticket in trac to try improving upon backrefs to not load
unloaded collections in any case, this is ticket #871.
On Dec 5, 12:07 pm, Martin Pengelly-Phillips
[EMAIL PROTECTED] wrote:
Hello again,
I have recently noticed that a particular assignment seems to be
taking a relatively long time.
Not being a database expert I am confused as to whether the last
assignment 'person.tags = tags' should be so slow when referencing
existing tags that are used by other entities - it seems to try and
get a list of all employees that use the given tag and then spends the
time doing something with the resulting set, but why?
Test case below.
If the slow assignment is expected do you have any advice on how to
speed up such a statement?
Thank you in advance,
Martin
--
import os, datetime, time
from sqlalchemy import *
from sqlalchemy.orm import *
file = '/tmp/test.db'
if os.path.isfile(file): os.remove(file)
engine = create_engine('sqlite:///%s' % file, echo=True)
metadata = MetaData()
Session = scoped_session(sessionmaker(autoflush=True,
transactional=False, bind=engine))
mapper = Session.mapper
# Classes
#--
class Employee(object):
def __init__(self, name=None):
self.name = name
def __repr__(self):
return '%s:%s' % (self.id, self.name)
class Tag(object):
def __init__(self, label):
self.label = label
# Setup tables
#--
employees = Table('employees', metadata,
Column('id', Integer, primary_key=True),
Column('name', String, nullable=False,
default='bob'),
Column('dob', DateTime, nullable=False,
default=datetime.datetime.now),
)
tags = Table('tags', metadata,
Column('id', Integer, primary_key=True),
Column('label', String, nullable=False),
)
employeesTags = Table('employeesTags', metadata,
Column('employee_id', Integer,
ForeignKey('employees.id')),
Column('tag_id', Integer,
ForeignKey('tags.id')),
)
# Mappers
#--
mapper(Employee, employees, properties={
'tags': relation(Tag, secondary=employeesTags,
backref='employees', lazy=False)})
mapper(Tag, tags)
# Test
#--
metadata.create_all(engine)
session = Session()
session.begin()
tags = []
for i in xrange(20):
tag = Tag(str(datetime.datetime.now()))
tags.append(tag)
for i in xrange(1000):
p = Employee('john%d' % i)
p.tags = tags
session.commit()
session.clear()
session.begin()
tags = session.query(Tag).all()[:2]
person = Employee('bob')
started = time.time()
person.tags = tags
print 'Took:', time.time()-started
session.commit()
session.clear()
Session.remove()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Slow relation based assignment.
Hello again,
I have recently noticed that a particular assignment seems to be
taking a relatively long time.
Not being a database expert I am confused as to whether the last
assignment 'person.tags = tags' should be so slow when referencing
existing tags that are used by other entities - it seems to try and
get a list of all employees that use the given tag and then spends the
time doing something with the resulting set, but why?
Test case below.
If the slow assignment is expected do you have any advice on how to
speed up such a statement?
Thank you in advance,
Martin
--
import os, datetime, time
from sqlalchemy import *
from sqlalchemy.orm import *
file = '/tmp/test.db'
if os.path.isfile(file): os.remove(file)
engine = create_engine('sqlite:///%s' % file, echo=True)
metadata = MetaData()
Session = scoped_session(sessionmaker(autoflush=True,
transactional=False, bind=engine))
mapper = Session.mapper
# Classes
#--
class Employee(object):
def __init__(self, name=None):
self.name = name
def __repr__(self):
return '%s:%s' % (self.id, self.name)
class Tag(object):
def __init__(self, label):
self.label = label
# Setup tables
#--
employees = Table('employees', metadata,
Column('id', Integer, primary_key=True),
Column('name', String, nullable=False,
default='bob'),
Column('dob', DateTime, nullable=False,
default=datetime.datetime.now),
)
tags = Table('tags', metadata,
Column('id', Integer, primary_key=True),
Column('label', String, nullable=False),
)
employeesTags = Table('employeesTags', metadata,
Column('employee_id', Integer,
ForeignKey('employees.id')),
Column('tag_id', Integer,
ForeignKey('tags.id')),
)
# Mappers
#--
mapper(Employee, employees, properties={
'tags': relation(Tag, secondary=employeesTags,
backref='employees', lazy=False)
})
mapper(Tag, tags)
# Test
#--
metadata.create_all(engine)
session = Session()
session.begin()
tags = []
for i in xrange(20):
tag = Tag(str(datetime.datetime.now()))
tags.append(tag)
for i in xrange(1000):
p = Employee('john%d' % i)
p.tags = tags
session.commit()
session.clear()
session.begin()
tags = session.query(Tag).all()[:2]
person = Employee('bob')
started = time.time()
person.tags = tags
print 'Took:', time.time()-started
session.commit()
session.clear()
Session.remove()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Inheritance and eager loading - is contains_eager the answer?
Hi there,
I have been using sqlalchemy for the past month with great success.
However, recently I came across an unexpected situation. Essentially
the results I was retrieving from the database via an ORM query were
not including all the related attributes despite indicating eager
loading on the mapper using lazy=False.
I have created an example script (below) to highlight the results I
was getting. Note that after a bit of reading I discovered I could use
contains_eager() on the query to obtain the correct result, but I am
not sure if indeed this is the correct solution or if there is a flaw
in my setup/understanding of the system.
As I see it from the SQL the first query is placing a limit 1 at the
end of the full statement rather than as part of a subselect causing
only 1 Tag to be retrieved with the Employee rather than the expected
2.
ta,
Martin
p.s. Apologies if the code displays badly - is there a format
indicator?
===
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite:tmp/example.db', echo=True)
metadata = MetaData()
Session = scoped_session(sessionmaker(autoflush=True,
transactional=True, bind=engine))
mapper = Session.mapper
# Person
#--
class Person(object):
def __init__(self):
self.id = None
class Employee(Person):
def __init__(self, name='bob'):
self.name = name
class Tag(object):
def __init__(self, label):
self.id = None
self.label = label
# Setup tables
#--
people = Table('people', metadata,
Column('id', Integer, primary_key=True),
Column('_type', String(30), nullable=False),
)
employees = Table('employees', metadata,
Column('id', Integer, ForeignKey('people.id'),
primary_key=True),
)
tags = Table('tags', metadata,
Column('id', Integer, primary_key=True),
Column('label', String, nullable=False),
)
peopleTags = Table('peopleTags', metadata,
Column('person_id', Integer,
ForeignKey('people.id')),
Column('tag_id', Integer,
ForeignKey('tags.id')),
)
# Mappers
#--
mapper(Person, people, polymorphic_on=people.c._type,
polymorphic_identity='person')
mapper(Employee, employees, inherits=Person,
polymorphic_identity='employee')
mapper(Tag, tags)
_mapper = class_mapper(Person)
_table = _mapper.local_table
_mapper.add_property('tags', relation(Tag, secondary=peopleTags,
backref='people', lazy=False))
metadata.create_all(engine)
# Test
#--
session = Session()
bob = Employee()
session.save(bob)
tag = Tag('crazy')
session.save(tag)
bob.tags.append(tag)
tag = Tag('funny')
session.save(tag)
bob.tags.append(tag)
session.commit()
session.clear()
instance = session.query(Employee).filter_by(id=1).first()
print instance, instance.tags
session.clear()
instance =
session.query(Employee).options(contains_eager('tags')).filter_by(id=1).first()
print instance, instance.tags
Session.remove()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Inheritance and eager loading - is contains_eager the answer?
Ah, thanks for the quick response Mike - I'll keep an eye out for the fix. Martin On Nov 29, 3:05 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Nov 29, 2007, at 7:54 AM, Martin Pengelly-Phillips wrote: Hi there, I have been using sqlalchemy for the past month with great success. However, recently I came across an unexpected situation. Essentially the results I was retrieving from the database via an ORM query were not including all the related attributes despite indicating eager loading on the mapper using lazy=False. I have created an example script (below) to highlight the results I was getting. Note that after a bit of reading I discovered I could use contains_eager() on the query to obtain the correct result, but I am not sure if indeed this is the correct solution or if there is a flaw in my setup/understanding of the system. As I see it from the SQL the first query is placing a limit 1 at the end of the full statement rather than as part of a subselect causing only 1 Tag to be retrieved with the Employee rather than the expected 2. hey Martin - that is completely a bug in SA. the query in the first test is not detecting that it needs to apply eager compilation rules when clearly it should. Ill have it analyzed and fixed today. - mike --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
