SQLAlchemy的是Python的SQL工具包和对象关系映射，给应用程序开发者提供SQL的强大功能和灵活性。

安装

pip install mysql-python
pip install sqlalchemy

初始化

SQLAlchemy ORM提供了一个连接数据库表和用户自定义Python类的方法。 在使用ORM时，我们要初始化一些基本的变量。

定义映射基类

类的映射(Mapping)使用已经在基类中定义的声明式系统，declarative_base类维持了一个从类到表的关系，通常一个应用使用一个base实例，所有实体类都应该继承此类对象。 我们的应用在一般导入的模块中将只有一个这个基类的实例，使用declarative_base()创建这个基类的实例。

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

创建engine

ORM操作数据库的句柄就是Session。在创建session前，我们先创建一个engine实例。

from sqlalchemy import create_engine
mysql_engine = create_engine("mysql://root:[email protected]/cms?charset=utf8",
                             pool_size=1,
                             max_overflow=10,
                             echo=False,
                             encoding='utf-8',
                             pool_recycle=20000
                             )

创建session

当我们启动我们的应用时，在create_engine()的同时，我们定义了一个Session类将作为一个创建Session实例的工厂。

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)

把上面三部整理到一个base.py文件中，方便后续调用：

# coding: utf-8
"""
base.py
Usage: 使用sqlalchemy中的base类型
"""
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

mysql_engine = create_engine("mysql://root:[email protected]/cms?charset=utf8",
                             pool_size=1,
                             max_overflow=10,
                             echo=False,
                             encoding='utf-8',
                             pool_recycle=20000
                             )

Session = sessionmaker(bind=mysql_engine)

实体类(model)

我们基于base.py定义了一个User类。sqlalchemy 就是把Base子类转变为数据库表，定义好User类后，会生成Table和mapper()，分别通过User.table 和User.__mapper__返回这两个对象。

# coding: utf-8
import logging
from sqlalchemy import *
from base import Base
from base import Session


class User(Base):
    __tablename__ = 'user'
    __table_args__ = {'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8'}

    id = Column(BigInteger, primary_key=True)
    name = Column(String(100), nullable=False)
    password = Column(String(100), nullable=False)

    @classmethod
    def to_dict(cls, row):
        if not row:
            return None

        d = {'id': row.id,
             'name': row.name,
             'password': row.password
             }

        return d

    @classmethod
    def get(cls, session, user_id):
        row = session.query(cls).filter(cls.id == user_id).first()

        return cls.to_dict(row)

    @classmethod
    def update(cls, session, user_id, name, password):
        try:
            session.query(cls.id == user_id).update({cls.name: name, cls.password: password})
            session.commit()
            return True
        except Exception, e:
            logging.error(e)
            return False

    @classmethod
    def add(cls, session, name, password):
        user = cls(name=name,
                   password=password)

        session.add(user)
        try:
            session.commit()
            return user.id
        except Exception, e:
            logging.error(e)
            return None

    @classmethod
    def remove(cls, session, user_id):
        try:
            session.query(cls.id == user_id).delete()
            session.commit()
            return True
        except Exception, e:
            logging.error(e)
            return False


if __name__ == '__main__':
    session = Session()

    user_id = User.add(session, name='test', password='123')
    print User.get(session, user_id=user_id)

    print User.update(session, user_id, name='update', password='456')
    print User.get(session, user_id=user_id)

    print User.remove(session, user_id=user_id)
    print User.get(session, user_id=user_id)

    session.close()

执行结果：

{'password': u'123', 'id': 3L, 'name': u'test'}
True
{'password': u'456', 'id': 3L, 'name': u'update'}
True
None

在这个model中基于session，实现了对数据增删查改的方法。

增删查改

add

    user = cls(name=name, password=password)
    session.add(user)

在调用add时，user实例是待定的(pending)，不执行任何SQL，不会触发insert，因此也不代表数据库中的一行数据。 当使用一个flush过程时，Session将执行SQL来持久化user。

对象状态 对象实例有四种状态，分别是：

1. Transient（瞬时的）：这个状态的对象还不在session中，也不会保存到数据库中，主键为None（不是绝对的，如果Persistent对象rollback后虽然主键id有值，但还是Transient状态的）。
2. Pending（挂起的）：调用session.add()后，Transient对象就会变成Pending，这个时候它还是不会保存到数据库中，只有等到触发了flush动作才会存在数据库，比如query操作就可以出发flush。同样这个时候的实例的主键一样为None
3. Persistent（持久的）：session中，数据库中都有对应的一条记录存在，主键有值了。
4. Detached（游离的）：数据库中有记录，但是session中不存在，对这个状态的对象进行操作时，不会触发任何SQL语句。

Session是真正与数据库通信的handler，我们告诉Session我们想要将所有的改变（add，delete，update）存入到数据库，提交事务；然后通过commit()来执行。

query

查询 Query对象通过Session.query获取，query接收类或属性参数，以及多个类。

    row = session.query(cls).filter(and_(cls.name == name, cls.password == password)).first()

执行query时，session返回同一行(对象)就是我们刚刚在session内部的类的字典持久化的对象，所以我们事实上获得的就是我们加入Session中的实例。 Session 使用 connection发送query，把返回的result row 填充到一个Python object中，该对象同时还会保存在Session中，Session内部有一个叫 Identity Map的数据结构，为每一个对象维持了唯一的副本。

filter()方法通常接收的是Python操作符，而filter_by()方法使用关键字参数。 filter可以接收更加灵活的SQL表达式结构，一般情况下，基本够用了。查询返回结果有多种：

1. query.all()，all()返回列表
2. query.first()，返回第一个元素
3. query.one()，有且只有一个元素时才正确返回

delete

    session.query(cls.id == user_id).delete()

update

    session.query(cls.id == user_id).update({cls.name: name, cls.password: password})

小结

本文记录了使用SQLAlchemy实现常见的CRUD操作，后续会继续记录使用中踩到的坑和一些使用技巧。 SQLAlchemy 在构建在 WSGI 规范上的Python Web 框架中得到了广泛应用，在我司的生产环境中久经考验，配合Flask和Tornado一起开发效率极高。

---