Python程序设计-第8章-数据库的访问与操作

1、Python程序设计8数据库的访问与操作第章在Python语言中，存储数据访问数据库采用标准数据库接口：Python DB-API，Python DB-API为编程人员提供数据库应用编程接口。Python 数据库接口支持非常多的数据库，编程者可以选择适合自己项目的数据库Python的DB-API，为大多数的数据库实现了接口，使用它连接各数据库后，就可以用相同的方式操作各数据库。本章将以开源数据库-MySQL数据库为例进行详细讲解数据库的访问和操作。前言本章内容8.1数据库8.2 SQL语言8.3 MySQL数据库导入8.4数据库连接8.5数据库的操作8.6执行事务8.7处理异常8.8本章总结8

2、.1数据库数据库通常采用文件系统作为基本的持久化存储，数据库存储可以采用普通的操作系统文件、专用的操作系统文件，甚至是原始的磁盘分区。数据库存储一般抽象为一张表，每行数据都有一些字段对应数据库的列，每一列的表定义的集合以及每个表的数据类型放到一起定义了数据库的模式。数据库可以创建和删除，表也一样。当查询一个数据库的时候，可以一次性取回一次性结果，也可以逐条遍历每个结果行，一些数据库使用游标的概念来提交SQL命令，查询以及获取结果，不管是一次性获取还是逐行获取都可以使用这个概念。8.1.1数据库访问在Python语言中，数据库的访问是通过适配器的方式进行访问，适配器是一个Python模块，使用它

3、可以与关系数据库的客户端库（通常是使用C语言编写的）接口相连。一般情况下会推荐所有的Python适配器应当符合Python数据库特殊兴趣小组的API标准。Python数据库应用结构如图8-1所示，Python数据库适配器（DB-API）是连接到数据库客户端的C语言库接口。8.1.1数据库访问8.1.2DB-API接口属性描述apilevel需要适配器兼容的DB-API版本threadsafety本模块线程安全级别paramstyle本模块sql语句参数风格connect()Connect()函数8.1.2DB-API接口下面将依次介绍这四个属性：（1）apilevel：该字符串指明了模块要兼容

4、的DB-API最高版本，比如，1.0,2.0等，默认值是1.0（2）threadsafety：这是一个整型值，可选值如下：0：不支持线程安全，线程间不能共享模块。1：最小化线程安全支持，线程间可以共享模块，但是不能共享连接。2：适度的线程安全支持，线程间可以共享模块和连接，但是不能共享游标。3：完整的线程安全支持，线程间可以共享模块，连接和游标。（3） paramstyle：该参数是一个字符串，用于指定构建查询行或命令时使用的字符串替代形式（4）函数属性：connect()函数通过Connection对象访问数据本章内容8.1数据库8.2 SQL语言8.3 MySQL数据库导入8.4数据库连接

5、8.5数据库的操作8.6执行事务8.7处理异常8.8本章总结8.2.1SQL简介SQL是结构化查询语言，全称是 Structured Query Language ，是用于访问和处理数据库的标准的计算机语言。尽管SQL 是一门 ANSI（American National Standards Institute 美国国家标准化组织）标准的计算机语言，但是仍然存在着多种不同版本的 SQL 语言。然而，为了与 ANSI 标准相兼容，它们必须以相似的方式共同支持一些主要的命令（比如 SELECT、UPDATE、DELETE、INSERT、WHERE 等等）8.2.1SQL简介采用SQL语言，能够实现

6、数据库的以下操作：（1）SQL 面向数据库执行查询（2）SQL 可从数据库取回数据（3）SQL 可在数据库中插入新的记录（4）SQL 可更新数据库中的数据（5）SQL 可从数据库删除记录（6）SQL 可创建新数据库（7）SQL 可在数据库中创建新表（8）SQL 可在数据库中创建存储过程（9）SQL 可在数据库中创建视图（10）SQL 可以设置表、存储过程和视图的权限8.2.2SQL语法SQL命令描 述SELECT从数据库中提取数据UPDATE更新数据库中的数据DELETE从数据库中删除数据INSERT INTO向数据库中插入新数据CREATE DATABASE创建新数据库ALTER DATAB

7、ASE修改数据库DROP TABLE删除表CREATE INDEX创建索引（搜索键）DROP INDEX删除索引8.2.3SQL视图视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，也可以呈现数据，就像这些数据来自于某个单一的表一样。（1）创建视图SQL CREATE VIEW 语法如下所示：CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condit

8、ion8.2.3SQL视图（2）更新视图SQL CREATE OR REPLACE VIEW 语法如下所示：CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition（3）撤销视图通过 DROP VIEW 命令来删除视图，具体语法如下：DROP VIEW view_name;8.2.4SQL函数SQL命令描 述AVG()返回平均值COUNT()返回行数FIRST()返回第一个记录的值LAST()返回最后一个记录的值MAX()返回最大值MIN()返回最小值SUM()返回总和SQ

9、L Aggregate 函数8.2.4SQL函数SQL命令描 述UCASE()将某个字段转换为大写LCASE() 将某个字段转换为小写MID() 从某个文本字段提取字符，MySql 中使用SubString(字段，1，end) 从某个文本字段提取字符LEN() 返回某个文本字段的长度ROUND() 对某个数值字段进行指定小数位数的四舍五入NOW() 返回当前的系统日期和时间SQL Scalar 函数本章内容8.1数据库8.2 SQL语言8.3 MySQL数据库导入8.4数据库连接8.5数据库的操作8.6执行事务8.7处理异常8.8本章总结8.3MySQL数据库导入MySQLdb 是用于Pyth

10、on连接MySQL数据库的接口，它实现了 Python 数据库 API 规范 V2.0。为了能够访问MySQL数据，需要下载MySQL数据库模块并安装此模块。安装MySQLdb，请访问/projects/MySQL-python，(Linux平台可以访问：/pypi/MySQL-python)从这里可选择适合平台的安装包，安装包分为预编译的二进制文件和源代码安装包。8.3MySQL数据库导入如果选择二进制文件发行版本的话，安装过程基本安装提示即可完成。如果从源代码进行安装的话，则需要切换到MySQLdb发行版本的顶级目录，并键入下列命令：$ gunzip MySQL-python-1.2.2.

11、tar.gz$ tar -xvf MySQL-python-1.2.2.tar$ cd MySQL-python-1.2.2$ python setup.py build$ python setup.py install8.3MySQL数据库导入使用Python DB API访问数据库的流程图如下图所示本章内容8.1数据库8.2 SQL语言8.3 MySQL数据库导入8.4数据库连接8.5数据库的操作8.6执行事务8.7处理异常8.8本章总结8.4数据库连接在Python语言中，在连接数据库前，编程人员首先需要确认以下事项：1）在程序中是否已经创建了数据库 TESTDB。2）在TESTDB数据

12、库中是否已经创建了表 EMPLOYEE。3）EMPLOYEE表字段为 FIRST_NAME, LAST_NAME, AGE, SEX 和 INCOME。4）是否设置了连接数据库TESTDB使用的用户名和密码，当然也可以自己设定或者直接使用root用户名及其密码，MySQL数据库用户授权请使用Grant命令。5）是否在机子上已经安装了 Python MySQLdb 模块。只有在确认完成了以上内容后，才可以进行数据库连接操作8.4.1创建数据库编程人员在编程时建立了数据库连接后，首先要创建数据库，创建数据库需要调用数据库API：MySQLdb通过import MySQLdb调用数据库API：MyS

13、QLdb，采用connect（）方法创建数据库的connect对象：db，然后使用db对象的cursor()方法获取操作游标cursor，使用execute方法执行SQL语句创建数据库，调用db对象对象的select_db（）方法选择数据库COMDB，最后关闭游标和数据库连接。8.4.2创建表用户在创建表之前，必须确认所在的数据库连接是否存在。如果数据库连接存在，则可以使用execute()方法来为数据库创建表调用connect()方法创建connect对象，打开数据库连接，使用cursor()方法获取操作游标cursor对象，如果数据表已经存在使用 execute() 方法删除表，执行创建数

14、据表，关闭游标cursor和数据库连接db对象。本章内容8.1数据库8.2 SQL语言8.3 MySQL数据库导入8.4数据库连接8.5数据库的操作8.6执行事务8.7处理异常8.8本章总结8.5数据库的操作数据库操作是数据库访问中最重要的操作，主要操作包括插入、修改、删除等操作，数据库操作首先创建数据库连接对象，获取游标cursor对象，调用游标cursor对象相关方法执行数据库操作，操作正常提交事务处理，操作异常回滚事务，然后关闭游标cursor对象，最后关闭数据库连接connect对象，结束操作，整个数据库操作的流程图如图8-15所示8.5.1插入数据INSERT INTO 语句用于向表

15、中插入新记录。INSERT INTO 语句可以有两种编写形式。第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：INSERT INTOtable_name VALUES (value1,value2,value3,.);第二种形式需要指定列名及被插入的值：INSERT INTOtable_name(column1,column2,column3,.) VALUES (value1,value2,value3,.);8.5.2更新数据更新操作用于更新数据表的的数据，UPDATE 语句用于更新表中已经存在的记录，UPDATE语法如下：UPDATEtable_name SETcolumn1

16、=value1,column2=value2,. WHEREsome_column=some_value;8.5.3删除数据删除操作用于删除数据表中的数据，DELETE 语句用于删除表中的记录。SQL DELETE 语法如下：DELETE FROMtable_name WHEREsome_column=some_value;8.5.4查询数据查询的结果将被存储在一个结果表中，称为结果集。SELECTcolumn_name,column_name FROMtable_name;或SELECT * FROMtable_name;Python查询MySQL使用 fetchone() 方法获取单条数据

17、，使用fetchall() 方法获取多条数据。1）fetchone()：该方法获取下一个查询结果集，结果集是一个对象。2）fetchall()：接收全部的返回结果行。3）Rowcount：这是一个只读属性，并返回执行execute()方法后影响的行数。本章内容8.1数据库8.2 SQL语言8.3 MySQL数据库导入8.4数据库连接8.5数据库的操作8.6执行事务8.7处理异常8.8本章总结8.6执行事务一个数据库事务通常包含对数据库进行读或写的一个操作序列。它的存在包含有以下两个目的：1）为数据库操作提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法

18、。2）当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。8.6执行事务但在现实情况下，失败的风险很高。在一个数据库事务的执行过程中，有可能会遇上事务操作失败、数据库系统/操作系统失败，甚至是存储介质失败等情况。这便需要DBMS对一个执行失败的事务执行恢复操作，将其数据库状态恢复到一致状态（数据的一致性得到保证的状态）。为了实现将数据库状态恢复到一致状态的功能，DBMS通常需要维护事务日志以追踪事务中所有影响数据库数据的操作。8.6执行事务事务机制可以确保数据一致性。事务必须具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID

19、特性。原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。本章内容8.1数据库8.2 SQL语

20、言8.3 MySQL数据库导入8.4数据库连接8.5数据库的操作8.6执行事务8.7处理异常8.8本章总结8.7处理异常异常描述Warning当有严重警告时触发，例如插入数据是被截断等等。必须是 StandardError 的子类Error警告以外所有其他错误类。必须是 StandardError 的子类InterfaceError当有数据库接口模块本身的错误（而不是数据库的错误）发生时触发。 必须是Error的子类DatabaseError和数据库有关的错误发生时触发。 必须是Error的子类DataError当有数据处理时的错误发生时触发，例如：除零错误，数据超范围等等。 必须是DatabaseError的子类OperationalError指