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

1. When your application starts up, call add_engine once for each database engine you will use. You will first have to create the engine using sqlalchemy.create_engine() or sqlalchemy.engine_from_config(). See Engine Configuration in the SQLAlchemy manual.

2. 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 call set_base(), otherwise they’ll get the old base. You can ensure this by calling set_base() early in the application’s execution, before importing the third-party libraries.

sqlahelper.reset()

Delete all engines and restore the initial module state.

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.