郭煒信息科學(xué)技術(shù)學(xué)院1

微博：/guoweiofpku

學(xué)會程序和算法，走遍天下都不怕!實(shí)用Python程序設(shè)計Python數(shù)據(jù)庫編程信息科學(xué)技術(shù)學(xué)院郭煒2數(shù)據(jù)庫和SQL信息科學(xué)技術(shù)學(xué)院黃山數(shù)據(jù)庫的概念數(shù)據(jù)庫可以用來存放大量數(shù)據(jù)，并且提供了方便的快速檢索手段便于快速找出符合某種條件的數(shù)據(jù)。比如：工作年限超過三年，工資超過10000元的北京籍員工一個數(shù)據(jù)庫可以是一個文件，比如c:/tmp/students.db數(shù)據(jù)庫中的表一個數(shù)據(jù)庫文件里可以有多張表。比如students.db

里包含"學(xué)生信息表"和"課程信息表"表由記錄構(gòu)成，比如“學(xué)生信息表”里的每個記錄，代表一個學(xué)生的信息。記錄由字段構(gòu)成，描述一個事物的多個屬性。比如學(xué)生記錄，可以由name,id，age,gender，gpa

等字段構(gòu)成字段

字段是有"類型"的。比如字段名

數(shù)據(jù)類型name text 字符串gpa real 小數(shù)age integer 整數(shù)profile text photo blob 二進(jìn)制數(shù)據(jù)（如圖片)birthday date 日期(本質(zhì)上就是text)registertime datetime

日期+時間(本質(zhì)上就是text)..........7SQL數(shù)據(jù)庫查詢語句參考：/sql/sql_syntax.aspSQL命令：用于進(jìn)行數(shù)據(jù)庫操作的標(biāo)準(zhǔn)語句CREATETABLE 創(chuàng)建表INSERTINTO 在表中插入記錄UPDATE 在表中更新記錄SELECT 在表中進(jìn)行查詢DELETE 從表中刪除記錄不區(qū)分大小寫8CREATECREATETABLEifnotexistsstudents(idintegerprimarykey,

nametext,gpareal,birthdaydate,ageinteger,pictureblob)創(chuàng)建了一張名為students的表，有以下字段:字段名

數(shù)據(jù)類型id integer primarykey表示不可重復(fù)name text 字符串gpa real 小數(shù)birthday date 日期(本質(zhì)上就是text)age integer 整數(shù)picture blob 二進(jìn)制數(shù)據(jù)（如圖片)9INSERTINSERTINTOstudentsVALUES(1000,'張三',3.81,'2000-09-12',18,null)在表students中插入一個記錄，該記錄暫無照片(null)VALUES(每個字段的值）創(chuàng)建數(shù)據(jù)庫信息科學(xué)技術(shù)學(xué)院長城入海處：老龍頭創(chuàng)建數(shù)據(jù)庫并寫入數(shù)據(jù)importsqlite3db=sqlite3.connect("c:/tmp/test2.db")#連接數(shù)據(jù)庫，若不存在則自動創(chuàng)建#文件夾c:/tmp必須事先存在,connect不會創(chuàng)建文件夾cur=db.cursor()#獲取光標(biāo)，要操作數(shù)據(jù)庫一般要通過光標(biāo)進(jìn)行sql='''CREATETABLEifnotexistsstudents(idintegerprimarykey,nametext,gpareal,birthdaydate,ageinteger,pictureblob)'''#如果表students不存在就創(chuàng)建它c(diǎn)ur.execute(sql)#執(zhí)行SQL命令cur.execute("insertintostudentsvalues(1600,'張三',3.81,'2000-09-12',18,null)")#插入一個記錄mylist=[(1700,'李四',"3.25",'2001-12-01',17,None),(1800,'王五',"3.35",'1999-01-01',19,None)]forsinmylist:#依次插入mylist中的每個記錄cur.execute('INSERTINTOstudentsVALUES(?,?,?,?,?,?)',(s[0],s[1],s[2],s[3],s[4],s[5]))#?對應(yīng)于后面某項mit()#真正寫入，寫入操作都需要這個cur.close()#關(guān)閉光標(biāo)db.close()#關(guān)閉數(shù)據(jù)庫數(shù)據(jù)庫的查詢和修改信息科學(xué)技術(shù)學(xué)院云崗石窟13SELECTSELECT*FROMstudents

檢索students表中全部記錄SELECT*FROMstudentsORDERBYage

檢索students表中全部記錄，并按年齡排序SELECTname,ageFROMstudents檢索students表中全部記錄，但每個記錄只取name和age字段SELECT*FROMstudentsWHEREname='張三'檢索students表中全部name字段為張三的記錄WHERE表示檢索條件SELECT*FROMstudentsWHEREname='張三'ANDage>20ORDERBYage

DESC

檢索students表中全部名為張三且年齡大于20的人，結(jié)果按年齡降序排列檢索數(shù)據(jù)庫importsqlite3db=sqlite3.connect("c:/tmp/test2.db")cur=db.cursor()sql='select*fromstudents'#檢索全部記錄cur.execute(sql)x=cur.fetchone()#fetchone取滿足條件的第一條記錄print(x) #=>(1600,'張三',3.81,'2000-09-12',18,None)print(x[1]) #=>張三forxincur.fetchall():#fetchall取得所有滿足條件的記錄

print(x[:-2])#age和picture字段不打出cur.execute("SELECT*FROMstudentsWHEREname='Jack'")x=cur.fetchone()ifx==None:print("can'tfindJack")cur.close()db.close()(1600,'張三',3.81,'2000-09-12',18,None)張三(1700,'李四',3.25,'2001-12-01')(1800,'王五',3.35,'1999-01-01')can'tfindJack檢索數(shù)據(jù)庫importsqlite3db=sqlite3.connect("c:/tmp/test2.db")cur=db.cursor()sql='selectname,gpa,agefromstudentswheregpa>3.3orderbyagedesc'#查找gpa>3.3的記錄，題取其中三個字段，按年齡降序排列cur.execute(sql)x=cur.fetchall()ifx!=[]:print("total:",len(x))#=>2forrinx:print(r)cur.close()db.close()total:2('王五',3.35,19)('張三',3.81,18)16UPDATEUPDATEstudentsSETgpa=3.9將所有記錄的gpa設(shè)置成3.9

UPDATEstudentsSETgpa=3.9,age=18WHEREname='李四'

修改李四的gpa和年齡17UPDATEimportsqlite3db=sqlite3.connect("c:/tmp/test2.db")cur=db.cursor()sql='UPDATEstudentsSETgpa=?,age=?WHEREname=?'cur.execute(sql,(4.0,20,'李四'))#元組三個元素分別對應(yīng)三個?#修改李四的gpa和年齡。若李四不存在，則無效果mit()#寫入操作必須cur.close()db.close()18DELETEDELETEFROMstudentsWHEREage<18

刪除年齡小于18的記錄

DELETEFROMstudents

刪除全部記錄別忘了最后commit19DROPTABLEDROPTABLEIFEXISTSstudents

刪除students表別忘了最后commit20DROPTABLEimportsqlite3db=sqlite3.connect("c:/tmp/test2.db")cur=db.cursor()cur.execute("DROPTABLEIFEXISTSstudents")mit()try:cur.execute("select*fromstudents")x=cur.fetchall()forrinx:print(r[:-1])except:print("notable")#=>notablecur.close()db.close()21列出數(shù)據(jù)庫中所有的表和表的結(jié)構(gòu)importsqlite3db=sqlite3.connect("c:/tmp/test3.db")cur=db.cursor()sql='CREATETABLEifnotexiststable2(idinteger,nametext)'cur.execute(sql)#執(zhí)行SQL命令sql='CREATETABLEifnotexiststable1(idinteger,schooktext)'cur.execute(sql)mit()cur.execute('selectnamefromSQLITE_MASTERwheretype="table"orderbyNAME')x=cur.fetchall()ifx!=[]:print(x)cur.execute("PRAGMATABLE_INFO(table1)")print(cur.fetchall())cur.close()db.close()[('table1',),('table2',)][(0,'id','integer',0,None,0),(1,'schook','text',0,None,0)]22注意事項對于修改表的操作，如插入，刪除，更新，關(guān)閉數(shù)據(jù)庫前不要忘了commit，否則

可能無效2.必要時用try...except語句來避免數(shù)據(jù)庫不存在，表不存在時的導(dǎo)致的runtimeerror處理二進(jìn)制字段信息科學(xué)技術(shù)學(xué)院大連金石灘24設(shè)置blob字段（二進(jìn)制字段)的值：importsqlite3importrequestsf=open('c:/tmp/tmp.jpg','rb')#二進(jìn)制方式打開圖片img=f.read()f.close()db=sqlite3.connect("c:/tmp/test2.db")cur=db.cursor()sql="UPDATEstudentsSETpicture=?WHEREname='李四'"cur.execute(sql,(img,))#設(shè)置李四的照片。img對應(yīng)于?25設(shè)置blob字段（二進(jìn)制字段)的值：imgUrl="/uploads/item/201605/19/20160519224441_VfMWR.