Python3----第十三章 数据库支持

#!usr/bin/env python  
# -*- coding:utf-8 _*-
""" 
@author:AIvision 
@file: lecture13.py 
@time: 2023/09/05 
"""
# 第十三章 数据库支持
# 13.1 Python 数据库 API
# 前面说过，有各种SQL数据库可供选择，其中很多都有相应的Python客户端模块（有些数据
# 库甚至有多个）。所有数据库的大多数基本功能都相同，因此从理论上说，对于使用其中一种数
# 据库的程序，很容易对其进行修改以使用另一种数据库。问题是即便不同模块提供的功能大致相
# 同，它们的接口（API）也是不同的。为解决Python数据库模块存在的这种问题，人们一致同意
# 开发一个标准数据库API（DB API）。这个API的最新版本（2.0）是在PEP 249（Python Database API
# Specification v2.0）中定义的，网址为http://python.org/peps/pep-0249.html。
# 本节概述有关该API的基础知识。这里不会涉及其可选部分，因为它们并不适用于所有数据
# 库。有关该API的详细信息，可参阅前面提到的PEP，也可参阅Python官方维基百科中的数据库编
# 程指南（http://wiki.python.org/moin/DatabaseProgramming）。如果你对这个API的细节不感兴趣，可跳过本节。

# 13.1.1 全局变量
# 所有与DB API2.0兼容的数据库模块都必须包含三个全局变量，它们描述了模块的特征。这
# 样做的原因是，这个API设计得很灵活，无需进行太多包装就能配合多种不同的底层机制使用。
# 如果要让程序能够使用多种不同的数据库，可能会比较麻烦，因为需要考虑众多不同的可能性。
# 在很多情况下，一种更现实的做法是检查这些变量，看看给定的模块是否是程序能够接受的。如
# 果不是，就显示合适的错误消息并退出或者引发异常。表13-1总结了这些全局变量。

# 表13-1 Python DB API的模块属性
# 变 量 名                      描 述
# apilevel              使用的Python DB API版本
# threadsafety          模块的线程安全程度如何
# paramstyle            在SQL查询中使用哪种参数风格

# API级别（apilevel）是一个字符串常量，指出了使用的API版本。DB API 2.0指出，这个变
# 量的值为'1.0'或'2.0'。如果没有这个变量，就说明模块不与DB API 2.0兼容，应假定使用的是
# DB API 1.0。编写代码时，允许这个变量为其他值也没有害处，因为说不定什么时候DB API 3.0
# 就出来了。
# 线程安全程度（threadsafety）是一个0~3（含）的整数。0表示线程不能共享模块，而3表
# 示模块是绝对线程安全的。1表示线程可共享模块本身，但不能共享连接（参见13.1.3节），而2
# 表示线程可共享模块和连接，但不能共享游标。如果你不使用线程（在大多数情况下可能不会是
# 这样的），就根本不用关心这个变量。
# 参数风格（paramstyle）表示当你执行多个类似的数据库查询时，如何在SQL查询中插入参
# 数。'format'表示标准字符串格式设置方式（使用基本的格式编码），如在要插入参数的地方插
# 入%s。'pyformat'表示扩展的格式编码，即旧式字典插入使用的格式编码，如%(foo)s。除这些
# Python风格外，还有三种指定待插入字段的方式：'qmark'表示使用问号，'numeric'表示使用:1
# 和:2这样的形式表示字段（其中的数字是参数的编号），而'named'表示使用:foobar这样的形式表
# 示字段（其中foobar为参数名）。如果你觉得参数样式令人迷惑，也不用担心。编写简单程序时，
# 不会用到它们。如果需要明白特定的数据库是如何处理参数的，可参阅相关的文档。


# 13.1.2 异常
# DB API定义了多种异常，让你能够细致地处理错误。然而，这些异常构成了一个层次结构，
# 因此使用一个except块就可捕获多种异常。当然，如果你觉得一切都正常运行，且不介意出现不
# 太可能出现的错误时关闭程序，可以根本不考虑这些异常。
# 表13-2说明了这个异常层次结构。异常应该在整个数据库模块中都可用。有关这些异常的深
# 入描述，请参阅DB API规范（前面提到的PEP）。

# 表13-2 Python DB API指定的异常
# 异 常               超 类                         描 述
# StandardError                             所有异常的超类
# Warning           StandardError               发生非致命问题时引发
# Error             StandardError               所有错误条件的超类
# InterfaceError        Error              与接口（而不是数据库）相关的错误
# DatabaseError         Error               与数据库相关的错误的超类
# DataError         DatabaseError           与数据相关的问题，如值不在合法的范围内
# OperationalError  DatabaseError           数据库操作内部的错误
# IntegrityError        DatabaseError       关系完整性遭到破坏，如键未通过检查
# InternalError         DatabaseError       数据库内部的错误，如游标无效
# ProgrammingError      DatabaseError       用户编程错误，如未找到数据库表
# NotSupportedError     DatabaseError       请求不支持的功能，如回滚

# 13.1.3 连接和游标
# 要使用底层的数据库系统，必须先连接到它，为此可使用名称贴切的函数connect。这个函
# 数接受多个参数，具体是哪些取决于要使用的数据库。作为指南，DB API定义了表13-3所示的参
# 数。推荐将这些参数定义为关键字参数，并按表13-3所示的顺序排列。这些参数都应该是字符串。

#               表13-3 函数connect的常用参数
# 参 数 名                   描 述                             是否可选
# dsn                  数据源名称，具体含义随数据库而异            否
# user                      用户名                              是
# password                  用户密码                            是
# host                      主机名                              是
# database                  数据库名称                          是

# 13.2.1节和第26章提供了函数connect的具体使用示例。
# 函数connect返回一个连接对象，表示当前到数据库的会话。连接对象支持表13-4所示的方法。

# 表13-4 连接对象的方法
# 方 法 名                         描 述
# close()               关闭连接对象。之后，连接对象及其游标将不可用
# commit()              提交未提交的事务——如果支持的话；否则什么都不做
# rollback()            回滚未提交的事务（可能不可用）
# cursor()              返回连接的游标对象

# 方法rollback可能不可用，因为并非所有的数据库都支持事务（事务其实就是一系列操作）。可用时，这个方法撤销所有未提交的事务。
# 方法commit总是可用的，但如果数据库不支持事务，这个方法就什么都不做。关闭连接时，
# 如果还有未提交的事务，将隐式地回滚它们——但仅当数据库支持回滚时才如此！如果你不想依
# 赖于这一点，应在关闭连接前提交。只要提交了所有的事务，就无需操心关闭连接的事情，因为
# 作为垃圾被收集时，连接会自动关闭。然而，为安全起见，还是调用close吧，因为这样做不需
# 要长时间敲击键盘。
# 说到方法cursor，就必须说说另一个主题：游标对象。你使用游标来执行SQL查询和查看结
# 果。游标支持的方法比连接多，在程序中的地位也可能重要得多。表13-5概述了游标的方法，而
# 表13-6概述了游标的属性。

# 表13-5 游标对象的方法
# 名 称                                       描 述
# callproc(name[, params])              使用指定的参数调用指定的数据库过程（可选）
# close()                               关闭游标。关闭后游标不可用
# execute(oper[, params])               执行一个SQL操作——可能指定参数
# executemany(oper, pseq)               执行指定的SQL操作多次，每次都序列中的一组参数
# fetchone()                            以序列的方式取回查询结果中的下一行；如果没有更多的行，就返回None
# fetchmany([size])                     取回查询结果中的多行，其中参数size的值默认为arraysize
# fetchall()                            以序列的序列的方式取回余下的所有行
# nextset()                             跳到下一个结果集，这个方法是可选的
# setinputsizes(sizes)                  用于为参数预定义内存区域
# setoutputsize(size[, col])            为取回大量数据而设置缓冲区长度

# 表13-6 游标对象的属性
# 名 称                                       描 述
# description                           由结果列描述组成的序列（只读）
# rowcount                              结果包含的行数（只读）
# arraysize                             fetchmany返回的行数，默认为1
# 有些方法将在本章后面详细讨论，还有一些（如setinputsizes和setoutputsizes）则不会讨
# 论。有关这些方法的详细信息，请参阅前面提到的PEP。

# 13.1.4 类型
# 对于插入到某些类型的列中的值，底层SQL数据库可能要求它们满足一定的条件。为了能够
# 与底层SQL数据库正确地互操作，DB API定义了一些构造函数和常量（单例），用于提供特殊的
# 类型和值。例如，要在数据库中添加日期，应使用相应数据库连接模块中的构造函数Date来创建
# 它，这让连接模块能够在幕后执行必要的转换。每个模块都必须实现表13-7所示的构造函数和特
# 殊值。有些模块可能没有完全遵守这一点。例如，接下来将讨论的模块sqlite3就没有导出表13-7
# 中特殊值（从STRING到ROWID）。

# 表13-7 DB API构造函数和特殊值
# 名 称                                       描 述
# Date(year, month, day)                创建包含日期值的对象
# Time(hour, minute, second)            创建包含时间值的对象
# Timestamp(y, mon, d, h, min, s)       创建包含时间戳的对象
# DateFromTicks(ticks)                  根据从新纪元开始过去的秒数创建包含日期值的对象
# TimeFromTicks(ticks)                  根据从新纪元开始过去的秒数创建包含时间值的对象
# imestampFromTicks(ticks)              根据从新纪元开始过去的秒数创建包含时间戳的对象
# Binary(string)                        创建包含二进制字符串值的对象
# STRING                                描述基于字符串的列（如CHAR）
# BINARY                                描述二进制列（如LONG或RAW）
# NUMBER                                描述数字列
# DATETIME                              描述日期/时间列
# ROWID                                 描述行ID列

# 13.2 SQLite 和 PySQLite
# 前面说过，可用的SQL数据库引擎有很多，它们都有相应的Python模块。这些数据库引擎大
# 都作为服务器程序运行，连安装都需要有管理员权限。为降低Python DB API的使用门槛，我选
# 择了一个名为SQLite的小型数据库引擎。它不需要作为独立的服务器运行，且可直接使用本地文
# 件，而不需要集中式数据库存储机制。
# 在较新的Python版本（从2.5开始）中，SQLite更具优势，因为标准库包含一个SQLite包装
# 器：使用模块sqlite3实现的PySQLite。除非从源代码编译Python，否则Python很可能包含这个
# 数据库。你可能应尝试运行13.2.1节中的程序片段，如果它能够运行，就无需专门安装PySQLite
# 和SQLite了。

# 注意 如果你使用的不是标准库中的PySQLite版本，可能需要修改前述程序片段中的import语
# 句。有关这方面的详细信息，请参阅相关的文档。

# 13.2.1 起步
# 要使用Python标准库中的SQLite，可通过导入模块sqlite3来导入它。然后，就可创建直接到
# 数据库文件的连接。为此，只需提供一个文件名（可以是文件的相对路径或绝对路径）；如果指
# 定的文件不存在，将自动创建它。
# >>> import sqlite3
# >>> conn = sqlite3.connect('somedatabase.db')
# 接下来可从连接获得游标。
# >>> curs = conn.cursor()
# 这个游标可用来执行SQL查询。执行完查询后，如果修改了数据，务必提交所做的修改，这
# 样才会将其保存到文件中。
# >>> conn.commit()
# 你可以（也应该）在每次修改数据库后都进行提交，而不是仅在要关闭连接前才这样做。要
# 关闭连接，只需调用方法close。
# >>> conn.close()

# 13.2.2 数据库应用程序示例
# 作为示例，我将演示如何创建一个小型的营养成分数据库，这个数据库基于美国农业部
# （USDA）农业研究服务（https://www.ars.usda.gov）提供的数据。美国农业部的链接常常会有细
# 微的变化，但只要按下面介绍的做，就应该能够找到相关的数据集。在网页https://www.ars.usda.gov
# 中，单击下拉列表Research中的链接Databases and Datasets进入相应的页面，再单击其中的链接
# Nutrient Data Laboratory。在打开的页面中，应该能够找到链接USDA National Nutrient Database for
# Standard Reference。在单击这个链接打开的页面中有大量的数据文件，它们使用的是我们需要的
# 纯文本（ASCII）格式。单击链接Download，并下载标题Abbreviated下链接ASCII指向的zip文件。
# 你将获得一个zip文件，其中包含一个名为ABBREV.txt的文本文件，还有一个描述该文件内容的
# PDF文件。如果你找不到这个文件，也可使用其他的旧数据，只是需要相应地修改源代码。
# 在文件ABBREV.txt中，每行都是一条数据记录，字段之间用脱字符（^）分隔。数字字段直
# 接包含数字，而文本字段用两个波浪字符（~）将其字符串值括起。下面是一个示例行（为简洁
# 起见删除了部分内容）：
# ~07276~^~HORMEL SPAM ... PORK W/ HAM MINCED CND~^ ... ^~1 serving~^^~~^0
# 要将这样的行分解成字段，只需使用line.split('^')即可。如果一个字段以波浪字符打头，
# 你就知道它是一个字符串，因此可使用field.strip('~')来获取其内容。对于其他字段（即数字
# 字段），使用float(field)就能获取其内容，但字段为空时不能这样做。本节接下来将开发一个
# 程序，将这个ASCII文件中的数据转换为SQL数据库，并让你能够执行一些有趣的查询。

# 注意 这个示例程序很简单，我是有意为之的。有关在Python中使用数据库的复杂示例，请参阅第26章。

# 1. 创建并填充数据库表
# 要创建并填充数据库表，最简单的解决方案是单独编写一个一次性程序。这样只需运行这个
# 程序一次，就可将它及原始数据源（文件ABBREV.txt）抛在脑后了，不过保留它们可能是个不
# 错的主意。
# 代码清单13-1所示的程序创建一个名为food的表（其中包含一些合适的字段）；读取文件
# ABBREV.txt并对其进行分析（使用工具函数convert对各行进行分割并对各个字段进行转换）；通
# 过调用curs.execute来执行一条SQL INSERT语句，从而将字段中的值插入数据库中。
# 注意：也可使用curs.executemany，并向它提供一个列表（其中包含从数据文件中提取的所
# 有行）。就这里而言，这样做速度稍有提高，但如果使用的是通过网络连接的客户/服务器SQL系
# 统，速度将有极大的提高。

# 代码清单13-1 将数据导入数据库（importdata.py）
# import sqlite3
# def convert(value):
#     if value.startswith('~'):
#         return value.strip('~')
#     if not value:
#         value = '0'
#     return float(value)
# conn = sqlite3.connect('food.db')
# curs = conn.cursor()
# 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
# )
# ''')
# query = 'INSERT INTO food VALUES (?,?,?,?,?,?,?,?,?,?)'
# field_count = 10
# for line in open('ABBREV.txt'):
#     fields = line.split('^')
#     vals = [convert(f) for f in fields[:field_count]]
#     curs.execute(query, vals)
# conn.commit()
# conn.close()

# 注意 在代码清单13-1中，使用的参数风格为qmark，即使用问号来标记字段。如果你使用的是
# 较旧的PySQLite版本，可能需要使用字符%来标记字段。

# 当你运行这个程序时（文件ABBREV.txt和它位于同一个目录），它将新建一个名为food.db的
# 文件，其中包含数据库中的所有数据。
# 建议你多多尝试这个程序：使用不同的输入、添加print语句等。

# 2. 搜索并处理结果
# 数据库使用起来非常简单：创建一条连接并从它获取一个游标；使用方法execute执行SQL
# 查询并使用诸如fetchall等方法提取结果。代码清单13-2是一个微型程序，它通过命令行参数接
# 受一个SQL SELECT条件，并以记录格式将返回的行打印出来。你可在命令行中像下面这样尝试运
# 行它：
# $ python food_query.py "kcal <= 100 AND fiber >= 10 ORDER BY sugar"
# 运行这个程序时，你可能发现了一个问题：第一行指出，生橘子皮（raw orange peel）好像
# 不含任何糖分。这是因为在数据文件中缺少这个字段。你可对导入脚本进行改进，以检测这种情
# 况，并插入None而不是0来指出缺失数据。这样，你就可使用类似于下面的条件：
# "kcal <= 100 AND fiber >= 10 AND sugar ORDER BY sugar"
# 这要求仅当sugar字段包含实际数据时才返回相应的行。这种策略恰好也适用于当前的数据
# 库——上述条件将丢弃糖分为0的行。
# 你可能想尝试使用ID搜索特定食品的条件，如使用ID 08323搜索Cocoa Pebbles。问题是SQLite
# 处理其值的方式不那么标准，事实上，它在内部将所有的值都表示为字符串，因此在数据库和
# Python API之间将执行一些转换和检查。通常，这没有问题，但使用ID搜索可能会遇到麻烦。如
# 果你提供值08323，它将被解读为数字8323，进而被转换为字符串"8323"，即一个不存在的ID。
# 在这种情况下，可能应该显示错误消息，而不是采取这种意外且毫无帮助的行为；但如果你很小
# 心，在数据库中就将ID设置为字符串"08323"，就不会出现这种问题。

# 代码清单13-2 食品数据库查询程序（food_query.py）
import sqlite3, sys
conn = sqlite3.connect('food.db')
curs = conn.cursor()
query = 'SELECT * FROM food WHERE ' + sys.argv[1]
print(query)
curs.execute(query)
names = [f[0] for f in curs.description]
for row in curs.fetchall():
 for pair in zip(names, row):
    print('{}: {}'.format(*pair))
 print()

# 13.3 小结
# 本章简要地介绍了如何创建与关系型数据库交互的Python程序。之所以只做简要的介绍，是
# 因为如果你掌握了Python和SQL，就很容易掌握它们之间的桥梁——Python DB API。下面是本章
# 介绍的一些概念。
#  Python DB API：这个API定义了一个简单的标准化接口，所有数据库包装器模块都必须
# 遵循它，这让编写使用多个不同数据库的程序更容易。
#  连接：连接对象表示到SQL数据库的通信链路，使用方法cursor可从连接获得游标。你还
# 可使用连接对象来提交或回滚事务。使用完数据库后，就可将连接关闭了。
#  游标：游标用于执行查询和查看结果。可逐行取回查询结果，也可一次取回很多（或全
# 部）行。
#  类型和特殊值：DB API指定了一组构造函数和特殊值的名称。构造函数用于处理日期和
# 时间对象，还有二进制数据对象；而特殊值用于表示关系型数据库的类型，如STRING、
# NUMBER和DATETIME。
#  SQLite：这是一个小型的嵌入式SQL数据库，标准Python发行版中包含其Python包装器，
# 即模块sqlite3。这个数据库速度快、易于使用，且不要求搭建专门的服务器。

# 13.3.1 本章介绍的新函数
# 函 数                       描 述
# connect(...)          连接到数据库并返回一个连接对象①