[sqlalchemy] Re: Sqlalchemy beaker cache and memcached
Hi, I use Beaker in production to help speed up the delivery of game content. We've seen enormous (seconds to milliseconds) speed ups for caching large queries that don't change. We don't use it at the query level but as a way to cache whole results from sqlalchemy. As long as you remember to merge all the objects back into the Session you're fine. We currently don't use the 'memcache' but the 'memory' setting, it's the easiest to setup and gives very good results. The only caveat I have about the memory caching is that there is no way of setting how big it gets, if that's a problem use memcache instead. For me, I just set the lifetime of objects to an hour and that stops the memory growing without bounds. File caching doesn't give as good results because you have to hit the disk and that will lead to IO problems especially if you are using a database which will be using the disk as well. I hope this helps, Ben Hesketh On Jun 8, 2:43 pm, Łukasz Czuja luk...@czuja.pl wrote: Hi, I reviewed a couple of messages on the list concerning caching. While: http://www.sqlalchemy.org/trac/browser/examples/beaker_caching is quite comprehensive, it does not include any information about performance gains or wether this example works for more complicated queries, joining couple of tables, returning agregates or diffrent objects from the same query. Are there any limitations on data types that cannot be cached? Also there is no information as wether this example would work if beaker is setup with memcache as storage and wether it would be faster than file storage and if so by how much? Can any one provide some numbers on this concept? Does anyone have a success story to share? I'm dying to see how this performs and if it is usable on production environment. Any input is appreciated. 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 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] DetachedInstanceError when calling __repr__
Hi, I have an objects that when I convert to a string using __repr__ throws a DetachedInstanceError. If I access any of their members or test the session (using 'user in Session') everything is fine but if I check 'self in Session' in __repr__ the result is False. I can reattach it to the Session using merge, inside the __repr__ call, but every time I call repr I get the same problem. I'm using PostgreSQL 8.4 and SqlAlchemy 0.6.4 and I'm using Pylons 1.0. I have a nested transaction (using Session.begin_nested) that is updating objects that are attached to a User object but that don't change the user object itself but the problem is seen in other, unrelated objects. Other than the nested transaction there are no other flushes or commits. Does anybody have any idea what is going on? Thanks, Ben -- 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: PostgreSQL, cascading and non-nullable ForeignKeys
I had misunderstood the documentation on relationships and then tied
myself in a knot, I thought that relationship() defined a strictly
parent to child relationship and that all the other parameters were
from the parents point of view.
My runs and is a lot cleaner!
Thank you for helping me,
BEN
On Sep 15, 3:20 pm, Conor conor.edward.da...@gmail.com wrote:
On 09/15/2010 05:04 PM, BenH wrote:
Hi,
I'm using SqlAlchemy 0.6.3 and PostgreSQL 8.4 and I'm trying to setup
a cascading delete between several levels of tables. The problem seems
to be that I can't have a relationship with cascade=all and a column
with ForeignKey that has nullable=False.
Your cascade clause is on the wrong side of the relationship. It means
you want to cascade TO the remote object, not FROM the remote object.
Here is my example:
from sqlalchemy import create_engine, Table, Column, Integer, String,
MetaData, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship
engine = create_engine('postgresql://tsq:passt%qw...@localhost:5432/
ce_cascade_test', echo=True)
Session = scoped_session(sessionmaker())
Base = declarative_base()
Session.configure(bind=engine)
s = Session()
class User(Base):
__tablename__ = users
id = Column(Integer, primary_key=True)
name = Column(String)
device = relationship(Device, uselist=False)
This should be:
device = relationship(Device, cascade=all, passive_deletes=True,
uselist=False)
Also, including uselist=False indicates that the User-Device
relationship is one-to-one. The lack of a unique constraint on
devices.user_id suggests a one-to-many relationship.
@classmethod
def create(cls, user_name, device_name, manufacturer):
new_user = User()
new_user.name = user_name
new_user.device = Device.create(device_name)
return new_user
class Device(Base):
__tablename__ = devices
id = Column(Integer, primary_key=True)
name = Column(String)
user_id = Column(Integer,
ForeignKey('users.id'),
nullable=False)
manufacturer_id = Column(Integer,
ForeignKey('manufacturers.id'),
nullable=False)
user = relationship(User,
uselist=False,
cascade=all)
This should be:
user = relationship(User)
Adding uselist=False here is redundant, since that is the default for
the side which contains the foreign key. Also, by including
'cascade=all', you are telling SQLAlchemy to implicitly delete the
user when the device is deleted. This is probably not what you want.
@classmethod
def create(cls, name):
new_device = Device()
new_device.name = name
return new_device
Base.metadata.create_all(engine)
user = User.create(bob, iphone)
s.add(user)
s.commit()
s.delete(user)
s.commit()
If I run this then I get an Integrity error during the delete (because
it is updating the user_id to null before deleting it). I would like
to keep the nullable=False on the ForeignKey.
I've tried adding ondelete=CASCADE to the ForeignKey and adding
passive_deletes=True to the relationship but it always throw the same
Integrity error.
I know I'm missing something but I can't find it in the docs, what am
I missing?
Thanks for your help,
BEN
-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=en.
[sqlalchemy] PostgreSQL, cascading and non-nullable ForeignKeys
Hi,
I'm using SqlAlchemy 0.6.3 and PostgreSQL 8.4 and I'm trying to setup
a cascading delete between several levels of tables. The problem seems
to be that I can't have a relationship with cascade=all and a column
with ForeignKey that has nullable=False.
Here is my example:
from sqlalchemy import create_engine, Table, Column, Integer, String,
MetaData, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship
engine = create_engine('postgresql://tsq:passt%qw...@localhost:5432/
ce_cascade_test', echo=True)
Session = scoped_session(sessionmaker())
Base = declarative_base()
Session.configure(bind=engine)
s = Session()
class User(Base):
__tablename__ = users
id = Column(Integer, primary_key=True)
name = Column(String)
device = relationship(Device, uselist=False)
@classmethod
def create(cls, user_name, device_name, manufacturer):
new_user = User()
new_user.name = user_name
new_user.device = Device.create(device_name)
return new_user
class Device(Base):
__tablename__ = devices
id = Column(Integer, primary_key=True)
name = Column(String)
user_id = Column(Integer,
ForeignKey('users.id'),
nullable=False)
manufacturer_id = Column(Integer,
ForeignKey('manufacturers.id'),
nullable=False)
user = relationship(User,
uselist=False,
cascade=all)
@classmethod
def create(cls, name):
new_device = Device()
new_device.name = name
return new_device
Base.metadata.create_all(engine)
user = User.create(bob, iphone)
s.add(user)
s.commit()
s.delete(user)
s.commit()
If I run this then I get an Integrity error during the delete (because
it is updating the user_id to null before deleting it). I would like
to keep the nullable=False on the ForeignKey.
I've tried adding ondelete=CASCADE to the ForeignKey and adding
passive_deletes=True to the relationship but it always throw the same
Integrity error.
I know I'm missing something but I can't find it in the docs, what am
I missing?
Thanks for your help,
BEN
--
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] Comparing a DateTime column to now() in a query filter
Hi, Sorry if this is an obvious question but I can't find the answer, perhaps my Google-fu is weak. I have a table 'Item' with an 'expires' DateTime column, I want to test it against now() and check whether the item in the table is out of date. I've tried: session.query(Item).filter(Item.expires now()).all() and it returns 0 results no matter what is in the 'expires' column. The few references I can find to working with DateTime talk about doing DB specific conversions but they were from several years ago and I wonder if things have been fixed since. Thanks for your help Ben -- 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: Comparing a DateTime column to now() in a query filter
Ok, I'm an idiot. This query works perfectly. My problem was with the table definition and setting the default. Sorry for the noise. BEN On Jun 9, 2:52 pm, BenH ben.hesk...@gmail.com wrote: Hi, Sorry if this is an obvious question but I can't find the answer, perhaps my Google-fu is weak. I have a table 'Item' with an 'expires' DateTime column, I want to test it against now() and check whether the item in the table is out of date. I've tried: session.query(Item).filter(Item.expires now()).all() and it returns 0 results no matter what is in the 'expires' column. The few references I can find to working with DateTime talk about doing DB specific conversions but they were from several years ago and I wonder if things have been fixed since. Thanks for your help Ben -- 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.
