On Oct 22, 2012, at 10:01 PM, Michael Bayer wrote: > > On Oct 22, 2012, at 9:42 PM, Eric S wrote: > >> Hi everyone, >> >> First some terminology to prevent confusion: >> >> Database - Mysql database server; currently I have two of them. >> >> Catalog - Also typically called databases, but in this situation it is >> the thing created by the database when you issue this command "create >> database foo"; Currently I have 256 of them spread evenly over the >> databases. >> >> Shard - Lives in my application code and allows me to know in which >> database the catalog is currently living, and helps me create connections to >> it. >> >> My problem is how do I tell the sqlalchemy engines to switch to the correct >> catalog? >> I only want to have one engine per database and share its connection pool. >> >> My current solution is to use a before_execute event which changes the >> catalog. >> This works, but is not great because the catalog isn't passed in, it exists >> "globally", and >> given that my app is multithreaded now I have to use thread local storage >> just to make sure the correct value is used. > > > you can stuff it in the .info dictionary of the Session's existing connection: > > sess.connection().info['my_shard'] = some_shard > > lots of ways. depends on usage.
let me just continue, stressing that the idea of connection.execution_options() means you actually get a new Connection object back, which is linked to the original and uses the same DBAPI connection. The ORM's flush procedure can work with this to use a distinct Connection for each individual object instance it flushes. The approach taken in horizontal_shard.py offers some clues how to do this - in that system, a special hook called "connection_callable" is set on the Session, which is significant to the ORM unit of work process when it flushes objects. This callable is invoked for every mapper/object being flushed, and has the job of returning a Connection that will be used to emit INSERT/UPDATE/DELETE for that object specifically. You can hook into this callable and return new Connection objects using execution_options(my_shard=some_shard) to produce the new Connection object. I'd probably try storing them in a dictionary-per-shard id so that you aren't creating one Connection object per object being flushed, but that's an optimization - you'd have to make sure they get closed out when the lead connection does. It might also be possible to use horizontal_shard's approach more fully, though poking around a bit it might give you some resistance. The simplest way would be to provide a new get_bind() method, but that method usually intends to return an Engine object - maybe building a "mock" Engine that will return a Connection with a given shard id applied as an execution_option() would work. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
