SQLAlchemy数据库操作例子

Python代码 
#建表 
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.execu