sqlalchemy的简单操作汇总

具体解答请参看注释

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker,mapper

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData
from sqlalchemy.dialects.mysql import \
        BIGINT, BINARY, BIT, BLOB, BOOLEAN, CHAR, DATE, \
        DATETIME, DECIMAL, DECIMAL, DOUBLE, ENUM, FLOAT, INTEGER, \
        LONGBLOB, LONGTEXT, MEDIUMBLOB, MEDIUMINT, MEDIUMTEXT, NCHAR, \
        NUMERIC, NVARCHAR, REAL, SET, SMALLINT, TEXT, TIME, TIMESTAMP, \
        TINYBLOB, TINYINT, TINYTEXT, VARBINARY, VARCHAR, YEAR

DB_CONNECT_STRING = 'mysql+mysqldb://root:root@localhost/asset_cache?charset=utf8'
engine = create_engine(DB_CONNECT_STRING, echo=True)
DB_Session = sessionmaker(bind=engine)
session = DB_Session()

metadata = MetaData()
#构建一个表结构,但是代码中并没有看到他是如何和engin和session产生关联的
#暂且认为在MetaData()里面有代码做了这个工作了吧
userTable = Table(
    "wzp_user",metadata,
    Column('user_id', Integer, primary_key=True),
    Column('user_name', VARCHAR(50), unique=True, nullable=False),
    Column('password', VARCHAR(40), nullable=True)
)

#没有就创建,有了就不管
metadata.create_all(engine)

#构建一个User类,用于操作单条记录
class User(object):
    pass
#和表结构绑定起来
mapper(User, userTable)

#这里是一个创建一条记录的步骤,但是这个只能做一次,下次就会因为重复而报错,所以代码中跳过了
def main1():
    u = User()
    u.user_name='alchemytestuser1'
    u.password='123456'
    session.add(u)
    session.flush()
    session.commit()

#这是一个查询的过程
def main():
    query = session.query(User)
    print '------------------------1'
    print list(query)
    print '------------------------2'
    print query.all()
    for u in query.all():
        print u.user_name,u.password

    print '------------------------3'
    print query.get(1).password
    session.close()

if __name__ == '__main__':
    main()