SQLAlchemy(2.0)完全入门

原创

已于 2023-12-03 16:54:06 修改 · 6.3k 阅读

29 ·

CC 4.0 BY-SA版权

文章标签：

#sql #python #数据库

于 2023-10-12 17:23:52 首次发布

原地址：https://cycloctane.github.io/posts/8/index.html

之前写flask应用练手的时候接触到了这个框架的用法。学习的时候发现各种博客参差不齐，大多只是搬了一套用法，没有特别能讲清楚的那种。官方文档很完善，但其中有些东西写得比较抽象，也有很多事项还是自己钻研过之后才理解的。在这里按自己的思路简单记录一下。

这些大部分都是咱自己的理解，大概会有一些不准确的地方，只用来参考一下。目前也并不完善，之后会逐步更新内容。

0x00

SQLAlchemy是python中常用的ORM层框架。它的位置处于DBAPI和web应用之间，自身并不包含连接数据库的功能，需要配合数据库驱动(DBAPI)使用。

对于一般的python的DBAPI，进行数据库操作需要建立一个连接，再从连接中获取一个游标(cursor)，再用游标执行SQL语句并从游标中获取结果。整个流程很直观。

而对于一个SQLAlchemy实例，通常需要：

engine: 通过DBAPI获取数据库连接(实际上是一个连接池)。即让SQLAlchemy知道如何连接到我们的数据库。
Metadata: 保存数据库中schema信息的集合。
Table: 数据库的表的对象。可以自己定义，或者通过engine从数据库中已经存在的表中反射。当然同时也有Column作为列的对象。
Mapped Class: 映射类。把数据库表映射成类。
Session: 构建一个绑定到engine的session，是最终用来进行各种数据库操作的接口。

最终使用SQLAlchemy具体方法其实多种多样。新旧版本之间也有些区别，有些方法是旧版的常规使用方法但新版已经不推荐使用了(但还在兼容)，有些非常规的方法在简单的情况下也可以正常使用(比如直接用Table实例在Session中进行数据库操作而不进行类映射)。看别的博客和说明的时候会发现怎么用的都有，很容易就绕晕了。但其实提到的方法在目前版本下基本上都可以正常使用，但用不用得明白又是另外一回事了。

不管怎样，我们只要分别理解以上几个部分的用法和作用，即可理解这些使用方法分别是怎么回事。

0x01 安装

这个不用多说，直接用pip安装。

pip install SQLAlchemy

当然上面提到过需要和DBAPI一起使用，所以DBAPI当然也得安装。咱用mysql多一点，使用的是mysql官方的mysql-connector。并不是SQLAlchemy推荐的mysql驱动，但咱用着好像也没什么问题

pip install mysql-connector-python

0x02 连接数据库

在SQLAlchemy中建立数据库连接需要用engine。首先安装数据库对应的DBAPI，然后用create_engine()函数创建一个连接engine。不同于python驱动，SQLAlchemy中engine连接参数的指定使用的是类似于JDBC的一种Database URL。总结一下就像这样：

<dialect>+<DBAPI>://<user>:<password>@<ip>:<port>/<schema>?<arg_key>=<value>&<arg_key>=<value>..

不同的数据库的差别被称为dialect(方言)。SQLAlchemy即是通过区分不同的dialect和DBAPI来同时兼容众多不同的数据库的。因此DATABASE URL最开头首先需要指定数据库dialect和使用的DBAPI。//后的内容会解析成python的DBAPI所识别的kwargs参数，原样传给DBAPI中建立连接池的方法，获取的当然也是一个连接池(并非单个连接)。DBAPI所支持的其他的参数，包括SSL连接相关的参数直接作为URL的参数加在?后即可。

顺便Database URL也是支持URL Encode的，特殊字符像网站URL一样escape一下就可以正常传了。

from sqlalchemy import create_engine

engine=create_engine("mysql+mysqlconnector://user:password@octanepi:3306/test?ssl_verify_cert=True&ssl_cert=client.crt&ssl_key=client_key.pem&ssl_ca=ca-chain.crt&pool_size=10")

如此就能得到一个使用mysql数据库和mysql-connector-python的DBAPI通过SSL连接到octanepi的mysql数据库服务的一个名为"test"的schema的engine实例，对应的就是DBAPI中的mysql.pooling.MySQLConnectionPool()方法。之后对数据库的操作都建立在这个engine实例的基础上即可。3306端口号可以省略。pool_size是DBAPI中的方法中指定连接池的连接数的参数，当然也可以直接在Database URL中指定，影响的就是engine对应的连接池大小。

0x03 Raw SQL

除了ORM查询方法外，SQLAlchemy也预留了直接使用SQL语句的接口。有了数据库的连接，我们就可以用raw sql方法直接进行操作了。其实就是相当于隔了一层SQLAlchemy core让DBAPI运行我们自己构建的SQL语句，没有涉及到SQLAlchemy的ORM功能，也就当然不用进行表和类的声明了。

from sqlalchemy.sql import text

conn = engine.connect()
print(conn.execute(text("SELECT * FROM information LIMIT 1")).fetchall())
print(conn.execute(text("SELECT * FROM information WHERE id=:id LIMIT 1"), {
   
   "id":1}).fetchall()) #参数化查询
conn.close()

注意我们自己写的SQL语句字符串需要先传入text()，但如果是SQLAlchemy Expression等生成的SQL语句就不用了。

这种方法乍一看没什么用，但这个方式还是可以利用到SQLAlchemy统一管理数据库连接的功能(SQLAlchemy自动使用连接池)。用这种方式在SQLAlchemy框架下直接从现有的sql语句中插入数据库初始数据是一个很好的选择。

0x04 构建表对象(Table)

在使用ORM之前，我们首先需要初始化一个Table对象(咱先叫它表对象吧orz)。可以从数据库中已经存在的表中加载，也可以通过定义一个python类而构建一个新的表。

如果我们需要的表是要新建的，则我们可以选择直接用声明式映射新建一个映射类而直接跳过此节构建表对象的过程。具体内容在0x05节的##声明式映射.

当然也可以选择先按照此节内容，构建一个新的表对象再用下一节的命令式/混合式映射从该表对象构建映射类。

metadata

在SQLAlchemy中metadata相当于表对象的一个容器和目录。表对象都要附加到metadata实例上。对于同一个schema的表，应当使用同一个metadata实例。

反射表

如果我们需要用到的表是数据库中存在的表，那么我们可以直接从该表直接创建一个SQLAlchemy中的表对象。这个过程叫做Reflect(反射)

表对象是需要基于Metadata的，所以首先我们需要构建一个MetaData实例。然后利用这个metadata实例借助engine从数据库中指定的一个存在的表中获取一个Table实例即可。

from sqlalchemy import Table, MetaData

metadata = MetaData()
metadata.reflect(bind=engine)
info_table = Table("information", metadata, autoload_with=engine)
status_table = Table("status", metadata, autoload_with=engine)

定义表

如果我们需要的数据库表在原数据库中不存在，而我们需要新建一个，则可以选择通过继承Table类作为表。表中的列通过在Table中传入Column实例即可定义。同时也要注意表对象是需要基于Metadata的，因此我们也需要一个metadata实例并将这个表对象注册到其中，随后这个表就可以由metadata实例借助engine在原数据库中新建了。