Test Setup

Packages

My Environment is Ubuntu 8.10 and the stock SQLalchemy package version 0.4.6.

Version 0.5 is way on it's way (Release Candidate available, Documentation up to date) but for these notes i there is not much difference, because we start at the lower end (in terms of abstraction).

I will use sqlite3 as database backend.

user@host$ sudo apt-get install python-sqlalchemy python-pysqlite2 sqlite3

Database

My assumption is, that someone else created a database and we need to glue some stuff together, shuffle data around, fix missing features. So we start with an existing Dataset. Here is the database schema (Same as in the SQLAlchemy Object Relation Tutorial).

create table users (
        id Integer primary key,
        name String,
        fullname String,
        password String
);
create table addresses (
        id Integer primary key,
        email_address String,
        user_id Integer constraint 
                fk_users_id references users(id)
);

(SQLite will ignore the foreign key constraint, it's just there in case you use a real database engine).

Create the database, look at our schema and quit

user@host$ sqlite3 -init schema.sql db.sql3
-- Loading resources from schema.sql
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> .schema
CREATE TABLE addresses (
...
sqlite> .quit
user@host$

user@host$ sqlite3 -init data.sql db.sql3
-- Loading resources from data.sql
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> select count(*) from users;
4
sqlite> select count(*) from addresses;
6
sqlite> .quit
user@host$