《Python教材》-模块14 Python访问数据库

模块14Python访问数据库1已内嵌在Python中，使用时需要导入sqlite3。使用c语言开发，支持大多数SQL91标准，不支持外键限制。支持原子的、一致的、独立和持久的事务。通过数据库级上的独占性和共享锁定来实现独立事务，当多个线程和进程同一时间访问同一数据库时，只有一个可以写入数据。支持140TB的数据库，每个数据库完全存储在单个磁盘文件中，以B+数据结构的形式存储，一个数据库就是一个文件，通过复制即可实现备份。14.1Python访问SQLite数据库14.1Python访问SQLite数据库常用的SQLite可视化管理工具==>SQLiteManagerSQLiteDatabaseBrowser14.1Python访问SQLite数据库访问和操作SQLite数据时，需要首先导入sqlite3模块，然后创建一个与数据库关联的Connection对象：importsqlite3#导入模块conn=sqlite3.connect('example.db')#连接数据库14.1Python访问SQLite数据库成功创建Connection对象以后，再创建一个Cursor对象，并且调用Cursor对象的execute()方法来执行SQL语句创建数据表以及查询、插入、修改或删除数据库中的数据：c=conn.cursor()#创建表c.execute('''CREATETABLEstocks(datetext,transtext,symboltext,qtyreal,pricereal)''')#插入一条记录c.execute("INSERTINTOstocksVALUES('2006-01-05','BUY','RHAT',100,35.14)")#提交当前事务，保存数据mit()#关闭数据库连接conn.close()14.1Python访问SQLite数据库如果需要查询表中内容，那么重新创建Connection对象和Cursor对象之后，可以使用下面的代码来查询。forrowinc.execute('SELECT*FROMstocksORDERBYprice'):print(row)14.1.1Connection对象connect(database[,timeout,isolation_level,detect_types,factory])：连接数据库文件，也可以连接":memory:"在内存中创建数据库。sqlite3.Connection.cursor()：返回游标对象sqlite3.Connection.execute()：执行SQL语句sqlite3.Connection.executemany()：执行SQL语句,一次影响多条记录sqlite3.Connection.executescript()：执行SQL语句，可以是一段脚本sqlite3.Connection.total_changes()：数据改变的总行数sqlite3.Cmit()：提交事务sqlite3.Connection.rollback()：回滚事务sqlite3.Connection.close()：关闭连接sqlite3.Connection.createfunction():创建可在SQL中调用的函数14.1.1

Connection对象在sqlite3连接中创建并调用自定义函数：importsqlite3importhashlib#自定义Python函数defmd5sum(t):returnhashlib.md5(t).hexdigest()#在内存中创建临时数据库conn=sqlite3.connect(":memory:")#创建可在SQL调用的函数，其中第二个参数表示函数的参数个数conn.create_function("md5",1,md5sum)cur=conn.cursor()#在SQL语句中调用自定义函数cur.execute("selectmd5(?)",["山东济南趵突泉".encode()])print(cur.fetchone()[0])mit()conn.close()14.1.2Cursor对象Cursor对象常用方法：execute(...)：执行SQL语句executemany(...)：重复执行多次SQL语句executescript(...)：一次执行多条SQL语句

fetchone(...)：从结果集中返回一行记录fetchmany(...)：从结果集中返回多行记录fetchall(...)：从结果集中返回所有行记录close(...)：关闭游标

14.1.2Cursor对象execute(sql[,parameters])：该方法用于执行一条SQL语句，下面的代码演示了用法，以及为SQL语句传递参数的两种方法，分别使用问号和命名变量作为占位符。importsqlite3conn=sqlite3.connect(":memory:")cur=conn.cursor()cur.execute("CREATETABLEpeople(name,age)")who="QiBaosheng"age=52#使用问号作为占位符cur.execute("INSERTINTOpeopleVALUES(?,?)",(who,age))#使用命名变量作为占位符cur.execute("SELECT*FROMpeopleWHEREname=:whoANDage=:age",{"who":who,"age":age})print(cur.fetchone())mit()conn.close()14.1.2Cursor对象executemany(sql,seq_of_parameters)：该方法用来对于所有给定参数执行同一个SQL语句，参数序列可以使用不同的方式产生。14.1.2Cursor对象使用迭代来产生参数序列：importsqlite3#自定义迭代器，按顺序生成小写字母classIterChars:def__init__(self):self.count=ord('a')def__iter__(self):returnselfdef__next__(self):ifself.count>ord('z'):raiseStopIterationself.count+=1return(chr(self.count-1),)14.1.2Cursor对象conn=sqlite3.connect(":memory:")cur=conn.cursor()cur.execute("CREATE

TABLEcharacters(c)")#创建迭代器对象theIter=IterChars()#插入记录，每次插入一个英文小写字母cur.executemany("INSERT

INTOcharacters(c)VALUES(?)",theIter)#读取并显示所有记录cur.execute("SELECTcFROMcharacters")print(cur.fetchall())mit()conn.close()14.1.2Cursor对象使用生成器对象来产生参数：importsqlite3importstring#包含yield语句的函数可以用来创建生成器对象defchar_generator():forcinstring.ascii_lowercase:yield(c,)14.1.2Cursor对象conn=sqlite3.connect(":memory:")cur=conn.cursor()cur.execute("CREATETABLEcharacters(c)")#使用生成器对象得到参数序列cur.executemany("INSERTINTOcharacters(c)VALUES(?)",char_generator())cur.execute("SELECTcFROMcharacters")print(cur.fetchall())14.1.2Cursor对象使用直接创建的序列作为SQL语句的参数：importsqlite3persons=[("Hugo","Boss"),("Calvin","Klein")]conn=sqlite3.connect(":memory:")#创建表conn.execute("CREATETABLEperson(firstname,lastname)")#插入数据conn.executemany("INSERTINTOperson(firstname,lastname)VALUES(?,?)",persons)#显示数据forrowinconn.execute("SELECTfirstname,lastnameFROMperson"):print(row)print("Ijustdeleted",conn.execute("DELETEFROMperson").rowcount,"rows")mit()conn.close()14.1.2Cursor对象fetchone()、fetchmany(size=cursor.arraysize)、fetchall()：用来读取数据。假设数据库通过下面代码插入数据：importsqlite3conn=sqlite3.connect("D:/addressBook.db")cur=conn.cursor()#创建游标cur.execute('''INSERTINTOaddressList(name,sex,phon,QQ,address)VALUES('王小丫','女',,'66735','北京市')''')cur.execute('''INSERTINTOaddressList(name,sex,phon,QQ,address)VALUES('李莉','女',,'675797','天津市')''')cur.execute('''INSERTINTOaddressList(name,sex,phon,QQ,address)VALUES('李星草','男',,'3232099','昆明市')''')mit()#提交事务，把数据写入数据库conn.close()14.1.2Cursor对象使用fetchall()读取数据：importsqlite3conn=sqlite3.connect('D:/addressBook.db')cur=conn.cursor()cur.execute('SELECT*FROMaddressList')li=cur.fetchall()#返回所有查询结果forlineinli:foriteminline:print(item,end='')print()conn.close()14.1.3Row对象假设数据以下面的方式创建并插入数据：conn=sqlite3.connect("test.db")c=conn.cursor()c.execute('''CREATETABLEstocks(datetext,transtext,symboltext,qtyreal,pricereal)''')c.execute("""INSERTINTOstocksVALUES('2020-03-27','BUY','RHAT',100,35.14)""")mit()c.close()14.1.3Row对象使用fetchone()方法读取其中数据：conn.row_factory=sqlite3.Rowc=conn.cursor()c.execute('SELECT*FROMstocks')r=c.fetchone()print(type(r))print(tuple(r))print(r[2])print(r.keys())print(r['qty'])forfieldinr:print(field)c.close()14.2Python访问Access数据库需要首先安装扩展模块PythonforWindowsextensions，即Pywin32。在Access中创建test.mdb数据库，数据库中创建表MyRecordset，其结构如表14-3所示。表14-3表MyRecordset的结构字段名数据类型含义sid数字学号sname文本姓名sex文本性别14.2Python访问Access数据库1、建立数据库连接importwin32com.clientconn=win32com.client.Dispatch(r'ADODB.Connection')DSN=‘PROVIDER=Microsoft.Jet.OLEDB.4.0;DATASOURCE=test.mdb;'conn.Open(DSN)2、打开记录集rs=win32com.client.Dispatch(r'ADODB.Recordset')rs_name='MyRecordset'#表名rs.Open('['+rs_name+']',conn,1,3)2214.2Python访问Access数据库3、对记录集操作rs.AddNew()#添加一条新记录rs.Fields.Item(0).Value=1#新记录的第一个字段的值设为1rs.Fields.Item(1).Value="李晨"#新记录的第二个字段的值设为"李晨"rs.Fields.Item(2).Value="女"#新记录的第三个字段的值设为"女"rs.Update()#更新14.2Python访问Access数据库4、用SQL语句来增、删、改数据#增sql="InsertInto"+rs_name+"(sid,sname,ssex)Values(2,'王晓燕','女')"#sql语句conn.Execute(sql)#执行sql语句sql="InsertInto"+rs_name+"(sid,sname,ssex)Values(3,'李颖','女')"#sql语句conn.Execute(sql)#执行sql语句sql="InsertInto"+rs_name+"(sid,sname,ssex)Values(4,'王军','男')"#sql语句conn.Execute(sql)#执行sql语句#删sql="Delete*FROM"+rs_name+"wheresid=2"conn.Execute(sql)#改sql="Update"+rs_name+"Setssex='女'wheresid=4"conn.Execute(sql)14.2Python访问Access数据库5、遍历记录rs.MoveFirst()#光标移到首条记录count=0whileTrue:ifrs.EOF:breakelse:foriinrange(rs.Fields.Count):#字段名：字段内容

print(rs.Fields[i].Name,"：",rs.Fields[i].Value)count+=1rs.MoveNext()conn.Close()#关闭连接print(“count=”,count)14.3Python访问MySQL数据库PyMySQL是在Python3.x版本中用于连接MySQL服务器的一个扩展库，PyMySQL模块中主要包含两个对象：connect连接对象和cursor游标对象。connect连接对象用来创建与数据库服务器的连接，其语法示例如下：conn=pymysql.connect(host='localhost',port='3306',user='root',passwd='',db='testdb',charset='utf8')host表示访问MySQL服务器的地址，port是MySQL服务器端口号,连接数据库testdb使用的用户名为"root"，密码为空串，字符编码采用utf-8。14.3Python访问MySQL数据库connect连接对象支持的方法：cursor()：使用该连接创建并返回游标commit()：提交当前事务rollback()：回滚当前事务close() ：关闭连接cursor对象支持的方法：execute(op)：执行一个数据库的查询命令fetchone()：取得结果集的下一行fetchmany(size)：获取结果集的下几行fetchall()：获取结果集中的所有行rowcount()：返回数据条数或影响行数close() ：关闭游标对象14.3Python访问MySQL数据库1.数据库连接连接数据库前，您已经创建了数据库testdb，在TESTDB数据库中已经创建了表EMPLOYEE，EMPLOYEE表字段为FIRST_NAME,LAST_NAME,AGE,SEX和INCOME,在机器上已经安装了PyMySQL模块。以下实例链接Mysql的TESTDB数据库：importpymysql#打开数据库连接conn=pymysql.connect("localhost","root","","testdb")#使用cursor()方法创建一个游标对象cursorcursor=conn.cursor()14.3Python访问MySQL数据库#使用execute()方法执行SQL查询cursor.execute("SELECTVERSION()")#使用fetchone()方法获取单条数据.data=cursor.fetchone()print("Databaseversion:%s"%data)#关闭数据库连接db.close()2914.3Python访问MySQL数据库2.创建数据库表如果数据库连接存在，可以使用execute()方法来为数据库创建表，如下所示创建表EMPLOYEE：importpymysql#打开数据库连接conn=pymysql.connect("localhost","root","","testdb")#使用cursor()方法创建一个游标对象cursorcursor=conn.cursor()#使用execute()方法执行SQL，如果表存在则删除cursor.execute("DROPTABLEIFEXISTSEMPLOYEE")14.3Python访问MySQL数据库#使用CREATE语句创建表sql="""CREATETABLEEMPLOYEE(FIRST_NAMECHAR(20)NOTNULL,LAST_NAMECHAR(20),AGEINT,SEXCHAR(1),INCOMEFLOAT)"""

cursor.execute(sql)#关闭数据库连接conn.close()14.3Python访问MySQL数据库3.数据库插入操作以下实例使用执行SQLINSERT语句向表EMPLOYEE插入记录：importpymysql#打开数据库连接conn=pymysql.connect("localhost","root","","testdb")#使用cursor()方法获取操作游标cursor=conn.cursor()#SQL插入语句sql="""INSERTINTOEMPLOYEE(FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)VALUES('Mac','Mohan',20,'M',2000)"""14.3Python访问MySQL数据库try:#执行sql语句cursor.execute(sql)#提交到数据库执行mit()except:#如果发生错误则回滚conn.rollback()#关闭数据库连接conn.close()3314.3Python访问MySQL数据库4.数据库查询操作Python查询Mysql，使用fetchone()方法获取单行数据,使用fetchall()方法获取所有行的数据，使用rowcount来获取执行execute()方法后影响的行数。下面实例查询EMPLOYEE表中salary（工资）字段大于1000的所有数据：importpymysql#打开数据库连接conn=pymysql.connect("localhost","root","","testdb")#使用cursor()方法获取操作游标cursor=conn.cursor()#SQL查询语句sql="SELECT*FROMEMPLOYEE\WHEREINCOME>%s"%(1000)3414.3Python访问MySQL数据库try:#执行SQL语句cursor.execute(sql)#获取所有记录列表results=cursor.fetchall()forrowinresults:fname=row[0]lname=row[1]age=row[2]sex=row[3]income=row[4]3514.3Python访问MySQL数据库

#打印结果print("fname=%s,lname=%s,age=%s,sex=%s,income=%s"%\(fname,lname,age,sex,income))except:print("Error:unabletofetchdata")#关闭数据库连接conn.close()3614.3Python访问MySQL数据库5.数据库更新操作更新操作用于更新数据表中的数据，以下实例将testdb表中SEX为'M'的AGE字段递增1。importpymysql#打开数据库连接conn=pymysql.connect("localhost","root","","testdb")#使用cursor()方法获取操作游标cursor=conn.cursor()#SQL更新语句sql="UPDATEEMPLOYEESETAGE=AGE+1WHERESEX='%c'"%('M')3714.3Python访问MySQL数据库try:#执行SQL语句

cursor.execute(sql)#提交到数据库执行

mit()except:#发生错误时回滚

conn.rollback()#关闭数据库连接conn.close()3814.3Python访问MySQL数据库6.删除操作删除操作用于删除数据表中的数据，以下实例演示了删除数据表EMPLOYEE中AGE大于20的所有数据。importpymysql#打开数据库连接conn=pymysql.connect("localhost","root","","testdb")#使用cursor()方法获取操作游标cursor=conn.cursor()#SQL删除语句sql="DELETEFROMEMPLOYEEWHEREAGE>%s"%(20)3914.3Python访问MySQL数据库try:#执行SQL语句cursor.execute(sql)#提交修改mit()except:#发生错误时回滚conn.rollback()#关闭连接conn.close()4014.3Python访问MySQL数据库7.执行事务事务机制可以确保数据一致性。事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。4114.3Python访问MySQL数据库PythonDBAPI2.0的事务提供了两个方法commit或rollback。#SQL删除记录语句sql="DELETEFROMEMPLOYEEWHEREAGE>%s"%(20)try:#执行SQL语句cursor.execute(sql)#向数据库提交mit()except:#发生错误时回滚conn.rollback()4214.4Python访问MongoDB数据库MongoDB是一个基于分布式文件存储的文档数据库，可以说是非关系型（NoSQL，NotOnlySQL）数据库中比较像关系型数据库的一个，具有免费、操作简单、面向文档存储、自动分片、可扩展性强、查询功能强大等特点，对大数据处理支持较好，旨在为WEB应用提供可扩展的高性能数据存储解决方案。MongoDB将数据存储为一个文档，数据结构由键值(key=>value)对组成。MongoDB文档类似于JSON对象。字段值可以包含其他文档，数组及文档数组。MongoDB数据库可以到网站/downloads下载，安装之后打开命令提示符环境并切换到MongoDB安装目录中的server\3.2\bin文件夹，然后执行命令mongod--dbpathD:\data--journal--storageEngine=mmapv1启动MongoDB，当然需要首先在D盘根目录下新建文件夹data。让刚才那个命令提示符环境始终处于运行状态，然后再打开一个命令提示符环境，执行mongo命令连接MongoDB数据库，如果连接成功的话，会显示一个>符号作为提示符，之后就可以输入MongoDB命令了。14.4Python访问MongoDB数据库1.准备工作在开始之前，请确保已经安装好了MongoDB并启动了其服务，并且安装好了Python的PyMongo库。2.连接MongoDB连接MongoDB时，我们需要使用PyMongo库里面的MongoClient。一般来说，传入MongoDB的IP及端口即可，其中第一个参数为地址host，第二个参数为端口port（如果不给它传递参数，默认是27017）：importpymongoclient=pymongo.MongoClient(host='localhost',port=27017)这样就可以创建MongoDB的连接对象了。14.4Python访问MongoDB数据库3.指定数据库MongoDB中可以建立多个数据库，接下来我们需要指定操作哪个数据库。这里我们以test数据库为例来说明，下一步需要在程序中指定要使用的数据库：db=client.test这里调用client的test属性即可返回test数据库。当然，我们也可以这样指定：db=client['test']这两种方式是等价的。4.指定集合MongoDB的每个数据库又包含许多集合（collection），它们类似于关系型数据库中的表。下一步需要指定要操作的集合，这里指定一个集合名称为students。与指定数据库类似，指定集合也有两种方式：collection=db.studentscollection=db['students']这样我们便声明了一个Collection对象。14.4Python访问MongoDB数据库5.插入数据接下来，便可以插入数据了。对于students这个集合，新建一条学生数据，这条数据以字典形式表示：student={'id':'20170101','name':'Jordan','age':20,'gender':'male'}这里指定了学生的学号、姓名、年龄和性别。接下来，直接调用collection的insert()方法即可插入数据，代码如下：result=collection.insert(student)print(result)14.4Python访问MongoDB数据库实际上，在PyMongo3.x版本中，官方已经不推荐使用insert()方法了。当然，继续使用也没有什么问题。官方推荐使用insert_one()和insert_many()方法来分别插入单条记录和多条记录，示例如下：student={'id':'20170101','name':'Jordan','age':20,'gender':'male'}result=collection.insert_one(student)print(result)print(result.inserted_id)14.4Python访问MongoDB数据库6.查询插入数据后，我们可以利用find_one()或find()方法进行查询，其中find_one()查询得到的是单个结果，find()则返回一个生成器对象。示例如下：result=collection.find_one({'name':'Mike'})print(type(result))print(result)此外，我们也可以根据ObjectId来查询，此时需要使用bson库里面的objectid：frombson.objectidimportObjectIdresult=collection.find_one({'_id':ObjectId('5e81ba0f0af6c769a8406131')})print(result)14.4Python访问MongoDB数据库14.4Python访问MongoDB数据库对于多条数据的查询，我们可以使用find()方法。例如，这里查找年龄为20的数据，示例如下：results=collection.find({'age':20})print(results)forresultinresults:print(result)如果要查询年龄大于20的数据，则写法如下：results=collection.find({'age':{'$gt':20}})5014.4Python访问MongoDB数据库符号含义示例$lt小于{'age':{'$lt':20}}$gt大于{'age':{'$gt':20}}$lte小于等于{'age':{'$lte':20}}$gte大于等于{'age':{'$gte':20}}$ne不等于{'age':{'$ne':20}}$in在范围内{'age':{'$in':[20,23]}}$nin不在范围内{'age':{'$nin':[20,23]}}51比较符号功能说明

14.4Python访问MongoDB数据库还可以进行正则匹配查询。例如，查询名字以M开头的学生数据，示例如下：results=collection.find({'name':{'$regex':'^M.*'}})52符号含义示例示例含义$regex匹配正则表达式{'name':{'$regex':'^M.*'}}name以M开头$exists属性是否存在{'name':{'$exists':True}}name属性存在$type类型判断{'age':{'$type':'int'}}age的类型为int$mod数字模操作{'age':{'$mod':[5,0]}}年龄模5余0$text文本查询{'$text':{'$search':'Mike'}}text类型的属性中包含Mike字符串$where高级条件查询{'$where':'obj.fans_count==obj.follows_count'}自身粉丝数等于关注数功能符号说明

14.4Python访问MongoDB数据库7.计数要统计查询结果有多少条数据，可以调用count()方法。比如，统计所有数据条数：count=collection.find().count()print(count)或者统计符合某个条件的数据：count=collection.find({'age':20}).count()print(count)运行结果是一个数值，即符合条件的数据条数。

5314.4Python访问MongoDB数据库8.排序排序时，直接调用sort()方法，并在其中传入排序的字段及升降序标志即可。示例如下：results=collection.find().sort('name',pymongo.ASCENDING)print([result['name']forresultinresults])运行结果如下：

['Harden','Jordan','Kevin','Mark','Mike']这里我们调用pymongo.ASCENDING指定升序。如果要降序排列，可以传入pymongo.DESCENDING。5414.4Python访问MongoDB数据库9.偏移在某些情况下，我们可能想只取某几个元素，这时可以利用skip()方法偏移几个位置，比如偏移2，就忽略前两个元素，得到第三个及以后的元素：results=collection.find().sort('name',pymongo.ASCENDING).skip(2)print([result['name']forresultinresults])运行结果如下：['Kevin','Mark','Mike']另外，还可以用limit()方法指定要取的结果个数，示例如下：results=collection.find().sort('name',pymongo.ASCENDING).skip(2).limit(2)pri