Python語(yǔ)言程序設(shè)計(jì)（微課版）-課件 CH13-數(shù)據(jù)庫(kù)操作（寬屏版）

第13章

數(shù)據(jù)庫(kù)程序設(shè)計(jì)1.SQLite數(shù)據(jù)庫(kù)3.Python操作數(shù)據(jù)庫(kù)2.SQL語(yǔ)句簡(jiǎn)介4.綜合案例主要內(nèi)容1SQLite數(shù)據(jù)庫(kù)SQLite數(shù)據(jù)庫(kù)SQLite是一款輕量級(jí)的關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)主要應(yīng)用場(chǎng)景：作為手機(jī)應(yīng)用的數(shù)據(jù)庫(kù)以及小型桌面軟件的數(shù)據(jù)庫(kù)SQLite安裝官方下載地址：/download.htmlAnaconda中自動(dòng)集成SQLite庫(kù)，可直接使用安裝SQLite可視化管理工具NavicatforSQLiteSQLite數(shù)據(jù)庫(kù)操作創(chuàng)建數(shù)據(jù)庫(kù)創(chuàng)建數(shù)據(jù)庫(kù)表添加數(shù)據(jù)SQL語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)第一步：新建連接填寫(xiě)連接名選擇數(shù)據(jù)文件存放位置，并命名為Mydata創(chuàng)建數(shù)據(jù)庫(kù)表在“表”處單擊鼠標(biāo)右鍵新建數(shù)據(jù)庫(kù)表圖書(shū)數(shù)據(jù)庫(kù)主要操作包括：查詢(xún)圖書(shū)信息、添加新信息、修改信息、刪除信息。創(chuàng)建數(shù)據(jù)庫(kù)表圖書(shū)表：圖書(shū)ID（book_id）書(shū)名（title）作者（author）價(jià)格（price）出版社（publisher）ISBN號(hào)（isbn）備注（remark）創(chuàng)建數(shù)據(jù)庫(kù)表圖書(shū)表（book）添加數(shù)據(jù)向book表中添加相應(yīng)數(shù)據(jù)2SQL語(yǔ)句簡(jiǎn)介SQL語(yǔ)句簡(jiǎn)介結(jié)構(gòu)化查詢(xún)語(yǔ)言（StructuredQueryLanguage，SQL）是一種通用且功能強(qiáng)大的關(guān)系數(shù)據(jù)庫(kù)操作語(yǔ)言，具有數(shù)據(jù)定義、數(shù)據(jù)處理、數(shù)據(jù)控制等功能。包括：（1）數(shù)據(jù)定義語(yǔ)言（DataDefinitionLanguage，DDL）（2）數(shù)據(jù)處理語(yǔ)言（DataManipulationLanguage，DML）（3）數(shù)據(jù)控制語(yǔ)言（DataControlLanguage，DCL）創(chuàng)建表語(yǔ)句CreateTable使用CREATETABLE語(yǔ)句定義表CREATETABLE<表名>(<字段名l><數(shù)據(jù)類(lèi)型1>[(<大小>)][NOTNULL][PRIMARYKEY|UNIQUE][,<字段名2><數(shù)據(jù)類(lèi)型2>[(<大小>)][NOTNULL][PRIMARYKEY|UNIQUE][,…])創(chuàng)建表語(yǔ)句CreateTableSQLite中提供5種數(shù)據(jù)類(lèi)型CreateTable【例13.1】創(chuàng)建book表。CREATETABLEbook(book_idINTEGERPRIMARYKEY,titleTEXT,authorTEXT,priceREAL,publisherTEXT,isbnTEXT,remarkTEXT)CreateTable【例13.1】創(chuàng)建book表。CREATETABLEbook(book_idINTEGERPRIMARYKEY,titleTEXT(50),authorTEXT(50),priceREAL,publisherTEXT,isbnTEXT(50),remarkTEXT(100))插入數(shù)據(jù)記錄的語(yǔ)句insert此時(shí)可以，打開(kāi)表，錄入數(shù)據(jù)。插入數(shù)據(jù)記錄的語(yǔ)句insert使用INSERTINTO語(yǔ)句將數(shù)據(jù)插入表中。INSERTINTO<表名>[(<字段名1>[,<字段名2>[,…]])]VALUES(<表達(dá)式1>[,<表達(dá)式2>[,…]])如果INTO后省略字段名，則必須為新記錄中的所有字段賦值，且使各項(xiàng)數(shù)據(jù)和表定義的字段順序一一對(duì)應(yīng)。插入數(shù)據(jù)記錄的語(yǔ)句insert【例13.2】向book表中添加一條記錄。INSERTINTObook(title,author,price,publisher,isbn,remark)VALUES('信息與智能科學(xué)導(dǎo)論','寧愛(ài)軍等',50,'人民郵電出版社','9787115514660','無(wú)')修改表中數(shù)據(jù)的語(yǔ)句update使用UPDATE語(yǔ)句修改表中數(shù)據(jù)。UPDATE<表名>SET<字段名1>=<表達(dá)式1>[,<字段名2>=<表達(dá)式2>[,…]][WHERE<條件>]如果不帶WHERE子句，則更新表中所有的記錄；

如果帶WHERE子句，則只更新表中滿(mǎn)足條件的記錄。修改表中數(shù)據(jù)的語(yǔ)句update【例13.3】修改book表中某一本圖書(shū)的價(jià)格。修改圖書(shū)信息：根據(jù)書(shū)名更新價(jià)格：updatebooksetprice={價(jià)格}wheretitle={書(shū)名}UPDATEbookSETprice=59WHEREtitle='圍城'刪除表中數(shù)據(jù)的語(yǔ)句delete使用DELETE語(yǔ)句刪除表中數(shù)據(jù)。DELETEFROM<表名>[WHERE<條件>]如果不帶WHERE子句，則刪除表中所有記錄，該表對(duì)象仍保留在數(shù)據(jù)庫(kù)中。如果帶WHERE子句，則只刪除表中滿(mǎn)足條件的記錄。刪除表中數(shù)據(jù)的語(yǔ)句delete【例13.4】刪除book表中某一本圖書(shū)的信息。deletefrombookwheretitle={書(shū)名}DELETEFROMbookWHEREtitle='圍城'查詢(xún)表中數(shù)據(jù)的語(yǔ)句select使用SELECT語(yǔ)句查詢(xún)表中數(shù)據(jù)。SELECT[ALL|DISTINCT][TOP<數(shù)值>[PERCENT]]<目標(biāo)列>[[AS]<列標(biāo)題>]FROM<表或查詢(xún)1>[[AS]<別名1>],<表或查詢(xún)2>[[AS]<別名2>][WHERE<聯(lián)接條件>AND<篩選條件>][GROUPBY<分組項(xiàng)>[HAVING<分組篩選條件>]][ORDERBY<排序項(xiàng)>[ASC|DESC]]查詢(xún)表中數(shù)據(jù)的語(yǔ)句select【例13.5】查詢(xún)所有圖書(shū)信息。SELECTtitle,author,price,publisher,isbn,remarkFROMbook查詢(xún)表中數(shù)據(jù)的語(yǔ)句select【例13.6】查詢(xún)某一本圖書(shū)的信息。SELECTtitle,author,price,publisher,isbn,remarkFROMbookWHEREtitle={書(shū)名}SELECTtitle,author,price,publisher,isbn,remarkFROMbookWHEREtitle='紅樓夢(mèng)'SQL語(yǔ)句--select【例13.7】根據(jù)價(jià)格范圍查詢(xún)圖書(shū)信息。selecttitle,author,price,publisher,isbn,remarkfrombookwherepricebetween{開(kāi)始值}and{結(jié)束值}SELECTtitle,author,price,publisher,isbn,remarkFROMbookWHEREpricebetween20and40SQL語(yǔ)句--select【例13.8】統(tǒng)計(jì)不同出版社的圖書(shū)出版數(shù)量。SELECTpublisher,COUNT(title)ASbook_countFROMbookGROUPBYpublisher3Python操作數(shù)據(jù)庫(kù)Python操作數(shù)據(jù)庫(kù)con=sqlite3.connect("Mydata.db")cur=con.cursor()cur.execute()cur.close()con.close()Python操作SQLite從Python3.x版本開(kāi)始，在標(biāo)準(zhǔn)庫(kù)中已經(jīng)內(nèi)置了SQLlite3模塊導(dǎo)入SQLite3模塊importsqlite3

1：創(chuàng)建數(shù)據(jù)庫(kù)連接在Python中通過(guò)connect()函數(shù)連接數(shù)據(jù)庫(kù)并獲取數(shù)據(jù)庫(kù)連接對(duì)象。connect(database,timeout)database：指定路徑的數(shù)據(jù)庫(kù)文件，默認(rèn)為當(dāng)前文件夾timeout：訪問(wèn)數(shù)據(jù)的超時(shí)設(shè)定例如：con=sqlite3.connect("Mydata.db")2：創(chuàng)建數(shù)據(jù)庫(kù)操作游標(biāo)Python通過(guò)游標(biāo)來(lái)讀取和操作數(shù)據(jù)庫(kù)。在Python中通過(guò)cursor()函數(shù)創(chuàng)建數(shù)據(jù)庫(kù)操作游標(biāo)cursor()例如：cur=con.cursor()3：執(zhí)行SQL語(yǔ)句在創(chuàng)建了數(shù)據(jù)庫(kù)操作游標(biāo)后，就可以執(zhí)行SQL語(yǔ)句。execute(sql[,optionalparameters])執(zhí)行一個(gè)SQL語(yǔ)句例如：cur.execute("SELECT*FROMbook")#獲取所有數(shù)據(jù)book_all=cur.fetchall()4：關(guān)閉數(shù)據(jù)庫(kù)操作游標(biāo)和連接在數(shù)據(jù)庫(kù)操作完成后，需要先關(guān)閉數(shù)據(jù)庫(kù)操作游標(biāo)，再關(guān)閉數(shù)據(jù)庫(kù)連接。close()例如：cur.close()#關(guān)閉數(shù)據(jù)庫(kù)操作游標(biāo)con.close()#關(guān)閉數(shù)據(jù)庫(kù)連接數(shù)據(jù)庫(kù)操作過(guò)程【例13.9】創(chuàng)建、關(guān)閉數(shù)據(jù)庫(kù)連接和數(shù)據(jù)庫(kù)操作游標(biāo)close()importsqlite3#導(dǎo)入SQLite3模塊con=sqlite3.connect("Mydata.db")#創(chuàng)建數(shù)據(jù)庫(kù)連接cur=con.cursor()#創(chuàng)建數(shù)據(jù)庫(kù)操作游標(biāo)pass#此處可以添加數(shù)據(jù)庫(kù)操作代碼cur.close()#關(guān)閉數(shù)據(jù)庫(kù)操作游標(biāo)con.close()#關(guān)閉數(shù)據(jù)庫(kù)連接操作數(shù)據(jù)庫(kù)的基本方法Python中通過(guò)execute()函數(shù)執(zhí)行一條SQL語(yǔ)句execute(sql[,optionalparameters])（1）sql：參數(shù)化的SQL語(yǔ)句。某些不固定的數(shù)據(jù)，我們可通過(guò)使用占位符（問(wèn)號(hào)？)和命名占位符（:xxx）。（2）[,optionalparameters]：替換占位符。操作數(shù)據(jù)庫(kù)的基本方法【例13.10】帶有參數(shù)的SQL語(yǔ)句。cur.execute("INSERTINTObook(title,author)VALUES(?,?)",(title,author))cur.execute("INSERTINTObook(title,author)VALUES(:title,

:author)",{'title':title,'author':author})操作數(shù)據(jù)庫(kù)的基本方法事務(wù)提交：在執(zhí)行完SQL語(yǔ)句后，還需要通過(guò)事務(wù)提交才能真正完成對(duì)數(shù)據(jù)庫(kù)的操作。commit()mit()#提交事務(wù)1.創(chuàng)建表【例13.11】創(chuàng)建book表。importsqlite3con=sqlite3.connect("Mydata.db")#創(chuàng)建數(shù)據(jù)庫(kù)連接cur=con.cursor()#創(chuàng)建數(shù)據(jù)庫(kù)操作游標(biāo)cur.execute("CREATETABLEbook(book_idINTEGERNOTNULLPRIMARYKEYAUTOINCREMENT,titleTEXT,authorTEXT,priceREAL,publisherTEXT,isbnTEXT,remarkTEXT)")mit()#提交事務(wù)print('創(chuàng)建圖書(shū)表成功')cur.close()#關(guān)閉數(shù)據(jù)庫(kù)操作游標(biāo)con.close()#關(guān)閉數(shù)據(jù)庫(kù)連接2．添加數(shù)據(jù)到表【例13.12】向book表中添加一條記錄。#創(chuàng)建數(shù)據(jù)庫(kù)連接con和游標(biāo)cur#添加一本圖書(shū)print("======添加新圖書(shū)=====")title=input("書(shū)名：")author=input("作者：")price=eval(input("價(jià)格："))#注意price為數(shù)字格式，#因此需要用eval()進(jìn)行轉(zhuǎn)換publisher=input("出版社：")isbn=input("ISBN號(hào)：")remark=input("備注：")cur.execute("INSERTINTObook(title,author,price,publisher,isbn,remark)VALUES(?,?,?,?,?,?)",(title,author,price,publisher,isbn,remark))mit()print('添加圖書(shū)信息成功')#關(guān)閉數(shù)據(jù)庫(kù)游標(biāo)cur和連接con3．修改表中數(shù)據(jù)【例13.13】修改book表中某一本圖書(shū)的價(jià)格#根據(jù)書(shū)名修改圖書(shū)價(jià)格print("======修改圖書(shū)信息===========")title=input("書(shū)名：")price=eval(input("價(jià)格："))cur.execute("UPDATEbookSETprice=?WHEREtitle=?",(price,title))mit()print('修改圖書(shū)價(jià)格成功')#關(guān)閉數(shù)據(jù)庫(kù)游標(biāo)cur和連接con4．刪除表中數(shù)據(jù)【例13.14】刪除book表中某一本圖書(shū)的信息。#創(chuàng)建數(shù)據(jù)庫(kù)連接con和游標(biāo)cur#根據(jù)書(shū)名刪除圖書(shū)信息print("======刪除圖書(shū)信息===========")title=input("書(shū)名：")cur.execute("DELETEFROMbookWHEREtitle=?",(title,))#title后的逗號(hào)不能少mit()print('刪除圖書(shū)成功')#關(guān)閉數(shù)據(jù)庫(kù)游標(biāo)cur和連接con5．查詢(xún)表中多條數(shù)據(jù)在Python中通過(guò)使用fetchall()函數(shù)后取得SQL查詢(xún)語(yǔ)句的多條數(shù)據(jù)。fetchall()獲取查詢(xún)結(jié)果的所有數(shù)據(jù)，返回一個(gè)列表5．查詢(xún)表中多條數(shù)據(jù)【例13.15】查詢(xún)所有圖書(shū)信息（不顯示圖書(shū)ID）。#查詢(xún)所有圖書(shū)信息（不顯示圖書(shū)ID）print("======所有圖書(shū)信息===========")cur.execute("SELECTtitle,author,price,publisher,isbn,remarkFROMbook")#獲取所有數(shù)據(jù)book_all=cur.fetchall()#遍歷forbookinbook_all:print(book)#關(guān)閉數(shù)據(jù)庫(kù)游標(biāo)cur和連接con5．查詢(xún)表中多條數(shù)據(jù)【例13.16】根據(jù)價(jià)格范圍查詢(xún)圖書(shū)信息（不顯示圖書(shū)ID）。#創(chuàng)建數(shù)據(jù)庫(kù)連接con和游標(biāo)cur#根據(jù)價(jià)格范圍查詢(xún)圖書(shū)信息（不顯示圖書(shū)ID）print("======價(jià)格在一定范圍的圖書(shū)信息===========")low_price=eval(input("最低價(jià)格："))high_price=eval(input("最高價(jià)格："))cur.execute("SELECTtitle,author,price,publisher,isbn,remarkFROMbookWHEREpriceBETWEEN?AND?",(low_price,high_price))book_all=cur.fetchall()

#獲取所有數(shù)據(jù)forbookinbook_all:

#遍歷print(book)#關(guān)閉數(shù)據(jù)庫(kù)游標(biāo)cur和連接con5．查詢(xún)表中多條數(shù)據(jù)【例13.17】統(tǒng)計(jì)不同出版社的圖書(shū)出版數(shù)量。#創(chuàng)建數(shù)據(jù)庫(kù)連接con和游標(biāo)cur#統(tǒng)計(jì)不同出版社的圖書(shū)出版數(shù)量print("======不同出版社的圖書(shū)出版數(shù)量===========")cur.execute("SELECTpublisher,COUNT(title)ASbook_countFROMbookGROUPBYpublisher")book_all=cur.fetchall()

#獲取所有數(shù)據(jù)forbookinbook_all:

#遍歷

print(book)#關(guān)閉數(shù)據(jù)庫(kù)游標(biāo)cur和連接con6．查詢(xún)表中單條數(shù)據(jù)在Python中通過(guò)使用fetchone()函數(shù)執(zhí)行一條SQL查詢(xún)語(yǔ)句fetchone()返回一個(gè)包含查詢(xún)結(jié)果的元組。6．查詢(xún)表中單條數(shù)據(jù)【例13.18】根據(jù)書(shū)名查詢(xún)圖書(shū)信息（不顯示圖書(shū)ID）#創(chuàng)建數(shù)據(jù)庫(kù)連接con和游標(biāo)cur#根據(jù)書(shū)名查詢(xún)圖書(shū)信息print("======一本書(shū)的信息===========")title=input("書(shū)名：")cur.execute("SELECTtitle,author,price,publisher,isbn,remarkFROMbookWHEREtitle=?",(title,))#獲取一條數(shù)據(jù)book_one=cur.fetchone()print(book_one)#關(guān)閉數(shù)據(jù)庫(kù)游標(biāo)cur和連接con4綜合案例員工數(shù)據(jù)庫(kù)【例13.19】簡(jiǎn)易數(shù)據(jù)庫(kù)操作程序的主要功能包括創(chuàng)建表，以及數(shù)據(jù)添加、修改、刪除、查詢(xún)等操作，并創(chuàng)建相應(yīng)菜單。在磁盤(pán)上創(chuàng)建了一個(gè)Mydata.db數(shù)據(jù)。創(chuàng)建數(shù)據(jù)庫(kù)表員工檔案表employee：?jiǎn)T工ID（employee_id），姓名（employee_name），性別（gender），學(xué)歷（education），生日（birthday），電話（mobile），住址（address），備注（remark）添加數(shù)據(jù)向employee表中添加相應(yīng)數(shù)據(jù)創(chuàng)建數(shù)據(jù)庫(kù)表創(chuàng)建員工表cur.execute("CREATETABLEemployee(employee_idINTEGERNOTNULLPRIMARYKEYAUTOINCREMENT,

employee_nameTEXT,genderTEXT,educationTEXT,birthdayTEXT,mobileTEXT,addressTEXT,remarkTEXT)")mit()插入數(shù)據(jù)添加員工SQL語(yǔ)句cur.execute("INSERTINTOemployee(employee_name,gender,education,birthday,mobile,address,remark)VALUES(?,?,?,?,?,?,?)",(employee_name,

gender,

education,birthday,mobile,address,remark))mit()修