[sqlalchemy] A per-mapper query_class for easy DSL creation
Hi,
I would like to be able to write a query like:
session.query(Post).recent().include_authors().limit(10).all()
that would translate into:
session.query(Post).order_by(Post.created_at.desc()).options(joinedload('author')).limit(10).all()
The former code hides persistence details from the user. Methods
recent() and include_authors() are specific for the Post class.
Is this possible to achieve that kind of syntax on top of SQLAlchemy,
for example, by extending the Query class? I know that I can pass a
custom Query class to the constructor of a Session. It is possible to
set a different Query class for each mapped class?
Regards,
Adam
--
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: A per-mapper query_class for easy DSL creation
Thanks!
On 8 Maj, 16:24, Michael Bayer mike...@zzzcomputing.com wrote:
On May 8, 2010, at 9:40 AM, Adam Dziendziel wrote:
Hi,
I would like to be able to write a query like:
session.query(Post).recent().include_authors().limit(10).all()
that would translate into:
session.query(Post).order_by(Post.created_at.desc()).options(joinedload('author')).limit(10).all()
The former code hides persistence details from the user. Methods
recent() and include_authors() are specific for the Post class.
Is this possible to achieve that kind of syntax on top of SQLAlchemy,
for example, by extending the Query class? I know that I can pass a
custom Query class to the constructor of a Session. It is possible to
set a different Query class for each mapped class?
sure, pass in a callable to the session for query_cls which does whatever
translation to an actual Query instance you'd like.
--
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
athttp://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.
[sqlalchemy] Re: Loader not called when accessed relation attribute of non-persisted instance
On 2 Sty, 02:52, Michael Bayer mike...@zzzcomputing.com wrote: On Jan 1, 2010, at 9:08 AM, Adam Dziendziel wrote: A relation represents an in-database relation between two sets of state, the parent and child, which means rows that are related by some kind of joining condition. In this case there is no parent row. So you're really just trying to load some arbitrary CourseTranslation objects in which case you should just use a session.query() to get them, if you want them to be present on your pending object. If you want to dig your own on access callable into the attribute you can say CourseRevision.translations.impl.set_callable(instance_state(my_course_revision), some_callable), where instance_state is in sqlalchemy.orm. I haven't tried it for this purpose, though. 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] Loader not called when accessed relation attribute of non-persisted instance
I have a mapper:
orm.mapper(CourseRevision, course_revisions_table, properties={
'translations': orm.relation(CourseTranslation,
collection_class=VersionedTranslationsDict,
foreign_keys=
[course_translations_table.c.course_id,
course_translations_table.c.effective_from,
course_translations_table.c.effective_to],
primaryjoin=(
(course_revisions_table.c.course_id==course_translations_table.c.course_id)
(course_translations_table.c.effective_from =
course_revisions_table.c.effective_from)
(course_translations_table.c.effective_to
course_revisions_table.c.effective_from)),
viewonly=True),
})
The objects under the 'translations' attribute does not depend on
CourseRevision.id. This is a view for versioned CourseTranslation
objects which are filtered using revision time. When I create a new
CourseRevision object I set course_id, effective_from, effective_to
attributes. Then I would like to access 'translations', but the
collection is empty. Lazy loader is not fired. Object is not persisted
yet, but it is in the session.
Is possible to tell SQLAlchemy that this relation is not directly
populated and therefore not necessarily empty when a new object is
created?
Regards,
Adam
--
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: Mapping temporal data for easy querying using time dimension
On 28 Gru, 21:16, Michael Bayer mike...@zzzcomputing.com wrote: A query in the general can be against any number of individual columns, mapped entities, or aggregate functions. It can be selecting from many tables at once, as well as from other Query objects as subqueries. Trying to guess if filter_by() happens to be selecting exactly a single row primary key from all of that seems complicated. It would also be a surprise that it very occasionally pulls from cache, but in most cases not - all based on the particular structure of a query. If filter_by() did do that, in this specific case it still would not pull from cache since the criterion would be against more than just the primary key columns. I'd say the direction here is to not use get() if you would like any filtering to be applied, including from this new option. I understand. Thank you very much for the elaborate answer and the recipe. That was exactly what I was looking for. Thanks! Best regards, Adam -- 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: Mapping temporal data for easy querying using time dimension
On 28 Gru, 20:19, Michael Bayer mike...@zzzcomputing.com wrote: query.get() doesn't work with filtering criterion. This because it looks up in the current session by primary key, and if present issues no SQL. If it were filtered, you'd get different results based on whether or not the object were already loaded or not. It actually should be raising an error as get() should be asserting that no existing filter criterion is set up. Have you considered making get(*primary_key_as_list) a synonym for filter_by(**primary_key_as_dict)? Then if a filtering criterion contains only a primary key, do a look up in the session. -- 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] Mapping temporal data for easy querying using time dimension
Hi, I am trying to implement versioning of a structure of interconnected objects so that I can see how the content looked at some point of time. Probably I would need to use a temporal database with effective_from/ effective_to timestamps telling the time period when the information held in the record was true: http://martinfowler.com/ap2/timeNarrative.html If I had a flat object structure, I would simply pass a filter on the query: session.query(Object).filter(Object.effective_from = time time Object.effective_to) However, in my schema, the Object has related entities which is also temporal data. The mapped relations ideally should have the 'time' parameter embedded in the ON part of JOIN clause. I know that I can declare all relations as lazy='dynamic' and always pass 'time'. But I would then lost the possibility to eagerly load data. It would be inefficient to retrieve a record set and for each record issue another queries to get values of temporal properties ( http://martinfowler.com/eaaDev/TemporalProperty.html ) What I am looking for is a parameter, which value is specified once during a query, that could be used in mapper declarations. Attribute loaders would then use that parameter automatically. Is this possible in SQLAlchemy? Regards, Adam -- 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: Query caching and garbage collection of related instances
On 8 Lis, 20:32, Michael Bayer mike...@zzzcomputing.com wrote: On Nov 8, 2009, at 6:21 AM, Adam Dziendziel wrote: that behavior occurs if theme has been expired. this is expire_on_commit which you should probably disable, since the fact that you are caching the objects means you aren't concerned about new state coming in from transactions external to your application thread. http://www.sqlalchemy.org/docs/05/session.html#committing Indeed, that solved the problem. Thank you for help! Cheers, Adam --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Query caching and garbage collection of related instances
On 8 Lis, 20:29, Michael Bayer mike...@zzzcomputing.com wrote: On Nov 7, 2009, at 6:03 PM, Adam Dziendziel wrote: What I usually do when i want to ensure what gets cached (since im usually serializing into memcached), and i dont want to worry what the particular eager loading configuration is, is to make a method like full_load() which ensures all the important attributes and collections are present. this will issue lazy loads for anything that wasn't already loaded: def full_load(self): self.collection1 self.some_reference return self However, if you are truly eager loading all of those attributes then this step is unnecessary. By the way, is this possible to tell SQLAlchemy to load two lazy attributes together, using a single query i.e. like it does with eagerly-loaded attributes, but on demand? If collection1 and some_reference points to different rows in the database, the code: self.collection1 self.some_reference would issue two SELECTs, two round-trips to the database. When I know that I need these two, I would write instead something like: orm.attributes.load(self, ['collection1', 'some_reference']) Regards, Adam --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Query caching and garbage collection of related instances
After some tests with weak_identity_map=False see that the source of the ORM queries for attributes is not related to garbage collection. They aren't issued if query caching is off, so the problem is related to caching. When I write: theme = session.query(Theme).cache_key().get(..) # gets from cache print theme.id # here the 'id' attribute access issues a new SELECT When the cached object is accessed first, no queries are issued, then I do a lot of other record creation and flushing (I'm assigning this 'theme' to that records' attributes), then the cache is accessed again and this is the moment when the unwanted queries are issued. It seems that my record creation and flushing between cache calls makes that the ORM expires the cached object. However, the 'theme' object is itself never modified. What might be important, they queries aren't issued if the whole long- running operation is run inside a big transaction. My caching query implementation is here: http://pastebin.com/mf738cc5 I am using a Session with autoflush=False, autocommit=True. Do you know what might cause that behavior? Thanks, Adam On 8 Lis, 00:03, Adam Dziendziel adam.dziendz...@gmail.com wrote: Hi, I am trying to use the query caching solution described here:http://svn.sqlalchemy.org/sqlalchemy/trunk/examples/query_caching/per... In most cases it works, the returned records are cached, I store them in a LRU cache modeled afterhttp://code.activestate.com/recipes/498245/ However, when I run a long running operation, which operates on hundreds of other records, apparently the garbage collection is run on the session's weak-referencing identity map. The cache keeps the returned records, but other eagerly loaded related instances of the returned records are lost. The ORM issues queries to load them again from the database. I understand that there are no strong references between an instance and other related instances. What is the best solution to keep related instances in a session? If I create a session with weak_identity_map=False, then during my long running operation I will run out of memory, unless I expunge unused records, however, it is easy to miss one record and the identity map will be growing anyway. Is there possible to get a list of referenced instances of another instance, so that I could store the list together with the instance in the MRU cache? Or to make a session with a strong-referencing map and LRU policy that keeps it below a given size? Regards, Adam --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Query caching and garbage collection of related instances
Hi, I am trying to use the query caching solution described here: http://svn.sqlalchemy.org/sqlalchemy/trunk/examples/query_caching/per_session.py In most cases it works, the returned records are cached, I store them in a LRU cache modeled after http://code.activestate.com/recipes/498245/ However, when I run a long running operation, which operates on hundreds of other records, apparently the garbage collection is run on the session's weak-referencing identity map. The cache keeps the returned records, but other eagerly loaded related instances of the returned records are lost. The ORM issues queries to load them again from the database. I understand that there are no strong references between an instance and other related instances. What is the best solution to keep related instances in a session? If I create a session with weak_identity_map=False, then during my long running operation I will run out of memory, unless I expunge unused records, however, it is easy to miss one record and the identity map will be growing anyway. Is there possible to get a list of referenced instances of another instance, so that I could store the list together with the instance in the MRU cache? Or to make a session with a strong-referencing map and LRU policy that keeps it below a given size? Regards, Adam --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Testing SQLAlchemy applications without the database
Hello, I'm working on a larger project which is using SQLAlchemy. We started with writing automated tests using an in-memory SQLite database and inserting test data in setUp() then emptying tables in tearDown(). Even though the in-memory SQLite is pretty fast, the process of inserting test data (sometimes a hundred of records) takes significant amount some time. Eventually, a test which should take 30ms, takes 300ms. One solution is probably to create a DAO layer with find_by..() methods on top of the SQLAlchemy layer and then write a DAO mock which stores records in an array. Another one would be to create a fake session which stores records in an array. The latter would probably be better because we are not creating another unnecessary layer, but harder to implement, because of the complex query API. How do you cope with this situation? Best regards, Adam --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Adjacency List tree - inefficient reading
On 18 Sty, 19:47, Michael Bayer mike...@zzzcomputing.com wrote: However what I cant figure out with nested sets is, how do I load only the immediate children of a node ? That is the most common accessor I'd like on a self referential node and I'm not aware of how to do it. It makes it sort of impossible for there to be a .children accessor on a node, unless you load the full subtree and organize. It is possible if you add a 'depth' column which holds the absolute distance of the node from the tree root. Then, in order to load immediate children of a node, we can make a query: SELECT * FROM nodes WHERE lft node.lft and rgt node.rgt and depth=node.depth+1 Best regards, Adam --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Instance added to session and flushed by backref before save() is actually called
I have a ContentObject class mapped using:
co_mapper = orm.mapper(ContentObject,
content_objects_table,
extension=COMapperExtension(),
polymorphic_on=content_objects_table.c.type,
polymorphic_identity='ContentObject',
properties={
'theme': orm.relation(Theme),
'resources': orm.relation(Resource,
secondary=co_resources_table),
'children': orm.relation(ContentObject,
secondary=co_children_table,
primaryjoin=content_objects_table.c.id==co_children_table.c.parent_id,
secondaryjoin=co_children_table.c.child_id==content_objects_table.c.id,
backref='parents'),
'translations': orm.relation(ContentObjectTranslation,
backref='co')
}
)
co = ContentObject()
co.children.append(Session.query(ContentObject).get(1))
# assert co not in Session # fails!
co.theme = Session.query(Theme).get(1) # Session flushes automatically
when retrieving a theme, and I get an error because the theme_id
column (with nullable=False) is not set yet
IntegrityError: (IntegrityError) content_objects.theme_id may not be
NULL u'INSERT INTO content_objects (type, parent_id, position,
theme_id, data, keywords) VALUES (?, ?, ?, ?, ?, ?)' ['group', None,
None, None, None, '']
If I remove backref='parents' it works as expected. Do I have to turn
auto-flush off to avoid incidental flushes?
Best regards,
Adam
--~--~-~--~~~---~--~~
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] OrderingList and list.sort()
Hi, It seems that sorting of ordering list doesn't work. Attribute object.items is an OrderingList: object.items.sort(cmp=my_cmp) The list is sorted, but the ordering column is not updated. I need to call explicitly: object.items._reorder() Maybe override sort() in OrderingList to invoke self._reorder() after sorting? Thanks, Adam --~--~-~--~~~---~--~~ 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: Instance added to session and flushed by backref before save() is actually called
Thanks. --~--~-~--~~~---~--~~ 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: How to mark an attribute dirty manually?
This description was very hard to parse, but I think what you're saying is an attribute changes on a *different* object somewhere, which you would like to mark the XML-holding object as dirty. Sorry. The same object, but these attributes are pure Python attributes, they aren't mapped to columns in the database. They are defined using property() and act as proxies to nodes in XML document. To mark the object dirty you can say instance_state(myobject).modified = True , where instance_state is in sqlalchemy.orm.attributes. Theres no dirty flag on individual attributes, the change status is determined by the contents of the attribute. So just blanking out the XML attribute until needed is a pretty easy approach here too. Yet another option which is the most lightweight would be to use a SessionExtension with an after_flush() method that issues an UPDATE to the XML-holding column directly. This is what I was looking for. Thank you! Best regards, Adam --~--~-~--~~~---~--~~ 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] Generic way to check if a primary key has been set
Hello everybody,
Is there a generic way to check if a primary key of an instance has
been set that works even if it has been set indirectly, via a
relation?
For example, I have tables and mappers:
category_translations_table = Table('category_translations', metadata,
Column('category_id', types.Integer,
ForeignKey('categories.id'), primary_key=True),
Column('lang_id', types.String(2), ForeignKey('languages.id'),
primary_key=True),
Column('title', types.Unicode(100), nullable=False),
Column('description', types.Unicode(255), nullable=True),
Column('page_text', types.Text, nullable=True)
)
languages_table = Table('languages', metadata,
Column('id', types.String(2), primary_key=True),
Column('native_name', types.Unicode(30)),
Column('english_name', types.Unicode(30))
)
mapper(CategoryTranslation, category_translations_table, properties={
'category': relation(Category),
'language': relation(Language)
})
Then, I set a primary key indirectly:
translation = CategoryTranslation()
translation.category = session.query(Category).filter().one()
translation.language = session.query(Language).filter().one()
1. How to check whether the primary key is set? I cannot simply check
if every column of class_mapper(CategoryTranslation).primary_key is
set, because these field are empty until I save save the object.
2. How to retrieve that key? I would like to check if the record
already exists.
Regards,
Adam
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
