python sqlalchemy--数据炼金术--使用简介

学习，始于学，成于习，长于思。所以，在使用后总结和梳理，可能比学和看更加重要。

1. Sqlalchemy做什么

sqlalchemy由SQL和Alchemy组成，直译就是数据炼金术，属于一种ORM工具，与其类似的由Hibernate和MyBatis等。我的感觉是：与普通SQL操作相比，它将数据封装为对象，这样我们就是对对象进行增删改查的数据库操作，其内部一些设计将保证我们的操作不会破坏数据的一致性和正确性，同时符合面向对象编程理念降低了编程难度。

它主要是由数据映射和数据增删改查操组成。数据映射是将数据库中表映射到程序的类对象，具体说：定义一个类，声明它所对应的表，定义变量成员和对应的表的列名和类型，另外值得注意的是：表可以没有主键，但是声明这个类的时候一定要指明主键，否则报错（这和session相关，下面会讲到）。这里以班级和学生的场景做一个例子，例子以后会用到。设三个班级和三个学生，两门课程。具体表如下：
班级表class_room：

id	name
room1	高1-1班
room2	高1-2班
room3	高1-3班

学生表student:

id	room_id	name
s1	room1	小明
s2	room1	小花
s3	room2	小红
s4	room2	小刚
s5	room3	小花

那么它的数据映射类，就应该写为如下：

from sqlalchemy import Column, Integer, Unicode, DateTime, Float, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base

# 所有的数据映射类都需要继承这个Base类
Base = declarative_base()

class ClassRoom(Base):
    # 通过___tablename__确定对应的表
	___tablename__ = "class_room"
	# 声明主键id，一定要有，不然报错
	id = Column(String, primary_key=True, nullable=True)
	room_name = Column("name", String(length=60), nullable=True)

class Student(Base):
    __tablename__ = "student"
    id = Column(String, primary_key=True, nullable=True)
    # 设置外键，在join时会用到。
    room_id = Column(String, ForeignKey('class_room.id'))
    name = Column("name", String(length=60), nullable=True)

上面的例子非常简单，我要简单提一下：成员变量和列名保持一致，如id,也可以不一致，如room_name，此时需要指明变量所对应的列名。在room_name = Column("name", String(length=60), nullable=True)中"name"即是列名。

数据的增删改查一般是通过Session来完成，内容比较多所以单独开第二节来讲。数据库的重要作用是保存数据，session的重要作用是保证用户的操作的正确性，无冲突性。比如：三个人同时对一个账户进行存钱和取钱的操作，账户余额应该是正确的等等。数据分析和数据挖掘是依赖于数据库中的数据进行操作的，但是他们更多的是大量的读取数据操作，不怎么属于事务类型，所以ORM最重要的作用就体现不出来了，如果你使用了ORM也许会拖慢开发进程，所以此时使用ORM要进行合理设计。做特征使用大数据这套会更加方便些。

2. Session及数据的增删改查

session的最佳参考资料

2.1 Session简介

一次最简单的数据库操作必须具备以3步：1. 与数据库建立连接同时申请各种资源，2. 数据操作，3.关闭连接并释放各种资源。真实场景下，ORM层与数据库操作比较频繁，所以采用连接池的方式连接数据库，操作频繁也意味着程序与数据库存在多个会话，要有模块管理会话。考虑到这些功能，一次简单的数据库操作流程如下：1. 连接池管理员为此次操作分配连接和各种资源，2.对话管理中心分配对话相关资源，创建新的会话，3. 数据操作，4.关闭连接释放资源，连接池管理者和对话管理中心回收资源。SqlAlchemy中，Engine对象管理连接池，会话工厂sessionmark生成新的会话，Session负责进行数据操作以及最后管理连接和释放资源。

Session主要负责处理数据库的增删改查操作。它提供了Query入口，用户将对应的查询语句改写成一系列的Query操作函数。然后，将其改写为Sql语句，完成查询即可。在读取数据的时候，如果读取了两份相同的数据，会很容易发生数据不一致等问题（这种看似相当弱智的问题，非常有可能发生，原因多种多样)，所以Session中实现了Identity Map，防止出现类似错误。所以，上述定义映射类的时候一定要有主键，这和identity map相关。它将读取的数据转换为用户指定的映射类对象或item。

我们对数据进行的增删改操作都会先保存在session中，然后使用commit提交任务，session会将数据真正发送到数据库中。提交不成功，需要进行回滚操作。

任务结束后，需要释放资源，调用session.close()即可。请及时关闭，释放资源。否则，有可能会报QueuePool limit of size overflow reached, connection timed out, timeout

2.1 创建Session

根据上述简单数据库操作的过程及Session的作用，我们可以大致猜到session的创建方法。其详细代码如下：

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# an Engine, which the Session will use for connection
# resources
some_engine = create_engine('postgresql://scott:tiger@localhost/')

# create a configured "Session" class
Session = sessionmaker(bind=some_engine)

# create a Session
session = Session()

这里sessionmark是一个会话生成工厂（对，此处就是工厂模式或者抽象工厂模式），它负责根据给定的配置生成一个新的会话类，这个类的实例便是一个个新的会话。

2.2 查询操作

查询操作主要是有两种操作，第一个是session.execute函数，第二种是通过Query对象进行操作。

execute函数可以接受sql语句和SQL表达式结构体，返回的是结果代理对象(ResultProxy)。Sql语句可以使用text()函数包裹，也可以不用，如下：

# sql语句形式
result = session.execute(
    text("select name where id = :para"),
    {"para": "322"}
)

# 等价于
result = session.execute(
    "select name where id = :para",
    {"para": "322"}
)

Query对象实现了一些列的函数，来模拟sql语句种的各种where条件、join、order_by等功能，将Sql转换为一系列的Query操作。首先，我们要查找的内容，也就是select后紧跟的列名部分，就是Query对象的初始化参数。其次，限制部分，通过函数实现，filter和filter_by，等价于where条件部分、group_by函数实现了group_by操作。这些函数的返回值是Query对象本身，可以进行链式调用。最后，我们可能需要取出全部/第一条/最后一条结果，就调用query的all()/first()/last()。另外，session.query()返回一个Query对象，提供了入口。代码如下：

# 查找出来班级
# 等价于 'select * from class_room'
all_class_rooms = session.query(ClassRoom).all()

# 查找出来id为room1和room2的班级
target_ids = ['room1', 'room2']
# 等价于 "select * from class_room where id in ('room1', 'room2')"
class_rooms = session.query(ClassRoom).filter(ClassRoom.id.in_(target_ids)).all()

题外话：作者的这些设计非常精巧，使得query相关操作高度还原了Sql语句的编写习惯和感觉。节约了程序员学习Sqlalchemy的时间成本，写出来的代码赏心悦目。套用局座的名言：颜值就是战斗力。

2.2.1 具体例子和函数

filter和filter_by功能类似，都是做条件过滤用的，可以接受一个或多个条件表达式，多个表达式的时候之间是and的关系。他们之间的不同是，filter_by的表达式只需要指明列名即可，filter的表达式还需要指明实体。具体代码，如下：

# 找到名为小花的所有学生
student = session.query(Student).filter(Student.name == '小花').all()

# 找到3班的所有名为小花的学生
student = session.query(Student).filter(Student.name == '小花', Student.room_id == 'room3').first()
# 结果同上
student = session.query(Student).filter(Student.name == '小花').filter(Student.room_id == 'room3').first()

# 找到3班的所有名为小花的学生
student = session.query(Student).filter_by(name == '小花', room_id == 'room3').first()
# 结果同上
student = session.query(Student).filter_by(name == '小花').filter_by(room_id == 'room3').first()

常用的表达式，如果是关键字类型的，在sqlalchemy.sql.expression中都有对应的实现。其他的符号类型的表达式（如“>=”）其对应形式基本与python中表达形式一致。部分条件表达式常用的操作有如下表：

SQL操作名	filter实现方式	具体例子
like	映射类对象.数据成员.like	session.query(Student).filter(Student.name.like('小%'))
>=	>=	session.query(Student).filter(Student.id >= 's2')
=	==	session.query(Student).filter(Student.id == 's2')
in	sqlalchemy.sql.operators.in_	session.query(Student).filter(Student.id.in_(sids))
and	sqlalchemy.sql.expression.and_	session.query(Student).filter(and_(Student.room_id == 'room3', Student.name=='小花') )
or	sqlalchemy.sql.expression.or_	session.query(Student).filter(or_(Student.room_id == 'room3', Student.name=='小花') )

其他一些常见的例子，如下：

# 每个班有多少人
from sqlalchemy import func
room_n_student_items = session.query([Student.room_id, func.count(1)]).group_by(Student.room_id).all()

# 有多少个班
n_classrooms = session.query(ClassRoom).count()

# 查询出来所有“高1-2班”的学生
students_in_1g2r = session.query(Student).join(Student.room_id).filter(ClassRoom.name == "高1-2班").all()