[sqlalchemy] Re: Can't shake off the 'MySQL server has gone away' Error
I still seem to get the 'MySQL server has gone away' Error. I am suspecting that the problem is in my use of FCGI in production. Thats the only difference between my Development environment and Production and it works 100% without the error in Development env. I guess I'll have to put some more debugging in my application in Production setup. Oh and, after the 'MySQL server has gone away' Error, I immediately get the InvalidRequestError: Can't reconnect until invalid transaction is rolled back. SQLAlchemy version I'm running by the way is '0.4.6'. I created a WSGI middleware class that handles the SQLAlchemy Connections and Sessions like so: Note: the middleware instance gets loaded only once on start of the application. And the get_engine() method lazy-loads the engine once and then returns existing thereafter. sql.py class SQLAlchemyMiddleware(object): Middleware for providing clean SQLAlchemy Session objects for each Request. def __init__(self, application): self.application = application self.__engine = None def get_engine(self): if self.__engine is None: self.__engine = create_engine( settings.SQLALCHEMY_DEFAULT_URL, pool_recycle=3600, pool_size=20, echo_pool=True ) return self.__engine def init_model(self, engine): Call before using any of the tables or classes in the model. sm = orm.sessionmaker(autoflush=True, transactional=True, bind=engine) meta.engine = engine meta.Session = orm.scoped_session(sm) def __call__(self, environ, start_response): try: engine = self.get_engine() self.init_model(engine) return self.application(environ, start_response) finally: if meta.Session is not None: meta.Session.remove() models/meta.py from sqlalchemy import MetaData __all__ = ['engine', 'metadata', 'Session'] engine = None# Global metadata. If you have multiple databases with overlapping table # names, you'll need a metadata for each database. Session = None metadata = MetaData() Regards, -Alen On Aug 15, 4:44 pm, Alen Ribic [EMAIL PROTECTED] wrote: one-per-application level That seems to have worked. I'll monitor the log for the next day or so and see how it goes. Thx -Alen On Aug 15, 4:27 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Aug 15, 2008, at 10:21 AM, Alen Ribic wrote: To me it seems like the connections are not being returned back to the pool. Here is my engine code which gets called on each user HTTP request: engine = create_engine( settings.SQLALCHEMY_URL, pool_recycle=3600, pool_size=20, echo_pool=True ) if you call create_engine() on every request, then you're creating a new connection pool for every request. move this up to the module one- per-application level. --~--~-~--~~~---~--~~ 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 deal with simple python subclasses and the declarative extension?
On Aug 17, 2008, at 10:18 PM, Russell Warren wrote: After struggling with this a lot longer I've realized that my subclassing problem is fundamentally simpler than I've described. My application generates the database from scratch. I use declarative to avoid having to define the database table fields, redefine them in my objects, and then map them. I have a lot of tables in my application and it saves a lot of copy-pasting. Two other things: 1. I like having the entire database definition in one file - ie: all table defs in one place - with declarative this means having the objects there, too 2. I want to define unmapped functionality in additional files - eg: a User object's table in the dbdef file, but actual user functions (eg: User.SendEmail) defined in another file (eg: User.py) where the full support code exists for user management - I don't want the dbdef file cluttered with code not related to table defs if you want your mapping defintions decoupled from your class definitions, here are your options: 1. use mapper(). This is why SQLA was built this way. There is no need for re defintion of columns or copy-pasting as the mapper pulls them from the Table automatically - its completely DRY (but more spread out). 2. use mixins. I.e. your unmapped functionality is provided by a mixin which you define as a second base class for a mapped class along with declarative base. --~--~-~--~~~---~--~~ 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: Can't shake off the 'MySQL server has gone away' Error
On Aug 18, 2008, at 5:08 AM, Alen Ribic wrote: sql.py class SQLAlchemyMiddleware(object): Middleware for providing clean SQLAlchemy Session objects for each Request. def __init__(self, application): self.application = application self.__engine = None def get_engine(self): if self.__engine is None: self.__engine = create_engine( settings.SQLALCHEMY_DEFAULT_URL, pool_recycle=3600, pool_size=20, echo_pool=True ) return self.__engine def init_model(self, engine): Call before using any of the tables or classes in the model. sm = orm.sessionmaker(autoflush=True, transactional=True, bind=engine) meta.engine = engine meta.Session = orm.scoped_session(sm) def __call__(self, environ, start_response): try: engine = self.get_engine() self.init_model(engine) return self.application(environ, start_response) finally: if meta.Session is not None: meta.Session.remove() The big mistake here is creating a brand new ScopedSession on each request. This is not how ScopedSession was intended to be used; its created, like Engine, once per application. Some details on this pattern are here: http://www.sqlalchemy.org/docs/05/session.html#unitofwork_contextual_lifespan The way you have it, a concurrent thread can easily interrupt the ScopedSession instance attached to meta and replace with a new one, with the old one being lost. Here's a more reasonable approach: class SQLAlchemyMiddleware(object): def __init__(self, application): self.application = application meta.engine = create_engine( settings.SQLALCHEMY_DEFAULT_URL, pool_recycle=3600, pool_size=20, echo_pool=True ) meta.Session = orm.scoped_session(orm.sessionmaker(autoflush=True, transactional=True, bind=meta.engine)) def __call__(self, environ, start_response): try: return self.application(environ, start_response) except: meta.Session.rollback() raise finally: meta.Session.remove() --~--~-~--~~~---~--~~ 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: Can't shake off the 'MySQL server has gone away' Error
The way you have it, a concurrent thread can easily interrupt the ScopedSession instance attached to meta and replace with a new one, with the old one being lost. Ouch, that would be no good. Thank goodness my prod env aint really prod yet. Thank you for your help again. Much appreciated. -Alen On Aug 18, 3:13 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Aug 18, 2008, at 5:08 AM, Alen Ribic wrote: sql.py class SQLAlchemyMiddleware(object): Middleware for providing clean SQLAlchemy Session objects for each Request. def __init__(self, application): self.application = application self.__engine = None def get_engine(self): if self.__engine is None: self.__engine = create_engine( settings.SQLALCHEMY_DEFAULT_URL, pool_recycle=3600, pool_size=20, echo_pool=True ) return self.__engine def init_model(self, engine): Call before using any of the tables or classes in the model. sm = orm.sessionmaker(autoflush=True, transactional=True, bind=engine) meta.engine = engine meta.Session = orm.scoped_session(sm) def __call__(self, environ, start_response): try: engine = self.get_engine() self.init_model(engine) return self.application(environ, start_response) finally: if meta.Session is not None: meta.Session.remove() The big mistake here is creating a brand new ScopedSession on each request. This is not how ScopedSession was intended to be used; its created, like Engine, once per application. Some details on this pattern are here: http://www.sqlalchemy.org/docs/05/session.html#unitofwork_contextual_... The way you have it, a concurrent thread can easily interrupt the ScopedSession instance attached to meta and replace with a new one, with the old one being lost. Here's a more reasonable approach: class SQLAlchemyMiddleware(object): def __init__(self, application): self.application = application meta.engine = create_engine( settings.SQLALCHEMY_DEFAULT_URL, pool_recycle=3600, pool_size=20, echo_pool=True ) meta.Session = orm.scoped_session(orm.sessionmaker(autoflush=True, transactional=True, bind=meta.engine)) def __call__(self, environ, start_response): try: return self.application(environ, start_response) except: meta.Session.rollback() raise finally: meta.Session.remove() --~--~-~--~~~---~--~~ 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] sqlalchemy 0.4 and multiple database binds
Hello everyone, According to the Pylons wiki docs on multiple engines with SQLALchemy 0.4, I should be able to do: def init_model(default_engine, alternate_engine): binds = { 'tableA': default_engine, 'tableB': alternate_engine } meta.session = orm.scoped_session(orm.sessionmaker(binds=binds)) This doesn't work for me on Pylons 0.9.6.2 and SQLAlchemy 0.4.7. Is the example out of date? I've reviewed the code in sqlalchemy.orm.sessionmaker and I don't see anything that does something with a kwarg binds, only bind - which leads me to believe that the documentation gives the incorrect approach. -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 --~--~-~--~~~---~--~~ 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: sqlalchemy 0.4 and multiple database binds
On Aug 18, 2008, at 10:07 AM, Ross Vandegrift wrote: Hello everyone, According to the Pylons wiki docs on multiple engines with SQLALchemy 0.4, I should be able to do: def init_model(default_engine, alternate_engine): binds = { 'tableA': default_engine, 'tableB': alternate_engine } meta.session = orm.scoped_session(orm.sessionmaker(binds=binds)) This doesn't work for me on Pylons 0.9.6.2 and SQLAlchemy 0.4.7. Is the example out of date? I've reviewed the code in sqlalchemy.orm.sessionmaker and I don't see anything that does something with a kwarg binds, only bind - which leads me to believe that the documentation gives the incorrect approach. binds is passed through via **kwargs to the Session constructor. The keys inside of binds are expected to be Table, mapper(), or class objects. Documentation at: http://www.sqlalchemy.org/docs/04/session.html#unitofwork_contextual_partitioning_vertical --~--~-~--~~~---~--~~ 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: Can't shake off the 'MySQL server has gone away' Error
Alen Ribic wrote: I still seem to get the 'MySQL server has gone away' Error. I am suspecting that the problem is in my use of FCGI in production. Thats the only difference between my Development environment and Production and it works 100% without the error in Development env. I guess I'll have to put some more debugging in my application in Production setup. MySQL will also throw that error when a query needs more resources than the configuration allows. If there's more data in your production environment or the my.cnf differs that could be it. In any case, enabling error logging for the MySQL server process may shed some light on the root cause. --~--~-~--~~~---~--~~ 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: sqlalchemy 0.4 and multiple database binds
On Mon, Aug 18, 2008 at 10:14:44AM -0400, Michael Bayer wrote: On Aug 18, 2008, at 10:07 AM, Ross Vandegrift wrote: Hello everyone, According to the Pylons wiki docs on multiple engines with SQLALchemy 0.4, I should be able to do: def init_model(default_engine, alternate_engine): binds = { 'tableA': default_engine, 'tableB': alternate_engine } meta.session = orm.scoped_session(orm.sessionmaker(binds=binds)) This doesn't work for me on Pylons 0.9.6.2 and SQLAlchemy 0.4.7. Is the example out of date? I've reviewed the code in sqlalchemy.orm.sessionmaker and I don't see anything that does something with a kwarg binds, only bind - which leads me to believe that the documentation gives the incorrect approach. binds is passed through via **kwargs to the Session constructor. The keys inside of binds are expected to be Table, mapper(), or class objects. Documentation at: http://www.sqlalchemy.org/docs/04/session.html#unitofwork_contextual_partitioning_vertical Aha, that makes all the difference in the world - thanks again for your quick and clear help Mike! I'll comment the Pylons wiki to clarify that section. Ross -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 --~--~-~--~~~---~--~~ 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] AttributeError: 'property' object has no attribute 'impl'
I receive the following error with SA 0.4.7p1 and the latest 0.4 svn revision. AttributeError: 'property' object has no attribute 'impl' Mappers == mapper(TransactionType, transaction_types) transactions_mapper = mapper(Transaction, transactions, order_by=transactions.c.date, extension=[HistoryMapperExtension(), TransactionDateMapperExtension()], polymorphic_on=transactions.c.transaction_type_id, polymorphic_identity=0, exclude_properties=['number', 'address', 'shipping', 'handling', 'purchase_order', 'contact_note', 'notes', 'valid_until'], properties={ '_voided_at': transactions.c.voided_at, '_date': transactions.c.date, 'method': relation(PaymentMethodType, backref='transactions'), 'transaction_type': relation(TransactionType, backref='transactions'), 'created_by_id': transactions.c.created_by, 'updated_by_id': transactions.c.updated_by, 'created_by': relation(Contact, primaryjoin=transactions.c.created_by==contacts.c.id), 'updated_by': relation(Contact, primaryjoin=transactions.c.updated_by==contacts.c.id), }) mapper(Cost, inherits=transactions_mapper, polymorphic_identity=1, properties={ 'line_items': relation(LineItem, backref='cost', cascade='delete') }) mapper(Payment, inherits=transactions_mapper, polymorphic_identity=2) mapper(Receipt, inherits=transactions_mapper, polymorphic_identity=4) mapper(Adjustment, inherits=transactions_mapper, polymorphic_identity=5) abstract_sales_mapper = mapper(AbstractSale, inherits=transactions_mapper, polymorphic_on=transactions.c.transaction_type_id, properties={ 'number': transactions.c.number, 'address': transactions.c.address, 'shipping': transactions.c.shipping, 'handling': transactions.c.handling, 'purchase_order': transactions.c.purchase_order, 'contact_note': transactions.c.contact_note, 'notes': transactions.c.notes, 'payment_terms': relation(PaymentTerm, backref='abstract_sales'), 'line_items': relation(LineItem, backref='sale', cascade='delete', order_by=line_items.c.position) }) mapper(Sale, inherits=abstract_sales_mapper, polymorphic_identity=3) mapper(Quote, inherits=abstract_sales_mapper, polymorphic_identity=6, properties={ '_valid_until': transactions.c.valid_until }) The error occurs when performing a query on Transaction ex: Transaction.query.get(111) where row id 111 is a Quote type. If a query uses the specific class type ex: Quote.query.get(111) it completes successfully. I don't know if this is an SA bug or an error in my mappers. Any help is appreciated. -brad Full Traceback === In [53]: m.Transaction.query.get(112) ERROR: An unexpected error occurred while tokenizing input The following traceback may be corrupted or invalid The error message is: ('EOF in multi-line statement', (141, 0)) --- AttributeErrorTraceback (most recent call last) C:\Documents and Settings\bwells\workspace\ERP GIT TEST\erp\ipython console in module() C:\Program Files\Python\lib\site-packages\sqlalchemy-0.4.7p1-py2.5.egg \sqlalchemy\orm\query.pyc in get(self, ident, **kw args) 267 268 key = self.mapper.identity_key_from_primary_key(ident) -- 269 return self._get(key, ident, **kwargs) 270 271 def load(self, ident, raiseerr=True, **kwargs): C:\Program Files\Python\lib\site-packages\sqlalchemy-0.4.7p1-py2.5.egg \sqlalchemy\orm\query.pyc in _get(self, key, ident , refresh_instance, lockmode, only_load_props) 1066 try: 1067 # call using all() to avoid LIMIT compilation complexity - 1068 return q.all()[0] 1069 except IndexError: 1070 return None C:\Program Files\Python\lib\site-packages\sqlalchemy-0.4.7p1-py2.5.egg \sqlalchemy\orm\query.pyc in all(self) 876 877 -- 878 return list(self) 879 880 C:\Program Files\Python\lib\site-packages\sqlalchemy-0.4.7p1-py2.5.egg \sqlalchemy\orm\query.pyc in iterate_instances(sel f, cursor, *mappers_or_columns, **kwargs) 1001 rows = [] 1002 for row in fetch: - 1003 process[0](context, row, rows) 1004 elif single_entity: 1005 rows = [process[0](context, row) for row in fetch] C:\Program Files\Python\lib\site-packages\sqlalchemy-0.4.7p1-py2.5.egg \sqlalchemy\orm\query.pyc in main(context, row, re sult) 1579 row = context.row_adapter(row) 1580 self.mapper._instance(context, row, result, - 1581 extension=context.extension, only_load_props=context.only_load_props, refresh_instance=conte
[sqlalchemy] Re: AttributeError: 'property' object has no attribute 'impl'
it has to do with a name conflict between a regular python property you've configured somewhere, and the name of a mapped attribute or relation(). The descriptors which SQLAlchemy places on the class (InstrumentedAttribute) have an impl attribute. You'd have to find what name is conflicting; a pdb or print statement within attributes.py could reveal the key and class names in play. (FTR, this kind of error wouldn't occur with SQLA 0.5 which checks for user-defined descriptors explicitly). On Aug 18, 2008, at 11:13 AM, Brad Wells wrote: I receive the following error with SA 0.4.7p1 and the latest 0.4 svn revision. AttributeError: 'property' object has no attribute 'impl' Mappers == mapper(TransactionType, transaction_types) transactions_mapper = mapper(Transaction, transactions, order_by=transactions.c.date, extension=[HistoryMapperExtension(), TransactionDateMapperExtension()], polymorphic_on=transactions.c.transaction_type_id, polymorphic_identity=0, exclude_properties=['number', 'address', 'shipping', 'handling', 'purchase_order', 'contact_note', 'notes', 'valid_until'], properties={ '_voided_at': transactions.c.voided_at, '_date': transactions.c.date, 'method': relation(PaymentMethodType, backref='transactions'), 'transaction_type': relation(TransactionType, backref='transactions'), 'created_by_id': transactions.c.created_by, 'updated_by_id': transactions.c.updated_by, 'created_by': relation(Contact, primaryjoin=transactions.c.created_by==contacts.c.id), 'updated_by': relation(Contact, primaryjoin=transactions.c.updated_by==contacts.c.id), }) mapper(Cost, inherits=transactions_mapper, polymorphic_identity=1, properties={ 'line_items': relation(LineItem, backref='cost', cascade='delete') }) mapper(Payment, inherits=transactions_mapper, polymorphic_identity=2) mapper(Receipt, inherits=transactions_mapper, polymorphic_identity=4) mapper(Adjustment, inherits=transactions_mapper, polymorphic_identity=5) abstract_sales_mapper = mapper(AbstractSale, inherits=transactions_mapper, polymorphic_on=transactions.c.transaction_type_id, properties={ 'number': transactions.c.number, 'address': transactions.c.address, 'shipping': transactions.c.shipping, 'handling': transactions.c.handling, 'purchase_order': transactions.c.purchase_order, 'contact_note': transactions.c.contact_note, 'notes': transactions.c.notes, 'payment_terms': relation(PaymentTerm, backref='abstract_sales'), 'line_items': relation(LineItem, backref='sale', cascade='delete', order_by=line_items.c.position) }) mapper(Sale, inherits=abstract_sales_mapper, polymorphic_identity=3) mapper(Quote, inherits=abstract_sales_mapper, polymorphic_identity=6, properties={ '_valid_until': transactions.c.valid_until }) The error occurs when performing a query on Transaction ex: Transaction.query.get(111) where row id 111 is a Quote type. If a query uses the specific class type ex: Quote.query.get(111) it completes successfully. I don't know if this is an SA bug or an error in my mappers. Any help is appreciated. -brad Full Traceback === In [53]: m.Transaction.query.get(112) ERROR: An unexpected error occurred while tokenizing input The following traceback may be corrupted or invalid The error message is: ('EOF in multi-line statement', (141, 0)) --- AttributeErrorTraceback (most recent call last) C:\Documents and Settings\bwells\workspace\ERP GIT TEST\erp\ipython console in module() C:\Program Files\Python\lib\site-packages\sqlalchemy-0.4.7p1-py2.5.egg \sqlalchemy\orm\query.pyc in get(self, ident, **kw args) 267 268 key = self.mapper.identity_key_from_primary_key(ident) -- 269 return self._get(key, ident, **kwargs) 270 271 def load(self, ident, raiseerr=True, **kwargs): C:\Program Files\Python\lib\site-packages\sqlalchemy-0.4.7p1-py2.5.egg \sqlalchemy\orm\query.pyc in _get(self, key, ident , refresh_instance, lockmode, only_load_props) 1066 try: 1067 # call using all() to avoid LIMIT compilation complexity - 1068 return q.all()[0] 1069 except IndexError: 1070 return None C:\Program Files\Python\lib\site-packages\sqlalchemy-0.4.7p1-py2.5.egg \sqlalchemy\orm\query.pyc in all(self) 876 877 -- 878 return list(self) 879 880 C:\Program Files\Python\lib\site-packages\sqlalchemy-0.4.7p1-py2.5.egg \sqlalchemy\orm\query.pyc in iterate_instances(sel f, cursor, *mappers_or_columns, **kwargs) 1001
[sqlalchemy] Re: tracking parents, names
Hey Michael, Thanks for the helpful answer. Michael Bayer wrote: [snip] (after I've reread the above two paragraphs many times it seems like the idea is that the target object doesn't know anything about the name of the relation in which its collected). The idea is that if the object graph says: foo.bar.baz that'll be: bar.__name__ == 'bar' bar.__parent__ is foo baz.__name__ == 'baz' baz.__parent__ is bar In this case: foo.bar[key] it'll be: bar[key].__parent__ is foo.bar bar[key].__name__ == key where 'bar' is a collection. I'm not as fluent with the collection API (since Jason wrote it) but it turns out its pretty easy to get at the parent object and the relation which its mapped through a collection directly, since collections always have an adapter present which maintains this relationship. Thanks, that's interesting to know. It turns out though that the adapter considers the owner state of bar[key] to be foo, so we can't use that as a parent. That actually simplifies things and it turns out that in my modified version of _receive the adapter doesn't appear to be necessary. Still, good to have some idea about how to use them. I have this now: def _receive(self, item): item.__name__ = unicode(self.keyfunc(item)) item.__parent__ = self This would still involve overriding the behavior of all the mutator methods on collections. I'm not sure why you'd want to override retrieve methods as well, are we able to assign __name__ and __parent__ to elements as they are added, or do these change depending on where the item is accessed from ? (and if the latter, is it wrapped in a container of some kind, or are we guaranteeing single- threaded access?) It's important to have this information no matter how the object graph is constructed, either by construction (as I think we covered with the __setitem__ change), or by retrieval from the database. So yes, the __parent__ information does indeed change depending on where the item is accessed. I take it however that there might be a problem with threaded access. It's certainly possible for the same object to be accessed in multiple threads, but each thread has its own session associated with it using scoped sessions. I take it that isn't enough? We do have support for a proxy object that can add these attributes without modifying the object itself. That wouldn't handle the __setitem__ case probably though, but it could be used to make sure an object is properly wrapped when accessing, even though the same object may be retrieved with multiple parents. Hm, I see this conflicting with SQLAlchemy's ORM, where it's certainly possible for the same object to appear multiple times in the object graph. Thanks for the proof of concept. With my modifications it makes my tests pass, but that's a sign the tests are insufficient. :) Given the complexities involved, I need to rethink whether this whole approach is the right one. If it could be made to work it'd make URL construction work out of the box, but there are other ways... Regards, Martijn --~--~-~--~~~---~--~~ 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: tracking parents, names
On Aug 18, 2008, at 11:42 AM, Martijn Faassen wrote: The idea is that if the object graph says: foo.bar.baz that'll be: bar.__name__ == 'bar' bar.__parent__ is foo baz.__name__ == 'baz' baz.__parent__ is bar In this case: foo.bar[key] it'll be: bar[key].__parent__ is foo.bar bar[key].__name__ == key where 'bar' is a collection. The part missing for me here is that bar, if its a MappedCollection, is not itself a mapped object in the same sense that foo or baz is. Keep in mind that a SQLAlchemy relation looks like: mapped class - collection - mapped class - (etc.) If this is correct, then the assignment of __name__ and __parent__ to bar, which falls under the collection, would go through different channels than the assignment of __name__ and __parent__ to baz, which falls under mapped class. In the former case, bar would receive its __name__ and __parent__ upon construction through its own collection_adapter where information about its relationship to foo is readily available, and baz would receive its __name__ and __parent__ when it is added to bar using bar's own instrumented mutator methods, where bar obviously knows the __name__ (the keyfunc()) and the __parent__ is itself. Does this make sense ? It's important to have this information no matter how the object graph is constructed, either by construction (as I think we covered with the __setitem__ change), or by retrieval from the database. The ORM populates collections through the @appender method, so there is no need to instrument the getter methods; nothing would enter the collection without being assigned a __parent__ and __name__. The only restriction is that if an object is placed in a collection with __parent__ and __name__, but is placed in *another* collection simultaneously, then __parent__ and __name__ would be ambiguous. My comments regarding threading and wrappers refer to the latter use case, which may not be needed. --~--~-~--~~~---~--~~ 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: SQLAlchemy getting slow when do a lots of things in transaction
I found that it seems not the transaction cause the performance problem. I think it maybe cause by use one session for a long time. Performance getting slower with the program run longer time. Once I stop the program and run it again, at first it works fast. So I think it is the problem of using a session for too many query and insert? I think the identify map or something else getting bigger and bigger with time. Is that correct? How to solve this problem? Thanks. Victor Lin. On 8月17日, 下午10時16分, Michael Bayer [EMAIL PROTECTED] wrote: On Aug 17, 2008, at 8:50 AM, Victor Lin wrote: Hi, I am using SQLAlchemy to write a application. I add a losts of rows into database and commit. Code like this: for item in itemList: # do some query session.query(...) # add a row newItem = newItem(...) session.commit() At first few rows, it gos fast. But after that, I found the loop getting slow when there is more and more data in one transaction. If I put commit inside loop, it runs very fast. for item in itemList: # do some query session.query(...) # add a row newItem = newItem(...) session.commit() What makes my program getting slow? And how to solve that problem(all stuff in transaction). Thanks. the size of the object list to be reconciled and sorted during flush gets very large - so issue a flush() after every 100 or so entries added to the session. they'll all execute within the context of the enclosing transaction. --~--~-~--~~~---~--~~ 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: SQLAlchemy getting slow when do a lots of things in transaction
On Aug 18, 2008, at 1:02 PM, Victor Lin wrote: I found that it seems not the transaction cause the performance problem. I think it maybe cause by use one session for a long time. Performance getting slower with the program run longer time. Once I stop the program and run it again, at first it works fast. So I think it is the problem of using a session for too many query and insert? I think the identify map or something else getting bigger and bigger with time. Is that correct? How to solve this problem? Assuming youre on at least version 0.4 with the default settings, the Session only holds onto objects that are referenced elsewhere in your application. If your app needs to hold many objects in memory but you dont want them all being maintained within the Session, remove them using Session.expunge(someobject) or everything using Session.expunge_all() (which used to be called Session.clear()). The flush() process in particular may be scanning the whole collection of objects. The 0.5 session has some enhancements such that a flush() usually does not scan the entire Session's contents. --~--~-~--~~~---~--~~ 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] Elixir 0.6.1 released!
I am very pleased to announce that version 0.6.1 of Elixir (http://elixir.ematia.de) is now available. As always, feedback is very welcome, preferably on Elixir mailing list. This is a minor release featuring some bug fixes (one of them to handle a late rename in SQLAlchemy's 0.5 beta cycle), a new, slighty nicer, syntax for providing custom arguments to the column(s) needed for ManyToOne relationships and some exception messages improvements. The full list of changes can be seen at: http://elixir.ematia.de/trac/browser/elixir/tags/0.6.1/CHANGES What is Elixir? - Elixir is a declarative layer on top of the SQLAlchemy library. It is a fairly thin wrapper, which provides the ability to create simple Python classes that map directly to relational database tables (this pattern is often referred to as the Active Record design pattern), providing many of the benefits of traditional databases without losing the convenience of Python objects. Elixir is intended to replace the ActiveMapper SQLAlchemy extension, and the TurboEntity project but does not intend to replace SQLAlchemy's core features, and instead focuses on providing a simpler syntax for defining model objects when you do not need the full expressiveness of SQLAlchemy's manual mapper definitions. Mailing list http://groups.google.com/group/sqlelixir/about -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ 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] Multiple Foreign Keys
Hi all, I’m fairly new to DBs and SQA and I’m having a few issues with multiple foreign keys. Essentially, I have a “Character” table with Character IDs and their associated name, and a Stats table, with containing data about various events, with two separate columns both with FKs to the Character ID table. These tables are stored in on my HDD relected at runtime, using the Table('Character', meta, autoload=True) format. My problems arise whenever I try and join these tables, I keep getting an error similar to this: “Can't determine join between Stats and 'Character'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.” Fair enough, but when I try to do this, along the lines of: s = join(Stats,Character,DeathKill.c.OBj1_uid==Character.c.character_uid) I get: “sqlalchemy.exc.ArgumentError: Not an executable clause: [DeathKill] JOIN [Character] ON [DeathKill].killer_uid = [Character].character_uid” Any suggestions or pointers would be greatly appreciated! Sorry I can’t post more code just now as I not near my work PC! Thanks, Ally --~--~-~--~~~---~--~~ 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: Multiple Foreign Keys
On Aug 18, 12:30 pm, Ally [EMAIL PROTECTED] wrote: Hi all, I’m fairly new to DBs and SQA and I’m having a few issues with multiple foreign keys. Essentially, I have a “Character” table with Character IDs and their associated name, and a Stats table, with containing data about various events, with two separate columns both with FKs to the Character ID table. These tables are stored in on my HDD relected at runtime, using the Table('Character', meta, autoload=True) format. My problems arise whenever I try and join these tables, I keep getting an error similar to this: “Can't determine join between Stats and 'Character'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.” Fair enough, but when I try to do this, along the lines of: s = join(Stats,Character,DeathKill.c.OBj1_uid==Character.c.character_uid) I get: “sqlalchemy.exc.ArgumentError: Not an executable clause: [DeathKill] JOIN [Character] ON [DeathKill].killer_uid = [Character].character_uid” Any suggestions or pointers would be greatly appreciated! Sorry I can’t post more code just now as I not near my work PC! There's some context missing here that would help with an answer. If you are just taking s and saying something along the lines of s.execute(), you'd need to first convert s into a select() construct using something like select([stats_table]).select_from(s). --~--~-~--~~~---~--~~ 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: SQLAlchemy getting slow when do a lots of things in transaction
Finally, I found the real reason of performance problem. There is a pickle filed in my table. SQLAlchemy update all rows every query. That's why it is so slow. By following the guide of FAQ, I have solved that problem. Thanks your help. Victor Lin. On 8月19日, 上午1時16分, Michael Bayer [EMAIL PROTECTED] wrote: On Aug 18, 2008, at 1:02 PM, Victor Lin wrote: I found that it seems not the transaction cause the performance problem. I think it maybe cause by use one session for a long time. Performance getting slower with the program run longer time. Once I stop the program and run it again, at first it works fast. So I think it is the problem of using a session for too many query and insert? I think the identify map or something else getting bigger and bigger with time. Is that correct? How to solve this problem? Assuming youre on at least version 0.4 with the default settings, the Session only holds onto objects that are referenced elsewhere in your application. If your app needs to hold many objects in memory but you dont want them all being maintained within the Session, remove them using Session.expunge(someobject) or everything using Session.expunge_all() (which used to be called Session.clear()). The flush() process in particular may be scanning the whole collection of objects. The 0.5 session has some enhancements such that a flush() usually does not scan the entire Session's contents. --~--~-~--~~~---~--~~ 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: Elixir 0.6.1 released!
I'm not trying to be an ass, but what are the advantages to using Elixer over using the declarative syntax such as: from sqlalchemy import * from sqlalchemy.orm import relation, scoped_session, sessionmaker from sqlalchemy.ext.declarative import declarative_base dbe = create_engine(somedatabaseconnectionstring) session = scoped_session(sessionmaker(bind=dbe)) mapper = session.mapper meta = MetaData(bind=dbe) Base = declarative_base(mapper=mapper) class People(Base): __table__ = Table('people', meta, autoload=True) def __repr__(self): return 'user: %s %s' % (self.fname, self.lname) print 'first get everyone in the database' people = People.query() for p in people: print p Jose Gaetan de Menten wrote: I am very pleased to announce that version 0.6.1 of Elixir (http://elixir.ematia.de) is now available. As always, feedback is very welcome, preferably on Elixir mailing list. This is a minor release featuring some bug fixes (one of them to handle a late rename in SQLAlchemy's 0.5 beta cycle), a new, slighty nicer, syntax for providing custom arguments to the column(s) needed for ManyToOne relationships and some exception messages improvements. The full list of changes can be seen at: http://elixir.ematia.de/trac/browser/elixir/tags/0.6.1/CHANGES What is Elixir? - Elixir is a declarative layer on top of the SQLAlchemy library. It is a fairly thin wrapper, which provides the ability to create simple Python classes that map directly to relational database tables (this pattern is often referred to as the Active Record design pattern), providing many of the benefits of traditional databases without losing the convenience of Python objects. Elixir is intended to replace the ActiveMapper SQLAlchemy extension, and the TurboEntity project but does not intend to replace SQLAlchemy's core features, and instead focuses on providing a simpler syntax for defining model objects when you do not need the full expressiveness of SQLAlchemy's manual mapper definitions. Mailing list http://groups.google.com/group/sqlelixir/about --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---