第9章 基于SQLite数据库的数据采集

第9章基于SQLite数据库的数据采集董付国微信公众号：Python小屋本章学习目标熟悉SQLite数据库的结构和特点了解使用可视化工具管理和操作SQLite数据库的方法熟练掌握常用SQL语句熟练掌握从SQLite数据库中读取数据的相关技术9.1SQLite数据库基础SQLite是内嵌在Python中的轻量级、基于磁盘文件的关系型数据库管理系统，不需要安装和配置服务器，支持使用SQL语句来访问数据库。该数据库使用C语言开发，支持大多数SQL91标准，支持原子的、一致的、独立的和持久的事务，不支持外键限制；通过数据库级的独占性和共享锁定来实现独立事务，当多个线程同时访问同一个数据库并试图写入数据时，每一时刻只有一个线程可以写入数据。默认情况下，SQLite数据库必须和相应的服务端程序在同一台服务器上，除非自己编写专门的代理程序。SQLite支持最大140TB大小的单个数据库，每个数据库完全存储在单个磁盘文件中，一个数据库就是一个文件，通过直接复制数据库文件就可以实现备份。如果需要使用可视化管理工具来操作SQLite数据库，可以使用SQLiteManager、SQLiteDatabaseBrowser或其他类似工具。9.1SQLite数据库基础许多SQL数据库引擎使用静态、严格的数据类型，每个字段只能存储指定类型的数据，而SQLite则使用更加通用的动态类型系统。SQLite的动态类型系统兼容静态类型系统的数据库引擎，每种数据类型的字段都可以支持多种类型的数据。在SQLite数据库中，主要有以下几种数据类型（或者说是存储类别）：NULL:值为一个NULL空值。INTEGER:值被标识为整数，依据值的大小可以依次被存储为1,2,3,4,6或8个字节。REAL:所有值都是浮点数值，被存储为8字节的IEEE浮点数。TEXT:值为文本字符串，使用数据库编码存储，如UTF-8、UTF-16-BE或UTF-16-LE。BLOB:值是数据的二进制对象，如何写入就如何存储，不改变格式。9.2标准库sqlite3用法简介Python标准库sqlite3提供了SQLite数据库访问接口，不需要额外配置，连接数据库之后可以使用SQL语句对数据进行增、删、改、查等操作。>>>importsqlite3>>>conn=sqlite3.connect('test.db')#连接或创建数据库>>>cur=conn.cursor()#创建游标>>>cur.execute('CREATETABLEtableTest(field1numeric,field2text)')#创建数据表<sqlite3.Cursorobjectat0x000001C7AB3B43B0>>>>data=zip(range(5),'abcde')>>>cur.executemany('INSERTINTOtableTestvalues(?,?)',data)#插入多条记录#问号是占位符，执行时被替换<sqlite3.Cursorobjectat0x000001C7AB3B43B0>>>>cur.execute('SELECT*FROMtableTestORDERBYfield1DESC')#查询记录<sqlite3.Cursorobjectat0x000001C7AB3B43B0>>>>forrecincur.fetchall():print(rec)(4,'e')(3,'d')(2,'c')(1,'b')(0,'a')>>>mit()#提交事务，保存数据>>>conn.close()9.2标准库sqlite3用法简介Connection对象常用方法方法说明backup(target,*,pages=-1,progress=None,name='main',sleep=0.25)备份当前数据库close()关闭数据库连接commit()提交事务，如果不提交的话，那么自上次调用commit()方法之后的所有修改都不会真正保存到数据库中create_function(name,num_params,func)把Python可调用对象转换为可以在SQL语句中调用的函数，其中name为可以在SQL语句中调用的函数名，num_params表示该函数可以接收的参数个数，func表示Python可调用对象的名称cursor()创建并返回游标对象execute(sql,parameters=(),/)执行一条SQL语句，SQL语句中的参数由parameters提供executemany(sql,parameters,/)重复执行同一条SQL语句，每次执行时SQL语句中的参数由parameters提供executescript(sql_script,/)一次执行多条SQL语句rollback()撤销事务，将数据库恢复至上次调用commit()方法后的状态9.2标准库sqlite3用法简介下面的代码演示了把Python函数转换为可以在SQL语句中调用的函数的用法，以及使用Connection对象的execute()方法执行SQL语句并为SQL语句传递参数的一种方法——在SQL语句中使用问号作为占位符，执行SQL语句时将其替换为execute()方法的第二个参数的值。code\9.2_1.pyimportsqlite3importhashlib#定义Python函数defmd5_sum(t):#标准库函数hashlib.md5()用来计算一个字节串的MD5值returnhashlib.md5(t).hexdigest()#参数':memory:'表示在内存中创建临时数据库#Connection对象支持上下文管理关键字withwithsqlite3.connect(':memory:')asconn:#把Python函数转换为可以在SQL语句中调用的函数conn.create_function('md5',1,md5_sum)#SQL语句中的问号表示占位符，会被替换为execute()方法的第二个参数的值result=conn.execute('SELECTmd5(?)',['Python小屋'.encode()])#把查询结果集转换为列表，输出MD5值计算结果#可以删除其中的[0][0]并重新运行程序，以帮助理解print(list(result)[0][0])9.2标准库sqlite3用法简介游标对象的常用方法方法说明close()关闭当前游标对象execute(sql,parameters=(),/)执行一条SQL语句，SQL语句中的参数由parameters提供executemany(sql,parameters,/)多次执行同一条SQL语句，SQL语句中的参数由parameters提供executescript(sql_script,/)一次执行多条SQL语句fetchall()返回查询结果集中的所有行fetchmany(size=1)返回查询结果集中的size行fetchone()返回查询结果集中的1行9.2标准库sqlite3用法简介下面的代码演示了游标对象的execute()方法和fetchone()方法的用法，以及为SQL语句传递参数的另一种方法——使用变量名作为占位符，执行SQL语句时将其替换为execute()第二个参数中同名变量的值。code\9.2_2.pyimportsqlite3withsqlite3.connect(':memory:')asconn:#创建游标对象cur=conn.cursor()#执行SQL语句，创建数据表cur.execute('CREATETABLEpeople(name_last,age)')who,age='董付国',45#执行SQL语句，往数据表中写入一条记录#使用问号作为占位符，使用元组提交参数#同样的用法也适用于Connection对象的execute()方法cur.execute('INSERTINTOpeopleVALUES(?,?)',(who,age))#使用变量作为占位符，使用字典提交参数#同样的用法也适用于Connection对象的execute()方法cur.execute('SELECT*FROMpeopleWHEREname_last=:whoANDage=:age',

{'who':who,'age':age})#返回并输出查询结果集中的一条记录print(cur.fetchone())#使用变量作为占位符，使用元组提交参数cur.execute('SELECT*FROMpeopleWHEREname_last=:whoANDage=:age',

(who,age))print(cur.fetchone())9.2标准库sqlite3用法简介下面的代码演示了游标对象的executemany()方法和fetchall()的用法，以及使用迭代器为SQL语句提交参数的用法。code\9.2_3.pyimportsqlite3#定义迭代器，按顺序生成小写字母classIterChars:def__init__(self):self.count=ord('a')-1def__iter__(self):returnselfdef__next__(self):ifself.count>=ord('z'):raiseStopIterationself.count+=1return(chr(self.count),)#创建迭代器对象lowercase=IterChars()withsqlite3.connect(':memory:')asconn:cur=conn.cursor()cur.execute('CREATETABLElowercases(c)')#重复执行SQL语句，每次执行时的参数来自迭代器对象#SQL语句被执行的次数取决于迭代器对象能够生成的字母的数量#同样的用法也适用于Connection对象的executemany()方法cur.executemany('INSERTINTOlowercases(c)VALUES(?)',lowercase)#读取并显示所有记录cur.execute('SELECTcFROMlowercases')print(cur.fetchall())9.2标准库sqlite3用法简介下面的代码演示了游标对象的execute()方法、executemany()方法和fetchmany()方法的用法，以及使用生成器函数创建生成器对象并为SQL语句提交参数的用法。code\9.2_4.pyimportsqlite3importstringdefchar_generator():forcinstring.ascii_lowercase:yield(c,)withsqlite3.connect(':memory:')asconn:cur=conn.cursor()cur.execute('CREATETABLElowercases(c)')#使用生成器对象提供SQL语句需要的参数#同样的用法也适用于Connection对象的executemany()方法cur.executemany('INSERTINTOlowercases(c)VALUES(?)',char_generator())cur.execute('SELECTcFROMlowercases')whileTrue:#每次最多读取7条记录result=cur.fetchmany(7)#如果返回的是空列表，表示已无数据，结束循环ifnotresult:break#输出本次读取到的记录print(result)9.2标准库sqlite3用法简介下面的代码演示了使用列表为SQL语句提交参数的用法，同样的用法也适用于元组、字典、集合以及其他类型的可迭代对象。code\9.2_5.pyimportsqlite3persons=[('张','三'),('李','四'),('王','五')]withsqlite3.connect(':memory:')asconn:cur=conn.cursor()#创建数据表cur.execute('CREATETABLEperson(firstname,lastname)')#插入多条记录#同样的用法也适用于Connection对象的executemany()方法cur.executemany('INSERTINTOperson(firstname,lastname)VALUES(?,?)',persons)#查询并显示数据，同样的用法也适用于Connection对象的execute()方法forrowincur.execute('SELECTfirstname,lastnameFROMperson'):print(row)#删除数据，同样的用法也适用于Connection对象的execute()方法print('删除了',cur.execute('DELETEFROMperson').rowcount,'条记录')9.3常用SQL语句（1）创建数据表可以使用CREATETABLE语句来创建数据表，并指定所有字段的名字、类型、是否允许为空以及是否为主键。CREATETABLEtablename(col1type1[NOTNULL][PRIMARYKEY],col2type2[NOTNULL],..)（2）删除数据表DROPTABLEtablename（3）插入记录可以使用INSERTINTO往数据表中插入记录，同时设置指定字段的值。INSERTINTOtablename(field1,field2)VALUES(value1,value2)9.3常用SQL语句（4）查询记录从指定的数据表中查询并返回字段field1大于value1的那些记录的所有字段：SELECT*FROMtablenameWHEREfield1>value1模糊查询，返回字段field1中包含字符串value1的那些记录的3个字段：SELECTfield1,field2,field3FROMtablenameWHEREfield1LIKE'%value1%'查询并返回字段field1的值介于value1和value2之间的那些记录的所有字段：SELECT*FROMtablenameWHEREfield1BETWEENvalue1ANDvalue2查询并返回所有记录所有字段，按字段field1升序、field2降序排列：SELECT*FROMtablenameORDERBYfield1,field2DESC查询并返回数据表中所有记录总数：SELECTCOUNT(*)AStotalcountFROMtablename9.3常用SQL语句对数据表中指定字段field1的值进行求和：SELECTSUM(field1)ASsumvalueFROMtablename对数据表中指定字段field1的值求平均：SELECTAVG(field1)ASavgvalueFROMtablename对数据表中指定字段field1的值求最大值、最小值：SELECTMAX(field1)ASmaxvalueFROMtablenameSELECTMIN(field1)ASminvalueFROMtablename查询并返回数据表中符合条件的前10条记录：SELECTTOP10*FROMtablenameWHEREfield1LIKE'%value1%'ORDERBYfield1或（SQLite语法）SELECT*FROMtablenameWHEREfield1LIKE'%value1%'ORDERBYfield1LIMIT109.3常用SQL语句（5）更新记录可以使用UPDATE语句来更新数据表中符合条件的那些记录指定字段的值，如果不指定条件则默认把所有记录的指定字段都修改为指定的值，一定要慎重操作。UPDATEtablenameSETfield1=value1,field2=value2WHEREfield3=value3（6）删除记录可以使用DELETE语句来删除符合条件的记录，如果不指定条件则默认删除数据表中所有记录，一定要慎重操作。DELETEFROMtablenameWHEREfield1=value1andfield2=value29.4案例精选例9-1编写程序，统计给定的SQLite数据库中所有用户级数据表中的记录数量，返回一个元组。元组中第一个数字为所有用户级数据表中记录数量之和，然后是每个数据表中包含的记录数量。importsqlite3defmain(database_path):result=[0]withsqlite3.connect(database_path)asconn:foriteminconn.execute('SELECT*FROMsqlite_master'):ifitem[0]!='table':continuetable_name=item[1]sql=f'SELECTCOUNT(*)FROM{table_name}'(num,),=*conn.execute(sql),result.append(num)result[0]=result[0]+numreturntup