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:1@127.0.0.1/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)

1

2

把上面三部整理到一个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:1@127.0.0.1/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

1

2

3

4

5

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

增删查改

add

        user = cls(name=name,

                   password=password)

        session.add(user)

1

2

3

4

在调用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()

1

执行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()

1

update

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

1

小结

本文记录了使用SQLAlchemy实现常见的CRUD操作，后续会继续记录使用中踩到的坑和一些使用技巧。

SQLAlchemy 在构建在 WSGI 规范上的Python Web 框架中得到了广泛应用，在我司的生产环境中久经考验，配合Flask和Tornado一起开发效率极高。