chaouche yacine wrote: > --- On Fri, 11/20/09, Conor <[email protected]> wrote: > > >> Also, there is a recipe that looks for a matching object in >> the session >> before querying the database: >> http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject >> >> -Conor >> >> > > Thank you Conor for you useful pointer. I have used this recipe and changed a > little bit to use beaker as a caching mechanism with a memcached backend. So > here's how it looks like : > > from elixir import Entity, EntityMeta,setup_all,create_all,metadata > from pylons import cache > > class MetaTag(EntityMeta): > cache = cache.get_cache("tags",type="memory") > def __call__(cls,name): > """ > If it's in the cache, return the cached version > If not in the cache : > If it's in the database, retrieve it, cache it and return it > If it's not there, create it, cache it and return it > """ > theTag = MetaTag.cache.get_value(key=name,createfunc=lambda:None) > if not theTag : > #not in the cache > theTag = cls.query.filter_by(name=name).first() > if not theTag: > #not in the database either > print "not in the database" > theTag = type.__call__(cls,name) > session.add(theTag) > #Adding it to the cache, after creating it in the database if it > wasn't there > MetaTag.cache.set_value(key=name,value=theTag) > return theTag > > class Tag(Entity): > __metaclass__ = MetaTag > using_options (tablename="Tags") > name = Field(Unicode(64)) > > def __init__(self,name,*args,**kw): > Entity.__init__(self,*args,**kw) > self.name = name > > def __repr__(self): > return "<Tag %s>" % self.name > > metadata.bind = "mysql://username:passw...@localhost:3306/db" > metadata.bind.echo = True > > setup_all() > create_all() > > And here's how it plays on the interpreter (ipython) : > Alger is already in the database, Constantine is not. I find the metaclass > approach very API-friendly. > > ... > > In [4]: Tag("Alger") > 21:38:21,328 INFO [sqlalchemy.engine.base.Engine.0x...2fec] BEGIN > /home/chaouche/PYTHONENV/lib/python2.6/site-packages/SQLAlchemy-0.5.5-py2.6.egg/sqlalchemy/engine/default.py:230: > SAWarning: Unicode type received non-unicode bind param value 'Alger' > param.append(processors[key](compiled_params[key])) > 21:38:21,382 INFO [sqlalchemy.engine.base.Engine.0x...2fec] SELECT `Tags`.id > AS `Tags_id`, `Tags`.name AS `Tags_name` > FROM `Tags` > WHERE `Tags`.name = %s > LIMIT 0, 1 > 21:38:21,382 INFO [sqlalchemy.engine.base.Engine.0x...2fec] ['Alger'] > Out[4]: <Tag Alger> > > In [5]: Tag("Alger") > Out[5]: <Tag Alger> > > In [8]: Tag("Constantine") > /home/chaouche/PYTHONENV/lib/python2.6/site-packages/SQLAlchemy-0.5.5-py2.6.egg/sqlalchemy/engine/default.py:230: > SAWarning: Unicode type received non-unicode bind param value 'Constantine' > param.append(processors[key](compiled_params[key])) > 21:39:17,487 INFO [sqlalchemy.engine.base.Engine.0x...2fec] SELECT `Tags`.id > AS `Tags_id`, `Tags`.name AS `Tags_name` > FROM `Tags` > WHERE `Tags`.name = %s > LIMIT 0, 1 > 21:39:17,487 INFO [sqlalchemy.engine.base.Engine.0x...2fec] ['Constantine'] > not in the database > Out[8]: <Tag Constantine> > > In [9]: Tag("Constantine") > Out[9]: <Tag Constantine> > > Any comments are very appreciated. > > Y.Chaouche >
There is a problem with your code when the tag is in the cache: if the tag is added to the session via session.add or a relation "add" cascade, SQLAlchemy will try to INSERT the tag into the database on the next flush. To prevent this, you need to tell SQLAlchemy that the tag object's state is persistent (already in the DB) instead of pending (needs to be inserted). You can do this by adding this code for a cache hit: theTag = session.merge(theTag, dont_load=True) You can read more about session.merge at http://www.sqlalchemy.org/docs/05/session.html#merging. Searching this group for "merge" and "dont_load" is also a good idea. -Conor -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=.
