Advanced topics

Multiple databases

Sometimes you may need to talk to multiple databases. This requires you to do two things: define the data model for each database, and configure the connection for each database. pyramid_sqlalchemy has support for multiple databases build-in.

The first thing you will need to do is specify for each ORM class or table which connection it is associated with. Each connection is identified by a name. If you do not specify a name default is used instead.

Caution

It is strongly recommended to enable two-phased transactions when using multiple databases. Without this there is a risk that the databases can be out of sync.

Here is an example that defines a user table in the default database, and a transaction table in a data warehouse:

from pyramid_sqlalchemy import orm_base

class User(orm_base()):
    __tablename__ = 'user'


class Transaction(orm_base('warehouse')):
    __tablename__ = 'transaction'

You can now see why orm_base is a function: you can pass it the connection name and it will do the necessary linking.

The same approach works if you want to use the metadata instance directly, without using the ORM:

from pyramid_sqlalchemy import get_metadata
from sqlalchemy import Column, Table, Integer, String

transaction = Table('transaction', get_metadata('warehouse),
    Column('tx_id', Integer, primary_key=True),
    ...
)

To connect to a database you must call bind_connection once for each connection:

from sqlalchemy import create_engine
from pyramid_sqlalchemy import bind_connection

# Use a local PostgreSQL database for each default data
bind_connection(create_engine('postgresql:///myapp'))
# And connect to a remote instance for our data warehouse
bind_connection(create_engine('postgresql://data-warehouse/myapp'), 'warehouse')

When querying or updating a database you can request a session for a specific connection. Building on our example, this is how you can retrieve the latest transaction from the data warehouse:

from pyramid_sqlalchemy import get_sql_session

sql_session = get_sql_session('warehouse')
latest_tx = sql_session.query(Transaction)\
    .order_by(Transaction.timestamp)\
    .limit(1)\
    .first()

Since it can be tedious to always pass the name of the connection to get_sql_session you can also use get_sql_query, which is smart enough to figure out which session it needs to use.

from pyramid_sqlalchemy import get_sql_query

latest_tx = get_sql_query(Transaction)\
    .order_by(Transaction.timestamp)\
    .limit(1)\
    .first()

When writing tests you will need to define an extra fixture for extra connections you create. This is easily done via the pyramid_sqlalchemy.fixtures.sql_session_fixture() helper.

import pytest
from pyramid_sqlalchemy.fixtures import sql_session_fixture

@pytest.yield_fixture
def warehouse_sql_session():
    """A SQLAlchemy session for the warehouse database.
    """
    yield from sql_session_fixture('warehouse', 'sqlite:///')

If you need to support Python versions before 3.3 you can not use yield from and need to use this more verbose variant:

import pytest
from pyramid_sqlalchemy.fixtures import sql_session_fixture

@pytest.yield_fixture
def warehouse_sql_session():
    """A SQLAlchemy session for the warehouse database.
    """
    for f in sql_session_fixture('warehouse', 'sqlite:///'):
        yield f

Two-phase transactions

If your application uses multiple databases or other transaction-aware systems such as repoze.filesafe, AcidFS, pyramid_mailer or ZODB you need to two-phase commits to coordinate transactions. You can enable these using the enable_sql_two_phase_commit configuration directive.

from pyramid_sqlalchemy import configure_all_connections

configure_all_connections(twophase=True)

For Pyramid application you can also do this through the configurator instance:

def main(global_config, **settings):
    config = Configurator(settings=settings)
    config.include('pyramid_sqlalchemy')
    config.enable_sql_two_phase_commit()

Warning

Please note that this is not supported for all SQL servers. PostgreSQL is the only server where this is guaranteed to work. SQL Server does support two -phase transactions, but the Python driver support for it is unusable.