Python语言实用教程第11章-数据库应用课件.ppt

1、一般高级语言都支持数据库操作，一般高级语言都支持数据库操作，PythonPython对多种数据库操作提供了很好的支持。对多种数据库操作提供了很好的支持。PythonPython语语言标准数据库接口为言标准数据库接口为Python DB-APIPython DB-API，Python Python DB-APIDB-API为开发人员提供了一致的数据库应用编程为开发人员提供了一致的数据库应用编程接口。本章主要内容包括：数据库基本知识、接口。本章主要内容包括：数据库基本知识、PythonPython数据库编程技术、多种数据库操作模块的数据库编程技术、多种数据库操作模块的介绍与基本使用等内容。介绍与基

2、本使用等内容。学习重点或难点：学习重点或难点：关系数据库与关系数据库与SQL语言语言 Python数据库编程简介数据库编程简介 通过通过DB-API访问数据库访问数据库 Python数据库编程实例数据库编程实例学习本章后，将能利用学习本章后，将能利用Python数据库访问技术数据库访问技术来编写数据库应用程序，实现对信息的高级管理功能。来编写数据库应用程序，实现对信息的高级管理功能。11.1 关系数据库概述关系数据库概述11.2 Python Python数据库编程概述数据库编程概述11.3 Python Python与与ODBCODBC11.4 Python Python与与SQLite3S

3、QLite311.5 Python Python与与MySQLMySQL11.6 Python Python与与SQL ServerSQL Server 从知识的完整性角度考虑，本节简单介绍关从知识的完整性角度考虑，本节简单介绍关系数据库的基本知识。主要是关系数据库的定义系数据库的基本知识。主要是关系数据库的定义及关系数据库的国际标准及关系数据库的国际标准SQLSQL语言。语言。数据库数据库(Database)是指长期存储在计算机内是指长期存储在计算机内的、有组织的、可共享的数据集合。数据以记录的、有组织的、可共享的数据集合。数据以记录(Record)和字段和字段(Field)的形式存储在数据

4、表的形式存储在数据表(Table)中，由若干个相关联的数据表构成一个数据库。中，由若干个相关联的数据表构成一个数据库。数据库国际标准操作语言数据库国际标准操作语言SQL语言的操作对象语言的操作对象主要是数据表或视图。主要是数据表或视图。SQL语言可分为：数据定语言可分为：数据定义语言义语言DDL、数据操纵语言、数据操纵语言DML、数据查询语言、数据查询语言DQL和数据控制语言和数据控制语言DCL四大类。四大类。1.创建数据表创建数据表CREATE TABLE 表名表名(字段名字段名l 数据类型数据类型 列级约列级约束条件束条件,字段名字段名2 数据类型数据类型 列级约束条件列级约束条件,字段字

5、段名名n 数据类型数据类型 列级约束条件列级约束条件,表级完整性约束表级完整性约束)创建学生信息表创建学生信息表studentcreate table student(sno char(10)primary key,sname char(20),sage integer,sdept char(10)2.修改数据表修改数据表 ALTER TABLE 表名表名 ADD 字段名字段名 数据类型数据类型 约束条件约束条件 在学生表在学生表student中添加一个性别字段中添加一个性别字段ssex其其SQL语句为：语句为：ALTER TABLE student add ssex char(2)not n

6、ull 删除字段使用的格式为：删除字段使用的格式为：ALTER TABLE 表名表名 DROP 字段名字段名 在学生信息表在学生信息表student中删除一个字段性别中删除一个字段性别ssex其其SQL语句语句为：为：ALTER TABLE student DROP ssex3.3.删除数据表删除数据表 在在SQLSQL语言中使用语言中使用DROP TABLEDROP TABLE语句删除某个表语句删除某个表格及表格中的所有记录，其命令格式为：格及表格中的所有记录，其命令格式为：DROP TABLE DROP TABLE 表名表名 在在testtest数据库中删除学生表数据库中删除学生表stud

7、entstudent其其SQLSQL语句语句为：为：DROP DROP TABLE studentTABLE student4.向数据表中插入数据向数据表中插入数据 INSERT语句实现向数据库表格中插入或增加新的数语句实现向数据库表格中插入或增加新的数据行，其据行，其格式如下：格式如下：INSERT INTO 表名表名(字段名字段名l,字段名字段名n)VALUES(值值l,值值n)例如：在学生表例如：在学生表student中插入一条记录，其中插入一条记录，其SQL语句语句为：为：insert into student(sno,sname,sage,sdept)values(20160305,

8、董华董华,19,cs)5.数据更新语句数据更新语句 UPDATE语句实现更新或修改满足规定条件的现有记语句实现更新或修改满足规定条件的现有记录，使用格式如下：录，使用格式如下：UPDATE 表名表名 SET 字段名字段名l=新值新值l,字段名字段名2=新值新值2 WHERE 条件表达式条件表达式 例如：学生表例如：学生表student中的中的sage加加1岁，其岁，其SQL语句为：语句为：Update student set sage=sage+16.删除记录语句删除记录语句 DELETE语句删除数据库表格中的行或记录，其语句删除数据库表格中的行或记录，其命令格式如下：命令格式如下：DELET

9、E FROM 表名表名 WHERE 条件表达式条件表达式 例如：删除学生表例如：删除学生表student中的中的sage字段的值超字段的值超过过24的记录，其语句为：的记录，其语句为：DELETE FROM student where sage247.数据查询语言数据查询语言 最基本的最基本的SELECT查询语句格式：查询语句格式：SELECT DISTINCT 字段名字段名l,字段名字段名2,FROM 表表名名 WHERE 条件表达式条件表达式 例如查询出学生表例如查询出学生表student中的所有姓王的学生中的所有姓王的学生信息，其语句为：信息，其语句为：Select*from stude

10、nt where sname like 王王%关系型数据库拥有共同的规范关系型数据库拥有共同的规范Python Database API Specification V2.0(Python DB-API)，MySQL、Oracle等都实现了此规范，然后增加自己的扩展。等都实现了此规范，然后增加自己的扩展。为了增强自己数据库操作的性能与效率等，不同数为了增强自己数据库操作的性能与效率等，不同数据库还会提供操作自己数据库的专门接口模块。据库还会提供操作自己数据库的专门接口模块。sqlite3：sqlite3模块提供了模块提供了SQLite数据库访问的数据库访问的接口。接口。SQLite数据库是以一

11、个文件或内存的形式存数据库是以一个文件或内存的形式存在的自包含的关系型数据库。在的自包含的关系型数据库。DBM-style数据库模块：数据库模块：python提供了多个提供了多个modules来支持来支持UNIX DBM-style数据库文件。数据库文件。dbm模块用来读取标准的模块用来读取标准的UNIX-dbm数据库文件数据库文件;gdbm用来读取用来读取GNU dbm数据库文件数据库文件;dbhash用来读取用来读取Berkeley DB数据库文件。所有的这些模块提供了数据库文件。所有的这些模块提供了一个对象实现了基于字符串的持久化的字典，它一个对象实现了基于字符串的持久化的字典，它与字典

12、与字典dict非常相似，但是它的非常相似，但是它的keys和和values都必都必须是字符串。须是字符串。这里主要介绍这里主要介绍PythonPython标准数据库接口标准数据库接口(Python(Python DB-API)DB-API)。Python DB-APIPython DB-API为开发人员提供了数据为开发人员提供了数据库应用编程接口。库应用编程接口。DB-APIDB-API是一个规范是一个规范,它定义了一它定义了一系列必须的对象和数据库存取方式系列必须的对象和数据库存取方式,以便为各种各以便为各种各样的底层数据库系统和多种多样的数据库接口程样的底层数据库系统和多种多样的数据库接口

13、程序提供一致的访问接口。序提供一致的访问接口。Python 数据库接口支持非常多的数据库，可数据库接口支持非常多的数据库，可以选择适合你项目的数据库：以选择适合你项目的数据库：GadFly、mSQL、MySQL、PostgreSQL、Microsoft SQL Server、Informix、Interbase、Oracle、Sybase等。可以访等。可以访问问Python数据库接口及数据库接口及API查看详细的支持数据库查看详细的支持数据库列表。列表。不同的数据库需要下载不同的不同的数据库需要下载不同的DB-API模块，模块，例如需要访问例如需要访问Oracle数据库和数据库和Mysql数据

14、，你需要数据，你需要下载下载Oracle和和MySQL数据库数据库DB-API模块。模块。Python的的DB-API，为大多数的数据库实现了接口，使用它，为大多数的数据库实现了接口，使用它连接各数据库后，就可以用相同的方式操作各种连接各数据库后，就可以用相同的方式操作各种数据库。数据库。Python DB-API使用流程：使用流程：1）引入）引入DB-API 模块；模块；2）获取与数据库的连接；）获取与数据库的连接；3）执行）执行SQL语句和存语句和存储过程；储过程；4）关闭数据库连接。）关闭数据库连接。pyodbc封装了封装了ODBC API，通过它可以访问各种有，通过它可以访问各种有OD

15、BC驱动的数据库。驱动的数据库。Pyodbc提供的相关对象方法说明如下：提供的相关对象方法说明如下：1、connection 对象方法对象方法 1）close()：关闭数据库；：关闭数据库；2）commit()：提交当前事务；：提交当前事务；3）rollback()：取消当前事务；：取消当前事务；4）cursor()：获取当前连接的游标；：获取当前连接的游标；5）errorhandler()：作为已给游标的句柄。：作为已给游标的句柄。2.cursor游标对象和方法游标对象和方法（具体略）（具体略）【例【例11-1】查询某个查询某个ODBC数据源的某个表数据源的某个表#encoding=gbki

16、mport pyodbc,sys#conn=pyodbc.connect(DSN=mysqldsn;UID=root;PWD=root)#连接连接MySQL#conn=pyodbc.connect(DRIVER=MySQL ODBC 5.3 ANSI Driver;SERVER=localhost;PORT=3306;DATABASE=Jxgl;USER=root;PASSWORD=root)#conn=pyodbc.connect(DRIVER=MySQL ODBC 5.3 Unicode Driver;SERVER=localhost;PORT=3306;DATABASE=Jxgl;USE

17、R=root;PASSWORD=root)conn =pyodbc.connect(DRIVER=SQL Server;DATABASE=%s;SERVER=%s;UID=%s;PWD=%s%(Jxgl,127.0.0.1,sa,sasasasa)#连接连接SQL Servercursor=conn.cursor()cursor.execute(SELECT*FROM student)while True:row=cursor.fetchone()if not row:break print(row)cursor.close()conn.close()sqlite3是一个很优秀的轻量级数据库。

18、是一个很优秀的轻量级数据库。sqlite3从从Python 2.5版本开始加入到标准库中。通过它，版本开始加入到标准库中。通过它，可以很方便地操作可以很方便地操作SQLite数据库。操作数据库。操作SQLite数据数据库的方法或语句与上面操作库的方法或语句与上面操作ODBC数据源数据的基数据源数据的基本相同。下面直接举例说明：本相同。下面直接举例说明：【例【例11-2】创建一个内存数据库，建表并插入创建一个内存数据库，建表并插入记录后，再显示出来记录后，再显示出来import sqlite3conn=sqlite3.connect(:memory:)cursor=conn.cursor()cu

19、rsor.execute(CREATE TABLE person(name text,age int)cursor.execute(INSERT INTO person VALUES(TOM,20)cursor.execute(INSERT INTO person VALUES(Jhon,22)mit()cursor.execute(SELECT*FROM person)while True:row=cursor.fetchone()if not row:break print(row)cursor.close();conn.close()Python和和MySQL交互的模块有交互的模块有 M

20、ySQLdb和和PyMySQL(pymysql)等。等。Python操作操作MySQL数据库数据库的方法首先可选的方法首先可选MySQL for Python，即，即MySQLdb，原因是这个是原因是这个是C写的，速度快。写的，速度快。MySQLdb是用于是用于Python连接连接Mysql数据库的接口，它实现了数据库的接口，它实现了 Python 数据库数据库 API 规范规范V2.0，是基于，是基于 MySQL C API上建立的。但上建立的。但Python3不再支持不再支持MySQLdb。若想在。若想在Linux和和Windows中都同时能够运行，恐怕更得考中都同时能够运行，恐怕更得考虑

21、采用虑采用PyMySQL。PyMySQL是一个全是一个全Python写的写的Python操作操作MySQL数据数据库的第三方库。使用时可以把它放入代码工程中调用，当库的第三方库。使用时可以把它放入代码工程中调用，当然也可以在系统里先安装后使用。然也可以在系统里先安装后使用。PyMySQL的性能和的性能和MySQLdb几乎相当，如果对性能要求不是特别的强，使用几乎相当，如果对性能要求不是特别的强，使用PyMySQL将更加方便。将更加方便。PyMySQL的使用方法和的使用方法和MySQLdb几乎一样，如果你以几乎一样，如果你以前使用过前使用过MySQLdb，只需要将，只需要将import MySQ

22、Ldb修改为修改为 import pymysql就可以了。就可以了。1、数据库连接、数据库连接 连接数据库前，请先确认以下事项：连接数据库前，请先确认以下事项：1）已经）已经创建了数据库，如：创建了数据库，如：Company。2）在）在Company数数据库中已经创建了表据库中已经创建了表 EMPLOYEE。EMPLOYEE表字表字段有：段有：FIRST_NAME,LAST_NAME,AGE,SEX和和INCOME。3）连接数据库）连接数据库Company使用的用户名使用的用户名为为testuser，密码为，密码为test123，也可自己设定或，也可自己设定或者直接使用者直接使用root用户名

23、及其密码，用户名及其密码，Mysql数据库用数据库用户授权请使用户授权请使用Grant命令。命令。4）在机器上已经安装）在机器上已经安装了了Python MySQLdb 模块。模块。2、创建数据库表、创建数据库表 如果数据库连接存在，可以使用如果数据库连接存在，可以使用execute()方法来为数据方法来为数据库创建表。库创建表。【例【例11-4】创建表创建表EMPLOYEE。import MySQLdb db=MySQLdb.connect(localhost,root,root,Company)cursor=db.cursor()#使用使用cursor()方法获取操作游标方法获取操作游标#

24、如果数据表已经存在使用如果数据表已经存在使用 execute()方法删除表。方法删除表。cursor.execute(DROP TABLE IF EXISTS EMPLOYEE)#创建数据表创建数据表SQL语句语句sql=CREATE TABLE EMPLOYEE(FIRST_NAME CHAR(20)NOT NULL,LAST_NAME CHAR(20),AGE INT,SEX CHAR(1),INCOME FLOAT)cursor.execute(sql)db.close()#关闭数据库连接关闭数据库连接 3、数据库插入操作、数据库插入操作 以下例题使用执行以下例题使用执行 SQL INS

25、ERT 语句向表语句向表 EMPLOYEE 插入记录：插入记录：【例【例11-5】在在EMPLOYEE表中添加记录。表中添加记录。import MySQLdbdb=MySQLdb.connect(localhost,root,root,Company)cursor=db.cursor()sql=INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)VALUES(Mac,Mohan,20,M,2000)#SQL 插入语句插入语句 try:cursor.execute(sql)#执行执行sql语句语句 mit()#提交到数据库执行提交到数

26、据库执行 except:db.rollback()#执行中遇到错误回滚前面已做的操作执行中遇到错误回滚前面已做的操作 db.close()以上例子中的以上例子中的SQL语句也可以写成如下形式：语句也可以写成如下形式：sql=INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)VALUES(%s,%s,%d,%c,%d)%(Mac,Mohan,20,M,2000)#SQL 插入语句插入语句 以下代码使用变量向以下代码使用变量向SQL语句中传递参数：语句中传递参数：user_id=root;password=rootcon.execut

27、e(insert into Login values(%s,%s)%(user_id,password)4、数据库查询操作、数据库查询操作 Python查询查询Mysql使用使用 fetchone()方法获取单条数据，方法获取单条数据，使用使用fetchall()方法获取多条数据。说明：方法获取多条数据。说明：1）fetchone()：该方法获取下一个查询结果集。结果集是一个对象。该方法获取下一个查询结果集。结果集是一个对象。2）fetchall()：接收全部的返回结果行。：接收全部的返回结果行。3）rowcount：这是一：这是一个只读属性，并返回执行个只读属性，并返回执行execute()

28、方法后影响的行数。方法后影响的行数。【例【例11-6】查询查询EMPLOYEE表中表中salary（工资）字段大于（工资）字段大于1000的所有数据。的所有数据。import MySQLdb#打开连接打开连接db=MySQLdb.connect(localhost,root,root,Company)cursor=db.cursor()#使用使用cursor()方法获取操作游标方法获取操作游标sql=SELECT*FROM EMPLOYEE WHERE INCOME%d%(1000)try:cursor.execute(sql)results=cursor.fetchall()#获取所有记录列

29、表获取所有记录列表 for row in results:fname=row0;lname=row1;age=row2;sex=row3;income=row4 print(fname=%s,lname=%s,age=%d,sex=%s,income=%d%(fname,lname,age,sex,income)#打印结果打印结果 except:print(错误错误:不能获取到数据。不能获取到数据。)db.close()#结果如下结果如下：fname=Mac,lname=Mohan,age=20,sex=M,income=2000 5、数据库更新操作、数据库更新操作 更新操作用于更新数据表的的

30、数据，例如：更新操作用于更新数据表的的数据，例如：【例【例11-7】将将 EMPLOYEE表中所有记录的表中所有记录的SEX 字段改为字段改为M，AGE 字段递增字段递增1。import MySQLdbdb=MySQLdb.connect(localhost,root,root,Company)cursor=db.cursor()sql=UPDATE EMPLOYEE SET AGE=AGE+1 WHERE SEX=%c%(M)try:cursor.execute(sql);mit()except:db.rollback()#发生错误时回滚发生错误时回滚db.close()6、执行事务、执行事

31、务 事务机制可以确保数据一致性。事务应该具有事务机制可以确保数据一致性。事务应该具有4个属性：个属性：原子性、一致性、隔离性、持久性。这原子性、一致性、隔离性、持久性。这四个属性通常称为四个属性通常称为ACID特性。特性。【例【例11-8】基于事务机制实现记录删除的程序段。基于事务机制实现记录删除的程序段。sql=DELETE FROM EMPLOYEE WHERE AGE%d%(20)#SQL删除语句删除语句try:cursor.execute(sql)#执行执行SQL语句语句 mit()#向数据库提交向数据库提交except:db.rollback()#发生错误时回滚发生错误时回滚 PyM

32、ySQL(pymysql)是一个纯是一个纯Python写的写的MySQL客户端，客户端，它的目标是最终替代它的目标是最终替代MySQLdb，可以在，可以在CPython、PyPy、IronPython和和Jython环境下运行，环境下运行，PyMySQL在在MIT许可下发许可下发布。在开发基于布。在开发基于Python语言的项目中，为了以后系统能兼语言的项目中，为了以后系统能兼容容Python3，建议使用，建议使用PyMySQL替换替换MySQLdb。通过如下例子来说明使用通过如下例子来说明使用PyMySQL操作操作mysql。【例例11-9】对对student表插入一条记录。表插入一条记录。

33、Python与与MySQL服务器通信，还可以使用服务器通信，还可以使用MySQL-connector-python，一个，一个Python模块模块MySQL/Connector。试着使用。试着使用pip安装，若不行可到安装，若不行可到https:/ mysql-connector-python-2.1.3.zip后，解压到后，解压到mysql-connector-python-2.1.3目录，目录，进入该目录后，利用进入该目录后，利用“sudo python setup.py install”来安装。来安装。安装成功后，就可以在安装成功后，就可以在python程序里正常使程序里正常使用用“im

34、port mysql.connector as mysql”导入导入MySQL的的connector模块了，使用也很简单。模块了，使用也很简单。【例例11-10】一个简单查询一个数据表数据的实一个简单查询一个数据表数据的实例。例。通过通过Python查询数据库中数据时，数据会呈现查询数据库中数据时，数据会呈现乱码，只需要在连接字符串中指定数据编码，如乱码，只需要在连接字符串中指定数据编码，如“utf8”、“GBK”等就可以。注意：等就可以。注意：Python 3.x才才支持支持Unicode编码。编码。#-*-coding:gbk-*-import pymysqldb=pymysql.conn

35、ect(host=localhost,user=root,password=root,database=Jxgl,charset=gbk)cursor=db.cursor();sqlstr=SELECT*FROM course;try:cursor.execute(sqlstr);courses=cursor.fetchall()except:print(u数据读取错误数据读取错误)else:print(courses)db.close()python连接微软的连接微软的SQL Server数据库用的第三数据库用的第三方模块叫做方模块叫做pymssql（官方文档：（官方文档：http:/www

36、.pymssql.org/en/stable/index.html）。）。在官方文档可以看到，在官方文档可以看到，pymssql是基于是基于_mssql模块模块做的封装，是为了遵守做的封装，是为了遵守python的的DB-API规范接口。规范接口。pymssql的安装可以通过：的安装可以通过：pip install pymssql 或或 pip3 install pymssql 另 外，另 外，p y m s s q l 2.1.3 可 由 网 址：可 由 网 址：https:/pypi.python.org/pypi/pymssql/2.1.3获取。获取。从该网址获得安装包，解压释放后，尝试

37、通过命从该网址获得安装包，解压释放后，尝试通过命令：令：python setup.py install来安装；也可以对下载来安装；也可以对下载的相应的相应“文件名文件名.whl”安装文件，使用：安装文件，使用：“pip install 文件名文件名.whl”来安装。安装与使用请参照官来安装。安装与使用请参照官方文档。方文档。下面直接通过下面直接通过Python操作操作SQL Server数据库数数据库数据来说明：据来说明：（1）基本使用方法）基本使用方法 使用使用pymssql连接连接sql server数据库并实现数据数据库并实现数据库基本操作库基本操作(详见书本，详见书本，API请参照官网

38、：请参照官网：http:/www.pymssql.org/en/stable/ref/pymssql.html)（2）可以使用另一种语法：）可以使用另一种语法：with 来避免手动来避免手动关闭关闭cursors和和connection连接连接import pymssqlserver=187.32.43.13#连接服务器地址连接服务器地址user=root#连接帐号连接帐号password=1234#连接密码连接密码with pymssql.connect(server,user,password,默认默认数据库名称数据库名称)as conn:with conn.cursor(as_dict=

39、True)as cursor:#数据存放到字典中数据存放到字典中 cursor.execute(SELECT*FROM persons WHERE salesrep=%s,John Doe)for row in cursor:print(ID=%d,Name=%s%(rowid,rowname)（3）调用存储过程：）调用存储过程：with pymssql.connect(server,user,password,tempdb)as conn:with conn.cursor(as_dict=True)as cursor:cursor.execute(CREATE PROCEDURE FindPerson name VARCHAR(100)AS BEGIN SELECT*FROM persons WHERE name=name END )cursor.callproc(FindPerson,(Jane Doe,)for row in cursor:print(ID=%d,Name=%s%(rowid,rowname)