【脚本语言系列】关于Python数据库处理SQLAlchemy，你需要知道的事

如何使用SQLAlchemy

使用引擎层

# -*- coding:utf-8 -*-import sqlalchemy as saconn = sa.create_engine('sqlite://')conn.execute('''CREATE TABLE zoo(critter VARCHAR(20) PRIMARY KEY,count INT,damages FLOAT)''')ins = "INSERT INTO zoo (critter, count, damages) VALUES(?,?,?)"print conn.execute(ins, ("duck",5, 0.0))print conn.execute(ins, ("bear",2, 1000.0))print conn.execute(ins, ("weasel", 1, 2000.0))rows = conn.execute("SELECT * FROM zoo")for row in rows:    print row

<sqlalchemy.engine.result.ResultProxy object at 0x0000000006C9FAC8><sqlalchemy.engine.result.ResultProxy object at 0x0000000006CFA0F0><sqlalchemy.engine.result.ResultProxy object at 0x0000000006CFA198>(u'duck', 5, 0.0)(u'bear', 2, 1000.0)(u'weasel', 1, 2000.0)

使用中间层

# -*- coding:utf-8 -*-import sqlalchemy as saconn = sa.create_engine('sqlite://')meta = sa.MetaData()zoo = sa.Table("zoo",meta,               sa.Column("critter",sa.String,primary_key=True),               sa.Column("count",sa.Integer),               sa.Column("damages",sa.Float)              )meta.create_all(conn)print conn.execute(zoo.insert(("duck",5, 0.0)))print conn.execute(zoo.insert(("bear",2, 1000.0)))print conn.execute(zoo.insert(("weasel", 1, 2000.0)))result = conn.execute(zoo.select())rows = result.fetchall()print(rows)

<sqlalchemy.engine.result.ResultProxy object at 0x0000000006CFA400><sqlalchemy.engine.result.ResultProxy object at 0x0000000006CFA400><sqlalchemy.engine.result.ResultProxy object at 0x0000000006CFA668>[(u'duck', 5, 0.0), (u'bear', 2, 1000.0), (u'weasel', 1, 2000.0)]

使用顶层

# -*- coding:utf-8 -*-import sqlalchemy as safrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import sessionmakerconn = sa.create_engine('sqlite:///zoo.db')Base = declarative_base()class Zoo(Base):    __tablename__="zoo"    critter = sa.Column("critter", sa.String, primary_key = True)    count = sa.Column("count", sa.Integer)    damages = sa.Column("damages", sa.Float)    def __init__(self, critter, count, damages):        self.critter = critter        self.count = count        self.damages = damages    def __repr__(self):        return "<Zoo({},{},{}>".format(self.critter, self.count, self.damages)Base.metadata.create_all(conn)first = Zoo("duck",10,0.0)second = Zoo("bear",2,1000.0)third = Zoo("weasel",1,2000.0)Session = sessionmaker(bind=conn)session = Session()session.add(first)session.add_all([second, third])session.commit()

什么是SQLAlchemy

Python DB-API实现所有的关系型数据库的SQL语言的交集。
SQLAlchemy用来实现每种关系型数据库的SQL语言的合集。
其为分层结构：
底层引擎层，类似DB-API，处理数据库连接，执行SQL命令和返回结果；
中间层类似SQL生成器，处理SQL方言之间的差异；
顶层是对象关系模型，使用SQL表达式语言，隐藏实际数据库的机制。