Python学习笔记_第十三章：数据库支持

了解关系型数据库：http://www.sqlcourse.com
了解python包支持的数据库：http://www.python.org/topics/database/
关系型数据库并不是唯一的数据库，还有类似ZODB(http://wiki.zope.org/ZODB)的对象数据库、类似Metakit(http://www.equi4.com/metakit/python.html)的基于紧凑表的数据库和类似BSD DB(http://docs.python.org/lib/module-bsddb.html)的更简单的键——值数据库。

Python的数据库编程接口

Python中有多种关系型数据库对应的客户端模块，为了解决Python中各种数据库模块之间的兼容问题，现在已经通过了一个标准的数据库编程接口（DB API）：http://python.org/peps/pep-0249.html
下面简单介绍API，了解更多API细节可移步：http://wiki.python.org/moin/DatabaseProgramming

全局变量

Python DB API的模块特性

变量名	用途
apilevel	所用的Python DB API版本
threadsafety	模块的线程安全等级
paramstyle	在SQL查询中使用的参数风格

apilevel：可能是1.0或2.0未来也可能有3.0，若不存在，则假定为1.0
threadsafety：0~3的等级

• 0：线程完全不共享
• 1：线程共享，但连接不共享
• 2：（连接共享）
• 3：完全线程安全

paramstyle：参数拼接方法，format：标准的字符串格式化（%s），pyformat：扩展的格式化，用于字典拼接，如%(foo)。除了Python风格，还有三种方式：qmark(?)、numeric(:1,:2,……)、named(类似于关键字参数)

异常

DB API中使用的异常
略

连接游标

connect函数常见参数

参数名	描述	是否可选
dsn	数据源名称，给出该参数表示数据库依赖	否
user	用户名	是
password	用户密码	是
host	主机名称	是
database	数据库名	是

connect连接对象支持的函数

名称	描述
close	关闭
commit	如果支持事务就提交挂起的事务，否则什么都不做
rollback	回滚挂起的事务，可能不支持
cursor	返回游标

cursor对象方法

名称	描述
callproc(name[, params])	调用name数据库的过程
close()	关闭游标
execute(oper[, params])	执行SQL
executemany(oper, pseq)	执行多个SQL
fetchone()	对下一行操作：将其保存为序列
fetchmany([size])	对剩余size行操作：保存为序列的序列
fetchall()	对剩余所有行操作：保存为序列的序列
nextset()	调至下一个可用结果集
setinputsize(sizes)	为参数预先定义内存区
setoutputsize(size[, col])	为获取的大数据值设定缓冲区尺寸

cursor对象特性

名称	描述
description	描述结果列的序列，只读
rowcount	结果列中的行数，只读
arraysize	fetchmany返回的行数，默认1

数据特殊类型（Data、 Time等）

略

SQLite和PySQLite

多数数据库引擎安装都需要管理员权限，他们作为服务器程序运行。所以，此处选用小型数据库引擎SQLite作为示例
从Python2.5开始，SQLite的优势是它的一个包装(PySQLite)已经被包括在标准库内，除非你是从源码编译Python

数据库应用程序示例

本例建立小型营养成分数据库作为示例程序，这个程序基于USDA的营养数据实验室提供的数据（http://www.ars.usda.gov/nutrientdata），由于里面有好多数据，我随便找的其中一个，链接为：https://pan.baidu.com/s/1NdSRJw_UcIe3UUr6wSbdJA 提取码：948d
代码为如下

import sqlite3 as sq
import os
conn = sq.connect('food.db')
curs = conn.cursor()
try:
    curs.execute('''
CREATE TABLE FOOD(
id TEXT PRIMARY KEY, desc TEXT, water FLOAT, kcal FLOAT, protein FLOAT, fat FLOAT, ash FLOAT, carbs FLOAT, fiber FLOAT, sugar FLOAT,
fat1 FLOAT, ash1 FLOAT, carbs1 FLOAT, fiber1 FLOAT, sugar1 FLOAT, fat2 FLOAT, ash2 FLOAT, carbs2 FLOAT, fiber2 FLOAT, sugar2 FLOAT,
fat3 FLOAT, ash3 FLOAT, carbs3 FLOAT, fiber3 FLOAT, sugar3 FLOAT, fat4 FLOAT, ash4 FLOAT, carbs4 FLOAT, fiber4 FLOAT, sugar4 FLOAT,
fat5 FLOAT, ash5 FLOAT, carbs5 FLOAT, fiber5 FLOAT, sugar5 FLOAT, fat6 FLOAT, ash6 FLOAT, carbs6 FLOAT, fiber6 FLOAT, sugar6 FLOAT,
fat7 FLOAT, ash7 FLOAT, carbs7 FLOAT, fiber7 FLOAT, sugar7 FLOAT, fat8 FLOAT, ash8 FLOAT, carbs8 FLOAT, fiber8 FLOAT, sugar8 FLOAT,
carbs9 FLOAT, fiber9 FLOAT, sugar9 FLOAT

)
''')
except sq.OperationalError :
    curs.execute('DROP TABLE FOOD')

def convert(value):
    if value.startswith('~'):
        return value.strip('~')
    if not value:
        value = 0
        return float(value)
query = 'INSERT INTO food VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,' \
        ' ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
for f in os.listdir('C:\Users\hcs\Desktop\sr24abbr'):
    for line in open('C:\Users\hcs\Desktop\sr24abbr\\' + f):
        data = line.split('^')
        vals = [convert(da) for da in data]
        curs.execute(query, vals)
conn.commit()
conn.close()

测试代码：

from __future__ import print_function
import sqlite3 as sq
import sys
conn = sq.connect('food.db')
cur = conn.cursor()
query = 'select * from food where %s' % sys.argv[1]
cur.execute(query)
name = [f[0] for f in cur.description]
for row in cur.fetchall():
    for pair in zip(name, row):
        print('%s:%s' % pair, end = ' ')
    print('\n')
conn.commit()
conn.close()

控制台输入：

python food_db_manager.py "id < 30 order by sugar"

结果：

D:\python_base_1>python food_db_manager.py "id < 30 order by sugar"
id:01001 desc:BUTTER,WITH SALT water:None kcal:None protein:None fat:None ash:None carbs:None fiber:None sugar:None fat1:None ash1:None carbs1:None fiber1:None sugar1:None fat2:None ash2:None carbs2:None fiber2:None s
ugar2:None fat3:None ash3:None carbs3:None fiber3:None sugar3:None fat4:None ash4:None carbs4:None fiber4:None sugar4:None fat5:None ash5:None carbs5:None fiber5:None sugar5:None fat6:None ash6:None carbs6:None fiber6
:None sugar6:None fat7:None ash7:None carbs7:None fiber7:None sugar7:None fat8:None ash8:None carbs8:None fiber8:None sugar8:1 cup carbs9:None fiber9:1 tbsp sugar9:None

在SQLite内部，所有值实际上都是字符串，一些转换和检查操作在数据库和Python API之间进行。