SQLAlchemy is a modern Object Relational Mapper, that provides an extremely powerful and flexible system for managing the connection between in-memory Python objects and the relational datastore that provides persistence for those objects. One of the main goals of SQLAlchemy is to allow for the full power of both Object Oriented development and Relational Algebra based datastores to be used together in a way that’s natural to your application.
TurboGears SQLAlchemy integration is entirely pushed into the generated quickstart template, so you are totally free to edit your model packages, remove all SQLAlchemy references, and turn off the use of SQLAlchemy in app_cfg.py.
The main reason for this is not to make it easy to remove SQLAlchemy though that is a nice side effect, instead, the motivation was to make it easier to build applications with multiple datastores, which is a common requirement for large-scale applications that either need to talk to so called integration databases which are shared between a large number of applications in an organization, or which need to do some horizontal partitioning of their database in order to scale up to thousands of requests per second.
If you don’t know how SQLAlchemy works at all, please take a few minutes to read over these excellent tutorials:
Your quickstarted project will have a subpackage called model, made up of the following files:
repoze.what.quickstart relies on: User (for the registered
members in your website), Group (for the teams a member may belong
to, and to which you can assign permissions) and Permission (a
permission granted to one or more groups); it also defines two
intermediary tables: One for the many-to-many relationship between
the groups and the permissions, and another one for the many-to-many
relationship between the users and the groups.Auto-reflection of tables has to happen after all the configuration is read, and the app is set up, so we provide simple init_model method (defined in model/__init__.py) that is not called until after everything is set up for you.
There are two methods for table definition with SQLAlchemy:
The tables defined by the quickstart in model/auth.py are based on the declarative method, so you may want to check it out to see how columns are defined for these tables, as well as to see real examples of many-to-one, one-to-many and many-to-many relationships. For more information, you may read the ORM Tutorial and documentation for the Declarative extension.
Once you have defined your tables in a separate module in the model package, they should be imported from model/__init__.py. So the end of this file would look like this:
# Import your model modules here.
from auth import User, Group, Permission
# Say you defined these three classes in the 'movies'
# module of your 'model' package.
from movies import Movie, Actor, Director
When you’re setting up the column types for your tables, you don’t have to think about your target database and its type system. SQLAlchemy provides a flexible underlying type system that, along with the table definition syntax above, allows you to define database-independent table objects.
SQLAlchemy provides a number of built-in types which it automatically maps to underlying database types. If you want the latest and greatest listing just type:
>>> from sqlalchemy import types
>>> dir(types)
The main types are:
| type | value |
|---|---|
| types.Binary | binary |
| types.Boolean | boolean |
| types.Integer | integer |
| types.Numeric | number |
| types.String | string |
| types.Date | date |
| types.Time | time |
| types.DateTime | datetime |
As you define a column, you can specify several properties to control the column’s behavior.
| property | value |
|---|---|
| primary_key | True/False |
| nullable | True/False |
Once you’ve got a table, such as the movie_table we’re using in this example, you can create a Movie class to support a more object oriented way of manipulating your data:
class Movie(object):
def __init__(self, title, year, description, **kw):
self.title = title
self.year = year
self.description = description
def __repr__(self):
return (u"<Movie('%s','%s', '%s')>" % (self.title, self.year, self.description)).encode('utf-8')
If you don’t define the __init__ method. You will need to update the properties of a movie object after it’s been created. like this:
>>> entry = Movie()
>>> entry.title = 'Dracula'
>>> entry.year = '1931'
>>> entry.description = 'vampire movie'
If you’re following along with the tutorial, you’ll want to make sure that you’ve defined the __init__ method. We’ll use the Movie class to create new Movie instances, and set their data all at once throughout the rest of the tutorial.
If you defined the __init__ method, it allows you to initialize the properties at the same time while you create the object:
>>> entry = Movie(title='Dracula', year='1931', description='vampire movie')
or
>>> entry = Movie('Dracula', '1931', 'vampire movie')
It looks better.
Once you’ve got your database table objects defined (and imported into __init__.py if you didn’t define your model in __init__.py), you can create the tables in the database with one simple command, just run:
paster setup-app development.ini
from within your project’s home directory.
Pylons (the TurboGears 2 underground framework) defines a setup-app function that paster will use to connect to the database and create all the tables we’ve defined.
The default database setup configurations are defined in development.ini. So if you just run the script without modification of development.ini, the script will create a single-file database called ‘devdata.db’ in your project directory. If you change your data model and want to apply the new database, delete ‘devdata.db’ and run the ‘paster setup-app’ command again.
TurboGears 2 does support database migrations. But that’s another tutorial (Database Schema Migrations).
Reference: