"""A front end for SQLAlchemy applications with optional Pylons support. STATUS 2007/08/13: - SAContext 0.3.5 is the current version, for both SQLAlchemy 0.4.x and 0.3.x. - SAContext is DEPRECATED and in BUGFIX-ONLY MODE. See "SQLAlchemy for people in a hurry" for the recommended way to use SQLAlchemy 0.4 in Pylons applications. http://wiki.pylonshq.com/display/pylonscookbook/SQLAlchemy+for+people+in+a+hurry - Compatible with Pylons 0.9.6, including rc1 and rc2 prereleases, and Subversion since July 2007 or so. Not compatible with Pylons 0.9.5. SAContext organizes your SQLAlchemy engines, metadatas, and sessions into one convenient object. That's *all* it does. SAContext helps new SQLAlchemists get their applications up and running quickly, while also scaling to large multi-database use cases. SAContext home page: http://sluggo.scrapping.cc/python/ A test suite (test_sacontext.py) and Pylons demo (SAC_Demo) are available on the home page. Copyright (c) 2007 by Mike Orr and Michael Bayer . Permission to copy & modify granted under the MIT license (http://opensource.org/licenses/mit-license.php). Basic Concepts ============== Here's the simplest one-database usage for normal (non-Pylons) applications:: 1 import sqlalchemy as sa 2 from sqlalchemy.orm import mapper 3 from sacontext import SAContext 4 sac = SAContext() 5 sac.add_engine(key=None, uri="sqlite://") 6 users = sa.Table("Users", sac.metadata, sa.Column(...)) 7 class User(object): pass 8 mapper(User, users) 9 me = sac.query(User).get(123) 10 me.age += 1 11 sac.session.flush() Line 5 creates the default engine and its metadata. We know it's the default engine because the 'key' arg is None. (You can use None, sacontext.DEFAULT, or "default" interchangeably to refer to the default engine.) The URI indicates this is a SQLite memory database. We can access the default engine later via "sac.engine", and the default metadata via "sac.metadata". In fact, we use the default metadata in line 6 to define a table. "sac.session" in line 11 is a SQLAlchemy ORM session local to the current thread. It comes from a hidden SessionContext ("sac.session_context"). "sac.query" in line 9 creates a SQLAlchemy Query; it's a shortcut for "sac.session.query", and equivalent to "session_context.current.query" in some other SQLAlchemy applications. Ah, but we can do more than this! Say you need to pass some engine options to SQLAlchemy.create_engine:: sac = SAContext() sac.add_engine(key=None, uri="mysql://...", engine_options={"echo": True}) Say you want to load a table schema from an existing database table:: table1 = sa.Table("Table1", sac.metadata, autoload=True) SAContext has built-in support for SQLAlchemy's SessionContext mapper extension:: mapper(User, users, extension=sac.ext) me = User() me.age = 24 sac.session.flush() Without the extension you'd have to do a separate "save" step:: mapper(User, users) me = User() me.age = 24 sac.session.save(me) sac.session.flush() More information on the SessionContext extension is in the "Plugins" section of the SQLAlchemy manual. If you use the "assignmapper" extension you'll have to supply the session context directly:: assign_mapper(sac.session_context, User, users, properties={...}, ...) Pylons usage ============ PylonsSAContext does everything SAContext does but it also knows how to get engine data from the Pylons configuration. Put this in your model (myapp/models/__init__.py):: from sacontext import PylonsSAContext sac = PylonsSAContext() sac.add_engine_from_config("default") This reads the URI from the Pylons 'app_conf' dict under the key "sqlalchemy.default.uri". As with .add_engine, you can use "default", sacontext.DEFAULT or None interchangeably to refer to the default engine. Other engine options may also be specified:: sqlalchemy.default.uri = mysql://username@localhost/mydb sqlalchemy.default.echo = true sqlalchemy.default.echo_pool = false sqlalchemy.default.pool_recycle = 3600 Options listed as boolean or integer in the SQLAlchemy manual are automatically coverted to the correct types. Other options are left as strings. There's no way to specify options that must be other types (e.g., 'poolclass' or 'creator'). All options are passed directly to sqlalchemy.create_engine, which may raise an exception if it doesn't like an option. *Note:* the 'pool_recycle' option is important for MySQL, which unilaterally closes connections after an idle period. Using a dead connection causes a "MySQL server has gone away" errors in your application. The default timeout is 8 hours (configurable in my.cnf), so long-running applications like Pylons shoud set 'pool_recycle' much lower than that: 3600 seconds = 1 hour. .add_engine_from_config has several other arguments which we'll look at later. *Important:* Put this line at the beginning of your base controller's .__call__ method (myapp/lib/base.py):: model.sac.session.clear() This erases any stray session data left from the previous request in this thread. Otherwise you may get random errors or corrupt data. Or "del model.sac.session_context.current" if you prefer. Multiple static databases ========================= Say your application stores its logging tables in a separate database:: sac = SAContext() sac.add_engine(None, "mysql://.../myapp") sac.add_engine("logs", "mysql://.../logs") table1 = sa.Table("Table1", sac.get_metadata(None), sa.Column(...)) access_log = sa.Table("Access", sac.get_metadata("logs"), autoload=True) Here we have two engines, the default and "logs". Each table is bound to its appropriate database via its metadata. SQLAlchemy will remember which tables go with which database no matter what SQL or ORM queries you do, even if you use tables from multiple databases in the same session. This approach is very simple but it has one limitation: each table remains connected to the *same* database throughout the lifetime of the application. You *can* reconnect a metadata to a different engine but we don't recommend it; it destroys the parallelism of an engine-metadata pair under the same key. If the same table needs to access different databases at different times, use one of the dynamic approaches below, create your own metadata, pass an explicit engine to every SQL or ORM operation, or use the "table.tometadata(metadata2)" construct. There's no problem using SAContext's engines and sessions with your own metadatas. Multiple dynamic databases, one per session =========================================== Say your application has the same tables in multiple databases, but uses exactly one database in each session. Think of a blog application with each blog in a separate database: every request connects to exactly one blog. For this you'll have to use a SAContext strategy called BoundSessionStrategy. In this case the metadatas are *unbound*; it's the *session* that's bound to an engine. :: # At the beginning of the application. from sacontext import BoundSessionStrategy sac = SAContext(strategy=BoundSessionStrategy()) sac.add_engine("green", "mysql://username@localhost/green") sac.add_engine("blue", "mysql://username@localhost/blue") table1 = sa.Table("Table1", sac.get_metadata("green"), ...) # At the beginning of the request. Say this request is for the "blue" blog. # SQLAlchemy 0.4 uses sac.session.bind instead of sac.session.bind_to. sac.session.bind_to = sac.get_engine("blue") Because the metadatas are unbound, it doesn't matter which one you use to define the tables with. The tradeoff is, you'll have to pass an explicit engine to any SQL construct that doesn't use the session:: table1.select(..., engine=sac.get_engine("green")).execute() sac.get_engine("blue").execute("ALTER TABLE foo CHANGE foocol1 ...") You can use BoundSessionStrategy even if you *only* intend to do low-level SQL queries and *never* use the session. The strategy name is a misnomer in this case but it still works. You'll have to pass an engine to every method as above, or create your own metadata and call its .connect method. (The latter is not thread safe unless you're using ThreadLocalMetaData/DynamicMetaData.) This example does not have a default engine, so the "sac.engine" and "sac.metadata" properties are not available. Multiple dynamic databases, several per session =============================================== This is a combination of the previous two scenarios. Say you need to bind different tables to different engines in the same session, but choose a different set of databases each session:: # At the beginning of the application. sac = SAContext(BoundSessionStrategy()) sac.add_engine("dba1", "mysql://username@localhost/dba1") sac.add_engine("dba2", "mysql://username@localhost/dba2") sac.add_engine("dbb1", "mysql://username@localhost/dbb1") sac.add_engine("dbb2", "mysql://username@localhost/dbb2") table1 = sa.Table("Table1", sac.get_metadata("dba1"), ...) table2 = sa.Table("Table1", sac.get_metadata("dba2"), ...) # At the beginning of the request. Say this request is for the "b" series. sac.session.bind_table(table1, "dbb1") sac.session.bind_table(table2, "dbb2") Now the session can access both table1 and table2, and any changes will be written to database "b1" and "b2" respectively. There's also a .bind_mapper method that binds a mapper rather than a table. These methods affect only the current session, not your global table and mapper objects. Non-ORM SQL operations will still require an explicit engine argument, as in the previous scenario. More about PylonsSAContext ========================== Now that we've discussed multiple engines, we see that PylonsSAContext.add_engine_from_config takes an argument 'key' that works exactly like .add_engine's 'key' argument. Passing None creates or replaces the default engine; passing a string creates or replaces a non-default engine. The prefix in the config file is assumed to be "sqlalchemy.default." for the default engine, or "sqlalchemy.the_key." for a non-default engine. You can explicitly set a different sub-prefix with the 'config_key' argument. :: sac.add_engine_from_config(None, config_key="db1") # 'sqlalchemy.db1.uri' -> default engine sac.add_engine_from_config("green", config_key="verde") # 'sqlalchemy.verde.uri' -> "green" engine The 'engine_options' and 'default_options' arguments are optional dicts containing additional options for sqlalchemy.create_engine. This is useful for non-scalar options that can't be specified in the config file; e.g., 'creator'. The keys should not have prefixes (e.g., "echo"). The values must be the correct types: no int/bool conversion is done. The difference between 'engine_options' and 'default_options' is that the default options are used if the corresponding keys do not exist in the config file, whereas keys in 'engine_options' override the config file. The 'uri' and 'default_uri' arguments work the same way: 'default_uri' is used if no URI was specified in the config file, and 'uri' overrides the config file's URI. 'config' is an optional dict which, if specified, will be used as the configuration dict. Otherwise the method will ask Pylons or PasteDeploy for the configuration. Two support methods .parse_engine_options and .get_app_config are called by .add_engine_from_config but may be used standalone. PylonsSAContext overrides ._get_session_scope to provide a session scope suitable for Pylons. The scope spans the current Pylons application in the current thread. Two routines are considered the same application if they share the same pylons.g object. It is assumed that other SAContext subclasses for other frameworks will eventually be written. More about BoundSessionStrategy =============================== BoundSessionStrategy was written by SQLAlchemy's author Mike Bayer, and I (Mike Orr) don't fully understand it. The constructor takes two arguments which don't seem useful to me but may be useful to advanced users. The argument are 'connectionbound' and 'binds'. 'binds' is a dict of tables/mappers to engine keys; it initializes every session to use those bindings. I'm not sure how useful it is given that you probably want to change the bindings depending on the request (otherwise you'd be using the default strategy that binds a table permanently to an engine), and many frameworks will have already created the session by the time you decide which tables to bind to. But it's here if you find it useful. You can try clearing the self.binds list and calling self.bind_table/mapper at every request; I'm not sure how well it will work. If 'connectionbound' is true, it binds each table/mapper to a specific connection rather than just to an engine. This may be useful for applications that want to keep a tight reign on which connection is used where. It works only with tables/mappers in the self.binds list, not with any you manually call sac.session.bind_table/mapper on. 'connectionbound' is not fully implemented; it doesn't share connections when it should. ElixirStrategy ============== This is one way to use Elixir with SAContext. I don't use Elixir so I don't know if it's the best way or not. This class was contributed by beachcoder. Usage:: sac = SAContext(strategy=ElixirStrategy) You can also use this strategy with PylonsSAContext. CHANGELOG ========= * 0.3.5 MO - SQLAlchemy 0.4 compatibility contributed by Jason Kirtland and endorsed by Michael Bayer. - SAContext is DEPRECATED and in BUGFIX-ONLY MODE. See "SQLAlchemy for people in a hurry" for the recommended way to use SQLAlchemy 0.4 in Pylons applications. http://wiki.pylonshq.com/display/pylonscookbook/SQLAlchemy+for+people+in+a+hurry * 0.3.4 MO - Change pylons.config import for forward compatibility. * 0.3.3 MO - Fix spelling of Elixir throughout. * 0.3.2 MO - Stable version for SQLAlchemy 0.3.x and Pylons 0.9.6. NOT forward compatible with SQLAlchemy 0.4. - .add_engine and .add_engine_from_config now returns the engine and metadata it created as a tuple, in case you want to hold an external reference to either or both. Requested by Andrey Petrov. - New strategy ElixirStategy contributed by beachcoder. This code is experimental. - Bugfix in ._check_engine_key. Thanks to Karl Guterin for reporting it. * 0.3.1 MO - All 'key' arguments recognize "default", sacontext.DEFAULT, and None as aliases for the default engine. Internally it's keyed under "default". * 0.3.0 MO - Several backward-incompatible changes. - .__init__ takes only the 'strategy' arg and does not configure a default engine. This means applications must call .add_engine or .add_engine_from_config when previously they didn't. - PylonsSAContext.add_engine no longer reads the Pylons configuration; use new method .add_engine_from_config for this. - .get_engine, .get_metadata, and .get_connectable require an argument. - The default engine is now registered under the None key rather than "default". When adding the default engine you must explicitly pass None to .add_engine and .add_engine_from_config; the argument is required. - Bugfixes in PylonsSAContext and BoundSessionStrategy thanks to Phillip Jenvey and Avdd. * 0.2.1 MO - Change imports for forward compatibility with SQLAlchemy 0.4. - .session_context is now a public attribute. - 'dburi' in a Pylons config file is now 'uri': sqlalchemy.default.uri. - Fix variable names in .get_engine and .bind_table. - Several of these are thanks to Waldemar Osuch's patch. * 0.2.0 MO - Add 'config' argument to PylonsSAContext.__init__ & .add_engine. - Fix variable name in PylonsSAContext.__init. * 0.1.0 (2007-06-??) MO - Initial unstable release. """ import thread import warnings import sqlalchemy from sqlalchemy import create_engine, MetaData, Table from sqlalchemy.orm import create_session from sqlalchemy.ext.sessioncontext import SessionContext # Has SQLALchemy 0.4 API? True for SQLAlchemy 0.3.9 and above. SQLALCHEMY_0_4_API = hasattr(sqlalchemy, "__version__") # Default engine/metadata. DEFAULT = "default" if SQLALCHEMY_0_4_API: # Yes, we know we're using deprecated features. warnings.filterwarnings("ignore", "SessionContext is deprecated") warnings.filterwarnings("ignore", "SessionContextExt is deprecated") def asbool(obj): """Borrowed from PasteDeploy-1.3/paste/deploy/converters.py (c) 2005 by Ian Bicking, MIT license. """ if isinstance(obj, (str, unicode)): obj = obj.strip().lower() if obj in ['true', 'yes', 'on', 'y', 't', '1']: return True elif obj in ['false', 'no', 'off', 'n', 'f', '0']: return False else: raise ValueError( "String is not true/false: %r" % obj) return bool(obj) def merge(*dicts): """Merge several dicts into one. Key collisions are resolved in favor of the dict on the left. `None` arguments are allowed and ignored. """ ret = {} for dic in reversed(dicts): if dic: ret.update(dic) return ret #### SAContext class for any application #### class SAContext(object): def __init__(self, strategy=None): """Create an SAContext. strategy: a strategy instance, or None for the default strategy. Strategies currently available are BoundMetaDataStrategy (default) and BoundSessionStrategy. """ self._engines = {} self._metadatas = {} self._strategy = strategy or BoundMetaDataStrategy() self.session_context = SessionContext( lambda: self._strategy.create_session(self), self._get_session_scope) def add_engine(self, key, uri, engine_options=None): """Add an engine and create a metadata for it. (The metadata will be bound or unbound according to the strategy.) key: string or None: An identifier for the engine and its metadata. Pass None to add or replace the default engine. Otherwise pass a short string name (e.g., "logs" or "western_region") to add or replace a non-default engine. uri: string: a SQLAlchemy database URI. engine_options: dict: extra args for sqlalchemy.create_engine(). Returns a tuple: (engine, metadata). Normally you'd access these as .engine, .metadata, .get_engine(key), .get_metadata(key), but if you want to hold external references to them you can capture the return value. """ if key is None: key = DEFAULT if engine_options is None: engine_options = {} engine = create_engine(uri, **engine_options) self._engines[key] = engine metadata = self._strategy.create_metadata(key, self) self._metadatas[key] = metadata return engine, metadata def get_connectable(self, key): """Get an engine or connection appropriate for 'key', which was previously passed to .add_engine. """ if key is None: key = DEFAULT return self._strategy.get_connectable(key, self) def get_engine(self, key): """Get the engine previously created by .add_engine(key, ...)""" if key is None: key = DEFAULT self._check_engine_key(self._engines, key) return self._engines[key] def get_metadata(self, key): """Get the metadata previously created by .add_engine(key, ...)""" if key is None: key = DEFAULT self._check_engine_key(self._metadatas, key) return self._metadatas[key] #### Properties @property def engine(self): return self.get_engine(DEFAULT) @property def metadata(self): return self.get_metadata(DEFAULT) meta = metadata @property def connectable(self): return self.get_connectable(DEFAULT) @property def ext(self): return self.session_context.mapper_extension @property def session(self): return self.session_context.current @property def query(self): return self.session_context.current.query #### Private methods def _get_session_scope(self): """Each session is local to the curent thread. This is identical to sqlalchemy.SessionContext's default behavior. """ return thread.get_ident def _check_engine_key(self, engine_or_metadata, key): """Raise KeyError if the key has not been registered.""" if key in engine_or_metadata: return if key is None: raise KeyError( "no default engine has been configured.\n" "Call self.add_engine('default', ...)") else: raise KeyError("engine '%s' has not been configured.\n" "Call self.add_engine." % key) #### PylonsSAContext class for Pylons applications #### class PylonsSAContext(SAContext): """I'm a subclass of SAContext that can read engine options from a Pylons config dict. """ def add_engine_from_config(self, key, config_key=None, uri=None, engine_options=None, default_uri=None, default_options=None, config=None): """Add an engine based on the 'config' dict or the current Pylons configuration. See module docstring for arguments. Returns a tuple: (engine, metadata). Normally you'd access these as .engine, .metadata, .get_engine(key), .get_metadata(key), but if you want to hold external references to them you can capture the return value. """ if key is None: key = DEFAULT if config is None: config = self.get_app_config() config_key = config_key or key or DEFAULT parsed_uri, parsed_options = self.parse_engine_options(config, config_key) real_uri = uri or parsed_uri or default_uri if not real_uri: full_key = "sqlalchemy.%s.uri" % config_key raise KeyError("no '%s' variable in config file" % full_key) real_options = merge(engine_options, parsed_options, default_options) return SAContext.add_engine(self, key, real_uri, engine_options=real_options) @staticmethod def parse_engine_options(config, config_key="default"): """Extract the database URI and engine options from a dict that's equivalent to Pylons `app_config`. Convert int/bool options to the appropriate types. For example, say your Pylons .ini file looks like this: [app_conf] sqlalchemy.default.uri = sqlite:////tmp/mydb.sqlite sqlalchemy.default.echo = false sqlalchemy.default.pool_recycle = 3600 sqlalchemy.database2.uri = mysql://user:pw@example.com/mydb Assume `config` is a dict corresponding to the above .ini file. Calling `self.parse_engine_options(config)` returns:: {"uri": "sqlite:///tmp/mydb.sqlite", "echo": False, "pool_recycle": 3600} Calling `self.parse_engine_options(config, "database2")` returns:: {"uri": "mysql://usr:pw/example.com/mydb"} Calling `self.parse_engine_options(config, "MISSING")` returns:: {} This is a static method so it can be called standalone. """ prefix = "sqlalchemy.%s." % config_key prefix_len = len(prefix) uri = None options = {} for full_key in config.iterkeys(): if not full_key.startswith(prefix): continue value = config[full_key] option = full_key[prefix_len:] if option in BOOL_OPTIONS: value = asbool(value) elif option in INT_OPTIONS: try: value = int(value) except ValueError: reason = "config variable '%s' is non-numeric" raise KeyError(reason % full_key) if option == "uri": uri = value else: options[option] = value return uri, options @staticmethod def get_app_config(): """Get the Pylons 'app_conf' dict for the currently-running application. This is a static method so it can be called standalone. """ from pylons import config if not hasattr(config, "__getitem__"): # Pylons 0.9.5 from paste.deploy import CONFIG as config return config #### Private methods def _get_session_scope(self): """Return the id keying the current database session's scope. The session is particular to the current Pylons application -- this returns an id generated from the current thread and the current Pylons application's Globals object at pylons.g (if one is registered). Copied from pylons.database in Pylons 0.9.5. """ import pylons try: app_scope_id = str(id(pylons.g._current_obj())) except TypeError: app_scope_id = '' return '%s|%i' % (app_scope_id, thread.get_ident()) BOOL_OPTIONS = set([ "convert_unicode", "echo", "echo_pool", "threaded", "use_ansi", "use_oids", ]) INT_OPTIONS = set([ "max_overflow", "pool_size", "pool_recycle", "pool_timeout", ]) #### Private strategy classes #### class ContextualStrategy(object): """Abstract base class.""" def create_session(self, context): raise NotImplementedError("subclass responsibility") def create_metadata(self, key, context): raise NotImplementedError("subclass responsibility") def get_connectable(self, key, context): raise NotImplementedError("subclass responsibility") class BoundMetaDataStrategy(ContextualStrategy): """A simple strategy that uses bound metadata. It's the SAContext default, and recommended for most applications. """ def create_session(self, context): return create_session() def create_metadata(self, key, context): if SQLALCHEMY_0_4_API: return MetaData(bind=context.get_engine(key)) else: return MetaData(engine=context.get_engine(key)) def get_connectable(self, key, context): return context.get_engine(key) class BoundSessionStrategy(ContextualStrategy): """A strategy that allows the same table to be simultaneously bound to one engine in one session and a different engine in another session. If you don't need this, use BoundMetaDataStrategy instead. """ def __init__(self, connectionbound=False, binds=None): self.binds = [] if binds is not None: for key in binds: if isinstance(key, Table): self.bind_table(key, binds[key]) else: self.bind_mapper(key, binds[key]) self.connectionbound = connectionbound def bind_mapper(self, mapper, engine_key): self.binds.append(('bind_mapper', mapper, engine_key)) def bind_table(self, table, engine_key): self.binds.append(('bind_table', table, engine_key)) def create_session(self, context): if self.connectionbound: bind = context.get_engine().connect() else: bind = context.get_engine() if SQLALCHEMY_0_4_API: session = create_session(bind=bind) else: session = create_session(bind_to=bind) # set up mapper/table -specific binds # TODO: in the case of "connectionbound", # need to organize the connections here so that one # connection per engine key for bind_func, source, engine_key in self.binds: bindto = context.get_engine(engine_key) if self.connectionbound: bindto = bindto.connect() getattr(session, bind_func)(source, bindto) return session def create_metadata(self, key, context): return MetaData() def get_connectable(self, key, context): # TODO: get "key" in here somehow, needs additional state stored # in order to get correct "bind" from the Session if SQLALCHEMY_0_4_API: return context.session.bind else: return context.session.bind_to class ElixirStrategy(ContextualStrategy): """Contributed by beachcoder. Not officially supported. Usage: sac = SAContext(strategy=ElixirStrategy()) """ def create_session(self, context): import elixir return elixir.objectstore.session def create_metadata(self, key, context): import elixir elixir.metadata.connect(context.get_engine(key)) return elixir.metadata def get_connectable(self, key, context): return context.get_engine(key)