Python程序设计教程 课件 第12章 数据库应用

Python程序设计实用教程xxx第12章数据库应用本章要点关系型数据库介绍SQLite数据库访问访问Access、MySQL和SQLServer数据库

在PythonDatabaseAPI2.0规范中，定义了Python数据库API接口的各个部分，如模块接口、连接对象、游标对象、类型对象和构造器、DBAPI的可选扩展以及可选的错误机制等。12.1关系型数据库

关系模型可以简单理解为二维表格，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。

1.关系数据库的特点

存储方式：传统的关系型数据库采用表格的储存方式，数据以行和列的方式进行存储。

存储结构：关系型数据库按照结构化的方法存储数据，每个数据表都必须对各个字段定义好，整个数据表的可靠性和稳定性都高。

扩展方式：由于关系型数据库将数据存储在数据表中，数据操作的瓶颈出现在多张数据表的操作中，而且数据表越多问题越严重，关系型数据库只具备纵向扩展能力。

查询方式：关系型数据库采用结构化查询语言（即SQL）来对数据库进行查询，它能够支持数据库的增加，查询，更新，删除操作。

规范化：开发人员通常需要对一个或者多个数据实体（包括数组、列表和嵌套数据）进行操作，一个数据实体一般首先要分割成多个部分，然后再对分割的部分进行规范化，规范化以后再分别存入到多张关系型数据表中。

事务性：关系型数据库强调ACID规则，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。可以满足对事务性要求较高或者需要进行复杂数据查询的数据操作。

读写性能：关系型数据库强调数据的一致性，面对海量数据的处理的时候效率就会变得很差，特别是遇到高并发读写的时候性能就会显著的下降。2.常用关系型数据库

主流的关系型数据库有Oracle、SQLite、MySQL、MicrosoftSQLServer、MicrosoftAccess等多个品种，每种数据库的语法、功能和特性也各具特色。12.2SQLite数据库访问SQLite数据库是一款轻量级的数据库，无服务器、零配置、事务性的SQL数据库引擎。SQLite的源代码不受版权限制，是小型项目和简单Web应用的理想选择。所以，可以直接通过Python来操作SQLite数据库。12.2.1sqlite3模块操作数据库的步骤1.创建一个访问数据库的连接

如果数据库文件不存在，该方法就会自动在当前目录下创建一个xxx.db的文件。

在调用connect函数的时候，指定库名称，如果指定的数据库存在就直接打开这个数据库，如果不存在就新创建一个数据库打开。

【例12.1】创建一个sqlite数据库importsqlite3cx=sqlite3.connect("E:/test.db")#也可以在内存中创建数据库。con=sqlite3.connect(":memory:")

程序运行后，在E盘上创建一个test.db的数据库文件，同时在内存创一个文件。2.创建游标#创建游标c=cx.cursor()获取到游标之后，就可以使用SQL语句来对数据进行操作，创建表、添加数据、遍历数据等。3．创建表#创建一个user表c.execute("CREATETABLEuser(user_idINTEGER,user_nameTEXT,user_sexINTEGER,user_ageINTEGER,user_createTEXT)")

通过SQL语句来创建一个user表，user表包括五个属性，user_id(用户id)、user_name(用户姓名)、user_sex(用户性别)、user_age(用户年龄)、user_create(用户创建日期)。SQLite的存储类保存这几个类型。4.向表中添加数据#向user表中添加一条记录c.execute("INSERTINTOuser(user_id,user_name,user_sex,user_age,user_create)VALUES(?,?,?,?,DATETIME('now'))",(101,"张三",'男',23))通过SQL语句向user表中添加一条数据。5．提交事务关闭数据库连接#提交事务mit()#关闭连接c.close()执行完成之后，可以发现在E盘上目录下会生成一个test.db的数据库文件。6．操纵数据库表中的数据(1).读取数据#创建一个访问数据库test.db的连接

【例12.2】读取操纵数据库中的数据。conn=sqlite3.connect("test.db")#创建游标c=conn.cursor()#获取user表中所有的记录c.execute("SELECT*FROMuser")#获取结果result=c.fetchall()#关闭连接conn.close()#查看数据print(result)(2).查询数据c.execute("select

*

from

user")

要提取查询到的数据,使用游标的fetch函数,如:c.fetchall()

如果使用c.fetchone(),则首先返回列表中的第一项,再次使用,则返回第二项,依次下去。(3).修改数据c.execute("update

user

set

name='Boy'

where

id

=

0")mit()

修改数据以后需要提交。(4).删除数据c.execute("delete

from

user

where

id

=

1")mit()(5).使用中文

先确定IDE或者系统默认编码是utf-8,并且在中文前加上ux=u'鱼'c.execute("update

user

set

name=?

where

id

=

0",x)c.execute("select

*

from

user")c.fetchall()如果要显示出中文字体，那需要依次打印出每个字符串。

for

item

in

cu.fetchall():

for

element

in

item:

print（element）12.2.2SQLite命令

1.系统命令SQLite中，常用的有help命令，用于显示所有命令。quit/exit命令用于退出sqlite3，database命令显示当前打开的数据库文件，tables命令用于显示数据库中的所有的表。schema命令用于查看表达结构。系统命令含义help显示所有命令。quit/exit退出sqlite3。database显示当前打开的数据库文件。tables显示数据库中的所有表格。schema查看表结构（显示表格字段和数据）。2.sql语句(1).表格新增、删除与表名修改。

新增表格语法：createtable表格名称(字段名

字段类型，字段名

字段类型，...)

【例12.3】新建表格，删除表格与修改表名示例程序。

程序运行时，使用不同的sql语句对表格进行处理，c.execute("createtablestu()语句创建表格。c.execute("droptablestu")删除表格。c.execute("altertablesturenametostu_info")表格重命名。(2).表格字段新增与删除。【例12.4】增加表格字段和删除字段示例，删除表格stu的address字段。程序运行时，c.execute("createtablestu()语句创建表格增加字段。c.execute("createtablestu1asselectid,name,agefromstu")语句将一个表格的一部分语句拷贝到另一个表格。c.execute("droptablestu")语句删除表格。c.execute("altertablestu1renametostu")语句重命名一个表格。(3).表格记录的增删查改。

【例12.5】表格中记录的增、删、改、查示例程序。

运行程序，实现增、删、改、查的数据处理。c.execute("insertintostuvalues()语句增加一条记录。c.execute("deletefromstuwhereid=1002")语句删除一条记录。c.execute("updatestusetage=20whereid=1001")语句修改一条记录。t=c.execute("select*fromstu")语句查询表中的记录。12.2.3SQLite数据库应用

创建一个简单的关系型数据库，为一个书店存储书的分类和价格。数据库中包含两个表：category用于记录分类，book用于记录某个书的信息。

一本书归属于某一个分类，因此book有一个外键(foreignkey)，指向catogory表的主键id。1.创建数据库

首先创建数据库，以及数据库中的表。在使用connect()连接数据库后，可以通过定位指针cursor，来执行SQL命令。【例12.6】SQLite数据库的应用，创建数据库和表。

利用execute()命令，执行了两个SQL命令，创建数据库中的两个表。创建完成后，保存并断开数据库连接。2.插入数据

上面创建了数据库和表，确立了数据库的抽象结构。下面将在同一数据库中插入数据。【例12.7】SQLite数据库的应用，插入数据。

用executemany()的方法来执行多次插入，增加多个记录。每个记录是表中的一个元素，如上面的books表中的元素。3、查询

在执行查询语句后，Python将返回一个循环器，包含有查询获得的多个记录。循环读取，也可以使用sqlite3提供的fetchone()和fetchall()方法读取记录：【例12.8】SQLite数据库的应用，查询表中的数据。4、更新与删除可以更新某个记录，或者删除记录：【例12.9】SQLite数据库的应用，更新于删除表中的数据。12.3访问Access、MySQL和SQLServer数据库

Python是一种功能强大的编程语言，广泛用于数据处理和数据库操作。本节将学习Python操作Access，Mysql，SQLServer数据库，包括连接数据库，执行查询，更新操作等。12.3.1.使用Access数据库

要操作Access数据库，首先要安装pywin32。1.建立数据库连接importwin32com.clientconn=win32com.client.Dispatch(r'ADODB.Connection')DSN='PROVIDER=Microsoft.Jet.OLEDB.4.0;DATASOURCE=C:/MyDB.mdb;'conn=Open(DSN)2.打开记录集rs=win32com.client.Dispatch(r'ADODB.Recordset')rs_name='MyRecordset'rs.Open('['+rs_name+']',conn,1,3)3.操作记录集rs.AddNew()rs.Fields.Item(1).Value='data'rs.Update()4.操作数据conn=win32com.client.Dispatch(r'ADODB.Connection')DSN='PROVIDER=Microsoft.Jet.OLEDB.4.0;DATASOURCE=C:/MyDB.mdb;'sql_statement="INSERTINTO[Table_Name]([Field_1],[Field_1])VALUES('data1','data2')"conn.Open(DSN)conn.Execute(sql_statement)conn.close()5.遍历记录rs.MoveFirst()count=0while1:ifre.EOF:

breakelse:

count=count+1

rs.MoveNext()12.3.2.使用MySQL数据库

Python中使用MySQL数据库，需要使用pymysql库来连接MySQL数据库。首先安装pymysql库，然后导入：importpymysql。1.连接MySQL数据库conn=pymysql.connect(

host='159.xxx.xxx.216',

#主机名

port=3306,

#端口号，MySQL默认为3306

user='xxxx',

#用户名

password='xxxx',#密码

database='xx',

#数据库名称2．执行SQL查询语句

连接MySQL数据库之后，就可以使用游标对象来执行SQL查询语句，如下所示：#创建游标对象cursor=conn.cursor()#执行SQL查询语句cursor.execute("SELECT*FROMusersWHEREgender='female'")#获取查询结果result=cursor.fetchall()3．将查询结果转化为Pandasdataframe对象

获取查询结果之后，可以将其转化为Pandasdataframe对象，以便于进行进一步的数据处理和分析。具体代码如下importpandasaspd#将查询结果转化为Pandasdataframe对象df=pd.DataFrame(result,columns=[i[0]foriincursor.description])4．关闭游标和数据库连接

需要关闭游标对象和数据库连接，以释放资源。具体代码如下：importpandasaspd#将查询结果转化为Pandasdataframe对象df=pd.DataFrame(result,columns=[i[0]foriincursor.description])12.3.3.使用SQlserver数据库

在Python中，可以使用pyodbc模块连接和操作SQLServer数据库。

以下是Python使用pyodbc模块连接和操作SQLServer数据库的步骤，包括连接SQLServer数据库、表、插入数据、查询数据更新数据、删除数据等操作。1.连接SQLServer数据库

在Python中，可以使用pyodbc模块连接SQLServer数据库。以下是连接SQLServer数据库的基本语法。importpyodbcserver='your_server_name'database='your_database_name'username='your_username'password='your_password'driver='{ODBCDriver17forSQLServer}'cnxn=pyodbc.connect('DRIVER='+driver+';SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+password)2.创建表

在SQLServer中，可以使用CREATETABLE语句创建表。以下是创建表的基本语法：importpyodbc

server='your_server_name'database='your_database_name'username='your_username'password='your_password'driver='{ODBCDriver17forSQLServer}'

cnxn=pyodbc.connect('DRIVER='+driver+';SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+password)cursor=cnxn.cursor()

cursor.execute("CREATETABLEcustomers(idINTIDENTITY(1,1)PRIMARYKEY,nameVARCHAR(255),addressVARCHAR(255))")3.插入数据

在SQLServer中，使用INSERTINTO语句插入数据。以下是插入数据的基本语法：importpyodbc

server='your_server_name'database='your_database_name'username='your_username'password='your_password'driver='{ODBCDriver17forSQLServer}'

cnxn=pyodbc.connect('DRIVER='+driver+';S