SQLAlchemy数据库操作例子

#建表
from sqlalchemy import create_engine,Table,Column,Integer,String,MetaData,ForeignKey
engine = create_engine('sqlite:///:memory:',echo=True)
metadata = MetaData()
users = Table('users',metadata,
              Column('id',Integer,primary_key=True),
              #SQLite和Postgresql允许不带长度，如果是其他数据库则应该为
              #Column('name',String(50)),
              Column('name',String),
              Column('fullname',String),
              )

address = Table('address',metadata,
                Column('id',Integer,primary_key=True),
                Column('user_id',None,ForeignKey('users.id')),
                Column('email_address',String,nullable=False),
                )
metadata.create_all(engine)

#插入
#coding:GBK
from connection import *

ins = users.insert().values(name='jack',fullname='jack Jones')
print str(ins)
print ins.compile().params

#Executing
conn = engine.connect()
print conn
result = conn.execute(ins)
print result.inserted_primary_key

#Executing Multipe Statements
ins = users.insert()
conn.execute(ins,id=2, name='wendy',fullname='Wendy Williams')

conn.execute(address.insert(),[
    {'user_id':1,'email_address':'jack@yahoo.com'},
    {'user_id':1,'email_address':'jack@msm.com'},
    {'user_id':2,'email_address':'www@www.org'},
    {'user_id':2,'email_address':'wendy@aol.com'},                          
])

#Bind Connection
metadata.bind = engine
result = users.insert().execute(name='mary',fullname='Mary contary')

#查询
from InsertExpressions import *
from sqlalchemy.sql import select,text
s = select([users])
result = conn.execute(s)
for row in result:
    print row
result = conn.execute(s)
row = result.fetchone()
print row
print row['name'],row['fullname']

s = select([users.c.name, users.c.fullname])
result = conn.execute(s)
for row in result:
    print row
    
for row in conn.execute(select([users, address])):
    print row

s = select([users, address], users.c.id==address.c.user_id)
for row in conn.execute(s):
    print row
    
s = text("""SELECT users.fullname || ', ' || address.email_address AS title
        FROM users, address
        WHERE users.id = address.user_id AND users.name BETWEEN :x AND :y AND
        (address.email_address LIKE :e1 OR address.email_address LIKE :e2)
    """)
print conn.execute(s,x='m', y='z', e1='%@aol.com', e2='%@msn.com').fetchall()

参考资料：http://www.sqlalchemy.org/docs/core/tutorial.html