Python学习之旅—ORM框架之SQLAlchemy

前言　　

　　工作有一段时间了，目前笔者负责项目经常需要与前段进行交互，也就是目前比较流行的前后端分离。撸了一段时间的SQAlchemy，感觉有必要记录下。特开此篇博客，本次笔者将从系统地介绍SQLAlchemy，希望能够对各位有所帮助。

一.ORM简介

　　在我们的实际工作中，随着项目复杂程度和规模的日趋庞大，当需要对数据库的数据进行处理时，如果完全采用写原生SQL的方式在代码中会出现大量的SQL语句，这样会造成如下几个问题：

SQL语句的重复利用率不高，越复杂的SQL语句条件越多，代码越长，此时会出现很多相似的SQL语句；
很多SQL语句是在业务逻辑中拼凑出来的，如果有数据库需要更改，这就要求我们开发人员非常了解这些逻辑，否则很容易漏掉对某些SQL语句的修改；
如果开发者个人写SQL语句的水平不够，写出来的SQL语句很容易出现诸如SQL注入这样的Web安全问题，给未来的工作造成极大的隐患；

　　基于写原生SQL语句来操作数据库中的数据有上述缺点，因此便引入了ORM框架。ORM，全称Object Relational Mapping，中文叫做对象关系映射，通过ORM我们可以通过类的方式去操作数据库，而不用再写原生的SQL语句。ORM的实质是通过把表映射成类，把行作为实例，把字段作为属性，ORM在执行对象操作的时候最终会将对应的操作转换为数据库原生语句，从而完成数据库的开发工作。使用ORM主要有许多几个优点：

易用性：使用ORM做数据库的开发可以有效的减少重复SQL语句的概率，写出来的模型也更加直观、清晰；
性能损耗小：ORM转换成底层数据库操作指令确实会有一些开销。但从实际的情况来看，这种性能损耗很少（不足5%），只要不是对性能有严苛的要求，综合考虑开发效率、代码的阅读性，带来的好处要远远大于性能损耗，而且项目越大作用越明显。
设计灵活：可以轻松的写出复杂的查询；
可移植性：例如SQLAlchemy，它支持多个关系数据库引擎，包括流行的MySQL、PostgreSQL和SQLite。可以非常轻松的切换数据库，而实际开发人员所需要做的就是修改简单的配置项即可

　　笔者在实际工作中对此深有体会，举一个比较实际的例子，例如现在我有一个需求：将Oracle中的数据全部迁移到Mysql中，但是业务逻辑不变。如果采用原生的SQL方式，需要对绝大部分的OracleSQL语句进行修改，因为Oracle和Mysql数据库两者之间的区别还是有的，这给后期维护和修改造成了极大的麻烦。如果我们采用ORM框架来操作数据库，而不用写纯生的SQL语句，这样即使后期我迁移了数据，只要处理逻辑是一样的，我们只需要写好一套操作数据库的类代码，让ORM框架根据我们后台所使用的数据库的不同，将类代码转换为指定数据库下的SQL语句，然后去执行，这样岂不是非常高效。如果需要手动编写SQL语句，不仅不同的数据库的SQL语句有差别，而且不同的人写的SQL语句水平也有高下之分，这会造成严重的效率问题。现在有了ORM框架，我们只需要专注于写好操作数据库的类代码即可。

二.ORM框架之SQLAlchemy

　　上一节笔者带大家详细梳理了ORM框架的前世今生，目前比较流行的ORM框架很多，例如Java中的MyBatis，Python中的SQLAlchemy,本次笔者将带领大家详细梳理SQLAlchemy，下一篇博客我们将专注于轻量级Web开发框架Flask中SQLAlchemy的使用。

2.1 SQLAlchemy简介

　　SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API(DBAPI)之上，使用关系对象映射进行数据库操作，简而言之就是：将对象转换成SQL，然后使用数据API(例如filter，order等操作)来执行SQL语句并获取执行结果。它有两个主要的组件： SQLAlchemy ORM 和 SQLAlchemy Core 。

其原理架构图如下所示：

我们一起来解析下SQLAlchemy的架构图：

使用者通过ORM对象提交命令
将命令交给SQLAlchemy Core（Schema/Types SQL Expression Language）转换成SQL，在转换完SQL语句后，程序将匹配使用者实现配置好的egine，然后通过egine从连接池中取出一个链接，最后该链接再通过Dialect来调用DB API，将转换完毕的SQL预计转交给DB API去执行。

通过上述的流程分析，我们可以将整个流程大致分为如下两个阶段：

#第一个阶段：将SQLAlchemy的对象换成可执行的sql语句
#第二个阶段：将sql语句交给数据库执行

　　从上面的流程分析可知，SQLAlchemy的本质作用是将我们写好的操作数据库的类转换为SQL语句，然后将SQL语句提交给Pymysql或者MysqlDB等第三方插件，然后通过第三方插件与DBAPI进行交互，从而实现对数据库的操作。很多人以为SQLAlchemy可以直接操作数据库，其实不然。但SQLAlchemy可以链接很多数据库，例如Mysql,Oracle，SQLite等，所以我们可以通过一套业务逻辑，完成对多个数据库的操作。接下来我们一起看看SQLAlchemy的使用。

2.2 SQLAlchemy的安装与使用

1.安装：

pip install SQLAlchemy
# 检查是否安装成功
>>> import sqlalchemy
>>> sqlalchemy.__version__
'1.1.15'

2.使用

　　　前面我们说过SQLAlchemy必须借助于Pymysql等第三方插件实现对数据库的操作。Dialect用于和DBAPI进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，我们来看看SQLAlchemy与常见数据库链接的URI格式：

dialect+driver://username:password@host:port/database
其中dialect是数据库的具体实现，例如Mysql,PostgreSQL,SQLite。Driver是Python对应的驱动，例如pymysql,mysqldb。如果不指定
会选择默认的驱动，Mysql的默认驱动就是mysqldb：
engine = create_engine('mysql+mysqldb://root:cisco@127.0.0.1:3306/alchemy')由于Mysqldb是默认的驱动，所以我们可以省略
不写驱动部分：

engine = create_engine('mysql://root:cisco@127.0.0.1:3306/alchemy')

　　我们来看看SQLAlchemy支持的常见数据库的格式，具体可以参考官网：http://docs.sqlalchemy.org/en/latest/dialects/index.html；

#1、MySQL-Python
    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
   
#2、pymysql
    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
   
#3、MySQL-Connector
    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
   
#4、cx_Oracle
    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]

3.链接Mysql数据库

　在执行如下的代码之前，我们首先在Mysql中新建一个数据库1121db,然后再执行如下代码：

# 先创建数据库1121db
create DATABASE 1121db;
use 1121db;

from sqlalchemy import create_engine

DB_CONNECT_STRING = 'mysql+pymysql://root:cisco@localhost/1121db?charset=utf8'
engine = create_engine(DB_CONNECT_STRING, echo=True)
engine.execute("select 1").scalar()

解析下上面的代码：

echo=True 代表打印日志和生成的SQL语句，使用的是标准python logging模块，生产环境要设置成False，这里只是用于测试，所以可以开启
create_engine()返回一个引擎实例engine,engine本质上是用于维护数据库的连接池，而后面的Session则从Engine的连接池中获取一个连接

当我们执行上面的代码时，sqlalchemy就会从数据库连接池(Connection Pooling)中获取一个连接用于执行语句。echo=True是回显命令，sqlalchemy与数据库通信的命令都将打印出来,如下：

2017-11-21 16:27:27,250 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2017-11-21 16:27:27,250 INFO sqlalchemy.engine.base.Engine {}
2017-11-21 16:27:27,253 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2017-11-21 16:27:27,253 INFO sqlalchemy.engine.base.Engine {}
2017-11-21 16:27:27,256 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
2017-11-21 16:27:27,256 INFO sqlalchemy.engine.base.Engine {}
2017-11-21 16:27:27,259 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2017-11-21 16:27:27,259 INFO sqlalchemy.engine.base.Engine {}
2017-11-21 16:27:27,261 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2017-11-21 16:27:27,261 INFO sqlalchemy.engine.base.Engine {}
2017-11-21 16:27:27,263 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1
2017-11-21 16:27:27,263 INFO sqlalchemy.engine.base.Engine {}
2017-11-21 16:27:27,265 INFO sqlalchemy.engine.base.Engine select 1
2017-11-21 16:27:27,266 INFO sqlalchemy.engine.base.Engine {}

4.声明一个映射(declare a Mapping)

　　　declarative_base类维持了一个从类到表的关系，通常一个应用使用一个base实例，所有实体类都应该继承此类对象,我们来看下面的代码：

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

现在我们创建一个domain类，如下：from sqlalchemy import Column, Integer, String

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base


DB_CONNECT_STRING = 'mysql+pymysql://root:cisco@localhost/1121db?charset=utf8'
engine = create_engine(DB_CONNECT_STRING, echo=True)
Base = declarative_base()


class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    fullname = Column(String(20))
    password = Column(String(20))  

这里需要特别指出的是：由于我们这里使用的是Mysql数据库，在创建表users时必须指定varchar类型字段的长度，例如上面的name,fullname
pasword等字段，都需要指定长度，要不然会报如下的错误：
sqlalchemy.exc.CompileError: (in table 'users', column 'name'): VARCHAR requires a length on dialect mysql
但像SQLite, Postgresql等数据库可以不指定长度，但是为了不同数据库的兼容性，还是加上长度为好，方便后期进行扩展，
这说明，即使使用 SQLAlchemy 做了数据库操作的封装，不同数据库之间还是有差异的。

    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password

    def __repr__(self):
        return "<User('%s','%s','%s')>" % (self.name, self.fullname, self.password)

Base.metadata.create_all(engine)

Base.metadata返回sqlalchemy.schema.MetaData对象，它是所有Table对象的集合，调用create_all()该对象会触发CREATE TABLE语句，如果数据库还不存在这些表的话。

上述代码的执行结果如下，因为我们在前面设置echo=True，所以这里我们看到了使用SQLAlchemy创建表的整个过程

2017-11-21 17:11:39,575 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2017-11-21 17:11:39,575 INFO sqlalchemy.engine.base.Engine {}
2017-11-21 17:11:39,576 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2017-11-21 17:11:39,577 INFO sqlalchemy.engine.base.Engine {}
2017-11-21 17:11:39,578 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
2017-11-21 17:11:39,578 INFO sqlalchemy.engine.base.Engine {}
2017-11-21 17:11:39,580 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2017-11-21 17:11:39,581 INFO sqlalchemy.engine.base.Engine {}
2017-11-21 17:11:39,582 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2017-11-21 17:11:39,582 INFO sqlalchemy.engine.base.Engine {}
2017-11-21 17:11:39,583 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1
2017-11-21 17:11:39,583 INFO sqlalchemy.engine.base.Engine {}
2017-11-21 17:11:39,584 INFO sqlalchemy.engine.base.Engine DESCRIBE `users`
2017-11-21 17:11:39,584 INFO sqlalchemy.engine.base.Engine {}
2017-11-21 17:11:39,591 INFO sqlalchemy.engine.base.Engine ROLLBACK
2017-11-21 17:11:39,593 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE users (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    name VARCHAR(30), 
    fullname VARCHAR(20), 
    password VARCHAR(20), 
    PRIMARY KEY (id)
)

2017-11-21 17:11:39,593 INFO sqlalchemy.engine.base.Engine {}
2017-11-21 17:11:39,600 INFO sqlalchemy.engine.base.Engine COMMIT

基于上面的描述，我们来总结下SQLAlchemy的使用流程：

自定义类继承Base类，在自定义类中指定数据库的表名；
第一步定义的是SQLAlchemy需要的，后面我们就可以正常定义类中的属性和方法了，例如name,password,__repr__()方法
接下来就是执行创建表的操作，使用我们开始创建的引擎：engine

sqlalchemy 就是把Base子类转变为数据库表，定义好User类后，会生成Table和mapper()，分别通过User.__table__ 和User.__mapper__返回这两个对象，对于主键，像Firebird和Oracle这样的数据库，要使用Sequence声明：

官网的描述：
Firebird and Oracle require sequences to generate new primary key identifiers
from sqlalchemy import Sequence
Column(Integer,Sequence('user_id_seq'),prmary_key=True)

5.创建session

　　要想使用ORM功能处理数据库，我们必须要使用Session。Session是真正与数据库通信的handler，我们可以把他理解为一个容器，add就是不断往容器中添加对象：

from sqlalchemy.orm import sessionmaker  将sessionmaker看作是一个session工厂

Session = sessionmaker(bind=engine)

session = Session()

我们接着来为Session()添加对象，执行下面完整的代码：

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

DB_CONNECT_STRING = 'mysql+pymysql://root:cisco@localhost/1121db?charset=utf8'
engine = create_engine(DB_CONNECT_STRING, echo=True)
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(30))
    fullname = Column(String(20))
    password = Column(String(20))  

    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password

    def __repr__(self):
        return "<User('%s','%s','%s')>" % (self.name, self.fullname, self.password)

Base.metadata.create_all(engine)  # 创建表

Session = sessionmaker(bind=engine) # 构造session工厂

session = Session()  # 使用工厂创建一个session对象

# 创建完session就可以添加数据了
# 创建一个对象，实际上就是表中的一条记录
ed_user = User('ed', 'Ed jone', 'edpasswd')
# 往表中添加一条记录，注意此时只是将对象加入到了会话中，并没有真正插入到数据库中，只有后面执行了session.commit()
# 后，数据才被真正插入到数据库中；
session.add(ed_user)

# 也可以使用session.add_all()添加多个对象
# 此时使用add_all方法，里面接收一个列表，列表中存放我们需要添加的对象
session.add_all([user1,user2,user3])
print(session)
#print(ed_user in session)  # True
# session.rollback()
# print(ed_user in session) # False

session.commit()

我们仔细观察上述代码的执行结果2017-11-21 19:13:25,522 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'2017-11-21 19:13:25,522 INFO sqlalchemy.engine.base.Engine {}

2017-11-21 19:13:25,527 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2017-11-21 19:13:25,527 INFO sqlalchemy.engine.base.Engine {}
2017-11-21 19:13:25,530 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
2017-11-21 19:13:25,530 INFO sqlalchemy.engine.base.Engine {}
2017-11-21 19:13:25,534 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2017-11-21 19:13:25,535 INFO sqlalchemy.engine.base.Engine {}
2017-11-21 19:13:25,537 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2017-11-21 19:13:25,537 INFO sqlalchemy.engine.base.Engine {}
2017-11-21 19:13:25,540 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1
2017-11-21 19:13:25,540 INFO sqlalchemy.engine.base.Engine {}
2017-11-21 19:13:25,544 INFO sqlalchemy.engine.base.Engine DESCRIBE `users`
2017-11-21 19:13:25,544 INFO sqlalchemy.engine.base.Engine {}
<sqlalchemy.orm.session.Session object at 0x0000025A580E7FD0>
上面这句话是在add_all方法执行后打印的，但是这句话的前面我们并没有看到任何打印出向数据库中插入数据的语句，直到最后我们执行了
session.commit()方法，才开始执行如下的插入SQL语句：
2017-11-21 19:13:25,562 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-11-21 19:13:25,563 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (%(name)s, %(fullname)s, %(password)s)
2017-11-21 19:13:25,563 INFO sqlalchemy.engine.base.Engine {'password': 'cisco123', 'name': 'Li', 'fullname': 'Carson Li'}
2017-11-21 19:13:25,564 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (%(name)s, %(fullname)s, %(password)s)
2017-11-21 19:13:25,565 INFO sqlalchemy.engine.base.Engine {'password': 'cisco', 'name': 'Tom', 'fullname': 'Egon Li'}
2017-11-21 19:13:25,566 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (%(name)s, %(fullname)s, %(password)s)
2017-11-21 19:13:25,566 INFO sqlalchemy.engine.base.Engine {'password': '123456', 'name': 'Alex', 'fullname': 'Alex Li'}
2017-11-21 19:13:25,567 INFO sqlalchemy.engine.base.Engine COMMIT

通过上面的例子，我们可以引出三个重要的问题：何时构造一个session，何时提交会话内的数据，合适关闭session?关于这个答案，可以参考官网的回答：

http://docs.sqlalchemy.org/en/rel_0_9/orm/session_basics.html。

从某种意义上来说，session 某种意义上类似于一个缓存，它有 commit, rollback, close 等方法。也因此 session 实例的生命周期维护应该位于使用它们的函数之外，与具体的应用逻辑区分开，以避免数据污染和保证 rollback/close 的调用；所以不管我们是增加数据还是修改数据，我们最后都使用了session的commit()方法。

6.SQLAlchemy的增删改查

　　前面我们仔细梳理了SQLAlchemy从创建表，增加记录的全部过程，下面我们来梳理它对记录的增删查改过程。

https://github.com/lzjun567/note/blob/master/note/python/sqlalchemy.md

posted @ 2017-11-21 15:40 哀乐之巅写年华阅读(1269) 评论(0) 收藏举报

刷新页面返回顶部

不为乌合不从众

Python学习之旅—ORM框架之SQLAlchemy

公告