On Oct 22, 2012, at 11:20 PM, Eric S wrote:
> 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.
well for a lot of this we have to rely on API documentation, and possibly API
docs are looking better in 0.8 than in 0.7.
here's execution options:
http://docs.sqlalchemy.org/en/rel_0_7/core/connections.html#sqlalchemy.engine.base.Connection.execution_options
but yes, those docs don't mention that you can really put whatever you want in
there and pull it from _execution_options, that hasn't been documented as
public API. It just seemed like a good solution to your issue as that API is
pretty stable in any case. I should probably add a fully public attribute to
read the current execution options.
and here's info:
http://docs.sqlalchemy.org/en/rel_0_7/core/connections.html#sqlalchemy.engine.base.Connection.info
which yeah, doesn't say much. More detail in this version regarding usage:
http://docs.sqlalchemy.org/en/latest/core/connections.html#sqlalchemy.engine.Connection.info
>
> 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.
--
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.
