Multi database support

[williamwu0220]

This is an awesome project which reduces a lot of configurations!! It would be very helpful if pyramid_sqlalchemy supports multi database configuration, like Flask-SQLAlchemy does:

https://pythonhosted.org/Flask-SQLAlchemy/binds.html

[leplatrem]

I would also need something like that.

What would be the way to go? A global dict with a get_session(alias) helper?

I may be interested to contribute it if you can guide me a bit ;)

Thanks!

[wichert]

FYI: I'm finally going to tackle this one

[wichert]

I'm trying to sort out what the API could look like to support multiple databases. I've copied some options and a proposal below.

Table definition

Approach 1a: use per-bind metadata and declarative base

from pyramid_sqlalchemy import get_metadata_and_base

(metadata, BaseObject) = get_metadata_and_base(bind='default')
(warehouse_metadata, WarehouseBaseObject) = get_metadata_and_base(bind='warehouse)

class MyTable(BaseObject):
    __tablename__ = 'my_table'

class Transaction(WarehouseBaseObject):
    __tablename__ = 'transaction'

A downside of this approach is that you need to store your metadata and BaseObject in a central place which you can access from everywhere.

Approach 1b: use per-bind metadata and base with getter

This is a variant 1a where pyramid_sqlalchemy keeps track of created metadata and declarative basis so you can always request them.

from pyramid_sqlalchemy import orm_base

class MyTable(orm_base()):
    __tablename__ = 'my_table'

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

Approach 2: set bind in table info

This is the approach used by flask-sqlalchemy.

class MyTable(BaseObject):
    __bind_key__ = 'default'
    __tabename = 'my_table'

class Transaction(BaseObject):
    __bind_key__ = 'warehouse'
    __tablename__ = 'transaction'

# Or without ORM
transaction_table = Table('transaction',
    ... # columns go here
    info={'bind_key': 'warsehouse'})

This approach has a major downside: since a single metadata instance you can not have two tables with the same name, but using different database.

Usage

Approach 1: specify connection on session accessor

This is very explicit, and does not require any magic.

def my_view(request):
    sql_session = get_sql_session('warehouse')
    return sql_session.query(Transaction).limit(5).all()

Approach 2: introspect query parameter

Instead of manually passing in the connection name we can also figure it out by looking at a relevant object that you want to query.

def my_view(request):
    query = get_sql_query(Transaction)
    return query.limit(5).all()

This can, and should, easily be combine with approach 1.

Approach 3: add methods to ORM classes

This is the approach used by flask-sqlalchemy. A downside of this approach is that it requires using the ORM.

def my_view(request):
    return Transaction.query.limit(5)

Proposal

My current preference is to use table definition approach 1a, with usage patterns 1 and 2. Code using that would look like this:

Configuration file:

[app:main]
sqlalchemy.url = mysql://localhost/my-app
sqlalchemy.url.warehouse = postgresql://warehouse/transaction'

Defining some tables:

from pyramid_sqlalchemy import orm_base

class MyTable(orm_base()):
    __tablename__ = 'my_table'

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

Performing a query:

from pyramid_sqlalchemy import get_sql_session
from pyramid_sqlalchemy import get_sql_query

# Grab some data from our app
records = get_sql_query(MyTable).limit(15)
# And use them to add transactions to our data warehouse
warehouse_session = get_sql_session(Transaction)
for record in records:
    tx = Transaction(....)
    warehouse_session.add(tx)

In a request context we can also support a property on the request:

def my_view(request):
    return request.sql_query(MyTable).limit(5)

The sql_ prefix is necessary to allow request.sql_session, removing a conflict with Pyramid sessions.

[wichert]

mage on irc asked how this would work with reflection. Extending usage approach 1b for examples would look something like this:

If you use reflection you need to do a bit of extra work, since at import time SQLAlchemy has not been configured yet, so you can't do reflection until later. This will work:

from pyramid_sqlalchemy import get_metadata

def main():
    config = Configurator()
    config.include('pyramid_sqlalchemy')
    get_metadata().reflect()
    get_metadata('warehouse').reflect()

At that point you can use get_metadata().tables['my_table']. This is cumbersome to write, but you can extend that with some magic. For example you can create an empty tables.py file, and populate that automatically:

from . import tables

def reflect(bind_name=None):
    metadata = get_metadata(bind_name)
    metadata.reflect()
    for (name, table) in metadata.tables.items():
        setattr(tables, name, table)

def main():
    config = Configurator()
    config.include('pyramid_sqlalchemy')
    reflect()
    reflect('warehouse')

You can use the tables by importing the tables module:

from . import tables

print(tables.Transaction)

[domenkozar]

A (hopefully useful) note: I've designed https://github.com/niteoweb/pyramid_deferred_sqla#getting-started see (g) to be able to decouple models from Base magic, so one could hook up different engines at some point, based on different strategies.

So that's 1b with pyramid semantics.

[williamwu0220]

Would you please tell us when this feature will be merged into master? :)