SQLAlchemy tutorial

关于这个教程

This tutorial is for SQLAlchemy version 0.2. You may notice that some sections are marked "New in 0.2". If this is the first time you're reading this tutorial, you can safely skip those sections. On the other hand, if you read the previous version of this tutorial and are now trying to learn SQLAlchemy 0.2, then just search for the text "New in 0.2" and you'll have a lot less reading to do this time around. (Do make sure to update all the demo code, though: every single file of the demo code has changed, and you'll get errors if you try to run the old demo code under SQLAlchemy 0.2).我会慢慢翻译完。

If you're using SQLAlchemy version 0.3, don't worry ― everything you'll learn in this tutorial still works just fine under SQLAlchemy 0.3. Nearly all the changes to SQLAlchemy between 0.2 and 0.3 were either internal code cleanups, or else were changes to advanced features that this tutorial doesn't cover. Therefore, I probably won't give this tutorial a rewrite for SQLAlchemy 0.3.

Now, on with the tutorial.

Getting Started

windows用户可以用setuptools来安装:

easy_install SQLALchemy

对于linux用户(ubuntu):

sudo pip install SQLAlchemy

第一步

让我们首先从一个简单的SQLALchemy程序开始。复制如下的内容并且命名为firststeps.py.

from sqlalchemy import *

db = create_engine('sqlite:///tutorial.db')

db.echo = False  # Try changing this to True and see what happens

metadata = BoundMetaData(db)

users = Table('users', metadata,
    Column('user_id', Integer, primary_key=True),
    Column('name', String(40)),
    Column('age', Integer),
    Column('password', String),
)
users.create()

i = users.insert()
i.execute(name='Mary', age=30, password='secret')
i.execute({'name': 'John', 'age': 42},
          {'name': 'Susan', 'age': 57},
          {'name': 'Carl', 'age': 33})

s = users.select()
rs = s.execute()

row = rs.fetchone()
print 'Id:', row[0]
print 'Name:', row['name']
print 'Age:', row.age
print 'Password:', row[users.c.password]

for row in rs:
    print row.name, 'is', row.age, 'years old'

First Steps, in detail第一步详细介绍

This code sample shows off a lot of the features of SQLAlchemy. Let's go through it step-by-step.上面的代码涉及到了SQLAlchemy的很多特性，让我们来详细介绍一下。

from sqlalchemy import *

db = create_engine('sqlite:///tutorial.db')

The first step in writing SQLAlchemy code is to open a connection to the database you'll be using. In SQLAlchemy, this is done by creating an SQLEngine object, which knows how to talk to one particular type of database (SQLite, PostgreSQL, Firebird, MySQL, Oracle...). The SQLEngine object also doubles as a connection object. Behind the scenes, it will create a pool of database connections and re-use them automatically as needed, to keep your application running quickly. But most of the time, you don't even need to think about the database connections; just use theSQLEngine object that create_engine() returns, and let SQLAlchemy handle the details for you.