SQLAlchemy数据库操作例子
#建表from sqlalchemy import create_engine,Table,Column,Integer,String,MetaData,ForeignKeyengine = 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:GBKfrom connection import *ins = users.insert().values(name='jack',fullname='jack Jones')print str(ins)print ins.compile().params#Executingconn = engine.connect()print connresult = conn.execute(ins)print result.inserted_primary_key#Executing Multipe Statementsins = 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 Connectionmetadata.bind = engineresult = users.insert().execute(name='mary',fullname='Mary contary')#查询from InsertExpressions import *from sqlalchemy.sql import select,texts = select([users])result = conn.execute(s)for row in result: print rowresult = conn.execute(s)row = result.fetchone()print rowprint 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 rows = 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()