Re: [sqlalchemy] per-host pooling vs per-engine pooling
Here's my v3, with a mechanism to refresh the shared pool when an engine rebuilds it via dispose http://tarek.pastebin.mozilla.org/1376367 It also have a few thread locks to try to make it thread-safe. I've written some tests with hundreds of threads and it seems to work fine, but I don't really know if there are other places where I should do something. On Fri, Nov 4, 2011 at 4:45 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Nov 4, 2011, at 3:41 AM, Tarek Ziadé wrote: On Fri, Nov 4, 2011 at 10:33 AM, Tarek Ziadé ziade.ta...@gmail.com wrote: ... Will try and come back ;) Hello, I am back \o/ Here's my v2: http://tarek.pastebin.mozilla.org/1373520 This time I have created a custom Strategy and Engine classes, and the get_engine() function takes care of instanciating a pool for the server if needed, and an engine instance per sqlurl, using that pool/ I have used Engine.contextual_connect for the schema switching, and Engine.execute has our custom execution things. I guess this is much cleaner since anyone can use create_engine() with my new strategy. The only assumption I am making is that there's a shared pool on the same host. Does this look better ? Thanks for your help so far OK this is clearer to me. So you're really trying to get it so that any number of create_engine() calls all use the same pool based on hostname, OK. If i have time today I might want to try paring this down a lot more. Also not sure why you need the disconnect check stuff in execute() , SQLA has that stuff built in. But it does raise an important point that the mechanism by which we dispose the pool when a disconnect is detected probably should be different here, since the disposal is per-engine, just gives itself a new pool. The other engines will still point to the old, disposed pool, probably causing some kind of pileup. Cheers Tarek -- Tarek Ziadé | http://ziade.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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://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 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. -- Tarek Ziadé | http://ziade.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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] using Events to reduce memory footprint?
I am curious if Events (new feature as of 0.7) could be used to reduce memory footprint in certain situations or is it better achieved with other features such as lazy loading. For example, consider a complex mapped object (containing at least one related table). Using joinedload_all option, SQLAlchemy conveniently produces complex object from the query but the entire object resides in session for the duration of the processing, which could take a lot of memory. In situations where complex mapped objects can be output as soon as they are retrieved from the database one could begin streaming the output as soon as the containing object gets loaded. The full list of contained objects also need not be kept in memory -- once a contained object gets output it could be removed from the list. I would appreciate your thoughts on this. -- 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] Select like but the other way around
I'm trying to do a like statement in a query filter. I'm fine doing it one way for instance session.query(Table).filter(Table.path.like(C:\Test\%)) which would hopefully return all folders and files in the folder Test but what if I want to do it the other way around and pass C:\Test\testfile.txt and return all the folders. I want something like C:\Test\testfile.txt.like(Table.path+%) but obviously a string wont have the method like. is there a function I can import that takes 2 arguements or another method I can use to achieve this? Thanks! Paul -- 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.
Re: [sqlalchemy] Select like but the other way around
sure it does, if you convert it to a SQL token first: literal(C:\test\testfile.txt).like(Table.path + %) or even literal(C:\test\testfile.txt).startswith(Table.path) On Nov 7, 2011, at 8:40 AM, Paul wrote: I'm trying to do a like statement in a query filter. I'm fine doing it one way for instance session.query(Table).filter(Table.path.like(C:\Test\%)) which would hopefully return all folders and files in the folder Test but what if I want to do it the other way around and pass C:\Test\testfile.txt and return all the folders. I want something like C:\Test\testfile.txt.like(Table.path+%) but obviously a string wont have the method like. is there a function I can import that takes 2 arguements or another method I can use to achieve this? Thanks! Paul -- 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. -- 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.
Re: [sqlalchemy] Re: Select like but the other way around
On Nov 7, 2011, at 9:16 AM, Paul wrote: Michael Bayer mike_mp at zzzcomputing.com writes: sure it does, if you convert it to a SQL token first: literal(C:\test\testfile.txt).like(Table.path + %) or even literal(C:\test\testfile.txt).startswith(Table.path) Thanks for the quick reply! One more quick question, how would I combine two of these like statements, if I just use 'and' between them I get 'TypeError: Boolean value of this clause is not defined'. you'd use and_() : http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=and_#sqlalchemy.sql.expression.and_ On Nov 7, 2011, at 8:40 AM, Paul wrote: I'm trying to do a like statement in a query filter. I'm fine doing it one way for instance session.query(Table).filter(Table.path.like(C:\Test\%)) which would hopefully return all folders and files in the folder Test but what if I want to do it the other way around and pass C:\Test\testfile.txt and return all the folders. I want something like C:\Test\testfile.txt.like(Table.path+%) but obviously a string wont have the method like. is there a function I can import that takes 2 arguements or another method I can use to achieve this? Thanks! Paul -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy at googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscribe at googlegroups.com. For more options, visit this group at http://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 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. -- 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: foreign key info lost across metadata pickling?
Yes that looks familiar :) I'll try to come up with a good test case here to prove the error (if any). On Nov 6, 7:45 am, Michael Bayer mike...@zzzcomputing.com wrote: FTR, pickling of metadata is a covered use case (meaning a regression of basic FK activity would have been detected), here's a simple test which passes: from sqlalchemy import * m = MetaData() a = Table('a', m, Column('x', Integer, primary_key=True), Column('z', Integer) ) b = Table('b', m, Column('x', Integer, primary_key=True), Column('y', Integer, ForeignKey('a.x')) ) assert b.c.y.references(a.c.x) assert not b.c.y.references(a.c.z) import pickle m2 = pickle.loads(pickle.dumps(m)) a2 = m2.tables['a'] b2 = m2.tables['b'] assert b2.c.y.references(a2.c.x) assert not b2.c.y.references(a2.c.z) On Nov 5, 2011, at 7:30 AM, Michael Bayer wrote: can you provide a very simple and pared down test case using table metadata only? On Nov 5, 2011, at 6:41 AM, botz wrote: version 0.7.3. I have tables with foreign keys defined, and the orm mapping (with relationships corresponding to the foreign keys) works fine with autoload=True on the tables. If I persist the metadata then with pickle, and then reload the app using pickled metadata and autoload=False, I get the following: sqlalchemy.exc.ArgumentError: Could not locate any foreign-key- equated, locally mapped column pairs for primaryjoin condition 'client_statuses.client_status_code = clients.client_status_code' on relationship Client.status. Ensure that the referencing Column objects have a ForeignKey present, or are otherwise part of a ForeignKeyConstraint on their parent Table, or specify the foreign_keys parameter to this relationship. For more relaxed rules on join conditions, the relationship may be marked as viewonly=True. Error occurs for both values of extend_existing. Seems to be a regression from 0.6 where it worked ok. -- 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 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 sqlalchemy@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 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.
Re: [sqlalchemy] using Events to reduce memory footprint?
On Nov 7, 2011, at 7:14 AM, Victor Olex wrote: I am curious if Events (new feature as of 0.7) could be used to reduce memory footprint in certain situations or is it better achieved with other features such as lazy loading. For example, consider a complex mapped object (containing at least one related table). Using joinedload_all option, SQLAlchemy conveniently produces complex object from the query but the entire object resides in session for the duration of the processing, which could take a lot of memory. In situations where complex mapped objects can be output as soon as they are retrieved from the database one could begin streaming the output as soon as the containing object gets loaded. The full list of contained objects also need not be kept in memory -- once a contained object gets output it could be removed from the list. I would appreciate your thoughts on this. Well there's a few angles to this so far. The Query can be instructed to yield rows in chunks as they come in using yield_per(). Though when using joinedload() for collections, it's pretty much guaranteed to produce incorrect results: Rows 1-3, user / address: userid=1, name='user', address_id=1, email='a1' userid=1, name='user', address_id=2, email='a2' userid=1, name='user', address_id=3, email='a3' now suppose it yields. You get back a User() object, with an addresses collection of three elements. Next iteration, there's two more addresses. So the collection you just got, and potentially operated upon, was wrong. userid=1, name='user', address_id=4, email='a4' userid=1, name='user', address_id=5, email='a5' So two more User rows - this means you'd get back either the same User object or another one, depending on if the previous one is still hanging around, and a different addresses collection - also blowing away any changes you might have made to .addresses. So the data consistency here is totally broken. This is why yield_per() says it can't be used safely with joinedload(). In the subqueryload use case, yield_per() is mostly useless, as all related collections are loaded at once. If Users 1, 2, and 3 are yielded, the addresses collection will proceed to load all the addresses for all users in the result, so you still spend time loading a record set at least as large as the whole thing before getting the first batch. Next angle, use events.The use case here would be, I don't really care about getting the result, I'd like to just intercept objects as they come in. You can do that using a handful of different events, including append_result, populate_instance, and probably most usefully load(): http://www.sqlalchemy.org/docs/orm/events.html#sqlalchemy.orm.events.InstanceEvents.load .If you then turn on yield_per() the results will be chunked and not stored in memory, but you'd still need to pretty much not access any collections (or at least, not rely on them remaining consistent and not mutating them) since they will be inconsistently populated. -- 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. -- 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: using Events to reduce memory footprint?
Thanks. I didn't know about yield_per. If I understand correctly, angles 1 and 3 share the same weakness in that object identities may be different between chunks. For situations where object state will not be changed that is not terribly important. Also it seems that in the first scenario as long as the containing object (User) remains in session it will retain its correct identity while the collection of contained objects (addresses) will be inconsistent between chunks. Again, fair enough for chunked output. Now if I can generalize this to n-level of relationship traversal we could be onto something good. On Nov 7, 1:36 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Nov 7, 2011, at 7:14 AM, Victor Olex wrote: I am curious if Events (new feature as of 0.7) could be used to reduce memory footprint in certain situations or is it better achieved with other features such as lazy loading. For example, consider a complex mapped object (containing at least one related table). Using joinedload_all option, SQLAlchemy conveniently produces complex object from the query but the entire object resides in session for the duration of the processing, which could take a lot of memory. In situations where complex mapped objects can be output as soon as they are retrieved from the database one could begin streaming the output as soon as the containing object gets loaded. The full list of contained objects also need not be kept in memory -- once a contained object gets output it could be removed from the list. I would appreciate your thoughts on this. Well there's a few angles to this so far. The Query can be instructed to yield rows in chunks as they come in using yield_per(). Though when using joinedload() for collections, it's pretty much guaranteed to produce incorrect results: Rows 1-3, user / address: userid=1, name='user', address_id=1, email='a1' userid=1, name='user', address_id=2, email='a2' userid=1, name='user', address_id=3, email='a3' now suppose it yields. You get back a User() object, with an addresses collection of three elements. Next iteration, there's two more addresses. So the collection you just got, and potentially operated upon, was wrong. userid=1, name='user', address_id=4, email='a4' userid=1, name='user', address_id=5, email='a5' So two more User rows - this means you'd get back either the same User object or another one, depending on if the previous one is still hanging around, and a different addresses collection - also blowing away any changes you might have made to .addresses. So the data consistency here is totally broken. This is why yield_per() says it can't be used safely with joinedload(). In the subqueryload use case, yield_per() is mostly useless, as all related collections are loaded at once. If Users 1, 2, and 3 are yielded, the addresses collection will proceed to load all the addresses for all users in the result, so you still spend time loading a record set at least as large as the whole thing before getting the first batch. Next angle, use events. The use case here would be, I don't really care about getting the result, I'd like to just intercept objects as they come in. You can do that using a handful of different events, including append_result, populate_instance, and probably most usefully load():http://www.sqlalchemy.org/docs/orm/events.html#sqlalchemy.orm.events. If you then turn on yield_per() the results will be chunked and not stored in memory, but you'd still need to pretty much not access any collections (or at least, not rely on them remaining consistent and not mutating them) since they will be inconsistently populated. -- 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 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 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.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
I've got some thoughts on this but time is scant today to come up with what I'm thinking. Basically doing this totally with the pool itself. I'll try to get some time to work on it On Nov 7, 2011, at 3:32 AM, Tarek Ziadé wrote: Here's my v3, with a mechanism to refresh the shared pool when an engine rebuilds it via dispose http://tarek.pastebin.mozilla.org/1376367 It also have a few thread locks to try to make it thread-safe. I've written some tests with hundreds of threads and it seems to work fine, but I don't really know if there are other places where I should do something. On Fri, Nov 4, 2011 at 4:45 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Nov 4, 2011, at 3:41 AM, Tarek Ziadé wrote: On Fri, Nov 4, 2011 at 10:33 AM, Tarek Ziadé ziade.ta...@gmail.com wrote: ... Will try and come back ;) Hello, I am back \o/ Here's my v2: http://tarek.pastebin.mozilla.org/1373520 This time I have created a custom Strategy and Engine classes, and the get_engine() function takes care of instanciating a pool for the server if needed, and an engine instance per sqlurl, using that pool/ I have used Engine.contextual_connect for the schema switching, and Engine.execute has our custom execution things. I guess this is much cleaner since anyone can use create_engine() with my new strategy. The only assumption I am making is that there's a shared pool on the same host. Does this look better ? Thanks for your help so far OK this is clearer to me. So you're really trying to get it so that any number of create_engine() calls all use the same pool based on hostname, OK. If i have time today I might want to try paring this down a lot more. Also not sure why you need the disconnect check stuff in execute() , SQLA has that stuff built in.But it does raise an important point that the mechanism by which we dispose the pool when a disconnect is detected probably should be different here, since the disposal is per-engine, just gives itself a new pool. The other engines will still point to the old, disposed pool, probably causing some kind of pileup. Cheers Tarek -- Tarek Ziadé | http://ziade.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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://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 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. -- Tarek Ziadé | http://ziade.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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://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 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] subquery with one-to-many table
Hi all, I'm using MySQL 5.1 for our database, Python 2.7.2 and SqlAlchemy 0.7.1, and I'm having trouble figuring out how to use a subquery to get a single record from a one-to-many relationship with the main query. Here's my code: note_stmt = session.query(CustomerNote.note) \ .filter(CustomerNote.order_id == OrderModel.order_id) \ .order_by(desc(CustomerNote.created)) \ .limit(1) q = session.query(OrderModel.order_date, \ OrderModel.reference, \ OrderModel.order_id,\ OrderItem.project_token, \ OrderItem.book_id, \ (OrderItem.gross + OrderItem.shipping_price).label(gross), \ OrderItem.qty, note_stmt.subquery().alias(customer_note)) \ .join((History, History.id == OrderModel.order_id)) \ .join((OrderItem, OrderItem.order_id == OrderModel.order_id)) \ .filter(History.note == params[exact_note]) \ .filter(History.type == params[type]) \ .filter(History.station == params[station]) \ .filter(OrderModel.state_id == params[state_id]) \ .filter(OrderModel.client_type_id == 3) \ .q.slice(start, start + length) retval = q.all() The q query is the main query and there exists one-to-one relationships with the two joins going on there. The note_stmt subquery has a one-to-many relationship with OrderModel between the order_id column of the two tables. I'm trying to get the latest CustomerNote included as a column in the main query (alias(customer_note)), but what I'm getting is some random (?) note because I don't think the subquery is being correlated with the main query with CustomerNote.order_id == OrderModel.order_id. I guess I don't understand how to set that up. Any help or pointers would be greatly appreciated! Thanks! Doug -- 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.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
OK, here's my attempt.Takes place pretty much at the pool level and is not too intrusive, also leaves in place all the existing reconnect stuff which should just work as is, not to mention leaves creator() in place which also has some exception handling. I'm hoping you can test it out and grok the general idea in case it needs adjustment, and if we can write full tests it can be part of the distribution too, maybe as an ext. On Nov 7, 2011, at 3:32 AM, Tarek Ziadé wrote: Here's my v3, with a mechanism to refresh the shared pool when an engine rebuilds it via dispose http://tarek.pastebin.mozilla.org/1376367 It also have a few thread locks to try to make it thread-safe. I've written some tests with hundreds of threads and it seems to work fine, but I don't really know if there are other places where I should do something. On Fri, Nov 4, 2011 at 4:45 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Nov 4, 2011, at 3:41 AM, Tarek Ziadé wrote: On Fri, Nov 4, 2011 at 10:33 AM, Tarek Ziadé ziade.ta...@gmail.com wrote: ... Will try and come back ;) Hello, I am back \o/ Here's my v2: http://tarek.pastebin.mozilla.org/1373520 This time I have created a custom Strategy and Engine classes, and the get_engine() function takes care of instanciating a pool for the server if needed, and an engine instance per sqlurl, using that pool/ I have used Engine.contextual_connect for the schema switching, and Engine.execute has our custom execution things. I guess this is much cleaner since anyone can use create_engine() with my new strategy. The only assumption I am making is that there's a shared pool on the same host. Does this look better ? Thanks for your help so far OK this is clearer to me. So you're really trying to get it so that any number of create_engine() calls all use the same pool based on hostname, OK. If i have time today I might want to try paring this down a lot more. Also not sure why you need the disconnect check stuff in execute() , SQLA has that stuff built in.But it does raise an important point that the mechanism by which we dispose the pool when a disconnect is detected probably should be different here, since the disposal is per-engine, just gives itself a new pool. The other engines will still point to the old, disposed pool, probably causing some kind of pileup. Cheers Tarek -- Tarek Ziadé | http://ziade.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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://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 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. -- Tarek Ziadé | http://ziade.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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. from sqlalchemy import event, pool, create_engine from sqlalchemy.engine import url class ProxyRecord(pool._ConnectionRecord): def __init__(self, pool): super(ProxyRecord, self).__init__(pool) self.proxied = self.connection._connection_record def close(self): self.proxied.close() def invalidate(self, e=None): self.proxied.invalidate(e=e) class ProxyPool(pool.Pool): def __init__(self, proxying): self.proxying = proxying pool.Pool.__init__(self, self.__creator) def __creator(self): return self.proxying.connect() def status(self): return ProxyPool(%s) % self.proxying.status() def _do_return_conn(self, conn): self.proxying._do_return_conn(conn.proxied) def _do_get(self): return ProxyRecord(self) def dispose(self): self.proxying.dispose() def recreate(self): return self def singleton_engine(key_from_url, new_engine): _reg = {} def create_engine_singleton(name_or_url, **kw): u = url.make_url(name_or_url) key = key_from_url(u) if key in _reg: eng = _reg[key] else: eng = _reg[key] = create_engine(u, **kw) kw['pool'] = pool = ProxyPool(eng.pool) eng = create_engine(u, **kw) new_engine(eng) return eng return create_engine_singleton if __name__ ==