Thanks -- the "before_compile" event seems interesting -- that could solve automatically adding a tenant on the SELECT side of things. What I wish for is a usage pattern like:
1. When a web request comes in, determine the appropriate tenant 2. Set the tenant somewhere associated with the context of the request 3. Use the database as if it were single-tenant, relying on event handlers/subclassed ORM objects/something to fill in the tenant for INSERT and SELECT statements. Does that seem realistic? For schema-per-tenant, I can image how that would be done with schema_translate_map or search_path, since that covers both INSERT and SELECT. For row-per-tenant, is there something roughly equivalent to before_compile to be able to modify INSERT statements to include a tenant? There's the engine before_execute event -- maybe that would work -- but someone in this group has said those are evil <https://groups.google.com/forum/#!searchin/sqlalchemy/evil%7Csort:date/sqlalchemy/rtgKPoplH74/rELcSA1LBAAJ> . The "MultiAlchemy <https://www.google.com/url?q=https%3A%2F%2Fgithub.com%2Fmwhite%2FMultiAlchemy&sa=D&sntz=1&usg=AFQjCNHl7sPWv5DqsD1eX9cyjzWinhg04g>" example I linked to earlier creates a Session subclass overriding the "add" method to automatically include the tenant, but it seems like that would miss objects that get added implicitly through relationships. Finally, a specific question about before_compile. If the query I want to modify is for first(), changing the query fails with: sqlalchemy.exc.InvalidRequestError: Query.filter() being called on a Query which already has LIMIT or OFFSET applied. To modify the row-limited results of a Query, call from_self() first. Otherwise, call filter() before limit() or offset() are applied. If I use from_self(), I get: RecursionError: maximum recursion depth exceeded while calling a Python object I guess because from_self causes the event handler to be fired again. I could use some kind of flag to avoid that, but needing to do so makes me wonder if I'm doing something wrong? Example: from sqlalchemy import create_engine, Column, Integer, String, event, Boolean from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker, Query engine = create_engine('sqlite:///:memory:', echo=True) Base = declarative_base() Session = sessionmaker(bind=engine) class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String) fullname = Column(String) password = Column(String) deleted = Column(Boolean, default=False) @event.listens_for(Query, "before_compile", retval=True) def no_deleted(query): for desc in query.column_descriptions: if desc['type'] is User: entity = desc['entity'] query = query.from_self().filter(entity.deleted == False) return query Base.metadata.create_all(engine) session = Session() ed_user = User(name='ed', fullname='Ed Jones', password='edspassword') session.add(ed_user) session.commit() any_not_deleted_user = session.query(User).first() Thanks again for the guidance, Eric -- 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.
