[sqlalchemy] Re: SQLAlchemy 0.6.4 Released
Thanks for the great release :) On 7 sep., 19:40, Michael Bayer mike...@zzzcomputing.com wrote: SQLAlchemy 0.6.4 is now available, with a significantly large number of enhancements and fixes. A large focus of this release is on clarification and ease of use, including: - the largest documentation reorganization since we moved to Sphinx, with an emphasis on one-location-per-concept. no more jumping around to the API Reference to find things. - revamp of the error messages when dealing with primaryjoin issues, more forgiving behavior when it's clear what the user intent was - more warnings for known configurational mistakes with the ORM Other changes include: - an up to 90% call reduction within mapper.py when flushing highly polymorphic structures - Fixed psycopg2 isolation mode setting - lots of ORM / SQL / engine fixes I am always amazed at how fast CHANGES grows from the previous release, only around 6 weeks ago. Download SQLAlchemy 0.6.4 at: http://www.sqlalchemy.org/download.html 0.6.4 = - orm - The name ConcurrentModificationError has been changed to StaleDataError, and descriptive error messages have been revised to reflect exactly what the issue is. Both names will remain available for the forseeable future for schemes that may be specifying ConcurrentModificationError in an except: clause. - Added a mutex to the identity map which mutexes remove operations against iteration methods, which now pre-buffer before returning an iterable. This because asyncrhonous gc can remove items via the gc thread at any time. [ticket:1891] - The Session class is now present in sqlalchemy.orm.*. We're moving away from the usage of create_session(), which has non-standard defaults, for those situations where a one-step Session constructor is desired. Most users should stick with sessionmaker() for general use, however. - query.with_parent() now accepts transient objects and will use the non-persistent values of their pk/fk attributes in order to formulate the criterion. Docs are also clarified as to the purpose of with_parent(). - The include_properties and exclude_properties arguments to mapper() now accept Column objects as members in addition to strings. This so that same-named Column objects, such as those within a join(), can be disambiguated. - A warning is now emitted if a mapper is created against a join or other single selectable that includes multiple columns with the same name in its .c. collection, and those columns aren't explictly named as part of the same or separate attributes (or excluded). In 0.7 this warning will be an exception. Note that this warning is not emitted when the combination occurs as a result of inheritance, so that attributes still allow being overridden naturally. [ticket:1896]. In 0.7 this will be improved further. - The primary_key argument to mapper() can now specify a series of columns that are only a subset of the calculated primary key columns of the mapped selectable, without an error being raised. This helps for situations where a selectable's effective primary key is simpler than the number of columns in the selectable that are actually marked as primary_key, such as a join against two tables on their primary key columns [ticket:1896]. - An object that's been deleted now gets a flag 'deleted', which prohibits the object from being re-add()ed to the session, as previously the object would live in the identity map silently until its attributes were accessed. The make_transient() function now resets this flag along with the key flag. - make_transient() can be safely called on an already transient instance. - a warning is emitted in mapper() if the polymorphic_on column is not present either in direct or derived form in the mapped selectable or in the with_polymorphic selectable, instead of silently ignoring it. Look for this to become an exception in 0.7. - Another pass through the series of error messages emitted when relationship() is configured with ambiguous arguments. The foreign_keys setting is no longer mentioned, as it is almost never needed and it is preferable users set up correct ForeignKey metadata, which is now the recommendation. If 'foreign_keys' is used and is incorrect, the message suggests the attribute is probably unnecessary. Docs for the attribute are beefed up. This because all confused relationship() users on the ML appear to be attempting to use foreign_keys due to the message, which only confuses them further since Table metadata is much clearer. - If the secondary table has no ForeignKey metadata and no
[sqlalchemy] hierarchical data storage and searching
Hi All, I'm trying to solve a hierarchical access control problem, both on the storage and querying side. So, say I have a tree of content: / /a/ /a/1 /a/2 /b/ /b/1 /b/2 I want to be able to express and search on the following types of requirements: User X should be able to access all content in /a and content in /b/1 The storage side just needs to be usable, speed wise, but obviously the query side needs to be lighting fast as it'll be hit *hard* and often. What schema/indexes would people recommend for this? How would I query that schema fast in SQLAlchemy to be able to answer the above type of questions. I guess the API that needs to be fast would be along the lines of: def can_access(user_id,path): return True or False The grant python API would look like: def grant_access(user_id,*paths): ... Any help much appreciated! cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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] Newbie problem with relational mapping
Hi everyone, I'm getting in troubles doing some experiments with
sqlalchemy (0.6.3) orm. Here are two code snippets to show the
problems I'm encountering.
## map_1.py
engine = create_engine(mysql://user:passw...@localhost/mydb)
metadata = MetaData(engine)
parent_table = Table('parent', metadata,
Column('id', Integer, primary_key=True))
child_table = Table('child', metadata,
Column('id', Integer, primary_key=True),
Column('parent_id', Integer, ForeignKey('parent.id')))
metadata.create_all(engine)
engine.execute(parent_table.insert(), [{'id':1}, {'id':2}, {'id':3},
{'id':4}])
engine.execute(child_table.insert(), [{'parent_id':1}, {'parent_id':
2}, {'parent_id':2}, {'parent_id':2}])
## map_2.py
engine = create_engine(mysql://user:passw...@localhost/mydb)
metadata = MetaData(engine)
parent_table = Table(parent, metadata, autoload=True)
child_table = Table(child, metadata, autoload=True)
class Parent(object):
pass
class Child(object):
pass
mapper(Parent, parent_table, properties={'children':
relationship(Child)})
mapper(Child, child_table)
Session = sessionmaker()
Session.configure(bind=engine)
sess = Session()
res = sess.query(Parent).all()
print res[0].children
Everything works fine for map_1.py, but when I run map_2.py I get the
following error:
sqlalchemy.exc.ArgumentError: Could not determine join condition
between parent/child tables on relationship Parent.children. Specify
a 'primaryjoin' expression. If this is a many-to-many relationship,
'secondaryjoin' is needed as well.
Process terminated with an exit code of 1
Can anyone point me out what I am doing wrong? Thanks in advance!
--
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] Combining aliases with labels
When I try to use both aliases and labels, the results are not named
as expected.
Instead of being able to access the columns as label-name_column-
name it appears as original-table-name_numeric-sequence_column-
name
Thanks,
Jack
Sample code follows:
parent = Table('parent', metadata,
Column('id', INTEGER(), primary_key=True),
Column('name', VARCHAR(length=128)),
Column('first_id', INTEGER(),
ForeignKey(u'child.id')),
)
child = Table('child', metadata,
Column('id', INTEGER(), primary_key=True),
Column('name', VARCHAR(length=128))
)
def test_labels1(conn):
s = select([parent,child], use_labels=True)
s = s.where(parent.c.first_id==child.c.id)
return conn.execute(s).fetchone()
def test_alias1(conn):
firstchild = child.alias()
s = select([parent,firstchild], use_labels=True)
s = s.where(parent.c.first_id==firstchild.c.id)
return conn.execute(s).fetchone()
conn = engine.connect()
results = test_labels1(conn)
print results.parent_name
print results.child_name
results = test_alias1(conn)
print 'alias1 results: '
print results.parent_name
#print results.firstchild_name # expected this to work
print results.child_1_name # this worked instead
--
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] Re: hierarchical data storage and searching
Hi Chris, this is more of a relational design question than SQLAlchemy-related, but take a look at this for an at-a-glance summary of different approaches and their pros and cons: http://vadimtropashko.wordpress.com/2008/08/09/one-more-nested-intervals-vs-adjacency-list-comparison/ ... here for some illustration and examples: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html http://communities.bmc.com/communities/docs/DOC-9902 and here for links to more than you really want to know on the subject: http://troels.arvin.dk/db/rdbms/links/#hierarchical SQLAlchemy support any of these approaches well enough that I don't think you need to factor SQLAlchemy into your choice of relational design at all. Check out /examples/nested_sets/ and /examples/ adjacency_list/ (in the SQLAlchemy distribution) for what are probably the two most common approaches. Regards, - Gulli On Sep 8, 8:22 am, Chris Withers ch...@simplistix.co.uk wrote: Hi All, I'm trying to solve a hierarchical access control problem, both on the storage and querying side. So, say I have a tree of content: / /a/ /a/1 /a/2 /b/ /b/1 /b/2 I want to be able to express and search on the following types of requirements: User X should be able to access all content in /a and content in /b/1 The storage side just needs to be usable, speed wise, but obviously the query side needs to be lighting fast as it'll be hit *hard* and often. What schema/indexes would people recommend for this? How would I query that schema fast in SQLAlchemy to be able to answer the above type of questions. I guess the API that needs to be fast would be along the lines of: def can_access(user_id,path): return True or False The grant python API would look like: def grant_access(user_id,*paths): ... Any help much appreciated! cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting -http://www.simplistix.co.uk -- 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] Re: Loading attributes for Transient objects
That is for comparing *Clauses* and handles: locations.siteid = :param_1 and :param_1 = locations.siteid However, locations.siteid = :param_1 AND locations.locationid = :param_2 and locations.locationid = :param_1 AND locations.siteid = :param_2 are ClauseLists, which compares their individual Clauses *in order*: def compare(self, other, **kw): Compare this :class:`ClauseList` to the given :class:`ClauseList`, including a comparison of all the clause items. if not isinstance(other, ClauseList) and len(self.clauses) == 1: return self.clauses[0].compare(other, **kw) elif isinstance(other, ClauseList) and \ len(self.clauses) == len(other.clauses): for i in range(0, len(self.clauses)): if not self.clauses[i].compare(other.clauses[i], **kw): return False else: return self.operator == other.operator else: return False (I know because if I flip the order of the composite ForeignKeyConstraint it no longer calls .get() for the lazyload... it always queries the database, so I checked out why in the debugger) On 9/8/2010 1:27 PM, Michael Bayer wrote: On Sep 8, 2010, at 1:15 PM, Kent Bower wrote: I imagine you are already aware of this... Unfortunately, the clause comparison says these two clauses are different: (Pdb) print self locations.siteid = :param_1 AND locations.locationid = :param_2 (Pdb) print other locations.locationid = :param_1 AND locations.siteid = :param_2 when they are really the equivalent. So composite foreign keys need to be listed in the correct order for LazyLoader.use_get to optimize the load with get(). use clause.compare(). Here's the source for _BinaryExpression: def compare(self, other, **kw): Compare this :class:`_BinaryExpression` against the given :class:`_BinaryExpression`. return ( isinstance(other, _BinaryExpression) and self.operator == other.operator and ( self.left.compare(other.left, **kw) and self.right.compare(other.right, **kw) or ( operators.is_commutative(self.operator) and self.left.compare(other.right, **kw) and self.right.compare(other.left, **kw) ) ) ) see the commutative in there ? its handled. I already saw it would be somewhat of an effort to refactor the clause comparison to work that out... bummer. On 9/7/2010 7:28 PM, Michael Bayer wrote: On Sep 7, 2010, at 6:41 PM, Kent Bower wrote: Two items: * How does the orm currently determine whether it is safe to try get() (e.i. there are no funny join conditions)? If you point me to the function where decision takes place, I can probably answer this myself it compares the join condition of the relationship() to that of the clause which the Mapper uses when it issues get(), then stores that away as a flag for future consultation. It's very unusual for a many-to-one relationship to be based on something other than a simple foreign-key-primary key relationship, though. * When I build up the primary key from the foreign key, is there an efficient way to build a composite key in the correct order to pass to get()? (I thought maybe synchronize_pairs, but that maybe has to do with getting the direction consistent instead?) Well if you aren't using any composite primary keys in many-to-ones, you wouldn't even have to worry about this. Otherwise, the two collections to correlate would be property.local_remote_pairs and property.mapper.primary_key. Perhaps make a dictionary out of dict([(r, l) for l, r in prop.local_remote_pairs]) and your PK value would be [getattr(instance, prop.parent.get_property_by_column(mydict[p]).key) for p in property.mapper.primary_key]. Or if you want to get ambitious you can just copy roughly whats in strategies.py on line 605 but then you're digging into internalsand looking at that now I'm wondering if strategy._equated_columns is really different than local_remote_pairs at all... Thanks again, you've been much help! On 9/7/2010 5:03 PM, Michael Bayer wrote: On Sep 7, 2010, at 4:38 PM, Kent Bower wrote: Don't want to strangle me, but when the orm (lazy)loads a MANYTONE object, it doesn't go to the database if the object is in the session. Can I get with_parent() to behave this way, or would I need to specifically build up the primary key of the related object and call query.get()? the latter. You can use get() for all many to ones if you aren't using any funny join conditions. On 9/7/2010 10:25 AM, Michael Bayer wrote: On Sep 7, 2010, at 10:12 AM, Kent Bower wrote: Mike, in your proof of concept, when __getstate__ detected transient, why did you need to make a copy of self.__dict__?
Re: [sqlalchemy] Re: Loading attributes for Transient objects
I've got a recipe for what will work well for us. I imagine it could be useful for others, although I left out the actual serialization mechanism, since that will likely be very project specific. I'd be happy to put this on the wiki, but if you wanted to look it over first, you are more than welcome (I'd prefer your feedback). If you are busy, I can just post it and hope someone may find it useful. Thank again for your help, Kent On 9/7/2010 7:28 PM, Michael Bayer wrote: On Sep 7, 2010, at 6:41 PM, Kent Bower wrote: Two items: * How does the orm currently determine whether it is safe to try get() (e.i. there are no funny join conditions)? If you point me to the function where decision takes place, I can probably answer this myself it compares the join condition of the relationship() to that of the clause which the Mapper uses when it issues get(), then stores that away as a flag for future consultation. It's very unusual for a many-to-one relationship to be based on something other than a simple foreign-key-primary key relationship, though. * When I build up the primary key from the foreign key, is there an efficient way to build a composite key in the correct order to pass to get()? (I thought maybe synchronize_pairs, but that maybe has to do with getting the direction consistent instead?) Well if you aren't using any composite primary keys in many-to-ones, you wouldn't even have to worry about this. Otherwise, the two collections to correlate would be property.local_remote_pairs and property.mapper.primary_key. Perhaps make a dictionary out of dict([(r, l) for l, r in prop.local_remote_pairs]) and your PK value would be [getattr(instance, prop.parent.get_property_by_column(mydict[p]).key) for p in property.mapper.primary_key]. Or if you want to get ambitious you can just copy roughly whats in strategies.py on line 605 but then you're digging into internalsand looking at that now I'm wondering if strategy._equated_columns is really different than local_remote_pairs at all... Thanks again, you've been much help! On 9/7/2010 5:03 PM, Michael Bayer wrote: On Sep 7, 2010, at 4:38 PM, Kent Bower wrote: Don't want to strangle me, but when the orm (lazy)loads a MANYTONE object, it doesn't go to the database if the object is in the session. Can I get with_parent() to behave this way, or would I need to specifically build up the primary key of the related object and call query.get()? the latter. You can use get() for all many to ones if you aren't using any funny join conditions. On 9/7/2010 10:25 AM, Michael Bayer wrote: On Sep 7, 2010, at 10:12 AM, Kent Bower wrote: Mike, in your proof of concept, when __getstate__ detected transient, why did you need to make a copy of self.__dict__? self.__dict__.copy() i was modifying the __dict__ from what would be expected in a non-serialized object, so that was to leave the original object being serialized unchanged. On 9/6/2010 2:35 PM, Michael Bayer wrote: On Sep 6, 2010, at 2:11 PM, Kent Bower wrote: Also, I was hoping you would tell me whether this would be a candidate for subclassing InstrumentedAttribute? Would that make more sense or providing custom __getstate__ __setstate__ ? __getstate__ / __setstate__ are pretty much what I like to use for pickle stuff, unless some exotic situation makes me have to use __reduce__. One problem with the recipe is that theres no 'deferred' loading of attributes. So in that sense playing with InstrumentedAttribute would give you a chance to put a callable in there that does what you want. There is also the possibility that __setstate__ can load up callables into the instance_state using state.set_callable(). This is a callable that triggers when you access the attribute that is otherwise None. There's a little bit of fanfare required to get that callable to assign to the attribute in the right way. Attached is an example of that. This is all a little more shaky since the state/callable API isn't really public. Hasn't changed for awhile but there's no guarantee. Thanks for your help, hopefully I'll be able to contribute such a recipe. Kent Since sqla won't load that for me in the case of transient, I need to load the relation manually (unless you feel like enhancing that as well). its not an enhancement - it was a broken behavior that was specifically removed. The transient object has no session, so therefore no SQL can be emitted - there's no context established. Now I can manually emulate the obj being persistent with your changes for On Sep 6, 2010, at 10:58 AM, Michael Bayermike...@zzzcomputing.com wrote: On Sep 6, 2010, at 9:06 AM, Kent wrote: with_parent seems to add a join condition. OK, so I guess you read the docs which is why you thought it joined and why you didn't realize it doesn't work for transient. r20b6ce05f194 changes all that so that
Re: [sqlalchemy] Re: Loading attributes for Transient objects
On Sep 8, 2010, at 2:34 PM, Kent Bower wrote: That is for comparing *Clauses* and handles: locations.siteid = :param_1 and :param_1 = locations.siteid However, locations.siteid = :param_1 AND locations.locationid = :param_2 and locations.locationid = :param_1 AND locations.siteid = :param_2 are ClauseLists, which compares their individual Clauses *in order*: def compare(self, other, **kw): Compare this :class:`ClauseList` to the given :class:`ClauseList`, including a comparison of all the clause items. if not isinstance(other, ClauseList) and len(self.clauses) == 1: return self.clauses[0].compare(other, **kw) elif isinstance(other, ClauseList) and \ len(self.clauses) == len(other.clauses): for i in range(0, len(self.clauses)): if not self.clauses[i].compare(other.clauses[i], **kw): return False else: return self.operator == other.operator else: return False oh right. We should add the same functionality to ClauseList then. A testcase like test_lazy_relations-test_uses_get for composite keys should also be added. (I know because if I flip the order of the composite ForeignKeyConstraint it no longer calls .get() for the lazyload... it always queries the database, so I checked out why in the debugger) On 9/8/2010 1:27 PM, Michael Bayer wrote: On Sep 8, 2010, at 1:15 PM, Kent Bower wrote: I imagine you are already aware of this... Unfortunately, the clause comparison says these two clauses are different: (Pdb) print self locations.siteid = :param_1 AND locations.locationid = :param_2 (Pdb) print other locations.locationid = :param_1 AND locations.siteid = :param_2 when they are really the equivalent. So composite foreign keys need to be listed in the correct order for LazyLoader.use_get to optimize the load with get(). use clause.compare(). Here's the source for _BinaryExpression: def compare(self, other, **kw): Compare this :class:`_BinaryExpression` against the given :class:`_BinaryExpression`. return ( isinstance(other, _BinaryExpression) and self.operator == other.operator and ( self.left.compare(other.left, **kw) and self.right.compare(other.right, **kw) or ( operators.is_commutative(self.operator) and self.left.compare(other.right, **kw) and self.right.compare(other.left, **kw) ) ) ) see the commutative in there ? its handled. I already saw it would be somewhat of an effort to refactor the clause comparison to work that out... bummer. On 9/7/2010 7:28 PM, Michael Bayer wrote: On Sep 7, 2010, at 6:41 PM, Kent Bower wrote: Two items: * How does the orm currently determine whether it is safe to try get() (e.i. there are no funny join conditions)? If you point me to the function where decision takes place, I can probably answer this myself it compares the join condition of the relationship() to that of the clause which the Mapper uses when it issues get(), then stores that away as a flag for future consultation. It's very unusual for a many-to-one relationship to be based on something other than a simple foreign-key-primary key relationship, though. * When I build up the primary key from the foreign key, is there an efficient way to build a composite key in the correct order to pass to get()? (I thought maybe synchronize_pairs, but that maybe has to do with getting the direction consistent instead?) Well if you aren't using any composite primary keys in many-to-ones, you wouldn't even have to worry about this. Otherwise, the two collections to correlate would be property.local_remote_pairs and property.mapper.primary_key. Perhaps make a dictionary out of dict([(r, l) for l, r in prop.local_remote_pairs]) and your PK value would be [getattr(instance, prop.parent.get_property_by_column(mydict[p]).key) for p in property.mapper.primary_key]. Or if you want to get ambitious you can just copy roughly whats in strategies.py on line 605 but then you're digging into internalsand looking at that now I'm wondering if strategy._equated_columns is really different than local_remote_pairs at all... Thanks again, you've been much help! On 9/7/2010 5:03 PM, Michael Bayer wrote: On Sep 7, 2010, at 4:38 PM, Kent Bower wrote: Don't want to strangle me, but when the orm (lazy)loads a MANYTONE object, it doesn't go to the database if the object is in the session. Can I get with_parent() to behave this way, or would I need to specifically build up the primary key of the related object and call query.get()?
