SQLAlchemyManager 0.1.0

Summary

• Provides a sensible way of using SQLAlchemy in WSGI applications

Get Started

• Download from the SQLAlchemyManager Cheeseshop page

• Install with easy_install SQLAlchemyManager.

Introduction

SQLAlchemy has two problems:

• It is very powerful and so can be used in many different ways but understanding the different options can be difficult.

• To try to make SQLAlchemy simpler in Pylons, objects like scoped_session, g, MetaData etc are used, these further abstract the user from what is going on and make SQLAlchemy appear even more inpenetrable when you do try to get to grips with it

This middleware sets up the standard SQLAlchemy objects in a piece of WSGI middleware without doing anything particularly clever and without using any global objects.

As well as making things slightly simpler in frameworks such as Pylons, the setup recommended in this module also provides an API for other applications such as AuthKit (which can use their own SQLAlchemy objects) to be setup at the same time as the application’s SQLAlchemy objects in the same model. This in turn begins to allow developers to componentise certain parts of their code even if they rely on a database.

Tutorial

Here’s how SQLAlchemyManager is used in a Pylons application.

In your config/middleware.py file at the top:

from yourproject.model import setup_model
from sqlalchemymanager import SQLAlchemyManager

Then, right after the line where you set up PylonsApp:

app = SQLAlchemyManager(app, app_conf, [setup_model])

Your model/__init__.py file then looks like this:

from sqlalchemy import Column, Table, types
from sqlalchemy.orm import mapper, relation

def setup_model(model, metadata, **p):
    model.table1 = Table("table1", metadata,
        Column("id", types.Integer, primary_key=True),
        Column("name", types.String, nullable=False),
    )
    class MyClass(object):
        pass
    model.MyClass = MyClass
    model.table1_mapper = mapper(model.MyClass, model.table1)

This means you can write code like this:

from sqlalchemy.sql import select

def app(environ, start_response):
    # The model is the same across requests so is safe to save as a global
    # somewhere in your application.
    model = environ['sqlalchemy.model']
    # You will get a new session object on each request so you shouldn't save it
    session = environ['sqlalchemy.session']

    # Use the SQLExpression API via the session object
    select_statement = select([model.table1])
    select_result = [row for row in session.execute(select_statement)]

    # Or use the ORM API
    mr_jones = model.MyClass()
    mr_jones.name = 'Mr Jones'
    session.save(mr_jones)
    session.commit()
    multiple_mr_jones = session.query(model.MyClass).filter(model.MyClass.name=='Mr Jones').all()

    # Return the data
    start_response('200 OK', [('Content-Type', 'text/plain')])
    return [
        '''
Select Result:
%s

Mr Jones Results:
%s
        '''%(
            select_result,
            ', '.join([person.name for person in multiple_mr_jones])
        )
    ]

Notice that we are using both the ORM and SQL Expression features of SQLAlchemy and that options such as connection pooling will still work perfectly well. Also, existing SQLAlchemy setups shouldn’t need much modification to work with this middleware. Simply wrapping their model definitions in a function and ensuring that all the SQLAlchemy objects are assigned to model explicitly should be enough.

That’s the basics. If you want to create the tables there are a number of ways:

You can create the required tables during a request like this:

# Create the tables
environ['sqlalchemy.manager'].create_all()

Or if you are using a script you can do this:

from yourproject.model import setup_model
from sqlalchemymanager import SQLAlchemyManager

Then, right after the line where you set up PylonsApp:

manager = SQLAlchemyManager(None, app_conf, [setup_model])
manager.create_all()

To do any manipulation you’ll need to create your own session:

connection = manager.engine.connect()
session = manager.session_maker(bind=connection)
try:
    # Do stuff here
    pass
finally:
    session.close()
    connection.close()

Changes

0.1.0

• First version

License

MIT License

Copyright (c) 2007 James Gardner

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Download