关系型数据库的一些功能：

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

之所以被称为关系型（relational）是因为数据库展现了表单（table）形式的不同类型数据 之间的关系。
表单是一个具有行和列的二元组，和电子数据表类似。要创建一个表单，需要给它命名， 明确次序、每一项的名称以及每一列的类型。每一行都会存在相同的列，即使允许缺失项 （也称为 null）。
某一行或者某几行通常作为表单的主键，在表单中主键的值是独一无二的，防止重复增添 数据项。这些键在查询时被快速索引，类似于图书的索引，方便快速地找到指定行。
每一个表单都附属于某数据库，类似于一个文件都存在于某目录下。两层的层次结构便于 更好地组织和管理。
数据库一词有多种用法 ，用于指代服务器、表单容器以及存储的数据。如 果你同时指代它们，可以称其为数据库服务器（database server）、数据库 （database）和数据（data）。
如果我们想要通过非主键的列的值查找数据，可以定义一个二级索引，否则数据库服务器需要扫描整个表单，暴力搜索每一行找到匹配列的值。
表单之间可以通过外键建立关系，列的值受这些键的约束。

SQL

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

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

SQL语句有两种主要类型：

• DDL（数据定义语言）
  处理用户，数据库以及表单的创建，删除，约束和权限等。
• DML（数据操作语言）
  处理数据插入，选择，更新

基本的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是不区分大小写的。

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

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

基本的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。使用它可以编写简单的程 序来处理多种类型的关系型数据库，不需要为每种数据库编写独立的程序。
它的主要函数如下所示：

• connect()
  连接数据库，包含参数用户名，密码，服务器地址，等等
• cursor()
  创建一个cursor()对象来惯例查询
• execute()和executemany()
  对数据库执行一个或多个SQL命令
• fetchone(),fetchmany()和fetchall()
  得到execute之后的结果

SQLite

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

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

下面的栗子会创建一个数据库enterprise.db（自己先创建一个文件） 和表单 zoo 用以管理路边繁华的宠物动物园 业务。表单的列如下所示。
• critter 可变长度的字符串，作为主键。
• count 某动物的总数的整数值。
• damages 人和动物的互动中损失的美元数目。

In [1]: import sqlite3

In [2]: conn = sqlite3.connect('enterprise.db') 

In [3]: curs = conn.cursor()

In [4]: curs.execute('''CREATE TABLE zoo
   ...: (critter VARCHAR(20) PRIMARY KEY，
   ...: count INT,
   ...: damages FLOAT)''') 
ERROR:root:An unexpected error occurred while tokenizing input
The following traceback may be corrupted or invalid
The error message is: ('EOF in multi-line string', (1, 14))

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
<ipython-input-4-819479c0680a> in <module>()
      2 (critter VARCHAR(20) PRIMARY KEY，
      3 count INT,
----> 4 damages FLOAT)''') 

OperationalError: near "KEY，": syntax error

In [5]: curs.execute('''CREATE TABLE zoo
   ...: (critter VARCHAR(20)  PRIMARY KEY, 
   ...: count INT,
   ...: damages FLOAT)''') 
Out[5]: <sqlite3.Cursor at 0x7fc4d86e2f10>
Python 只有在创建长字符串时才会用到三引号（'''），例如 SQL 查询。
现在往动物园中新增一些动物：
In [6]: curs.execute('INSERT INTO zoo VALUES("duck", 5, 0.0)') 
Out[6]: <sqlite3.Cursor at 0x7fc4d86e2f10>

In [7]: curs.execute('INSERT INTO zoo VALUES("bear", 2, 1000.0)') 
Out[7]: <sqlite3.Cursor at 0x7fc4d86e2f10>

使用 placeholder 是一种更安全的、插入数据的方法：
In [8]: ins = 'INSERT INTO zoo (critter,count,damages) VALUES (?,?,?)' 

In [9]: curs.execute(ins,('weasel',1,2000.0)) 
Out[9]: <sqlite3.Cursor at 0x7fc4d86e2f10>

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

现在使用 SQL 获取所有动物：
In [10]: curs.execute('SELECT * from zoo')
Out[10]: <sqlite3.Cursor at 0x7fc4d86e2f10>

In [11]: rows = curs.fetchall() 

In [12]: print(rows) 
[('duck', 5, 0.0), ('bear', 2, 1000.0), ('weasel', 1, 2000.0)]
需要按照降序得到它们：
In [13]: curs.execute('SELECT * from zoo ORDER BY count DESC') 
Out[13]: <sqlite3.Cursor at 0x7fc4d86e2f10>

In [14]: curs.fetchall()
Out[14]: [('duck', 5, 0.0), ('bear', 2, 1000.0), ('weasel', 1, 2000.0)]
哪种类型的动物花费最多呢？
In [15]: curs.execute('''SELECT * FROM zoo WHERE 
    ...: damages = (SELECT MAX(damages) FROM zoo)''')  
Out[15]: <sqlite3.Cursor at 0x7fc4d86e2f10>

In [16]: curs.fetchall() 
Out[16]: [('weasel', 1, 2000.0)]
我们打开了一个连接（connection）或者游标 （cursor），不需要时应该关掉它们：
In [17]: curs.close()
In [18]: conn.close() 

SQLAlchemy

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

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

pip install sqlalchemy

我们可以在以下层级上是用SQLAlchemy:

• 底层负责处理数据库连接池，执行SQL命令以及返回结果，这和DB-API相似；
• 再往上是SQL表达式语言，更像Python的SQL生成器。
• 较高级的是对象关系模型（ORM），使用SQK表达式语言，将应用程序代码和关系型数据结构结合起来。
  SQLAlchemy实现在前面几节提到的数据库驱动程序的基础上。因此不需要导入驱动程序，初始化的连接字符串会作出分配，例如：

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

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

• dialect
  数据库类型
• driver
  使用该数据库的特定驱动程序
• user和password
  数据库认证字符串
• host和port
  数据库服务器的位置（只有特定情况下会使用端口号：port）
• dbname
  初始连接到服务器中的数据库

SQLAlchemy连接

数据库类型 驱动程序
sqlite pysqlite（可以忽略）
mysql mysqlconnector
mysql pymysql
mysql oursql
postgresql psycopg2
postgresql pypostgresql

1.引擎层

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

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

下面是一个小栗子：

导入库函数，并起别名 sa
In [22]: import sqlalchemy as sa 
连接到数据库，并在内存中存储它（参数字符串 'sqlite:///:memory:' 也是可行的）：
In [23]: conn = sa.create_engine('sqlite://')
创建包含三列的数据库表单 zoo：
In [24]: conn.execute('''CREATE TABLE zoo 
    ...: (critter VARCHAR(20) PRIMARY KEY,
    ...: count INT, 
    ...: damages FLOAT)''') 
Out[24]: <sqlalchemy.engine.result.ResultProxy at 0x7fc4d2e6de48>
运行函数 conn.execute() 返回到一个 SQLAlchemy 的对象 ResultProxy
现在向空表单里插入三组数据：
In [25]: ins ='INSERT INTO zoo (critter, count, damages) VALUES (?, ?, ?)'  

In [26]: conn.execute(ins, 'duck', 10, 0.0)  
Out[26]: <sqlalchemy.engine.result.ResultProxy at 0x7fc4d2eb7fd0>

In [27]: conn.execute(ins, 'bear', 2, 1000.0)  
Out[27]: <sqlalchemy.engine.result.ResultProxy at 0x7fc4d2efc518>

In [28]:  conn.execute(ins, 'weasel', 1, 2000.0)  
Out[28]: <sqlalchemy.engine.result.ResultProxy at 0x7fc4d2eadc50>

In [29]: rows = conn.execute('SELECT * FROM zoo) 
  File "<ipython-input-29-337b763205d0>", line 1
    rows = conn.execute('SELECT * FROM zoo)
                                            ^
SyntaxError: EOL while scanning string literal

查询所有放进去的数据：
In [30]: rows = conn.execute('SELECT * FROM zoo') 
在SQLAlchemy中，row不是一个列表，不能直接输出：
In [31]: print(rows)
<sqlalchemy.engine.result.ResultProxy object at 0x7fc4d2e6d9e8>
但它可以像列表一样迭代，每次可以得到其中的一行：
In [32]: for row in rows: 
    ...:     print(row) 
    ...:      
('duck', 10, 0.0)
('bear', 2, 1000.0)
('weasel', 1, 2000.0)

这个例子几乎和 SQLite DB-API 提到的示例是一样的。一个优势是在程序开始时不需要导入数据库驱动程序，SQLAlchemy 从连接字符串（connection string）已经指定了。改变连 接字符串就可以使得代码可移植到另一种数据库。

2.SQL表达式语言

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

下面介绍如何创建和管理数据表 zoo。

In [33]: import sqlalchemy as  sa 

In [34]: conn = sa.create_engine('sqlite://')
在定义表单 zoo 时，开始使用一些表达式语言代替 SQL：
In [35]: meta = sa.MetaData() 

In [36]: zpptb = sa.Table('zoo',meta,
    ...: sa.Column('critter', sa.String, primary_key=True), 
    ...: sa.Column('count',sa.Integer),
    ...: sa.Column('damages',sa.Float) 
    ...: ) 
下面的的代码创建了数据表
In [37]: meta.create_all(conn)

注意多行调用时的圆括号。Table() 方法的调用结构和表单的结构相一致，此表单中包含 三列，在 Table() 方法调用时括号内部也调用三次 Column()。
同时，zpptb是连接 SQL 数据库和 Python 数据结构的一个对象。
使用表达式语言的更多函数插入数据：

In [38]: conn.execute(zpptb.insert(('bear',2,1000.0)))
Out[38]: <sqlalchemy.engine.result.ResultProxy at 0x7fc4d2e6d0b8>

In [39]: conn.execute(zoo.insert(('weasel', 1, 2000.0))) 
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-39-1f816288e04e> in <module>()
----> 1 conn.execute(zoo.insert(('weasel', 1, 2000.0)))

NameError: name 'zoo' is not defined

In [40]: conn.execute(zpptb.insert(('weasel', 1, 2000.0))) 
Out[40]: <sqlalchemy.engine.result.ResultProxy at 0x7fc4d2e64278>

In [41]: conn.execute(zpptb.insert(('duck', 10, 0))) 
Out[41]: <sqlalchemy.engine.result.ResultProxy at 0x7fc4d2e4a550>

接下来创建 SELECT 语句（zpptb.select() 会选择出 zpptb 对象表单的所有项，和 SELECT * FROM zoo 在普通 SQL 做的相同）
In [42]: result = conn.execute(zpptb.select()) 

最终得到的结果
In [43]: rows = result.fetchall() 

In [44]: print(rows) 
[('bear', 2, 1000.0), ('weasel', 1, 2000.0), ('duck', 10, 0.0)]

3.对象关系映射（Object Relation Mapping）

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

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

初始的import 还是一样，这一次需要导入新的东西：
In [1]: import sqlalchemy as  sa
In [2]: from sqlalchemy.ext.declarative import declarative_base
连接数据库：
In [3]: conn = sa.create_engine('sqlite:///zoo.db')
现在进入 SQLAlchemy 的 ORM，定义类 Zoo，并关联它的属性和表单中的列：
In [4]: Base = declarative_base()
In [5]: 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)
   ...:      
下面这行代码创建数据库和表单：
In [6]: Base.metadata.create_all(conn)
然后通过创建 Python 对象插入数据，ORM 内部会管理这些：
In [7]: first = Zoo('duck',10,0.0) 

In [8]: second = Zoo('bear', 2, 1000.0)  

In [9]: third = Zoo('weasel', 1, 2000.0)  

In [10]: first 
Out[10]: <Zoo(duck, 10, 0.0)>
接下来，利用 ORM 接触 SQL，创建连接到数据库的会话（session）：
In [11]: from sqlalchemy.orm import sessionmaker 
In [12]: Session = sessionmaker(bind=conn) 
In [13]: session = Session() 
借助会话，把创建的三个对象写入数据库。add() 函数增加一个对象，而 add_all() 增加一 个列表
In [14]: session.add(first) 

In [15]: session.add_all([second,third])
最后使整个过程完整：
In [16]: session.commit()

我们现在在当前目录下创建了文件zoo.db，可以使用命令行的 SQLite3 程序验证 一下：

[root@wangerxiao ~]# sqlite3 zoo.db 
SQLite version 3.5.6
Enter ".help" for instructions
sqlite> .tables           
zoo
sqlite> select * from zoo;
duck|10|0.0
bear|2|1000.0
weasel|1|2000.0

本节的目的是介绍 ORM 和它在顶层的实现过程。

四个层级按照需求选择：

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

MySQL

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

MySQL的驱动程序:Connector，PYMySQL，oursql

PostgreSQL

PostgreSQL（http://www.postgresql.org/） 是一款功能全面的开源关系型数据库，在很多方 面超过 MySQL.

名称 链接 Pypi包 导入 注意
psycopg2 http://initd.org/psycopg/ psycopg2 psycopg2 需要来自 PostgreSQL 客户端工具 的 pg_config

最流行的驱动程序是 psycopg2，但是它的安装依赖 PostgreSQL 客户端的相关库。

注：本文内容来自《Python语言及其应用》欢迎购买原书阅读