Firstly, thank you so much for the quick and amazing response, that almost
never happens for me.
I am indeed using a session object per request so this approach looks most
appealing:
sess.connection().info['my_shard'] = some_shard
I'm new to sqlalchemy and as such had no idea that you could add arbitrary
values to the execution_options/info fields.
Where in the docs does it say this? I only found things such as
"autocommit" and "stream_results" shown.
Also, I guess I'll have to do some research on horizontal_shard, but that
looks like it might provide the most sane solution as well.
thanks again,
eric
On Monday, October 22, 2012 7:21:26 PM UTC-7, Michael Bayer wrote:
>
>
> 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 view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/d-fca69a5pMJ.
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.
