Re: [sqlalchemy] Weird error with update
> > I am able to create the same bad SQL, but only if the key in the update > dictionary is not a column in the table being updated. Are you sure > 'last_access' is a valid column in ESMagicNumber? > Why not use update(ESMagicNumber.last_access: datetime.datetime.now()) ? -- 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=.
Re: [sqlalchemy] queries get progressively slower....(0.46, declarative, mysql)
On Nov 19, 2009, at 4:34 PM, bdb112 wrote: > > Any advice would be welcome. > > THe following should be read in monosapce font. > > Action Uptime Threads Questions Slowqurs Opens Flushtbls Opentbls > Qupersec dcpu > data1352 1594027 1 20 > 45.542 2.6 > repeat1 752 2161027 1 20 > 28.813 7.7 > data02 1342 4984027 1 20 > 37.194 14.5 > data1 1582 5545027 1 20 > 35.94 17.7 > session.close() then > data1 96 2 7130027 1 20 > 74.270 2.47 > speed is back to normal this behavior is likely due to a known issue that was fixed in version 0.5.4. It involves a full scan of all objects in the current unit of work each time flush() runs, so the more objects present in your session, the slower each flush() operation becomes.There are lots of speed improvements to be had if you get on the latest 0.5 and in particular this one is the most dramatic - 0.5.4 also greatly improved the memory usage of the session such that unreferenced objects are garbage collected more readily. Within 0.4 your only option would be to keep the size of the session very small - this is possible but its a lot easier just to upgrade to 0.5.6. -- 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=.
[sqlalchemy] queries get progressively slower....(0.46, declarative, mysql)
I am looking for hints on avoiding SA inefficiency. I have two apparently very different tasks which seem to "clog" SA after more than one execution. One involves rather straightforward data storage (but into many tables, using declarative SA). THe other involves moderate sized joins. Both become much slower after multiplie executions as if some overhead is building up. After processing ~100 chunkd of data, the data storage task execution time begins to scale as the number of executions, so it is much faster to exit and start again - otherwise a chunk that took 2 seconds will take minutes or more. The join task only doubles in execution time once - successive executions are t, 2t, 2t, 2t etc, but if I execute on differernt data, it becomes fully cumulative, so the execution time goes from 3 seconds to several minutes eventually. I have enclosed a table which shows the mysql activity and the SA delta cpu time (same as wall time) for a fresh data1, repeat the task with the same data (time doubles or a bit more, but does not increase further with repeats of the same dadta ), Then do two similar tasks on differernt data (delta time gets longer), then go back to data1 (delta time is not 5 times greater). session.close() then do again, -> delta time is short. mysql does not report any slow queries, and the answers are correct. session.flush() does not seem to help. Any advice would be welcome. THe following should be read in monosapce font. Action Uptime Threads Questions Slowqurs Opens Flushtbls Opentbls Qupersec dcpu data1352 1594027 1 20 45.542 2.6 repeat1 752 2161027 1 20 28.813 7.7 data02 1342 4984027 1 20 37.194 14.5 data1 1582 5545027 1 20 35.94 17.7 session.close() then data1 96 2 7130027 1 20 74.270 2.47 speed is back to normal -- 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=.
Re: [sqlalchemy] get_or_create(**kwargs) ?
chaouche yacine wrote:
> --- On Fri, 11/20/09, Conor 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 "" % 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]:
>
> In [5]: Tag("Alger")
> Out[5]:
>
> 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]:
>
> In [9]: Tag("Constantine")
> Out[9]:
>
> 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 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=.
Re: [sqlalchemy] get_or_create(**kwargs) ?
--- On Fri, 11/20/09, Conor 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 "" % 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]:
In [5]: Tag("Alger")
Out[5]:
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]:
In [9]: Tag("Constantine")
Out[9]:
Any comments are very appreciated.
Y.Chaouche
--
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=.
Re: [sqlalchemy] more than one "one to many" relation
this should work OK if you fix the ForeignKey definitions
Column('parent_id', Integer, ForeignKey('parent.parent_id'))
or
Column('parent_id', Integer,ForeignKey(parent_table.c.parent_id))
--
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=.
Re: [sqlalchemy] Getting a join in one object
query() will return a tuple of the items listed in the query. In your query you are asking for a and b objects, so that is what you get. If you want the result to be individual columns directly, you need to list them individually. db.query(a.a, a.acol1, b.b, b.bcol1).\ select_from(orm.join(a, b, a.a== b.a)).all() if there are duplicate names in a and b you need to apply .label() to distinguish the columns. Unless your select_from() is especially complicated this can be simplified and eliminate the subselect in the generated code db.query(a.a, a.acol1, b.b, b.bcol1).join((b, a.a== b.a)).all() or if you have foreign keys defined between a and b db.query(a.a, a.acol1, b.b, b.bcol1).join(b).all() -- 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=.
