Python数据库编程
简介
在任何应用中，都需要持久化存储，一般有3种基础的存储机制：文件、数据库系统以及一些混合类型。这种混合类型包括现有系统上的API、ORM、文件管理器、电子表格、配置文件等。在了解数据库以及如何在Python中使用他们之前，首先需要知道数据库概念以及SQL语句。
底层存储
数据库通常使用文件系统作为基本的持久化存储，它可以是普通的操作系统文件、专用的操作系统文件，甚至是原始的磁盘分区。
用户接口
大多数数据库系统提供了命令行工具，可以使用其执行SQL语句或查询。此外还有一些GUI工具，使用命令行客户端或数据库客户端库，向用户提供便捷的界面。
数据库
一个关系数据库管理系统(RDBMS)通常可以管理多个数据库，比如销售、市场、用户支持等，都可以在同一个服务端。
组件
数据库存储可以抽象为一张表。每行数据都有一些字段对应于数据库的列。每一行的表定义的集合以及每个表的数据类型放到一起定义了数据库的模式(schema)。数据库可以创建(create)和删除(drop)，表也一样。往数据库里添加新行叫做插入(insert)，修改表中已存在的行叫做更新(update)，而移除表中已存在的行叫做删除(delete)、这些动作通常称为数据库命令或操作。使用可选条件请求获取数据库中的行称为查询(query)。
SQL
数据库命令和查询操作是通过SQL语句提交给数据库的。虽然并非所有数据库都是用SQL语句，但是大多数关系数据库使用。下面是一些SQL命令示例，大部分数据库不区分大小写，但是对数据库关键字使用大写字母是最为广泛接受的风格。大多数命令需要结尾的分号(;)来结束这条语句。
创建数据库
mysql> CREATE DATABASE test; Query OK, 1 row affected (0.00 sec)
使用数据库与删除数据库
mysql> USE test; Database changed mysql> DROP DATABASE test; Query OK, 0 rows affected (0.00 sec)
创建表
mysql> CREATE TABLE users (login VARCHAR(8),userid INT,projid INT); Query OK, 0 rows affected (0.02 sec)
插入行
mysql> INSERT INTO users VALUES('lena',211,1);
Query OK, 1 row affected (0.00 sec)
更新行
mysql> UPDATE users SET userid=311 WHERE projid=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
删除行
mysql> DELETE FROM users WHERE projid=1; Query OK, 1 row affected (0.00 sec)
删除表并清空数据
mysql> DROP TABLE users; Query OK, 0 rows affected (0.00 sec)
在Python中数据库是通过适配器的方式进行访问。适配器是一个Python模块，使用它可以与关系型数据库的客户端接口相连。如图所示为编写Python数据库应用的结构，包括使用和没有使用ORM的情况。从图中可以看出DB-API是连接到数据库客户端的C语言的接口。
Python的DB-API
DB-API是阐明一系列所需对象和数据库访问机制的标准，它可以为不同的数据库适配器和底层数据库提供一致性的访问。DB-API标准要求必须提供下表的功能和属性。
| 属性 | 描述 |
| apilevel | 需要适配器兼容的DB-API版本 |
| threadsafety | 本模块的线程安全级别 |
| paramstyle | 本模块的SQL语句参数风格 |
| connect() | Connect()函数 |
| (多种异常) |
异常 |
数据属性
apilevel，该字符串致命模块需要兼容的DB-API最高版本
threadsafety，指明模块线程的安全级别
0：不支持线程安全，线程间不能共享模块。
1：最小化线程安全支持，线程间可以共享模块，但不能共享连接。
2：适度的线程安全支持，线程间可以共享模块和连接，但不能共享游标。
3：完全的线程安全支持，线程可以共享模块，连接和游标。
参数风格
DB-API支持以不同的方式指明如何将参数与SQL语句进行整合，并最终传递给服务器中执行。该参数是一个字符，用于指定构建查询行或命令时使用的字符串替代形式。
| 参数风格 | 描述 | 示例 |
| numeric | 数值位置风格 | WHERE name=:1 |
| named | 命名风格 | WHERE name=:name |
| pyformat | Python字典printf()格式转换 | WHERE name=%(name)s |
| qmark | 问号风格 | WHERE name=? |
| format | ANSIC的printf()格式转换 | WHERE name=%s |
函数属性
connect()函数通过Connection对象访问数据库。兼容模块继续实现connect()函数，该函数创建并返回一个Connection对象。connect()函数可以使用包含多个参数的字符串来传递数据库连接信息，也可以按照位置传递每个参数，或者时使用关键字参数的形式传递。
connect(host ='localhost', user = 'root', passwd ='123456',db='movie',charset='utf8')
| 参数 | 描述 |
| host | 主机名 |
| user | 用户名 |
| passwd | 密码 |
| db |
数据库名 |
| charset |
字符集 |
异常
| 异常 | 描述 |
| Warning | 警告异常基类 |
| Error | 错误异常基类 |
| InterfaceError | 数据接口错误 |
| DatabaseError |
数据库错误 |
| DataError |
处理数据时出现错误 |
| OperationError |
数据库操作执行期间出现错误 |
| IntegrityError | 数据库关系完整性错误 |
| InternalError |
数据库内部错误 |
| ProgrammingError | SQL命令执行失败 |
| NotSupportedError |
出现不支持的操作 |
Connection对象
应用与数据之间进行通信需要建立数据库连接。它是最基本的机制，只有通过数据库连接才能把命令传递到服务器，并得到返回的结果。当一个连接建立后，可以创建一个游标，向数据库发送请求，然后从数据库中接收回应。
Connection对象不需要包含任何数据，不过应当定义下标的几个方法：
| 方法名 | 描述 |
| close() | 关闭数据库连接 |
| commit() | 提交当前事务 |
| rollback() | 取消当前事务 |
| cursor() | 使用该链接创建一个游标或类游标的对象 |
| errorhandler(cxn,sur,errcls,errval) | 作为给定连接的游标的处理程序 |
Cursor对象
当建立连接后，就可以和数据库进行通信。游标可以让用户提交数据库命令，并且获得查询结果行。Python DB-API游标对象总能提供游标的功能，游标对象最重要的属性是execute()和fetch()方法，所有针对数据库的服务请求都是通过它们执行的。
| 对象属性 | 描述 |
| arraysize | 使用fetchmany()方法时，一次取出的结果行数，默认1 |
| connection | 创建次游标的连接 |
| description |
返回游标活动状态 |
| lastrowid |
上次修改行的行ID |
| rowcount | 上次execute()方法处理或影响的行数 |
| callproc(func[,args]) |
调用存储过程 |
| close() |
关闭游标 |
| execute(op[,args]) |
执行数据库查询或命令 |
| executemany(op,args) |
类似execute()和map()的结合，为给定的所有参数准备并执行数据库查询或命令 |
| fetchone() |
获取查询结果的下一行 |
| fetchmany([size=cursor,arraysize]) |
获取查询结果的下size行 |
| fetchall() |
获取查询结果的剩余所有行 |
| __iter__() | 为游标创建迭代器对象 |
| messages |
游标执行后从数据库中获得的消息列表 |
| next() |
被迭代器用于获取查询结果的下一行 |
| nextset() |
移动到下一个结果集合 |
| rownumber |
当前结果集中游标的索引 |
| setinputsizes(sizes) |
设置允许的最大输入大小 |
| setoutputsize(size[,col]) |
设置获取的最大缓冲区大小 |
ORM与SQLAlchemy
ORM(Object-Relational Mapping,对象关系映射)的作用实在关系型数据库和业务实体对象之间做一个映射，这样开发者在操作数据库的数据时，就不需要再去和复杂的SQL语句打交道，只需要简单的操作对象的属性和方法。所有ORM必须具备3个方面的基本能力：映射技术、CURD操作和缓存技术。
ORM在卡发者和数据库之间建立了中间层，把数据库中的数据转换成了Python中的对象实体，这样即屏蔽不同数据库之间的差异性，又使开发者可以非常方便的操作数据库中的数据。当前SQLAlchemy是Python中最成熟的ORM框架，资源和文档丰富。大多数Python Web框架对其都有很好的支持。
Dialect用于和数据API进行连接，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作：
|
MySQL-Python mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> pymysql mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] MySQL-Connector mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...] |
连接数据库：
In [1]: from sqlalchemy import create_engine
In [2]: engine = create_engine('mysql+mysqlconnector://root@127.0.0.1:3306
...: /test',echo=True)
创建表:
In [3]: from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey,Seque ...:nce
In [4]: metadata = MetaData()
In [5]: users = Table('users', metadata,
...: Column('id', Integer, Sequence('user_id_seq'), primary_key=True),
...: Column('name', String(50)),
...: Column('fullname', String(50)),
...: Column('password', String(12))
...: )
In [6]: addresses = Table('addresses', metadata,
...: Column('id', Integer, primary_key=True),
...: Column('user_id', None, ForeignKey('users.id')),
...: Column('email_address', String(50), nullable=False)
...: )
In [7]: metadata.create_all(engine)
2017-05-19 17:59:46,958 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2017-05-19 17:59:46,959 INFO sqlalchemy.engine.base.Engine {}
2017-05-19 17:59:46,960 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2017-05-19 17:59:46,960 INFO sqlalchemy.engine.base.Engine {}
2017-05-19 17:59:46,962 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2017-05-19 17:59:46,962 INFO sqlalchemy.engine.base.Engine {}
2017-05-19 17:59:46,963 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2017-05-19 17:59:46,963 INFO sqlalchemy.engine.base.Engine {}
2017-05-19 17:59:46,964 INFO sqlalchemy.engine.base.Engine DESCRIBE `users`
2017-05-19 17:59:46,964 INFO sqlalchemy.engine.base.Engine {}
2017-05-19 17:59:46,965 INFO sqlalchemy.engine.base.Engine DESCRIBE `addresses`
2017-05-19 17:59:46,965 INFO sqlalchemy.engine.base.Engine {}
2017-05-19 17:59:46,966 INFO sqlalchemy.engine.base.Engine ROLLBACK
2017-05-19 17:59:46,967 INFO sqlalchemy.engine.base.Engine
CREATE TABLE addresses (
id INTEGER NOT NULL AUTO_INCREMENT,
user_id INTEGER,
email_address VARCHAR(50) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(user_id) REFERENCES users (id)
)
2017-05-19 17:59:46,967 INFO sqlalchemy.engine.base.Engine {}
2017-05-19 17:59:46,994 INFO sqlalchemy.engine.base.Engine COMMIT
插入数据：
In [8]: ins = users.insert()
In [9]: str(ins)
Out[9]: 'INSERT INTO users (id, name, fullname, password) VALUES (:id, :name, :fullname, :password)'
In [10]: ins = users.insert().values(id=1,name='jack', fullname='Jack Jones')
In [11]: ins.compile().params
Out[11]: {'fullname': 'Jack Jones', 'id': 1, 'name': 'jack'}
In [12]: conn = engine.connect()
In [13]: result = conn.execute(ins)
2017-05-19 18:04:29,982 INFO sqlalchemy.engine.base.Engine INSERT INTO users (id, name, fullname) VALUES (%(id)s, %(name)s, %(fullname)s)
2017-05-19 18:04:29,982 INFO sqlalchemy.engine.base.Engine {'id': 1, 'name': 'jack', 'fullname': 'Jack Jones'}
2017-05-19 18:04:29,982 INFO sqlalchemy.engine.base.Engine COMMIT
In [16]: conn.execute(addresses.insert(), [ #多条语句插入
...: ... {'user_id': 1, 'email_address' : 'jack@yahoo.com'},
...: ... {'user_id': 1, 'email_address' : 'jack@msn.com'},
...: ... {'user_id': 2, 'email_address' : 'www@www.org'},
...: ... {'user_id': 2, 'email_address' : 'wendy@aol.com'},
...: ... ])
2017-05-19 18:07:29,203 INFO sqlalchemy.engine.base.Engine INSERT INTO addresses (user_id, email_address) VALUES (%(user_id)s, %(email_address)s)
2017-05-19 18:07:29,203 INFO sqlalchemy.engine.base.Engine ({'user_id': 1, 'email_address': 'jack@yahoo.com'}, {'user_id': 1, 'email_address': 'jack@msn.com'}, {'user_id': 2, 'email_address': 'www@www.org'}, {'user_id': 2, 'email_address': 'wendy@aol.com'})
2017-05-19 18:07:29,204 INFO sqlalchemy.engine.base.Engine COMMIT
Out[16]: <sqlalchemy.engine.result.ResultProxy at 0x7f3b8b4f2cf8>
查询
In [17]: from sqlalchemy.sql import select
In [18]: s = select([users])
In [19]: result = conn.execute(s)
2017-05-19 18:08:59,639 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname, users.password
FROM users
2017-05-19 18:08:59,639 INFO sqlalchemy.engine.base.Engine {}
In [20]: for row in result:
...: print(row)
...:
(1, 'jack', 'Jack Jones', None)
In [22]: for row in conn.execute(select([users, addresses])): #多条查询
...: print(row)
...:
2017-05-19 18:11:41,681 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname, users.password, addresses.id, addresses.user_id, addresses.email_address
FROM users, addresses
2017-05-19 18:11:41,681 INFO sqlalchemy.engine.base.Engine {}
(1, 'jack', 'Jack Jones', None, 1, 1, 'jack@yahoo.com')
(1, 'jack', 'Jack Jones', None, 2, 1, 'jack@msn.com')
(1, 'jack', 'Jack Jones', None, 3, 2, 'www@www.org')
(1, 'jack', 'Jack Jones', None, 4, 2, 'wendy@aol.com')
更新
In [27]: stmt = users.update().values(fullname="Fullname: " + users.c.name)
In [28]: conn.execute(stmt)
2017-05-19 18:27:33,489 INFO sqlalchemy.engine.base.Engine UPDATE users SET fullname=(concat(%(name_1)s, users.name))
2017-05-19 18:27:33,489 INFO sqlalchemy.engine.base.Engine {'name_1': 'Fullname: '}
2017-05-19 18:27:33,490 INFO sqlalchemy.engine.base.Engine COMMIT
Out[28]: <sqlalchemy.engine.result.ResultProxy at 0x7f3b8b50ca58>
删除
In [31]: conn.execute(addresses.delete())
2017-05-19 18:30:02,296 INFO sqlalchemy.engine.base.Engine DELETE FROM addresses
2017-05-19 18:30:02,296 INFO sqlalchemy.engine.base.Engine {}
2017-05-19 18:30:02,297 INFO sqlalchemy.engine.base.Engine COMMIT
Out[31]: <sqlalchemy.engine.result.ResultProxy at 0x7f3b8b4a3f28>
In [32]: conn.execute(users.delete().where(users.c.name > 'm'))
2017-05-19 18:30:12,159 INFO sqlalchemy.engine.base.Engine DELETE FROM users WHERE users.name > %(name_1)s
2017-05-19 18:30:12,159 INFO sqlalchemy.engine.base.Engine {'name_1': 'm'}
2017-05-19 18:30:12,159 INFO sqlalchemy.engine.base.Engine COMMIT
Out[32]: <sqlalchemy.engine.result.ResultProxy at 0x7f3b8b50bb70>