课件0904-SQLite数据库的使用

SQLite数据库的使用——网络爬虫库的应用制作：教材编写团队出版社：机械工业出版社《Python编程基础与应用》配套课件本课件为《Python编程基础与应用（第2版）》配套教学资源，由编写团队精心打造。为便于教学使用，团队同步提供了丰富的辅助材料，涵盖微课视频、教学课件、实训手册、习题答案、课程标准及程序源代码等。同时，教材配套超星教学示范包，支持一键克隆为网络在线课程，助力高效开展线上线下混合式教学。欢迎联系出版方订购使用。

书名：《Python编程基础与应用（第2版）》出版社：机械工业出版社主编：蓝永健、陈冬冬副主编：蔡德琛、朱海鑫、邓爱玲+contents目录SQLite数据库的简介和常用APISQLite数据库的使用和示范SQLite数据库的简介和常用API01SQLite的简介SQLite是一款轻型的数据库，是遵守ACID的关系型数据库管理系统。SQLite的官方网站为/index.html。SQLite是一个轻量级的嵌入式关系型数据库，具有以下特点：无服务器、零配置事务性、符合ACID跨平台（支持Windows/Linux/Mac等）单一磁盘文件存储整个数据库支持标准SQL语法开源免费SQLite的简介SQLite可使用sqlite3模块与Python进行集成。它提供了一个与PEP249描述的DB-API2.0规范兼容的SQL接口。不需要单独安装该模块，因为Python2.5.x以上版本默认自带了该模块。它的一些常用的常量、函数和对象如下：1)Sqlite3.version#常量，版本号。2)sqlite3.Connect(database)#函数，连接数据库，返回Connect对象。3)sqlite3.Connect#数据库连接对象4)sqlite3.Cursor#游标对象5)sqlite3.Row#行对象sqlite3的主要API及描述序号API及描述1sqlite3.connect(database[,timeout,otheroptionalarguments])该API打开一个到SQLite数据库文件database的链接。如果数据库成功打开，则返回一个连接对象。timeout参数表示连接等待锁定的持续时间，直到发生异常断开连接。timeout参数默认是5.0（秒）。如果给定的数据库名称database不存在，则该调用将创建一个数据库。如果您不想在当前目录中创建数据库，那么您可以指定带有路径的文件名。2connection.cursor([cursorClass])该例程创建一个cursor，将在Python数据库编程中用到。该方法接受一个单一的可选的参数cursorClass。如果提供了该参数，则它必须是一个扩展自sqlite3.Cursor的自定义的cursor类。3cursor.execute(sql[,optionalparameters])该例程执行一个SQL语句。该SQL语句可以被参数化。sqlite3模块支持两种类型的占位符：问号和命名占位符（命名样式）。例如：cursor.execute("insertintopeoplevalues(?,?)",(who,age))4connection.execute(sql[,optionalparameters])该例程是上面执行的由游标（cursor）对象提供的方法的快捷方式，它通过调用游标（cursor）方法创建了一个中间的游标对象，然后通过给定的参数调用游标的execute方法。sqlite3的主要API及描述序号API及描述5connection.total_changes()该例程返回自数据库连接打开以来被修改、插入或删除的数据库总行数。6mit()该方法提交当前的事务。如果您未调用该方法，那么自您上一次调用commit()以来所做的任何动作对其他数据库连接来说是不可见的。7connection.rollback()该方法回滚自上一次调用commit()以来对数据库所做的更改。8connection.close()该方法关闭数据库连接。请注意，这不会自动调用commit()。如果您之前未调用commit()方法，就直接关闭数据库连接，您所做的所有更改将全部丢失！9cursor.fetchone()该方法获取查询结果集中的下一行，返回一个单一的序列，当没有更多可用的数据时，则返回None。10cursor.fetchmany([size=cursor.arraysize])该方法获取查询结果集中的下一行组，返回一个列表。当没有更多的可用的行时，则返回一个空的列表。该方法尝试获取由size参数指定的尽可能多的行。11cursor.fetchall()该例程获取查询结果集中所有（剩余）的行，返回一个列表。当没有可用的行时，则返回一个空的列表。SQLite的使用流程打开数据库:sqlite3_open()准备SQL语句:sqlite3_prepare_v2()绑定参数(可选):sqlite3_bind_*()执行语句:sqlite3_step()获取结果(查询时):sqlite3_column_*()释放预处理语句:sqlite3_finalize()关闭数据库:sqlite3_close()SQLite的示例——创建/连接数据库importsqlite3#连接数据库（如果不存在会自动创建）conn=sqlite3.connect('example.db')#创建游标对象cursor=conn.cursor()#操作完成后记得关闭连接conn.close()SQLite的示例——创建表cursor.execute('''CREATETABLEIFNOTEXISTSusers(idINTEGERPRIMARYKEYAUTOINCREMENT,nameTEXTNOTNULL,ageINTEGER,emailTEXTUNIQUE)''')mit()#提交事务SQLite的示例——删除表cursor.execute("DROPTABLEIFEXISTSusers")mit()SQLite的示例——插入数据#插入单条数据cursor.execute("INSERTINTOusers(name,age,email)VALUES(?,?,?)",('张三',25,'zhangsan@'))#插入多条数据users=[('李四',30,'lisi@'),('王五',28,'wangwu@')]cursor.executemany("INSERTINTOusers(name,age,email)VALUES(?,?,?)",users)mit()#不要忘记提交SQLite的示例——查询数据#查询所有数据cursor.execute("SELECT*FROMusers")print(cursor.fetchall())#获取所有结果#查询单条数据cursor.execute("SELECT*FROMusersWHEREname=?",('张三',))print(cursor.fetchone())#获取一条结果#带条件查询cursor.execute("SELECTname,ageFROMusersWHEREage>?",(25,))forrowincursor:print(f"姓名:{row[0]},年龄:{row[1]}")SQLite的示例——更新数据cursor.execute("UPDATEusersSETage=?WHEREname=?",(26,'张三'))print(f"更新了{cursor.rowcount}行数据")mit()SQLite的示例——删除数据cursor.execute("DELETEFROMusersWHEREid=?",(3,))mit()SQLite的示例——事务处理try:

#开始事务（Python中默认每条execute都是一个事务）

cursor.execute("INSERTINTOusers(name)VALUES('赵六')")cursor.execute("UPDATEusersSETage=30WHEREname='赵六'")mit()#提交事务except:conn.rollback()#回滚事务

print("操作失败，已回滚")SQLite的示例——获取最后插入的IDcursor.execute("INSERTINTOusers(name)VALUES('钱七')")last_id=cursor.lastrowidprint(f"最后插入的ID:{last_id}")mit()SQLite的示例——使用字典形式返回结果conn.row_factory=sqlite3.Row#设置行工厂cursor=conn.cursor()cursor.execute("SELECT*FROMusersWHEREid=?",(1,))row=cursor.fetchone()print(f"ID:{row['id']},姓名:{row['name']}")#像字典一样访问SQLite的示例——批量插入大量数据（高效方式）data=[(f'用户{i}',20+i,f'user{i}@')foriinrange(1000)]withsqlite3.connect('example.db')asconn:cursor=conn.cursor()cursor.executemany("INSERTINTOusers(name,age,email)VALUES(?,?,?)",data)mit()记得始终在修改数据后调用commit()。SQLite数据库的使用和示范02范例11-8使用sqlite3创建数据库和数据表创建数据库名称为“Student.db”，存放路径为“D:\pythonBook\pythonProject10\SQLite3DB”。在数据库“Student.db”中创建数据表为“tbuser”，它的表结构如下所示字段名称类型备注IdIntId号，主键，不为空UserCodeVARCHAR(20)用户帐号，不为空UserNameVARCHAR(20)用户姓名，不为空范例11-8使用sqlite3创建数据库和数据表它的代码如下：importsqlite3#创建数据库Student.dbconn=sqlite3.connect(r'D:\SQLite3DB\Student.db')print("数据库Student.db创建成功")c=conn.cursor()#cursor()使用该连接创建（并返回）一个游标对象c.execute('''CREATETABLEtbuser(IDINTPRIMARYKEYNOTNULL,UserCodeVARCHAR(20)NOTNULL,UserNameVARCHAR(20)NOTNULL);''')#创建数据表tbuserprint("数据表tbuser创建成功")mit()#提交操作conn.close()#关闭Connection对象

范例11-8使用sqlite3创建数据库和数据表它的运行结果如下：数据库Student.db创建成功数据表tbuser创建成功范例11-9sqlite3进行增、删、改操作数据库中的数据需要在不同的程序中进行调用，最为常见的操作是“增、删、改、查”四种操作。在数据库中进行数据的增加、删除和修改操作，它的一般步骤如下：1）建立数据库的连接。2）创建游标对象。3）根据SQL的Insert，Delete和Update语句，使用Connection.execute(sql)执行数据的增加、删除和修改操作，并根据返回的值判断操作结果。4）提交操作。5）关闭数据库连接对象。把以下数据插入到数据表“tbuser”中，并对其中的一些数据进行修改和删除操作。范例11-9sqlite3进行增、删、改操作数据如下：IDuserCodeuserNameIDuserCodeuserName110001刘小红610006金奇210002王小林710007周泽310003朱梦810008朱秀娟410004金烨伟910009姜子文510005王婷1010010秦家兴范例11-9sqlite3进行增、删、改操作代码（1）importsqlite3#连接数据库conn=sqlite3.connect(r'D:\pythonBook\pythonProject10\SQLite3DB\Student.db')cur=conn.cursor()#创建游标对象#删除全部数据sqltxt0="deletefromtbuser"cur.execute(sqltxt0)print("成功删除一个表的数据")#插入一行数据（第1种方法）sqltxt1="insertintotbuser(id,UserCode,UserName)values(1,'10001','刘小红')"cur.execute(sqltxt1)print("成功插入一行数据（第1种方法）")范例11-9sqlite3进行增、删、改操作代码（2）#插入一行数据（第2种方法）sqltxt2="insertintotbuser(id,UserCode,UserName)values(?,?,?)"cur.execute(sqltxt2,(2,'10002','王小林'))print("成功插入一行数据（第2种方法）")#插入多行数据（第1种方法）sqltxt3="insertintotbuser(id,UserCode,UserName)values(3,'10003','朱梦'),(4,'10004','金烨伟'),(5,'10005','王婷'),(6,'10006','金奇')"cur.execute(sqltxt3)print("成功插入多行数据（第1种方法）")范例11-9sqlite3进行增、删、改操作代码（3）#插入多行数据（第2种方法）userList=[(7,'10007','周泽'),(8,'10008','朱秀娟'),(9,'10009','姜子文'),(10,'10010','秦家兴')]sqltxt3="insertintotbuser(id,UserCode,UserName)values(?,?,?)"cur.executemany(sqltxt3,userList)print("成功插入多行数据（第2种方法）")#修改一行数据sqltxt4="updatetbusersetUserName='刘丽红'whereid=1"cur.execute(sqltxt4)print("成功修改一行数据")范例11-9sqlite3进行增、删、改操作代码（4）#删除一行数据sqltxt5="deletefromtbuserwhereid=2"cur.execute(sqltxt5)print("成功删除一行数据")mit()#提交操作conn.close()#关闭Connection对象范例11-9sqlite3进行增、删、改操作结果如下成功删除全部数据成功插入一行数据（第1种方法）成功插入一行数据（第2种方法）成功插入多行数据（第1种方法）成功插入多行数据（第2种方法）成功修改一行数据成功删除一行数据范例11-10sqlite3进行数据的查询操作在数据库中进行数据的查询操作，它的一般步骤也与上面提到的“增、删、改”操作步骤类似，其中第三步改成Select查询语句即可，第四步则通常使用循环读取数据内容。#连接数据库conn=sqlite3.connect(r'D:\pythonBook\pythonProject10\SQLite3DB\Student.db')cur=conn.cursor()#创建游标对象#查