On Thu, Nov 23, 2017 at 6:55 PM, Антонио Антуан <a.ch....@gmail.com> wrote: > > > чт, 23 нояб. 2017 г. в 20:27, Mike Bayer <mike...@zzzcomputing.com>: >> >> On Thu, Nov 23, 2017 at 8:44 AM, Антонио Антуан <a.ch....@gmail.com> >> wrote: >> > >> >> A Query can have lots of entities in it, and if you're doing sharding a >> >> single result set can refer to any number of shard identifiers within >> >> not just a single result set but within a single row; they might have >> >> come from dozens of different databases at once >> > >> > In my case it is not possible: all entities in query can be gotten only >> > from >> > one particular shard. We have totally the same database structure for >> > each >> > shard. The difference is just data stored into database. No `shard_id` >> > or >> > any other key as part of primary key for any table. >> >> >> so just to note, these aren't "shards", they're tenants. you have a >> multi-tenant application, which is normally a really easy thing. but >> you have a few side applications that want to "cheat" and use the >> per-tenant object model across multiple tenants simultaneously in the >> scope of a single Session. >> >> > If I want to make query >> > for particular database I always want to retrieve data ONLY from that >> > database. And even more than that: ONLY one database during one session >> > transaction (or, in other words, one http-request to our app). >> >> if you have one "tenant id" per HTTP request, the standard HTTP >> request pattern is one Session per request. There's no problem in >> that case. You mentioned you have some non-flask applications that >> want to communicate with multiple tenants in one Session. > > > Yes, you're right. We have some offline actions, when we want to ask each > tenant about something specific. > I see, that currently the most safe way is to call `commit`, `rollback`, > `remove` or `expunge_all` on session instance: all this methods drops > identity map. Please let me know if I'm wrong.
A couple of things here. First, you are using ScopedSession, which is essentially a wrapper around an actual Session. The commit(), rollback() and expunge_all() methods are proxies that pass directly through to the underlying Session. I believe commit() and rollback() *expire* instances (so attributes will be reloaded on the next access), but don't actually remove them from the identity map (but I could be wrong about this). remove() is not a Session method though - it tells the ScopedSession to discard the current Session. A new Session will be created the next time you call one of the proxied methods. The default behaviour for ScopedSession is to use thread-locals, so each thread gets its own Session. However, you can provide your own scoping function that does whatever you want: http://docs.sqlalchemy.org/en/latest/orm/contextual.html#using-custom-created-scopes It sounds like you could pass a "get_current_tenant" function to the scoped session, along with a custom session_factory, to get the behaviour you want. Something like this (untested, would definitely require more care to make it thread-safe and so on): class SessionManager(object): def __init__(self, tenant_uris): self.engines = {} self.current_tenant = None for name, dburi in tenant_uris.items(): self.engines[name] = sa.create_engine(name) self.sessionmaker = saorm.sessionmaker() def get_current_tenant(self): return self.current_tenant def set_current_tenant(self, name): self.current_tenant = name def create_session(self): engine = self.engines[self.current_tenant] return self.sessionmaker(bind=engine) tenant_uris = { 'one': 'mysql://...', 'two': 'mysql://...', } manager = SessionManager(tenant_uris) Session = saorm.scoped_session(manager.create_session, scopefunc=manager.get_current_tenant) Base.query = Session.query_property As long as you call manager.set_current_tenant whenever you switch to querying a different tenant, this ought to work. But note that all of this confusion and complexity stems from using scoped sessions and Base.query. If you used explicit sessions everywhere, you would probably find your code less magical and easier to understand. Hope that helps, Simon -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
