Python
Last edited January 19, 2008
More by Ian Lewis »
Sections:
SQLAlchemy 0.4 for people in a hurry - Pylons Cookbook - PythonWeb
wiki.pylonshq.com/display/pylonscookbook/SQLAlchem...

An example of loading a database entry in a controller method, performing a sex change, and saving it:

1
2
3
4
5
person_q = Session.query(Person)           # An ORM Query object for accessing the Person table
mr_jones = person_q.filter(Person.name=='Mr Jones').one()
print mr_jones.name                                    # prints 'Mr Jones'
mr_jones.name = 'Mrs Jones'                            # only the object instance is changed here ...
Session.commit()                                       # ... only now is the database updated

SQLAlchemy 0.4 for people in a hurry - Pylons Cookbook - PythonWeb
wiki.pylonshq.com/display/pylonscookbook/SQLAlchem...

Working with joined objects

Recall that my_addresses property is a list of Address objects

1
print mr_jones.my_addresses[0].address         # prints first address

To add an existing address to 'Mr Jones' we do the following:

1
2
3
4
address_q = Session.query(Address)
address = address_q.filter(Address.address=='33 Pine Marten Lane, Pleasantville').one()     # Retrieve an existing address
mr_jones.my_addresses.append(new_address)                                      # Add to the list
Session.commit()                                                               # issue updates to the join table

To add an entirely new address to 'Mr Jones' we do the following:

1
2
3
4
new_address = Address()                                      # Construct an empty address object
new_address.address = '33 Pine Marten Lane, Pleasantville'
mr_jones.my_addresses.append(new_address)                    # Add to the list
Session.commit()                                             # Commit changes to the database

After making changes you must call Session.commit() to store them permanently in the database; otherwise they'll be discarded at the end of the web request. You can also call Session.rollback() at any time to undo any changes that haven't been committed.

To search on a joined object we can pass an entire object as a query:

1
2
3
search_address = Address()
search_address.address = '33 Pine Marten Lane, Pleasantville'
residents_at_33_pine_marten_lane = person_q.filter(Person.my_addresses.contains(search_address)).all()

  • All attributes must match in the query object.

Or we can can search on a joined objects' property,

1
residents_at_33_pine_marten_lane = person_q.join('my_addresses').filter(Address.address=='33 Pine Marten Lane, Pleasantville').all()

A shortcut for the above is to use any():

1
residents_at_33_pine_marten_lane = person_q.filter(Person.my_addresses.any(Address.address=='33 Pine Marten Lane, Pleasantville')).all()

To disassociate an address from Mr Jones we do the following:

1
2
del mr_jones.my_addresses[0]                        # Delete the reference to the address
Session.commit()

To delete the address itself in the address table, normally we'd have to issue a separate delete() for the Address object itself:

1
2
3
Session.delete(mr_jones.my_addresses[0])                   # Delete the Address object
del mr_jones.my_addresses[0]
Session.commit()                                    # Commit both operations to the database

However, SQLAlchemy supports a shortcut for the above operation. Configure the mapper relation using cascade = "all, delete-orphan" instead:

1
2
3
4
mapper(Address, addresses_table)
mapper(Person, people_table, properties = {
    'my_addresses' : relation(Address, secondary = addresses_people_table, cascade="all,delete-orphan"),
    })

Then, any items removed from mr_jones.my_addresses is automatically deleted from the database:

1
2
del mr_jones.my_addresses[0]                        # Delete the reference to the address, also deletes the Address
Session.commit()

For any relationship, you can add cascade = "all, delete-orphan" as an extra argument to relation() in your mappers to ensure that when a join is deleted the joined object is deleted as well, so that the above delete() operation is not needed - only the removal from the my_addresses list. Beware though that despite its name, delete-orphan removes joined objects even if another object is joined to it.

SQLAlchemy 0.4 for people in a hurry - Pylons Cookbook - PythonWeb
wiki.pylonshq.com/display/pylonscookbook/SQLAlchem...

To delete the address itself in the address table, normally we'd have to issue a separate delete() for the Address object itself:

1
2
3
Session.delete(mr_jones.my_addresses[0])                   # Delete the Address object
del mr_jones.my_addresses[0]
Session.commit()                                    # Commit both operations to the database

Pylons

SQLAlchemy 0.4 for people in a hurry - Pylons Cookbook - PythonWeb
wiki.pylonshq.com/display/pylonscookbook/SQLAlchem...

The config file

In development.ini enter the following in the [app:main] section, depending on your database,

For SQLite

1
sqlalchemy.url = sqlite:///%(here)s/mydatabasefilename.db

Where mydatabasefilename.db is the path to your SQLite database file. "%(here)s" represents the directory containing the development.ini file.

For MySQL

1
2
3
sqlalchemy.url = mysql://username:password@host:port/database
sqlalchemy.echo = True
sqlalchemy.pool_recycle = 3600

Enter your username, password, host (localhost if it is on your machine), port number (usually 3306) and the name of your database. The second two lines are optional; they're examples of setting engine options.

The "pool_recycle" line is important for MySQL; it makes SQLAlchemy close and reopen database connections after one hour (3600 seconds) of use; this prevents MySQL from unilaterally closing them after a few hours idle time, which avoids "MySQL server has gone away" errors.

For PostgreSQL

1
sqlalchemy.url = postgres://username:password@host:port/database

Enter your username, password, host (localhost if it is on your machine), port number (usually 5432) and the name of your database.

Note: Some people prefer "sqlalchemy.default.url" instead of "sqlalchemy.url", to make things easier if they add a second database later (described in "Multiple Engines" below).

SQLAlchemy 0.4 for people in a hurry - Pylons Cookbook - PythonWeb
wiki.pylonshq.com/display/pylonscookbook/SQLAlchem...

Model

Replace the content of myapp/model/__init__.py in your application with something like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
from pylons import config
from sqlalchemy import Column, MetaData, Table, types
from sqlalchemy.orm import mapper, relation
from sqlalchemy.orm import scoped_session, sessionmaker

# Global session manager.  Session() returns the session object
# appropriate for the current web request.
Session = scoped_session(sessionmaker(autoflush=True, transactional=True,
                                      bind=config['pylons.g'].sa_engine))

# Global metadata. If you have multiple databases with overlapping table
# names, you'll need a metadata for each database.
metadata = MetaData()

# Define a table.
table1 = Table("table1", metadata,
    Column("id", types.Integer, primary_key=True),
    Column("name", types.String, nullable=False),
    )

# Define another table, reading its structure from an existing database
# table.  You must provide an engine in this case.
table2 = Table("table2", metadata, autoload=True,
               autoload_with=config['pylons.g'].sa_engine)

# Define ORM classes (often called "mapped classes").
# attributes will be added by the mapper below
class MyClass(object):
    pass

# Map each class to its corresponding table.
mapper(MyClass, table1)

This provides access to one engine. Using multiple engines is described below.

Relation example

Here's an example of a Person and an Address class with a many:many relationship on people.my_addresses. See Relational databases for people in a hurry and the SQLAlchemy manual for details.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
from sqlalchemy import ForeignKey

people_table = Table('people', metadata,
    Column('id', types.Integer, primary_key=True),
    Column('name', types.String(100)),
    Column('email', types.String(100))
)

addresses_people_table = Table('addresses_people', metadata,
    Column('id', types.Integer, primary_key=True),
    Column('person_id', types.Integer, ForeignKey('people.id')),
    Column('address_id', types.Integer, ForeignKey('addresses.id'))
)

addresses_table = Table('addresses', metadata,
    Column('id', types.Integer, primary_key=True),
    Column('address', types.String(100))
)

mapper(Address, addresses_table)
mapper(Person, people_table, properties = {
    'my_addresses' : relation(Address, secondary = addresses_people_table),

SQLAlchemy 0.4 for people in a hurry - Pylons Cookbook - PythonWeb
wiki.pylonshq.com/display/pylonscookbook/SQLAlchem...

Controller

Add the following code to your base controller's .__call__ method in myapp/lib/base.py:

1
2
3
4
5
def __call__(self, environ, start_response):
    try:
        return WSGIController.__call__(self, environ, start_response)
    finally:
        model.Session.remove()

The .remove() method is very important! It discards any leftover ORM data in the current web request. Otherwise the stray data will leak into the next request handled by this thread, potentially causing errors or data corruption.

Any per-request behaviors can be configured at this stage. For example, to use just a single database connection per request, which removes all connection pool checkin/checkout overhead, the per-request Session can be configured with a Connection:

1
2
3
4
5
6
7
8
def __call__(self, environ, start_response):
    conn = config['pylons.g'].sa_engine.connect()
    model.Session(bind=conn)
    try:
        return WSGIController.__call__(self, environ, start_response)
    finally:
        model.Session.remove()
        conn.close()

Note that when using a session with transactional=True, the session holds onto a single connection through the lifespan of each transaction so the above optimization is not as significant.

SQLAlchemy 0.4 for people in a hurry - Pylons Cookbook - PythonWeb
wiki.pylonshq.com/display/pylonscookbook/SQLAlchem...

Data queries and modifications

Important: this section assumes you're putting the code in a high-level model function. If you're putting it directly into a controller method, you'll have to put a model. prefix in front of every object defined in the model, or import the objects individually. Also note that the Session object here (capital s) is not the same as the Beaker session object (lowercase s) in controllers.

Here's how to enter new data into the database:

1
2
3
4
mr_jones = Person()
mr_jones.name = 'Mr Jones'
Session.save(mr_jones)
Session.commit()

mr_jones here is an instance of Person. Its properties correspond to the column titles of people_table and contain the data from the selected row. A more sophisticated application would have a Person.__init__ method that automatically sets attributes based on its arguments.

An example of loading a database entry in a controller method, performing a sex change, and saving it:

1
2
3
4
5
person_q = Session.query(Person)           # An ORM Query object for accessing the Person table
mr_jones = person_q.filter(Person.name=='Mr Jones').one()
print mr_jones.name                                    # prints 'Mr Jones'
mr_jones.name = 'Mrs Jones'                            # only the object instance is changed here ...
Session.commit()                                       # ... only now is the database updated

To return a list of entries use:

1
all_mr_joneses = person_q.filter(Person.name=='Mr Jones').all()

To get all list of all the people in the table use:

1
everyone = person_q.all()

To retrieve by id:

1
someuser = person_q.get(5)

You can iterate over every person even more simply:

1
2
3
4
5
6
7
print "All people"
for p in person_q:
    print p.name
print
print "All Mr Joneses:"
for p in person_q.filter(Person.name=='Mr Jones'):
    print p.name

To delete an entry use the following:

1
2
3
mr_jones = person_q.filter(Person.name=='Mr Jones').one()
Session.delete(mr_jones)
Session.commit()

SQLAlchemy 0.4 for people in a hurry - Pylons Cookbook - PythonWeb
wiki.pylonshq.com/display/pylonscookbook/SQLAlchem...

Building the database

To actually create the tables in the database, customize myapp/websetup.py. After the load_environment() call put:

1
2
3
4
5
...
    from myapp import model
    log.info("Creating tables")
    model.metadata.create_all(bind=config['pylons.g'].sa_engine)
    log.info("Successfully setup")

Then run the following on the command line:

paster setup-app development.ini

The line we added connects to the database and creates all the tables we've defined.

SqlAlchemy
The content on this page is provided by a Google Notebook user, and Google assumes no responsibility for this content.