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.
This depends a lot on how you're using SQLA, if you are using the Session for
example and want it to be set per-session, or similar. I was going to
suggest the connection pool "on checkout" event but if before_execute() is
working for you, that's fine too - what I would suggest is that you store along
with the Connection what the last setting was, and don't actually emit a
catalog change statement if you're already on it.
To associate the desired value with the Connection so that it's available to
your before_execute() method in a non-global way, you should associate the
desired value with the Connection itself. An easy enough way is to use an
execution option:
conn = conn.execution_options(my_shard=some_shard)
your event can then read it from the _execution_options dictionary:
shard = conn._execution_options['my_shard']
if you want to do this with the ORM Query object, execution_options() is
available:
query = query.execution_options(my_shard=some_shard)
if you're looking to do this at the Session level, you can make a new Session
at the start of an operation, bind it with a connection:
sess = Session(bind=conn.execution_options(my_shard=some_shard))
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.
--
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.
