Python关系型数据库

Published on 2017 - 01 - 31

关系型数据库虽然只有 40 多年的历史,却无处不在。你一定曾经和它打过交道,使用时你会体会到它提供的如下功能:

  • 多用户同时访问数据;
  • 用户使用数据的保护;
  • 高效地存储和检索数据;
  • 数据被模式定义以及被约束限制;
  • Joins 通过连接发现不同数据之间的关系;
  • 声明式(非命令式)查询语言,SQL(Structured Query Language)。

之所以被称为关系型(relational)是因为数据库展现了表单(table)形式的不同类型数据之间的关系。例如之前菜单的例子中,每一项和它的价格是有对应关系的。

表单是一个具有行和列的二元组,和电子数据表类似。要创建一个表单,需要给它命名,明确次序、每一项的名称以及每一列的类型。每一行都会存在相同的列,即使允许缺失项(也称为 null)。在菜单例子中,你创建的表单中应该把每一个待售的食物作为一行;每一行都存在相同的列,包括它的价格。

某一行或者某几行通常作为表单的主键,在表单中主键的值是独一无二的,防止重复增添数据项。这些键在查询时被快速索引,类似于图书的索引,方便快速地找到指定行。

每一个表单都附属于某数据库,类似于一个文件都存在于某目录下。两层的层次结构便于更好地组织和管理。

如果你想通过非主键的列的值查找数据,可以定义一个二级索引,否则数据库服务器需要扫描整个表单,暴力搜索每一行找到匹配列的值。

表单之间可以通过外键建立关系,列的值受这些键的约束。

SQL

SQL 既不是一个 API 也不是一种协议,而是一种声明式语言,只需要告诉它做什么即可。它是关系型数据库的通用语言。SQL 查询是客户端发送给数据库服务器的文本字符串,指明需要执行的具体操作。

SQL 语言存在很多标准定义格式,但是所有的数据库制造商都会增加它们自己的扩展,导致产生许多 SQL 方言。如果你把数据存储在关系型数据库中,SQL 会带来一定的可移植性,但是方言和操作差异仍然会导致难以将数据移植到另一种类型的数据库中。

SQL 语句有两种主要的类型:

  • DDL(数据定义语言)

处理用户、数据库以及表单的创建、删除、约束和权限等。

  • DML(数据操作语言)

处理数据插入、选择、更新和删除。

表 1 列出了基本的 SQL DDL 命令。

[表1:基本的SQL DDL命令]

操作 SQL模式 SQL示例
创建数据库 CREATE DATABASE dbname CREATE DATABASE d
选择当前数据库 USE dbname USE d
删除数据库以及表单 DROP DATABASE dbname DROP DATABASE d
创建表单 CREATE TABLE tbname (coldefs) CREATE TABLE t(id INT, count INT)
删除表单 DROP TABLE tbname DROP TABLE t
删除表单中所有的行 TRUNCATE TABLE tbname TRUNCATE TABLE t

SQL 关系型数据库的主要 DML 操作可以缩略为 CRUD。

  • Create:使用 INSERT 语句创建
  • Read:使用 SELECT 语句选择
  • Update:使用 UPDATE 语句更新
  • Delete:使用 DELETE 语句删除

表 2 列出了一些 SQL DML 命令。

[表2:基本的SQL DML命令]

操作 SQL模式 SQL示例
增加行 INSERT INTO tbname VALUES(...) INSERT INTO t VALUES(7,40)
选择全部行和全部列 SELECT * FROM tbname SELECT * FROM t
选择全部行和部分列 SELECT cols FROM tbname SELECT id,count from t
选择部分行部分列 SELECT cols FROM tbname WHERE condition SELECT id,count from t WHERE count > 5 AND id = 9
修改一列的部分行 UPDATE tbname SET col = value WHERE condition UPDATE t SET count=3 WHERE id=5
删除部分行 DELETE FROM tbname WHERE condition DELETE FROM t WHERE count <= 10 OR id = 16

DB-API

应用程序编程接口(API)是访问某些服务的函数集合。DB-API 是 Python 中访问关系型数据库的标准 API。使用它可以编写简单的程序来处理多种类型的关系型数据库,不需要为每种数据库编写独立的程序,类似于 Java 的 JDBC 或者 Perl 的 dbi。

它的主要函数如下所示。

  • connect()

连接数据库,包含参数用户名、密码、服务器地址,等等。

  • cursor()

创建一个 cursor 对象来管理查询。

  • execute() 和 executemany()

对数据库执行一个或多个 SQL 命令。

  • fetchone()、fetchmany() 和 fetchall()

得到 execute 之后的结果。

下一节的 Python 数据库模块遵循 DB-API,但会有扩展和细节上的差别。

SQLite

SQLite 是一种轻量级的、优秀的开源关系型数据库。它是用 Python 的标准库实现,并且存储数据库在普通文件中。这些文件在不同机器和操作系统之间是可移植的,使得 SQLite 成为简易关系型数据库应用的可移植的解决方案。它不像功能全面的 MySQL 或者 PostgreSQL,SQLite 仅仅支持原生 SQL 以及多用户并发操作。浏览器、智能手机和其他应用会把 SQLite 作为嵌入数据库。

首先使用 connect() 函数连接本地的 SQLite 数据库文件,这个文件和目录型数据库(管理其他的表单)是等价的。字符串 ':memory:' 仅用于在内存中创建数据库,有助于方便快速地测试,但是程序结束或者计算机关闭时所有数据都会丢失。

下一个例子会创建一个数据库 enterprise.db 和表单 zoo 用以管理路边繁华的宠物动物园业务。表单的列如下所示。

  • critter

可变长度的字符串,作为主键。

  • count

某动物的总数的整数值。

  • damages

人和动物的互动中损失的美元数目。

>>> import sqlite3
>>> conn = sqlite3.connect('enterprise.db')
>>> curs = conn.cursor()
>>> curs.execute('''CREATE TABLE zoo
    (critter VARCHAR(20) PRIMARY KEY,
     count INT,
     damages FLOAT)''')
<sqlite3.Cursor object at 0x1006a22d0>

Python 只有在创建长字符串时才会用到三引号('''),例如 SQL 查询。

现在往动物园中新增一些动物:

>>> curs.execute('INSERT INTO zoo VALUES("duck", 5, 0.0)')
<sqlite3.Cursor object at 0x1006a22d0>
>>> curs.execute('INSERT INTO zoo VALUES("bear", 2, 1000.0)')
<sqlite3.Cursor object at 0x1006a22d0>

使用 placeholder 是一种更安全的、插入数据的方法:

>>> ins = 'INSERT INTO zoo (critter, count, damages) VALUES(?, ?, ?)'
>>> curs.execute(ins, ('weasel', 1, 2000.0))
<sqlite3.Cursor object at 0x1006a22d0>

在 SQL 中使用三个问号表示要插入三个值,并把它们作为一个列表传入函数 execute()。这些占位符用来处理一些冗余的细节,例如引用(quoting)。它们会防止 SQL 注入:一种常见的 Web 外部攻击方式,向系统插入恶意的 SQL 命令。

现在使用 SQL 获取所有动物:

>>> curs.execute('SELECT * FROM zoo')
<sqlite3.Cursor object at 0x1006a22d0>
>>> rows = curs.fetchall()
>>> print(rows)
[('duck', 5, 0.0), ('bear', 2, 1000.0), ('weasel', 1, 2000.0)]

按照数目(count)排序,重新获得它们:

>>> curs.execute('SELECT * from zoo ORDER BY count')
<sqlite3.Cursor object at 0x1006a22d0>
>>> curs.fetchall()
[('weasel', 1, 2000.0), ('bear', 2, 1000.0), ('duck', 5, 0.0)]

又需要按照降序得到它们:

>>> curs.execute('SELECT * from zoo ORDER BY count DESC')
<sqlite3.Cursor object at 0x1006a22d0>
>>> curs.fetchall()
[('duck', 5, 0.0), ('bear', 2, 1000.0), ('weasel', 1, 2000.0)]

哪种类型的动物花费最多呢?

>>> curs.execute('''SELECT * FROM zoo WHERE
...     damages = (SELECT MAX(damages) FROM zoo)''')
<sqlite3.Cursor object at 0x1006a22d0>
>>> curs.fetchall()
[('weasel', 1, 2000.0)]

你可能会认为是 bears(花费最多)。实际上,最好还是查看一下实际数据。

在结束本节之前,有一点需要明确,如果我们已经打开了一个连接(connection)或者游标(cursor),不需要时应该关掉它们:

>>> curs.close()
>>> conn.close()

MySQL

MySQL 是一款非常流行的开源关系型数据库。不同于 SQLite,它是真正的数据库服务器,因此客户端可以通过网络从不同的设备连接它。

MysqlDB 是最常用的 MySQL 驱动程序,但至今没有支持 Python 3。表 3 列出了 Python 连接 MySQL 的几个驱动程序。

[表3:MySQL的驱动程序]

名称 链接 Pypi包 导入
MySQL Connector http://dev.mysql.com/doc/connector-python/en/index.html mysql-connector-python mysql.connector
PYMySQL https://github.com/petehunt/PyMySQL/ pymysql pymysql
oursql http://pythonhosted.org/oursql/ oursql oursql

PostgreSQL

PostgreSQ 是一款功能全面的开源关系型数据库,在很多方面超过 MySQL。表 4 列出了 Python 连接 PostgreSQL 的几个驱动程序。

[表4:PostgreSQL的驱动程序]

名称 链接 Pypi包 导入
psycopg2 http://initd.org/psycopg/ psycopg2 psycopg2
py-postgresql http://python.projects.pgfoundry.org/ py-postgresql postgresql

SQLAlchemy

对于所有的关系型数据库而言,SQL 是不完全相同的,并且 DB-API 仅仅实现共有的部分。每一种数据库实现的是包含自己特征和哲学的方言。许多库函数用于消除它们之间的差异,最著名的跨数据库的 Python 库是 SQLAlchemy

它不在 Python 的标准库,但被广泛认可,使用者众多。在你的系统(Linux)中使用下面这条命令安装它:

$ pip install sqlalchemy

你可以在以下层级上使用 SQLAlchemy:

  • 底层负责处理数据库连接池、执行 SQL 命令以及返回结果,这和 DB-API 相似;
  • 再往上是 SQL 表达式语言,更像 Python 的 SQL 生成器;
  • 较高级的是对象关系模型(ORM),使用 SQL 表达式语言,将应用程序代码和关系型数据结构结合起来。

随着内容的深入,上面提到的术语会变得熟悉。SQLAlchemy 实现在前面几节提到的数据库驱动程序的基础上。因此不需要导入驱动程序,初始化的连接字符串会作出分配,例如:

dialect + driver :// user : password @ host : port / dbname

字符串中的值代表如下含义。

  • dialect

数据库类型。

  • driver

使用该数据库的特定驱动程序。

  • user 和 password

数据库认证字符串。

  • host 和 port

数据库服务器的位置(只有特定情况下会使用端口号 :port)。

  • dbname

初始连接到服务器中的数据库。

表 5 列出了常见方言和对应的驱动程序。

[表5:SQLAlchemy连接]

方言 驱动程序
sqlite pysqlite(可以忽略)
mysql mysqlconnector
mysql pymysql
mysql oursql
postgresql psycopg2
postgresql pypostgresql

引擎层

首先,我们试用一下 SQLAlchemy 的底层,它可以实现多于基本 DB-API 的功能。

以内置于 Python 的 SQLite 为例,连接字符串忽略 host、port、user 和 password。dbname 表示存储 SQLite 数据库的文件,如果省去 dbname,SQLite 会在内存创建数据库。如果 dbname 以反斜线(/)开头,那么它是文件所在的绝对路径(Linux 和 OS X 是反斜线,而在 Windows 是例如 C:\ 的路径名)。否则它是当前目录下的相对路径。

以下是一个程序的所有部分,为了解释把它们隔开。

开始导入库函数,例子中使用了 import 的别名,用字符串 sa 指代 SQLAlchemy。我通常会这样做是因为 sa 要比 sqlalchemy 简洁得多:

>>> import sqlalchemy as sa

连接到数据库,并在内存中存储它(参数字符串 'sqlite:///:memory:' 也是可行的):

>>> conn = sa.create_engine('sqlite://')

创建包含三列的数据库表单 zoo:

>>> conn.execute('''CREATE TABLE zoo
...     (critter VARCHAR(20) PRIMARY KEY,
...      count INT,
...      damages FLOAT)''')
<sqlalchemy.engine.result.ResultProxy object at 0x1017efb10>

运行函数 conn.execute() 返回到一个 SQLAlchemy 的对象 ResultProxy。马上你会看到它的用处。

现在向空表单里插入三组数据:

>>> ins = 'INSERT INTO zoo (critter, count, damages) VALUES (?, ?, ?)'
>>> conn.execute(ins, 'duck', 10, 0.0)
<sqlalchemy.engine.result.ResultProxy object at 0x1017efb50>
>>> conn.execute(ins, 'bear', 2, 1000.0)
<sqlalchemy.engine.result.ResultProxy object at 0x1017ef090>
>>> conn.execute(ins, 'weasel', 1, 2000.0)
<sqlalchemy.engine.result.ResultProxy object at 0x1017ef450>

接下来在数据库中查询放入的所有数据:

>>> rows = conn.execute('SELECT * FROM zoo')

在 SQLAlchemy 中,rows 不是一个列表,不能直接输出:

>>> print(rows)
<sqlalchemy.engine.result.ResultProxy object at 0x1017ef9d0>

但它可以像列表一样迭代,每次可以得到其中的一行:

>>> for row in rows:
...     print(row)
...
('duck', 10, 0.0)
('bear', 2, 1000.0)
('weasel', 1, 2000.0)

这个例子几乎和 SQLite DB-API 提到的示例是一样的。一个优势是在程序开始时不需要导入数据库驱动程序,SQLAlchemy 从连接字符串(connection string)已经指定了。改变连接字符串就可以使得代码可移植到另一种数据库。另外一个优势是 SQLAlchemy 的连接池,如果想了解更多可以阅读它的文档

SQL表达式语言

再往上一层是 SQLAlchemy 的 SQL 表达式语言。它介绍了创建多种 SQL 操作的函数。相比引擎层,它能处理更多 SQL 方言的差异,对于关系型数据库应用是一种方便的中间层解决方案。

下面介绍如何创建和管理数据表 zoo。同样也是一个程序的连续片段。

导入和连接同之前的完全一样:

>>> import sqlalchemy as sa
>>> conn = sa.create_engine('sqlite://')

在定义表单 zoo 时,开始使用一些表达式语言代替 SQL:

>>> meta = sa.MetaData()
>>> zoo = sa.Table('zoo', meta,
...     sa.Column('critter', sa.String, primary_key=True),
...     sa.Column('count', sa.Integer),
...     sa.Column('damages', sa.Float)
...    )
>>> meta.create_all(conn)

注意多行调用时的圆括号。Table() 方法的调用结构和表单的结构相一致,此表单中包含三列,在 Table() 方法调用时括号内部也调用三次 Column()。

同时,zoo 是连接 SQL 数据库和 Python 数据结构的一个对象。

使用表达式语言的更多函数插入数据:

... conn.execute(zoo.insert(('bear', 2, 1000.0)))
<sqlalchemy.engine.result.ResultProxy object at 0x1017ea910>
>>> conn.execute(zoo.insert(('weasel', 1, 2000.0)))
<sqlalchemy.engine.result.ResultProxy object at 0x1017eab10>
>>> conn.execute(zoo.insert(('duck', 10, 0)))
<sqlalchemy.engine.result.ResultProxy object at 0x1017eac50>

接下来创建 SELECT 语句(zoo.select() 会选择出 zoo 对象表单的所有项,和 SELECT * FROM zoo 在普通 SQL 做的相同):

>>> result = conn.execute(zoo.select())

最后得到结果:

>>> rows = result.fetchall()
>>> print(rows)
[('bear', 2, 1000.0), ('weasel', 1, 2000.0), ('duck', 10, 0.0)]

对象关系映射

在上一节中,对象 zoo 是 SQL 和 Python 之间的中间层连接。在 SQLAlchemy 的顶层,对象关系映射(ORM)使用 SQL 表达式语言,但尽量隐藏实际数据库的机制。你自己定义类,ORM 负责处理如何读写数据库的数据。在 ORM 这个复杂短语背后,最基本的观点是:同样使用一个关系型数据库,但操作数据的方式仍然和 Python 保持接近。

我们定义一个类 Zoo,把它挂接到 ORM。这一次,我们使用 SQLite 的 zoo.db 文件以便于验证 ORM 是否有效。

初始的 import 还是一样,这一次需要导入新的东西:

>>> import sqlalchemy as sa
>>> from sqlalchemy.ext.declarative import declarative_base

连接数据库:

>>> conn = sa.create_engine('sqlite:///zoo.db')

现在进入 SQLAlchemy 的 ORM,定义类 Zoo,并关联它的属性和表单中的列:

>>> Base = declarative_base()
>>> class Zoo(Base):
...     __tablename__ = 'zoo'
...     critter = sa.Column('critter', sa.String, primary_key=True)
...     count = sa.Column('count', sa.Integer)
...     damages = sa.Column('damages', sa.Float)
...     def __init__(self, critter, count, damages):
...         self.critter = critter
...         self.count = count
...         self.damages = damages
...     def __repr__(self):
...         return "<Zoo({}, {}, {})>".format(self.critter, self.count, self.damages)

下面这行代码可以很神奇地创建数据库和表单:

>>> Base.metadata.create_all(conn)

然后通过创建 Python 对象插入数据,ORM 内部会管理这些:

>>> first = Zoo('duck', 10, 0.0)
>>> second = Zoo('bear', 2, 1000.0)
>>> third = Zoo('weasel', 1, 2000.0)
>>> first
<Zoo(duck, 10, 0.0)>

接下来,利用 ORM 接触 SQL,创建连接到数据库的会话(session):

>>> from sqlalchemy.orm import sessionmaker
>>> Session = sessionmaker(bind=conn)
>>> session = Session()

借助会话,把创建的三个对象写入数据库。add() 函数增加一个对象,而 add_all() 增加一个列表:

>>> session.add(first)
>>> session.add_all([second, third])

最后使整个过程完整:

>>> session.commit()

成功了吗?好的,在当前目录下创建了文件 zoo.db,可以使用命令行的 SQLite3 程序验证一下:

$ sqlite3 zoo.db
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
zoo
sqlite> select * from zoo;
duck|10|0.0
bear|2|1000.0
weasel|1|2000.0

本节的目的是介绍 ORM 和它在顶层的实现过程。SQLAlchemy 的作者撰写了完整的教程 。阅读后决定哪一级最适合你的需求:

  • 普通 DB-API
  • SQLAlchemy 引擎层
  • SQLAlchemy 表达式语言
  • SQLAlchemy ORM

使用 ORM 避开复杂的 SQL 看似是个很自然的选择。到底应该使用哪一个?有些人认为应该避免使用 ORM ,但其他人觉得批判太重 。不管谁正确,ORM 终究是一种抽象,所有的抽象在某种情况下都会出现问题,毕竟它们是有纰漏的 。当 ORM 不能满足需求时,必须要弄明白在 SQL 如何实现修正。借用互联网的一句话:一些人在遇到问题时理所当然地认为“我明白了,要使用 ORM”。但现在他们会有两个困扰。谨慎使用 ORM 以及多用于简单应用,但是应用足够简单的话,或许至少可以直接使用 SQL(或者是 SQL 表达式语言)。

或者尝试一些更为简单的,例如 dataset 。它建立在 SQLAlchemy 之上,提供对于 SQL、JSON 以及 CSV 存储的简单 ORM。

参考文档