Python+MySQL数据库编程

使用简单的纯文本文件可实现的功能有限。诚然,使用它们可做很多事情,但有时可能还需要额外的功能。你可能希望能够自动完成序列化,此时可求助于shelve和pickle(类似于shelve)。不过你可能需要比这更强大的功能。例如,你可能想自动支持数据的并发访问,及允许多位用户读写磁盘数据,而不会导致文件受损之类的问题。还有可能希望同时根据多个数据字段或属性进行复杂的搜索,而不是采用shelve提供的简单的单键查找。尽管可供选择的解决方案有很多,但如果要处理大量的数据,并希望解决方案易于其他程序员理解,选择较标准的数据库可能是个不错的主意。

下面讨论Python数据库API(一种连接到SQL数据库的标准化方式),并演示如何使用这个API来执行一些基本的SQL。最后,将讨论其他一些数据库技术。

这里不会提供关系型数据库和SQL语言教程。通过阅读有关数据库(如PostgreSQL,SQLite,或马上使用的MySQL)的文档,应该能够学到你需要的知识。如果你以前没有使用过关系型数据库,可参阅www.sqlcourse.com或在网上搜索相关的主题,也可参阅Clare Churcher的著作Beginning SQL Queries, 2nd ed(Apress,2016)。

即将使用的是使用广泛而且可靠的开源数据库MySQL,但显然绝非只能使用它。有多种流行的商用数据库,如Orancle和Microsoft SQL Server,还有一些使用广泛而且可靠的开源数据库,如PostgreSQL和Firebird。有关Python支持的数据库清单,请参阅https://wiki.python.org/moin/Databaseinterfaces。数据库也并非只有关系型(SQL)这一种,还有对象数据库【如Zope Object Database(ZODB,http://www.zodb.org)】、基于表格的紧凑数据库【如Metakit(http://equi4.com/metakit)】、更简单的键-值数据库【如UNIX DBM(https://docs.python.org/3/library/dbm.html)】。另外,还有日益流行的各种NoSQL数据库,如MongoDB(http://mongodb.com)、Cassandra(http://cassandra.apache.org)和Redis(http://redis.io),这些数据库都可以使用Python来访问。

文章的重点是低级的数据库交互,但有一些高级库能够让你轻松地完成复杂的工作,如SQLAlchemy和SQLObject,要获悉这方面的信息,可参阅http://sqlalchemy.org和http://sqlobject.org,也可在网上搜索Python对象关系映射器。

Python数据库API

前面说过,有各种SQL数据库可供选择,其中很多都有相应的Python客户端模块(有些数据库甚至有多个)。所有数据库的大多数基本功能相同,因此从理论上来说,对于使用其中一种数据库的程序,很容易对其进行修改以使用另一种数据库。问题是即便不同模块提供的功能大致相同,它们的接口(API)也是不同的。为解决Python数据库模块存在的这种问题,人们一致同意开发一个标准数据库API(DB API)。这个API的最新版本(2.0)是在PEP249(Python Database API Specification v2.0)中定义的,网址为http://python.org/peps/pep-0249.html。如果你对这个API的细节不感兴趣,可以跳过本节。

全局变量

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

变量名

描述

apilevel

使用的Python DB API版本

threadsafety

模块的线程安全程度如何

paramstyle

在SQL查询中使用哪种参数风格

API级别(apilevel)是一个字符串常量,指出了使用的API版本。DB API2.0指出,这个变量的值为'1.0'或'2.0'。如果没有这个变量,就说明模块不与DB API2.0兼容,应假定是用的是DB API1.0。编写代码时,允许这个变量为其他值也没有害处,因为说不定什么时候DB API3.0就出来了。

线程安全程度(threadsafety)是一个0~3(含)的整数。0表示线程不能共享模块,而3表示模块是绝对线程安全的。1表示线程可共享模块本身,但不能共享连接,而2表示线程可共享模块和连接,但不能共享游标。如果你不使用线程(在大多数情况下可能不会是这样的),就根本不用关心这个变量。

参数风格(paramstyle)表示当你执行多个类似的数据库查询时,如何在SQL查询中加入参数。'format'表示字符串格式设置方式(使用基本的格式编码),如在插入参数的地方插入%s。'pyformat'表示扩展的格式编码,即旧式字典插入使用的格式编码,如%(foo)s。除这些Python风格外,还有三种指定待插入字段的方式:'qmark'表示使用问号,'numeric'表示使用:1和:2这样的形式表示字段(其中的数字是参数的编号),而'named'表示使用:foobar这样的形式表示字段(其中foobar为参数名)。如果你觉得参数样式令人迷惑,也不用担心。编写简单程序时,不会用到它们。如果需要明白特定的数据库是如何处理参数的,可参阅相关的文档。

异常

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

异常

超类

描述

StandardError

所有异常的超类

Warning

StandardError

发生非致命问题时引发

Error

StandardError

所有错误条件的超类

InterfaceError

Error

与接口(而不是数据库)相关的错误

DatabaseError

Error

与数据库相关的错误的超类

DataError

DatabaseError

与数据相关的问题,如值不在合法的范围内

OperationalError

DatabaseError

数据库操作内部的错误

IntegrityError

DatabaseError

关系完整性遭到破坏,如键未通过检查

InternalError

DatabaseError

数据库内部的错误,如游标无效

ProgrammingError

DatabaseError

用户编程错误,如未找到数据库表

NotSupportedError

DatabaseError

请求不支持的功能,如回滚

连接和游标

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

参数名

描述

是否可选

dsn

数据源名称,具体含义随数据库而异

user

用户名

password

用户密码

host

主机名

database

数据库名称

后面提供了函数connect的具体使用实例。

函数connect返回一个连接对象,表示当前到数据库的会话。连接对象支持下表所示的方法。

方法名

描述

close()

关闭连接对象。之后,连接对象及其游标将不可用

commit()

提交未提交的事务——如果支持的话;否则什么都不做

rollback()

回滚未提交的事务(可能不可用)

cursor()

返回连接的游标对象

方法rollback可能不可用,因为并非所有数据库都支持事务(事务其实就是一系列操作)。可用时,这个方法撤销所有未提交的事务。

方法commit总是可用的,但如果数据库不支持事务,这个方法就什么都不做。关闭连接时,如果还有未提交的事务,将隐式的回滚它们——但仅当数据库支持回滚时才如此!如果你不想依赖于这一点,应在关闭连接时提交。只要提交了所有的事务,就无需操心关闭连接的事情,因为作为垃圾被收集时,连接会自动关闭。然而,为了安全起见,还是调用close吧,因为这样做不需要长时间敲击键盘。

说到方法cursor,就必须说说另一个主题:游标对象。你使用游标来执行SQL查询和查看结果。游标支持的方法比连接多,在程序中的地位也可能重要得多。下面两张表分别概述了游标的方法和属性。

名称

描述

callproc(name[, params])

使用指定的参数调用指定的数据库过程(可选)

close()

关闭游标,关闭后游标不可用

execute(oper[, params])

执行一个SQL操作——可能指定参数

executemany(oper, pseq)

执行指定的SQL操作多次,每次都对应序列中的一个参数

fetchone()

以序列的方式取回查询结果中的下一行;如果没有更多的行,就返回None

fetchmany(size)

取回查询结果中的多行,其中size的值默认为arraysize

fetchall()

以序列的方式取回余下的所有行

nextset()

跳到下一个结果集,这个方法是可选的

setinputsizes(sizes)

用于为参数预定义的内存区域

setoutputsizes(size[, col])‍‍

为取回大量数据而设置缓冲区的长度

名称

描述

description

由结果列描述组成的序列(只读)

rowcount

结果包含的行数(只读)

arraysize

fetchmany返回的行数,默认为1

有些方法将在后面详细讨论,还有一些(如setinputsizes和sizeoutputsizes)则不会讨论。有关这方面的的详细信息,请参阅前面提到的PEP。

类型

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

名称

描述

Date(year, month, day)

创建包含日期值的对象

Time(hour, minute, second)

创建包含时间值的对象

Timestamp(y, mon, d, h, min, s)

创建包含时间戳的对象

DateFromTicks(ticks)

根据从新纪元过去的秒数创建包含日期值的对象

TimeFromTicks(ticks)

根据从新纪元过去的秒数创建包含时间值的对象

TimestampFromTicks(ticks)

根据从新纪元过去的秒数创建包含时间戳的对象

Binary(string)

创建包含二进制字符串值的对象

STRING

描述基于字符串的列(如CHAR)

BINARY

描述二进制列(如LONG或RAW)

NUMBER

描述数字列

DATETIME

描述日期/时间列

ROWID

描述行ID列

MySQL和pymysql

前面说过,可用的数据库引擎有很多,它们都有相应的Python模块。这些数据库引擎大都作为服务器运行,连安装都需要有管理员权限。为降低Python DB API和pymysql的使用门槛,我选择将MySQL和Python安装在一台机器上。

MySQL安装比较简单,网上一搜一堆教程,这里就不讲了。pymysql安装也是非常简单,执行命令pip install pymysql即可。如果使用的是Python2,就需要把pymysql换成mysqldb!

起步

要使用Python库中的pymysql,如果安装成功,可通过导入模块pymysql。然后,就可以创建到数据库的连接。为此,只需提供数据库地址,用户名,密码,要使用的数据库(一定要确保数据库存在)即可。

>>> import pymysql

>>> conn = pymysql.connect(host="localhost", user="root", password="123456789", db="test")

接下来可从连接获得游标。

>>> curs = conn.cursor()

这个游标可用来执行SQL查询。执行完查询后,如果修改了数据,务必提交所做的修改,这样才会将其保存到磁盘中。

>>> conn.commit()

你可以(也应该)在每次修改数据库后都进行提交,而不是仅在要关闭连接时才这样做。要关闭连接,只需调用方法close。

>>> conn.close()

数据库应用程序示例

作为示例,我将演示如何创建一个小型的营养成分数据库,这个数据库基于美国农业部(USDA)农业研究服务(https://www.ars.usda.gov)提供的数据。美国农业部的链接常常会有细微的变化。但只要按照下面介绍的做,就应该能够找到相关的数据集。在网页https://www.ars.usda.gov中,单击下拉列表Research中的链接Databases and Datasets进入相应的页面,再单击其中的链接Human Nutrition。在打开的页面中,应该能够找到链接Composition of Foods Raw, Processed, Prepared USDA National Nutrient Database for Standard Reference, Release 28。在单击这个链接打开的页面中有大量的数据文件,它们使用的是我们需要的纯文本(ASCII)格式。找到并单击链接ASCII (Abbreviated; 1.1Mb; ISO/IEC 8859-1)zip,在单击这个链接打开的页面中单击链接Download。你将获得一个zip文件,其中包含一个ABBREV.txt的文本文件,还有一个描述该文件内容的PDF文件。如果你找不到这个文件,也可使用其他的旧数据,只是需要相应的修改源代码。

在文件ABBREV.txt中,每一行都是一条数据记录,字段之间用脱字符(^)分隔。数字字段直接包含数字,而文本字段用两个波浪字符(~)将其字符串值括起。下面是一个示例行:

~01001~^~BUTTER,WITH SALT~^15.87^717^0.85^81.11^2.11^0.06^0.0^0.06^24^0.02^2^24^24^643^0.09^0.000^0.000^1.0^0.0^0.005^0.034^0.042^0.110^0.003^3^0^3^3^18.8^0.17^2499^684^671^0^158^0^0^0^2.32^0.0^0^7.0^51.368^21.021^3.043^215^5.0^~1 pat, (1" sq, 1/3" high)~^14.2^~1 tbsp~^0

要将这样的行分解成字段,只需使用line.split('^')即可。如果一个字段以波浪字符打头,你就知道他是一个字符串,因此可使用field.strip('~')来获取其内容。对于其他字段(即数字字段),使用float(field)就能获取其内容,但字段为空时不能这样做。本节接下来将开发一个程序,将这个ASCII文件中的数据转换为SQL数据库,并让你能够执行一些有趣的查询。

创建并填充数据表

要创建并填充数据表,最简单的解决方案是单独编写一个一次性程序。这样秩序运行这个程序一次,就可将它及原始数据源(文件ABBREV.txt)抛在脑后了,不过保留它们可能是个不错的主意。

下图所示的程序在food数据库中创建一个名为food的表(其中包含一些合适的字段);读取文件ABBREV.txt并对其进行分析(使用工具函数convert对各行进行分割并对各个字段进行转换);通过调用curs.execute来执行一条SQL INSERT语句,从而将字段中的值插入数据库中。

注意:也可使用curs.executemany,并向它提供一个列表(其中包含从数据文件中提取的所有行)。就这里而言,这样做速度稍有提高,但如果使用的是通过网络连接的客户/服务器SQL系统,速度将有极大的提高。

当你运行这个程序时(文件ABBREV.txt和它位于同一个目录),它将在food数据库中新建一个food的表,表中包含几乎所有数据。

建议你多多尝试这个程序:使用不同的输入,添加print语句等。

搜索并处理结果

数据库使用起来非常简单:创建一条连接并从它获取一个游标;使用方法execute执行SQL查询并使用诸如fetchall等方法提取结果。下面的代码是一个微型程序,它通过命令行参数接受一个SQL SELECT条件,并以记录格式将返回的行打印出来。你可在命令行中像下面这样运行它:

$ python food_query.py "kcal <= 100 AND fiber >= 10 ORDER BY sugar"

运行这个程序时,你可能发现了一个问题:第一行指出,生橘子皮(raw orange peel)好像不含任何糖分。这是因为在数据文件中缺少这个字段。你可对导入脚本进行改进,以检测这种情况,并插入NULL而不是0来指出缺失数据。这样,你就可使用类似下面的条件:

"kcal <= 100 AND fiber >= 10 AND sugar ORDER BY sugar"

这要求仅当sugar包含实际数据时才返回相应的行。这种策略恰好也适用于当前的数据库——上述条件将丢弃糖分为0的行。


警告 这个程序从用户那里获取输入,并将其插入到SQL查询中。在你是用户且不会输入太不可思议的内容时,这没有问题。然而,利用这种输入偷偷地插入恶意的SQL代码以破坏数据库是一种常见的计算机攻击方式,称为SQL注入攻击。请不要让你的数据库(以及其他任何东西)暴露在原始用户输入的“火力范围”内,除非你对这样做的后果心知肚明。

原文发布于微信公众号 - 小陈学Python(gh_a29b1ed16571)

原文发表时间:2018-11-11

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券