Usage and API¶
Installation¶
Install SQLAHelper like any Python package, using either “pip install SQLAHelper” or “easy_install SQLAHelper”. To check out the development repository: “hg clone http://bitbucket.org/sluggo/sqlahelper SQLAHelper”.
SQLAlchemy vocabulary¶
These are a few SQLAlchemy terms which are critical for understanding SQLAHelper.
An engine is a SQLAlchemy object that knows how to connect to a certain database. All SQLAlchemy applications have at least one engine.
A session is a SQLAlchemy object that does housekeeping for the object-relational mapper (ORM). These sessions have nothing to do with HTTP sessions despite the identical name. A session is required when using the ORM, but is not needed for lower-level SQL access.
A contextual session (often called a Session with a capital S, or a DBSession) is a threadlocal session proxy. It acts like a session and has the same API, but internally it maintains a separate session for each thread. This allows it to be a golabl variable in multithreaded web applications. (SQLAlchemy manual: contextual session.)
A declarative base (often called a Base) is a common superclass for all your ORM classes. An ORM class represents one database table, and is associated with a separate table object. An instance of the class represents one record in the table.
Most SQLAlchemy applications nowadays use all of these.
Usage¶
When your application starts up, call
add_engine
once for each database engine you will use. You will first have to create the engine usingsqlalchemy.create_engine()
orsqlalchemy.engine_from_config()
. See Engine Configuration in the SQLAlchemy manual.In models or views or wherever you need them, access the contextual session, engines, and declarative base this way:
import sqlahelper Session = sqlahelper.get_session() engine = sqlahelper.get_dbengine() Base = sqlahelper.get_base()
It gets slightly more complex with multiple engines as you’ll see below.
API¶
-
sqlahelper.
add_engine
(engine, name='default')¶ Add a SQLAlchemy engine to the engine repository.
The engine will be stored in the repository under the specified name, and can be retrieved later by calling
get_engine(name)
.If the name is “default” or omitted, this will be the application’s default engine. The contextual session will be bound to it, the declarative base’s metadata will be bound to it, and calling
get_engine()
without an argument will return it.
-
sqlahelper.
get_session
()¶ Return the central SQLAlchemy contextual session.
To customize the kinds of sessions this contextual session creates, call its
configure
method:sqlahelper.get_session().configure(...)
But if you do this, be careful about the ‘ext’ arg. If you pass it, the ZopeTransactionExtension will be disabled and you won’t be able to use this contextual session with transaction managers. To keep the extension active you’ll have to re-add it as an argument. The extension is accessible under the semi-private variable
_zte
. Here’s an example of adding your own extensions without disabling the ZTE:sqlahelper.get_session().configure(ext=[sqlahelper._zte, ...])
-
sqlahelper.
get_engine
(name='default')¶ Look up an engine by name in the engine repository and return it.
If no argument, look for an engine named “default”.
Raise
RuntimeError
if no engine under that name has been configured.
-
sqlahelper.
get_base
()¶ Return the central SQLAlchemy declarative base.
-
sqlahelper.
set_base
(base)¶ Set the central SQLAlchemy declarative base.
Subsequent calls to
get_base()
will return this base instead of the default one. This is useful if you need to override the default base, for instance to make it inherit from your own superclass.You’ll have to make sure that no part of your application’s code or any third-party library calls
get_base()
before you callset_base()
, otherwise they’ll get the old base. You can ensure this by callingset_base()
early in the application’s execution, before importing the third-party libraries.
-
sqlahelper.
reset
()¶ Restore the initial module state, deleting all modifications.
This function is mainly for unit tests and debugging. It undoes all customizations and reverts to the initial module state.
Examples¶
This application connects to one database. There’s only one engine so we make it the default engine.
import sqlalchemy as sa
import sqlahelper
engine = sa.create_engine("sqlite:///db.sqlite")
sqlahelper.add_engine(engine)
This second application is a typical Pyramid/Pylons/TurboGears application. Its engine args are embedded in a general settings dict, which was parsed from an application-wide INI file. All the values are strings because the INI parser is unaware of the appropriate type for each value.
import sqlalchemy as sa
import sqlahelper
settings = {
"debug_notfound": "false",
"mako.directories": "myapp:templates",
"sqlalchemy.url": "sqlite:////home/me/applications/myapp/db.sqlite",
"sqlalchemy.logging_name": "main",
"sqlalchemy.pool_size": "10",
}
engine = sa.engine_from_config(settings, prefix="sqlalchemy.")
sqlahelper.add_engine(engine)
The engine_from_config
method finds the keys with the matching prefix,
strips the prefix, converts the values to their proper type, and calls
add_engine
with the extracted arguments. It ignores keys that don’t have
the prefix. The only required key is the database URL (“sqlalchemy.url” in this
case). (Note: type conversion covers only a few most common arguments.)
If engine_from_config
raises “KeyError: ‘pop(): dictionary is empty’”, make
sure the prefix is correct. In this case it includes a trailing dot.
Multiple databases are covered in the next section.
Multiple databases¶
A default engine plus other engines¶
In this scenario, the default engine is used for most operations, but two other engines are also used occasionally:
import sqlalchemy as sa
import sqlahelper
# Initialize the default engine.
default = sa.engine_from_config(settings, prefix="sqlalchemy.")
sqlahelper.add_engine(default)
# Initialize the other engines.
engine1 = sa.engine_from_config(settings, prefix="engine1.")
engine2 = sa.engine_from_config(settings, prefix="engine2.")
sqlahelper.add_engine(engine1, "engine1")
sqlahelper.add_engine(engine2, "engine2")
Queries will use the default engine by default. To use a different engine
you have to use the bind=
argument on the method that executes the query;
or execute low-level SQL directly on the engine (engine.execute(sql)
).
Two engines, but no default engine¶
In this scenario, two engines are equally important, and neither is predominent enough to deserve being the default engine. This is useful in applications whose main job is to copy data from one database to another.
sqlahelper.add_engine(settings, name="engine1", prefix="engine1.")
sqlahelper.add_engine(settings, name="engine2", prefix="engine2.")
Because there is no default engine, queries will fail unless you specify an
engine every time using the bind=
argument or engine.execute(sql)
.
Different tables bound to different engines¶
It’s possible to bind different ORM classes to different engines in the same
database session. Configure your application with no default engine, and then
call the Session’s .configure
method with the binds=
argument to
specify which classes go to which engines. For instance:
import myapp.models as models
sqlahelper.add_engine(engine1, "engine1")
sqlahelper.add_engine(engine2, "engine2")
Session = sqlahelper.get_session()
binds = {models.Person: engine1, models.Score: engine2}
Session.configure(binds=binds)
The keys in the binds
dict can be SQLAlchemy ORM classes, table objects, or
mapper objects.