第9章 基于SQLite數(shù)據(jù)庫的數(shù)據(jù)采集

第9章基于SQLite數(shù)據(jù)庫的數(shù)據(jù)采集董付國微信公眾號：Python小屋本章學(xué)習(xí)目標(biāo)熟悉SQLite數(shù)據(jù)庫的結(jié)構(gòu)和特點了解使用可視化工具管理和操作SQLite數(shù)據(jù)庫的方法熟練掌握常用SQL語句熟練掌握從SQLite數(shù)據(jù)庫中讀取數(shù)據(jù)的相關(guān)技術(shù)9.1SQLite數(shù)據(jù)庫基礎(chǔ)SQLite是內(nèi)嵌在Python中的輕量級、基于磁盤文件的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，不需要安裝和配置服務(wù)器，支持使用SQL語句來訪問數(shù)據(jù)庫。該數(shù)據(jù)庫使用C語言開發(fā)，支持大多數(shù)SQL91標(biāo)準(zhǔn)，支持原子的、一致的、獨立的和持久的事務(wù)，不支持外鍵限制；通過數(shù)據(jù)庫級的獨占性和共享鎖定來實現(xiàn)獨立事務(wù)，當(dāng)多個線程同時訪問同一個數(shù)據(jù)庫并試圖寫入數(shù)據(jù)時，每一時刻只有一個線程可以寫入數(shù)據(jù)。默認(rèn)情況下，SQLite數(shù)據(jù)庫必須和相應(yīng)的服務(wù)端程序在同一臺服務(wù)器上，除非自己編寫專門的代理程序。SQLite支持最大140TB大小的單個數(shù)據(jù)庫，每個數(shù)據(jù)庫完全存儲在單個磁盤文件中，一個數(shù)據(jù)庫就是一個文件，通過直接復(fù)制數(shù)據(jù)庫文件就可以實現(xiàn)備份。如果需要使用可視化管理工具來操作SQLite數(shù)據(jù)庫，可以使用SQLiteManager、SQLiteDatabaseBrowser或其他類似工具。9.1SQLite數(shù)據(jù)庫基礎(chǔ)許多SQL數(shù)據(jù)庫引擎使用靜態(tài)、嚴(yán)格的數(shù)據(jù)類型，每個字段只能存儲指定類型的數(shù)據(jù)，而SQLite則使用更加通用的動態(tài)類型系統(tǒng)。SQLite的動態(tài)類型系統(tǒng)兼容靜態(tài)類型系統(tǒng)的數(shù)據(jù)庫引擎，每種數(shù)據(jù)類型的字段都可以支持多種類型的數(shù)據(jù)。在SQLite數(shù)據(jù)庫中，主要有以下幾種數(shù)據(jù)類型（或者說是存儲類別）：NULL:值為一個NULL空值。INTEGER:值被標(biāo)識為整數(shù)，依據(jù)值的大小可以依次被存儲為1,2,3,4,6或8個字節(jié)。REAL:所有值都是浮點數(shù)值，被存儲為8字節(jié)的IEEE浮點數(shù)。TEXT:值為文本字符串，使用數(shù)據(jù)庫編碼存儲，如UTF-8、UTF-16-BE或UTF-16-LE。BLOB:值是數(shù)據(jù)的二進(jìn)制對象，如何寫入就如何存儲，不改變格式。9.2標(biāo)準(zhǔn)庫sqlite3用法簡介Python標(biāo)準(zhǔn)庫sqlite3提供了SQLite數(shù)據(jù)庫訪問接口，不需要額外配置，連接數(shù)據(jù)庫之后可以使用SQL語句對數(shù)據(jù)進(jìn)行增、刪、改、查等操作。>>>importsqlite3>>>conn=sqlite3.connect('test.db')#連接或創(chuàng)建數(shù)據(jù)庫>>>cur=conn.cursor()#創(chuàng)建游標(biāo)>>>cur.execute('CREATETABLEtableTest(field1numeric,field2text)')#創(chuàng)建數(shù)據(jù)表<sqlite3.Cursorobjectat0x000001C7AB3B43B0>>>>data=zip(range(5),'abcde')>>>cur.executemany('INSERTINTOtableTestvalues(?,?)',data)#插入多條記錄#問號是占位符，執(zhí)行時被替換<sqlite3.Cursorobjectat0x000001C7AB3B43B0>>>>cur.execute('SELECT*FROMtableTestORDERBYfield1DESC')#查詢記錄<sqlite3.Cursorobjectat0x000001C7AB3B43B0>>>>forrecincur.fetchall():print(rec)(4,'e')(3,'d')(2,'c')(1,'b')(0,'a')>>>mit()#提交事務(wù)，保存數(shù)據(jù)>>>conn.close()9.2標(biāo)準(zhǔn)庫sqlite3用法簡介Connection對象常用方法方法說明backup(target,*,pages=-1,progress=None,name='main',sleep=0.25)備份當(dāng)前數(shù)據(jù)庫close()關(guān)閉數(shù)據(jù)庫連接commit()提交事務(wù)，如果不提交的話，那么自上次調(diào)用commit()方法之后的所有修改都不會真正保存到數(shù)據(jù)庫中create_function(name,num_params,func)把Python可調(diào)用對象轉(zhuǎn)換為可以在SQL語句中調(diào)用的函數(shù)，其中name為可以在SQL語句中調(diào)用的函數(shù)名，num_params表示該函數(shù)可以接收的參數(shù)個數(shù)，func表示Python可調(diào)用對象的名稱cursor()創(chuàng)建并返回游標(biāo)對象execute(sql,parameters=(),/)執(zhí)行一條SQL語句，SQL語句中的參數(shù)由parameters提供executemany(sql,parameters,/)重復(fù)執(zhí)行同一條SQL語句，每次執(zhí)行時SQL語句中的參數(shù)由parameters提供executescript(sql_script,/)一次執(zhí)行多條SQL語句rollback()撤銷事務(wù)，將數(shù)據(jù)庫恢復(fù)至上次調(diào)用commit()方法后的狀態(tài)9.2標(biāo)準(zhǔn)庫sqlite3用法簡介下面的代碼演示了把Python函數(shù)轉(zhuǎn)換為可以在SQL語句中調(diào)用的函數(shù)的用法，以及使用Connection對象的execute()方法執(zhí)行SQL語句并為SQL語句傳遞參數(shù)的一種方法——在SQL語句中使用問號作為占位符，執(zhí)行SQL語句時將其替換為execute()方法的第二個參數(shù)的值。code\9.2_1.pyimportsqlite3importhashlib#定義Python函數(shù)defmd5_sum(t):#標(biāo)準(zhǔn)庫函數(shù)hashlib.md5()用來計算一個字節(jié)串的MD5值returnhashlib.md5(t).hexdigest()#參數(shù)':memory:'表示在內(nèi)存中創(chuàng)建臨時數(shù)據(jù)庫#Connection對象支持上下文管理關(guān)鍵字withwithsqlite3.connect(':memory:')asconn:#把Python函數(shù)轉(zhuǎn)換為可以在SQL語句中調(diào)用的函數(shù)conn.create_function('md5',1,md5_sum)#SQL語句中的問號表示占位符，會被替換為execute()方法的第二個參數(shù)的值result=conn.execute('SELECTmd5(?)',['Python小屋'.encode()])#把查詢結(jié)果集轉(zhuǎn)換為列表，輸出MD5值計算結(jié)果#可以刪除其中的[0][0]并重新運(yùn)行程序，以幫助理解print(list(result)[0][0])9.2標(biāo)準(zhǔn)庫sqlite3用法簡介游標(biāo)對象的常用方法方法說明close()關(guān)閉當(dāng)前游標(biāo)對象execute(sql,parameters=(),/)執(zhí)行一條SQL語句，SQL語句中的參數(shù)由parameters提供executemany(sql,parameters,/)多次執(zhí)行同一條SQL語句，SQL語句中的參數(shù)由parameters提供executescript(sql_script,/)一次執(zhí)行多條SQL語句fetchall()返回查詢結(jié)果集中的所有行fetchmany(size=1)返回查詢結(jié)果集中的size行fetchone()返回查詢結(jié)果集中的1行9.2標(biāo)準(zhǔn)庫sqlite3用法簡介下面的代碼演示了游標(biāo)對象的execute()方法和fetchone()方法的用法，以及為SQL語句傳遞參數(shù)的另一種方法——使用變量名作為占位符，執(zhí)行SQL語句時將其替換為execute()第二個參數(shù)中同名變量的值。code\9.2_2.pyimportsqlite3withsqlite3.connect(':memory:')asconn:#創(chuàng)建游標(biāo)對象cur=conn.cursor()#執(zhí)行SQL語句，創(chuàng)建數(shù)據(jù)表cur.execute('CREATETABLEpeople(name_last,age)')who,age='董付國',45#執(zhí)行SQL語句，往數(shù)據(jù)表中寫入一條記錄#使用問號作為占位符，使用元組提交參數(shù)#同樣的用法也適用于Connection對象的execute()方法cur.execute('INSERTINTOpeopleVALUES(?,?)',(who,age))#使用變量作為占位符，使用字典提交參數(shù)#同樣的用法也適用于Connection對象的execute()方法cur.execute('SELECT*FROMpeopleWHEREname_last=:whoANDage=:age',

{'who':who,'age':age})#返回并輸出查詢結(jié)果集中的一條記錄print(cur.fetchone())#使用變量作為占位符，使用元組提交參數(shù)cur.execute('SELECT*FROMpeopleWHEREname_last=:whoANDage=:age',

(who,age))print(cur.fetchone())9.2標(biāo)準(zhǔn)庫sqlite3用法簡介下面的代碼演示了游標(biāo)對象的executemany()方法和fetchall()的用法，以及使用迭代器為SQL語句提交參數(shù)的用法。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),)#創(chuàng)建迭代器對象lowercase=IterChars()withsqlite3.connect(':memory:')asconn:cur=conn.cursor()cur.execute('CREATETABLElowercases(c)')#重復(fù)執(zhí)行SQL語句，每次執(zhí)行時的參數(shù)來自迭代器對象#SQL語句被執(zhí)行的次數(shù)取決于迭代器對象能夠生成的字母的數(shù)量#同樣的用法也適用于Connection對象的executemany()方法cur.executemany('INSERTINTOlowercases(c)VALUES(?)',lowercase)#讀取并顯示所有記錄cur.execute('SELECTcFROMlowercases')print(cur.fetchall())9.2標(biāo)準(zhǔn)庫sqlite3用法簡介下面的代碼演示了游標(biāo)對象的execute()方法、executemany()方法和fetchmany()方法的用法，以及使用生成器函數(shù)創(chuàng)建生成器對象并為SQL語句提交參數(shù)的用法。code\9.2_4.pyimportsqlite3importstringdefchar_generator():forcinstring.ascii_lowercase:yield(c,)withsqlite3.connect(':memory:')asconn:cur=conn.cursor()cur.execute('CREATETABLElowercases(c)')#使用生成器對象提供SQL語句需要的參數(shù)#同樣的用法也適用于Connection對象的executemany()方法cur.executemany('INSERTINTOlowercases(c)VALUES(?)',char_generator())cur.execute('SELECTcFROMlowercases')whileTrue:#每次最多讀取7條記錄result=cur.fetchmany(7)#如果返回的是空列表，表示已無數(shù)據(jù)，結(jié)束循環(huán)ifnotresult:break#輸出本次讀取到的記錄print(result)9.2標(biāo)準(zhǔn)庫sqlite3用法簡介下面的代碼演示了使用列表為SQL語句提交參數(shù)的用法，同樣的用法也適用于元組、字典、集合以及其他類型的可迭代對象。code\9.2_5.pyimportsqlite3persons=[('張','三'),('李','四'),('王','五')]withsqlite3.connect(':memory:')asconn:cur=conn.cursor()#創(chuàng)建數(shù)據(jù)表cur.execute('CREATETABLEperson(firstname,lastname)')#插入多條記錄#同樣的用法也適用于Connection對象的executemany()方法cur.executemany('INSERTINTOperson(firstname,lastname)VALUES(?,?)',persons)#查詢并顯示數(shù)據(jù)，同樣的用法也適用于Connection對象的execute()方法forrowincur.execute('SELECTfirstname,lastnameFROMperson'):print(row)#刪除數(shù)據(jù)，同樣的用法也適用于Connection對象的execute()方法print('刪除了',cur.execute('DELETEFROMperson').rowcount,'條記錄')9.3常用SQL語句（1）創(chuàng)建數(shù)據(jù)表可以使用CREATETABLE語句來創(chuàng)建數(shù)據(jù)表，并指定所有字段的名字、類型、是否允許為空以及是否為主鍵。CREATETABLEtablename(col1type1[NOTNULL][PRIMARYKEY],col2type2[NOTNULL],..)（2）刪除數(shù)據(jù)表DROPTABLEtablename（3）插入記錄可以使用INSERTINTO往數(shù)據(jù)表中插入記錄，同時設(shè)置指定字段的值。INSERTINTOtablename(field1,field2)VALUES(value1,value2)9.3常用SQL語句（4）查詢記錄從指定的數(shù)據(jù)表中查詢并返回字段field1大于value1的那些記錄的所有字段：SELECT*FROMtablenameWHEREfield1>value1模糊查詢，返回字段field1中包含字符串value1的那些記錄的3個字段：SELECTfield1,field2,field3FROMtablenameWHEREfield1LIKE'%value1%'查詢并返回字段field1的值介于value1和value2之間的那些記錄的所有字段：SELECT*FROMtablenameWHEREfield1BETWEENvalue1ANDvalue2查詢并返回所有記錄所有字段，按字段field1升序、field2降序排列：SELECT*FROMtablenameORDERBYfield1,field2DESC查詢并返回數(shù)據(jù)表中所有記錄總數(shù)：SELECTCOUNT(*)AStotalcountFROMtablename9.3常用SQL語句對數(shù)據(jù)表中指定字段field1的值進(jìn)行求和：SELECTSUM(field1)ASsumvalueFROMtablename對數(shù)據(jù)表中指定字段field1的值求平均：SELECTAVG(field1)ASavgvalueFROMtablename對數(shù)據(jù)表中指定字段field1的值求最大值、最小值：SELECTMAX(field1)ASmaxvalueFROMtablenameSELECTMIN(field1)ASminvalueFROMtablename查詢并返回數(shù)據(jù)表中符合條件的前10條記錄：SELECTTOP10*FROMtablenameWHEREfield1LIKE'%value1%'ORDERBYfield1或（SQLite語法）SELECT*FROMtablenameWHEREfield1LIKE'%value1%'ORDERBYfield1LIMIT109.3常用SQL語句（5）更新記錄可以使用UPDATE語句來更新數(shù)據(jù)表中符合條件的那些記錄指定字段的值，如果不指定條件則默認(rèn)把所有記錄的指定字段都修改為指定的值，一定要慎重操作。UPDATEtablenameSETfield1=value1,field2=value2WHEREfield3=value3（6）刪除記錄可以使用DELETE語句來刪除符合條件的記錄，如果不指定條件則默認(rèn)刪除數(shù)據(jù)表中所有記錄，一定要慎重操作。DELETEFROMtablenameWHEREfield1=value1andfield2=value29.4案例精選例9-1編寫程序，統(tǒng)計給定的SQLite數(shù)據(jù)庫中所有用戶級數(shù)據(jù)表中的記錄數(shù)量，返回一個元組。元組中第一個數(shù)字為所有用戶級數(shù)據(jù)表中記錄數(shù)量之和，然后是每個數(shù)據(jù)表中包含的記錄數(shù)量。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