[Python从零到壹] 八.数据库之MySQL和Sqlite基础知识及操作万字详解
歡迎大家來(lái)到“Python從零到壹”，在這里我將分享約200篇Python系列文章，帶大家一起去學(xué)習(xí)和玩耍，看看Python這個(gè)有趣的世界。所有文章都將結(jié)合案例、代碼和作者的經(jīng)驗(yàn)講解，真心想把自己近十年的編程經(jīng)驗(yàn)分享給大家，希望對(duì)您有所幫助，文章中不足之處也請(qǐng)海涵。Python系列整體框架包括基礎(chǔ)語(yǔ)法10篇、網(wǎng)絡(luò)爬蟲(chóng)30篇、可視化分析10篇、機(jī)器學(xué)習(xí)20篇、大數(shù)據(jù)分析20篇、圖像識(shí)別30篇、人工智能40篇、Python安全20篇、其他技巧10篇。您的關(guān)注、點(diǎn)贊和轉(zhuǎn)發(fā)就是對(duì)秀璋最大的支持，知識(shí)無(wú)價(jià)人有情，希望我們都能在人生路上開(kāi)心快樂(lè)、共同成長(zhǎng)。
前一篇文章講述了Requests庫(kù)爬取豆瓣電影TOP250，并存儲(chǔ)至CSV文件。豆瓣TOP250是非常適合入門(mén)的案例，也能普及簡(jiǎn)單的預(yù)處理知識(shí)。這篇文章將普及數(shù)據(jù)庫(kù)操作知識(shí)，這也為后續(xù)網(wǎng)絡(luò)爬蟲(chóng)存儲(chǔ)至數(shù)據(jù)庫(kù)奠定基礎(chǔ)。本文詳細(xì)介紹MySQL安裝、SQL語(yǔ)句和Python操作數(shù)據(jù)庫(kù)知識(shí)，希望對(duì)您有所幫助。
文章目錄
- 一.MySQL數(shù)據(jù)庫(kù)
- 1.MySQL的安裝與配置
- 2.SQL語(yǔ)句詳解
- 二.Python操作MySQL數(shù)據(jù)庫(kù)
- 1.安裝MySQL擴(kuò)展包
- 2.程序接口DB-API
- 3.Python調(diào)用MySQLdb擴(kuò)展包
- 三.Python操作Sqlite3數(shù)據(jù)庫(kù)
- 四.本章小結(jié)
下載地址：
- https://github.com/eastmountyxz/Python-zero2one
前文賞析：
- [Python從零到壹] 一.為什么我們要學(xué)Python及基礎(chǔ)語(yǔ)法詳解
- [Python從零到壹] 二.語(yǔ)法基礎(chǔ)之條件語(yǔ)句、循環(huán)語(yǔ)句和函數(shù)
- [Python從零到壹] 三.語(yǔ)法基礎(chǔ)之文件操作、CSV文件讀寫(xiě)及面向?qū)ο?/li>
- [Python從零到壹] 四.網(wǎng)絡(luò)爬蟲(chóng)之入門(mén)基礎(chǔ)及正則表達(dá)式抓取博客案例
- [Python從零到壹] 五.網(wǎng)絡(luò)爬蟲(chóng)之BeautifulSoup基礎(chǔ)語(yǔ)法萬(wàn)字詳解
- [Python從零到壹] 六.網(wǎng)絡(luò)爬蟲(chóng)之BeautifulSoup爬取豆瓣TOP250電影詳解
- [Python從零到壹] 七.網(wǎng)絡(luò)爬蟲(chóng)之Requests爬取豆瓣電影TOP250及CSV存儲(chǔ)
- [Python從零到壹] 八.數(shù)據(jù)庫(kù)之MySQL基礎(chǔ)知識(shí)及操作萬(wàn)字詳解
作者新開(kāi)的“娜璋AI安全之家”將專(zhuān)注于Python和安全技術(shù)，主要分享Web滲透、系統(tǒng)安全、人工智能、大數(shù)據(jù)分析、圖像識(shí)別、惡意代碼檢測(cè)、CVE復(fù)現(xiàn)、威脅情報(bào)分析等文章。雖然作者是一名技術(shù)小白，但會(huì)保證每一篇文章都會(huì)很用心地撰寫(xiě)，希望這些基礎(chǔ)性文章對(duì)你有所幫助，在Python和安全路上與大家一起進(jìn)步。
數(shù)據(jù)庫(kù)（Database）是按照數(shù)據(jù)結(jié)構(gòu)來(lái)組織、存儲(chǔ)和管理數(shù)據(jù)的倉(cāng)庫(kù)，在數(shù)據(jù)庫(kù)管理系統(tǒng)中，用戶(hù)可以對(duì)數(shù)據(jù)進(jìn)行新增、刪除、更新、查詢(xún)等操作，從而轉(zhuǎn)變?yōu)橛脩?hù)所需要的各種數(shù)據(jù)，并進(jìn)行靈魂的管理。前面介紹的Python網(wǎng)絡(luò)數(shù)據(jù)爬取，得到的語(yǔ)料通常采用TXT文本、Excel或CSV格式進(jìn)行存儲(chǔ)的，而本章節(jié)講述了如何將爬取的數(shù)據(jù)存儲(chǔ)至數(shù)據(jù)庫(kù)中，從而更方便數(shù)據(jù)分析和數(shù)據(jù)統(tǒng)計(jì)。本章將重點(diǎn)介紹MySQL數(shù)據(jù)庫(kù)相關(guān)知識(shí)及Python操作MySQL的方法。
一.MySQL數(shù)據(jù)庫(kù)
數(shù)據(jù)庫(kù)技術(shù)是信息管理系統(tǒng)、自動(dòng)化辦公系統(tǒng)、銷(xiāo)售統(tǒng)計(jì)系統(tǒng)等各種信息系統(tǒng)的核心部分，是進(jìn)行科學(xué)研究和決策管理的重要技術(shù)手段，常用的數(shù)據(jù)庫(kù)包括：Oracle、DB2、MySQL、SQL Server、Sybase、VF等。由于MySQL數(shù)據(jù)庫(kù)具有性能優(yōu)良、穩(wěn)定性好、配置簡(jiǎn)單、支持各種操作系統(tǒng)等優(yōu)勢(shì)，本章主要講述Windows系統(tǒng)下Python操作MySQL數(shù)據(jù)庫(kù)的內(nèi)容。
1.MySQL的安裝與配置
首先，打開(kāi)瀏覽器輸入MySQL官方網(wǎng)址“https://www.mysql.com/” ，點(diǎn)擊“DOWNLOADS”菜單，顯示如圖1所示的下載頁(yè)面。
接著找到MySQL軟件的下載頁(yè)面，網(wǎng)址如下，點(diǎn)擊“Download”按鈕下載相關(guān)軟件，如下圖所示的 mysql-installer-community-5.7.21.0.msi。同時(shí)，讀者也可以直接在百度搜索“MySQL”軟件下載安裝。
- https://dev.mysql.com/downloads/installer/
最新已更新至MySQL 8.0版本。
圖形化安裝過(guò)程如下，作者結(jié)合數(shù)據(jù)庫(kù)開(kāi)發(fā)及實(shí)際安裝經(jīng)驗(yàn)進(jìn)行敘述，讀者如果想系統(tǒng)地學(xué)習(xí)MySQL數(shù)據(jù)庫(kù)，推薦《深入淺出MySQL》這本書(shū)給大家。
(1) 雙擊MySQL安裝文件setup.exe，進(jìn)入MySQL安裝歡迎界面，如圖3所示，點(diǎn)擊“Next”進(jìn)行安裝。
(2) 緊接著進(jìn)入“Setup Type”界面，選擇MySQL安裝類(lèi)型，這里選擇“Typical”類(lèi)型，如圖4所示。
其中Typical表示安裝常用的組件，默認(rèn)安裝到C盤(pán)“Program Files\MySQL”文件夾下，推薦讀者選擇此安裝套件；Complete表示安裝所有的組件；Custom表示根據(jù)用戶(hù)自定義進(jìn)行安裝組件，可以更改默認(rèn)的安裝路徑，此類(lèi)型更為靈活。
(3) 然后點(diǎn)擊“Next”按鈕進(jìn)入下一個(gè)安裝步驟，選擇安裝目錄和“Developer Components”組件如圖5所示。
(4) 在安裝過(guò)程中，通常選擇默認(rèn)選項(xiàng)，點(diǎn)擊“Next”按鈕進(jìn)入下一步。同時(shí)，讀者也可以根據(jù)自己的電腦環(huán)境及喜好進(jìn)行配置，圖6選擇手動(dòng)準(zhǔn)確配置（Detailed Configuration）。
(5) 繼續(xù)點(diǎn)擊“Next”按鈕進(jìn)入下一步，在MySQL應(yīng)用類(lèi)型選擇界面中，提供了三種方式：
- Developer Machine（開(kāi)發(fā)機(jī)），使用最小數(shù)量的內(nèi)存。
- Server Machine（服務(wù)器），使用中等大小的內(nèi)存。
- Dedicated MySQL Server Machine（專(zhuān)用服務(wù)器），當(dāng)前可用的最大內(nèi)存。
這里，作者選擇服務(wù)器類(lèi)型（Server Machine），如圖7所示。
(6) 點(diǎn)擊“Next”下一步按鈕，進(jìn)入數(shù)據(jù)庫(kù)用途選擇界面，這里作者選擇“Multifunctional Database”，它表示多功能數(shù)據(jù)庫(kù)，此選項(xiàng)對(duì)事務(wù)性和非事務(wù)性存儲(chǔ)引擎的存取速度都很快。
(7) 然后點(diǎn)擊“Next”按鈕，進(jìn)入并發(fā)連接設(shè)置頁(yè)面，選擇“Decision Support(DSS)/ OLAP”，它表示決策支持系統(tǒng)，設(shè)置數(shù)據(jù)庫(kù)訪(fǎng)問(wèn)量連接數(shù)為15（默認(rèn)），如圖9所示。
(8) 設(shè)置MySQL端口號(hào)為3306（默認(rèn)），再點(diǎn)擊下一步。其中MySQL調(diào)用的端口號(hào)為3306，如圖10所示。
(9) 設(shè)置編碼方式為utf-8（中文編碼），如圖11選擇字符集“Character Set”為utf8。
讀者需要注意，軟件開(kāi)發(fā)過(guò)程中的編碼亂碼問(wèn)題是一個(gè)常見(jiàn)的典型問(wèn)題，尤其是處理中文字符，而其解決方法的核心思想是將所有開(kāi)發(fā)環(huán)境的編碼方式設(shè)置為一致，通常將數(shù)據(jù)庫(kù)、Python、HTML源碼、前端瀏覽器等編碼方式都配置成UTF-8中文編碼方式。其中MySQL數(shù)據(jù)庫(kù)設(shè)置編碼方式為utf-8的過(guò)程如下圖所示，否則數(shù)據(jù)庫(kù)存儲(chǔ)或查詢(xún)中文漢字時(shí)，可能出現(xiàn)亂碼錯(cuò)誤。
(10) 點(diǎn)擊“Next”按鈕，進(jìn)入Windows選項(xiàng)設(shè)置界面，再單擊“Next”按鈕進(jìn)入安全選項(xiàng)配置界面，超級(jí)用戶(hù)root的密碼通常設(shè)置為“123456”，如下圖12所示。
(11) 點(diǎn)擊“Next”按鈕，進(jìn)入準(zhǔn)備執(zhí)行界面，等待MySQL安裝配置，當(dāng)所有的選項(xiàng)都打上勾顯示成功的時(shí)候表示MySQL安裝成功，如圖13所示，最后點(diǎn)擊“Finish”完成全部安裝。
(12) 至此，MySQL數(shù)據(jù)庫(kù)安裝完成，Windows的“所有程序”菜單中已經(jīng)多了“MySQL 5.0”等選項(xiàng)。
2.SQL語(yǔ)句詳解
數(shù)據(jù)庫(kù)中最重要的就是SQL語(yǔ)句，它是結(jié)構(gòu)化查詢(xún)語(yǔ)言，英文Structure Query Language的縮寫(xiě)，SQL是使用關(guān)系模型的數(shù)據(jù)庫(kù)應(yīng)用語(yǔ)言。在MySQL安裝成功后，我們將詳細(xì)介紹SQL語(yǔ)句，并通過(guò)MySQL軟件介紹SQL語(yǔ)句的基礎(chǔ)用法及對(duì)應(yīng)代碼。
SQL語(yǔ)句主要?jiǎng)澐譃槿N類(lèi)別。
- DDL（Data Definition Language）語(yǔ)句
數(shù)據(jù)定義語(yǔ)言。該語(yǔ)句定義了不同的數(shù)據(jù)字段、數(shù)據(jù)庫(kù)、數(shù)據(jù)表、列、索引等數(shù)據(jù)庫(kù)對(duì)象。常用語(yǔ)句關(guān)鍵字包括create、drop、alter等。 - DML（Data Manipulation Language）語(yǔ)句
數(shù)據(jù)庫(kù)操縱語(yǔ)句。該語(yǔ)句用于插入、刪除、更新和查詢(xún)數(shù)據(jù)庫(kù)的記錄，是數(shù)據(jù)庫(kù)操作最常用的語(yǔ)句，并檢查數(shù)據(jù)完整性。常用的語(yǔ)句關(guān)鍵字包括insert、delete、update和select。 - DCL（Data Control Language）語(yǔ)句
數(shù)據(jù)控制語(yǔ)句。該語(yǔ)句用于控制不同數(shù)據(jù)字段的許可和訪(fǎng)問(wèn)級(jí)別，定義了數(shù)據(jù)庫(kù)、表、字段、用戶(hù)的訪(fǎng)問(wèn)權(quán)限和安全級(jí)別。常用的語(yǔ)句關(guān)鍵字包括grant、revoke等。
下面結(jié)合我們安裝的MySQL軟件具體的講解SQL語(yǔ)句的用法。
首先，運(yùn)行MySQL并輸入默認(rèn)的用戶(hù)密碼“123456”，如圖14所示。
(1) 顯示數(shù)據(jù)庫(kù)
輸入“show databases”語(yǔ)句，查看當(dāng)前MySQL數(shù)據(jù)庫(kù)中存在的所有數(shù)據(jù)庫(kù)，如果某個(gè)數(shù)據(jù)庫(kù)已經(jīng)存在，則可以使用use語(yǔ)句直接使用；如果數(shù)據(jù)庫(kù)不存在，則第一次需要使用create語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)。
(2) 使用數(shù)據(jù)庫(kù)
如果想直接使用已經(jīng)存在的數(shù)據(jù)庫(kù)test01，則直接使用如下語(yǔ)句。
(3) 創(chuàng)建數(shù)據(jù)庫(kù)
創(chuàng)建數(shù)據(jù)庫(kù)語(yǔ)句是：
- create database 數(shù)據(jù)庫(kù)名字
如果想創(chuàng)建新的數(shù)據(jù)庫(kù)，使用create關(guān)鍵字創(chuàng)建。創(chuàng)建成功后，再調(diào)用use關(guān)鍵詞選擇該數(shù)據(jù)庫(kù)進(jìn)行使用，代碼如下：
mysql> create database bookmanage; Query OK, 1 row affected (0.00 sec)mysql> use bookmanage; Database changed圖15顯示創(chuàng)建數(shù)據(jù)庫(kù)“bookmanage”圖書(shū)管理系統(tǒng)及選擇數(shù)據(jù)庫(kù)。
這里同樣可以使用“show tables”語(yǔ)句顯示該數(shù)據(jù)庫(kù)中所有存在的表，但是目前還沒(méi)有一張表，故返回“Empty set”。
(4) 創(chuàng)建表
創(chuàng)建表使用：
- create table 表名 (字段名 字段類(lèi)型 約束條件…)
例如創(chuàng)建books圖書(shū)表，包括圖書(shū)編號(hào)bookid、圖書(shū)名稱(chēng)bookname、價(jià)格price、圖書(shū)日期bookdate等字段。代碼如下所示：
create table books(bookid int primary key, bookname varchar(20),price float,bookdate date);其中創(chuàng)建的表名稱(chēng)為books，圖書(shū)編號(hào)為int類(lèi)型，同時(shí)為主鍵（primary key），用于唯一標(biāo)識(shí)表的字段；圖書(shū)名稱(chēng)位varchar類(lèi)型，長(zhǎng)度為20；價(jià)格為浮點(diǎn)型float；圖書(shū)日期為date類(lèi)型。詳細(xì)介紹推薦大家課后去學(xué)習(xí)，這里只是入門(mén)講解。
(5) 查看表信息
如果想查看當(dāng)前數(shù)據(jù)庫(kù)存在多少?gòu)埍?#xff0c;則使用show關(guān)鍵字。
當(dāng)前僅存在一張表books。如果想查看某張表的定義，則使用desc關(guān)鍵字。
desc books;運(yùn)行結(jié)果如下圖17所示，顯示了圖書(shū)表的詳細(xì)信息。
desc命令可以查看表的定義，但是如果想查看表更全面的信息，則通過(guò)更深入的SQL語(yǔ)句，比如通過(guò)查看創(chuàng)建表的SQL語(yǔ)句。
(6) 刪除表
如果想要?jiǎng)h除表books，使用“drop”關(guān)鍵詞。
(7) 插入語(yǔ)句
當(dāng)數(shù)據(jù)庫(kù)和表創(chuàng)建成果后，需要向表中插入數(shù)據(jù)，使用的insert關(guān)鍵字。
比如向books圖書(shū)表中插入信息，代碼如下：
insert into books(bookid, bookname, price, bookdate)values('1', '平凡的世界', '29.8', '2017-06-10');使用select查詢(xún)語(yǔ)句顯示結(jié)果如圖19所示，后面詳細(xì)介紹select語(yǔ)句。
在執(zhí)行insert插入過(guò)程中，如果省略所有字段，只需要values值一一對(duì)應(yīng)即可。
mysql> insert into books-> values('2', '活著', '25.0', '2017-06-11');如圖20所示，插入第二本書(shū)《活著》，select顯示的結(jié)果為兩本書(shū)的詳細(xì)信息。
同理，如果只想插入某幾個(gè)字段的數(shù)據(jù)，只需要對(duì)應(yīng)一致即可，比如查詢(xún)書(shū)序號(hào)和書(shū)名《鋼鐵是怎樣煉成的》。
mysql> insert into books(bookid, bookname)-> values('3', '鋼鐵是怎樣煉成的');運(yùn)行結(jié)果如圖21所示，《鋼鐵是怎樣煉成的》書(shū)籍中，省略的字段價(jià)格（price）和日期（bookdate）字段顯示為NULL空值。
(8) 查詢(xún)語(yǔ)句
查詢(xún)語(yǔ)句基本語(yǔ)法是：
- select 字段 from 表名 [where 條件]
該語(yǔ)句用于查詢(xún)指定字段的數(shù)據(jù)，當(dāng)字段為星號(hào)時(shí)，它用于查詢(xún)表中的所有字段；where緊跟著查詢(xún)條件，該參數(shù)可以省略。最簡(jiǎn)單的查詢(xún)語(yǔ)句如下所示，將顯示books表中的所有字段和數(shù)據(jù)，包括三本書(shū)籍的詳細(xì)信息。
mysql> select * from books; +--------+---------------------------------------------+--------+-------------------+ | bookid | bookname | price | bookdate | +--------+---------------------------------------------+--------+-------------------+ | 1 | 平凡的世界 | 29.8 | 2017-06-10 | | 2 | 活著 | 25 | 2017-06-11 | | 3 | 鋼鐵是怎樣煉成的 | NULL | NULL | +--------+--------------------------------------------+---------+--------------------+ 3 rows in set (0.00 sec)如果想顯示需要的字段，則可以用逗號(hào)分隔，如下所示：
select bookid,bookname,price from books;顯示結(jié)果如圖22所示，其中bookdate字段沒(méi)有顯示出來(lái)。
如果需要增加查詢(xún)條件，則使用where語(yǔ)句即可，比如查詢(xún)編號(hào)大于1的書(shū)籍、查詢(xún)價(jià)格非空的書(shū)籍，代碼如下：
select bookid,bookname,price,bookdate from books where bookid>1; select bookid,bookname,price,bookdate from books where price is not null;顯示結(jié)果如圖23所示，第一條語(yǔ)句顯示編號(hào)為2和3的結(jié)果，第二條語(yǔ)句顯示圖書(shū)價(jià)格不為空的結(jié)果。
更多的查詢(xún)語(yǔ)句希望讀者自己去研究，包括排序、group by分組、子查詢(xún)等，同時(shí)后面數(shù)據(jù)分析部分，會(huì)結(jié)合實(shí)際應(yīng)用介紹數(shù)據(jù)庫(kù)查詢(xún)語(yǔ)句及可視化分析處理。
(9) 更新語(yǔ)句
更新語(yǔ)句使用update關(guān)鍵字，基本格式是：
- update 表名 set 字段=新值 [where 條件]
例如將《活著》書(shū)名更新為《朝花夕拾》，代碼如下：
update books set bookname=’朝花夕拾’ where bookname=’活著’;運(yùn)行結(jié)果如圖24所示，其中圖書(shū)序號(hào)為2的信息進(jìn)行了更新。
(10) 刪除語(yǔ)句
刪除語(yǔ)句使用delete關(guān)鍵字，其基本格式是：
- delete from 表名 where 條件;
例如將價(jià)格為空的數(shù)據(jù)刪除，使用的條件是“where price is null”，代碼如下：
delete from books where price is null;運(yùn)行結(jié)果如圖25所示，可以看到第三本書(shū)《鋼鐵是怎樣煉成的》已經(jīng)被刪除。
此時(shí)，MySQL數(shù)據(jù)庫(kù)的基礎(chǔ)知識(shí)就介紹完了，更多知識(shí)推薦讀者下來(lái)自行補(bǔ)充學(xué)習(xí)，比如結(jié)合 Navicat for MySQL 工具可視化結(jié)合。下面將介紹Python調(diào)用MySQL及操作。
二.Python操作MySQL數(shù)據(jù)庫(kù)
Python訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)需要對(duì)應(yīng)的接口程序，我們可以把接口程序理解為Python的一個(gè)模塊，它提供了數(shù)據(jù)庫(kù)客戶(hù)端的接口供您訪(fǎng)問(wèn)。本小節(jié)主要介紹了Python操作MySQL數(shù)據(jù)庫(kù)，通過(guò)調(diào)用MySQLdb擴(kuò)展包操作數(shù)據(jù)庫(kù)。
1.安裝MySQL擴(kuò)展包
首先需要在Python環(huán)境下安裝MySQL擴(kuò)展包，有兩種方法。
第一種方法：通過(guò)“pip install mysql”安裝Python的MySQL庫(kù)。
但是使用該方法可能會(huì)遇到一些錯(cuò)誤，如“Microsoft Visual C++ 9.0 is required (Unable to find vcvarsall.bat)”或“_mysql.c(42) : fatal error C1083: Cannot open include file: ‘config-win.h’: No such file or directory”等，這些錯(cuò)誤可能來(lái)自于驅(qū)動(dòng)等問(wèn)題。可以安裝一個(gè)Micorsoft Visual C++ Compiler for Python 2.7包解決。請(qǐng)讀者百度自行解決，通常為路徑錯(cuò)誤或版本需要升級(jí)。
第二種方法：從Python官網(wǎng)下載安裝文件（推薦該方法）。
假設(shè)已經(jīng)下載了一個(gè)whl文件，然后安裝該文件。
- https://www.lfd.uci.edu/~gohlke/pythonlibs/#mysqlclient
- https://pypi.python.org/pypi/MySQL-python/
安裝過(guò)程如下圖所示。
同樣，可以下載MySQL-python-1.2.3.win-amd64-py2.7.exe文件安裝，如下圖所示。
2.程序接口DB-API
接著給大家介紹Python操作MySQL數(shù)據(jù)庫(kù)的API接口。Python接口程序一定要遵守Python DB-API規(guī)范。DB-API定義了一系列必須的操作對(duì)象和數(shù)據(jù)庫(kù)存取方式，以便為各種各樣的底層數(shù)據(jù)庫(kù)系統(tǒng)和不同的數(shù)據(jù)庫(kù)接口程序提供一致的訪(fǎng)問(wèn)接口。由于DB-API為不同的數(shù)據(jù)庫(kù)提供了一致的訪(fǎng)問(wèn)接口，這讓它在不同的數(shù)據(jù)庫(kù)之間移植代碼成為一件輕松的事情。
下面簡(jiǎn)單介紹DB-API的使用方法。
- 模塊屬性
一個(gè)DB-API模塊的定義如表1所示。
- 連接數(shù)據(jù)庫(kù)函數(shù)
連接數(shù)據(jù)庫(kù)的函數(shù)是connect()函數(shù)，其生成一個(gè)connect對(duì)象，用于訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)。Connect()函數(shù)的參數(shù)如表2所列。
下面是Python導(dǎo)入MySQLdb擴(kuò)展包，調(diào)用connect()函數(shù)連接數(shù)據(jù)庫(kù)的代碼。
import MySQLdb conn = MySQLdb.connect(host='localhost', db='bookmanage', user='root',passwd='123456', port=3306, charset='utf8')MySQLdb擴(kuò)展包的connect對(duì)象常用方法如表3所示。
commit()、rollback()、cursor()方法對(duì)于支持事務(wù)（Transaction）的數(shù)據(jù)庫(kù)更有意義，事務(wù)是指作為單個(gè)邏輯工作單元執(zhí)行的一系列操作，要么完整地執(zhí)行，要么完全地不執(zhí)行，從而保證數(shù)據(jù)的完整性和安全性。
- 游標(biāo)對(duì)象
上面說(shuō)了connect()方法用于提供連接數(shù)據(jù)庫(kù)的接口，如果要對(duì)數(shù)據(jù)庫(kù)操作那么還需要使用游標(biāo)對(duì)象，游標(biāo)對(duì)象的屬性和方法如表4所示。
下面通過(guò)簡(jiǎn)單的示例進(jìn)行講解。
3.Python調(diào)用MySQLdb擴(kuò)展包
前面我們創(chuàng)建了數(shù)據(jù)庫(kù)“bookmanage”和表“books”，它們用來(lái)記錄書(shū)籍管理系統(tǒng)中的書(shū)籍信息，接下來(lái)講解怎樣通過(guò)Python來(lái)顯示。
(1) 查詢(xún)數(shù)據(jù)庫(kù)名稱(chēng)
首先，我們查看本地?cái)?shù)據(jù)庫(kù)中所包含的數(shù)據(jù)庫(kù)名稱(chēng)，使用“show databases”語(yǔ)句。代碼如下：
調(diào)用 MySQLdb.connect() 訪(fǎng)問(wèn)用戶(hù)root本地MySQL數(shù)據(jù)庫(kù)，其默認(rèn)密碼為“123456”。然后調(diào)用cur.execute(‘show databases’)執(zhí)行顯示所有數(shù)據(jù)庫(kù)的語(yǔ)句，返回結(jié)果通過(guò)循環(huán)獲取，如圖29所示。
如果本地?cái)?shù)據(jù)庫(kù)已經(jīng)存在，并且讀者忘記其數(shù)據(jù)庫(kù)的名稱(chēng)，可以通過(guò)該方法查詢(xún)數(shù)據(jù)庫(kù)中所包含所有數(shù)據(jù)庫(kù)，再連接該數(shù)據(jù)庫(kù)進(jìn)行相關(guān)的操作。
(2) 查詢(xún)表
這里我們需要查詢(xún)“bookmanage”數(shù)據(jù)庫(kù)中的書(shū)籍表（books）內(nèi)容，代碼如下：
代碼通過(guò)connect()函數(shù)連接數(shù)據(jù)庫(kù)，通過(guò)cursor()函數(shù)定義游標(biāo)，然后調(diào)用游標(biāo)的 excute(‘select * from books’) 執(zhí)行數(shù)據(jù)庫(kù)操作，此處為查詢(xún)操作，再通過(guò)fetchall()函數(shù)獲取所有數(shù)據(jù)。其中查詢(xún)語(yǔ)句為“select * from books”，查找books表中所有數(shù)據(jù)，輸出結(jié)果如下：
>>> 表中包含 2 條數(shù)據(jù)1 平凡的世界 29.8 2017-06-10 2 朝花夕拾 25 2017-06-11 >>>對(duì)應(yīng)的MySQL中的結(jié)果是一致的，圖30是對(duì)應(yīng)的結(jié)果。
(3) 新建表
下面創(chuàng)建一張學(xué)生表，主要是調(diào)用commit()函數(shù)提交數(shù)據(jù)，執(zhí)行create table語(yǔ)句操作。
輸出結(jié)果如下所示，包括書(shū)籍表和學(xué)生表，其中學(xué)生表包括序號(hào)、姓名和性別。
>>> 插入后包含表: books student >>>(4) 插入數(shù)據(jù)
插入數(shù)據(jù)也是定義好SQL語(yǔ)句，調(diào)用execute(sql)方法實(shí)現(xiàn)。核心代碼是：
通常插入的新數(shù)據(jù)需要通過(guò)變量進(jìn)行賦值，其值不是固定的，參考文件。
# coding:utf-8 # By：Eastmount CSDN import MySQLdbtry:conn=MySQLdb.connect(host='localhost',user='root',passwd='123456',port=3306, db='bookmanage', charset='utf8')cur=conn.cursor()#插入數(shù)據(jù)sql = '''insert into student values(%s, %s, %s)'''cur.execute(sql, ('3', 'xiaoyang', '男'))#查看數(shù)據(jù)print('\n插入數(shù)據(jù):')cur.execute('select * from student')for data in cur.fetchall():print('%s %s %s' % data)cur.close()conn.commit()conn.close() except MySQLdb.Error as e:print("Mysql Error %d: %s" % (e.args[0], e.args[1]))輸出結(jié)果如下：
>>> 插入數(shù)據(jù): 3 xiaoyang 男 >>>這里作者只講述了這幾種常見(jiàn)的數(shù)據(jù)庫(kù)操作，其他SQL語(yǔ)句類(lèi)似，請(qǐng)讀者下來(lái)自行閱讀實(shí)踐。接下來(lái)講解Python操作輕量型數(shù)據(jù)集Sqlite3。
三.Python操作Sqlite3數(shù)據(jù)庫(kù)
SQLite是一款輕型數(shù)據(jù)庫(kù)，是一種遵守事務(wù)ACID性質(zhì)的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，它占用的資源非常低，能夠支持Windows/Linux/Unix等主流操作系統(tǒng)，同時(shí)能夠跟很多程序語(yǔ)言如C#、PHP、Java、Python等相結(jié)合使用。
SQLite可以使用Sqlite3模塊與Python進(jìn)行集成，Sqlite3模塊是由Gerhard Haring編寫(xiě)的，提供了一個(gè)與DB-API 2.0規(guī)范兼容的SQL接口。讀者可以直接使用Sqlite3模塊，因?yàn)镻ython 2.5.x 以上版本默認(rèn)自帶了該模塊。
Sqlite3使用方法和前面介紹的MySQLdb庫(kù)類(lèi)似，首先必須創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)的連接對(duì)象，然后有選擇地創(chuàng)建光標(biāo)對(duì)象，再定義SQL語(yǔ)句進(jìn)行執(zhí)行，最后需要關(guān)閉對(duì)象和連接。Sqlite3常用方法如下所示：
- sqlite3.connect()
打開(kāi)一個(gè)到SQLite數(shù)據(jù)庫(kù)文件database的連接。 - connection.cursor()
創(chuàng)建一個(gè)cursor，將在Python數(shù)據(jù)庫(kù)編程中用到。 - cursor.execute(sql)
執(zhí)行一個(gè)sql語(yǔ)句，注意sql 語(yǔ)句可以被參數(shù)化使用。 - cursor.executescript(sql)
該例程一旦接收到腳本，會(huì)執(zhí)行多個(gè)sql語(yǔ)句。sql語(yǔ)句應(yīng)該用分號(hào)分隔。 - connection.commit()
提交當(dāng)前的事務(wù)。 - connection.rollback()
回滾至上一次調(diào)用commit()對(duì)數(shù)據(jù)庫(kù)所做的更改。 - connection.close()
關(guān)閉數(shù)據(jù)庫(kù)連接。 - cursor.fetchone()
獲取查詢(xún)結(jié)果集中的下一行，返回一個(gè)單一的序列，當(dāng)沒(méi)有更多可用的數(shù)據(jù)時(shí)，則返回 None。 - cursor.fetchmany()
獲取查詢(xún)結(jié)果集中的下一行組數(shù)據(jù)，返回一個(gè)列表。 - cursor.fetchall()
獲取查詢(xún)結(jié)果集中所有的數(shù)據(jù)行，返回一個(gè)列表。
下面這段代碼是Python操作Sqlite3的基礎(chǔ)用法，其語(yǔ)法基本和前面講述的MySQLdb庫(kù)類(lèi)似。
#-*- coding:utf-8 -*- # By：Eastmount CSDN import sqlite3#連接數(shù)據(jù)庫(kù)：如果數(shù)據(jù)庫(kù)不存在則創(chuàng)建 conn = sqlite3.connect('test6.db') cur = conn.cursor() print('數(shù)據(jù)庫(kù)創(chuàng)建成功.\n')#創(chuàng)建表 PEOPLE(編號(hào),姓名,年齡,公司,薪水) cur.execute('''CREATE TABLE PEOPLE(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,COMPANY CHAR(50),SALARY REAL);''') print("PEOPLE表創(chuàng)建成功.\n") conn.commit()#插入數(shù)據(jù) cur.execute("INSERT INTO PEOPLE (ID,NAME,AGE,COMPANY,SALARY) \VALUES (1, '小楊', 26, '華為', 10000.00 )"); cur.execute("INSERT INTO PEOPLE (ID,NAME,AGE,COMPANY,SALARY) \VALUES (2, '小顏', 26, '百度', 8800.00 )"); cur.execute("INSERT INTO PEOPLE (ID,NAME,AGE,COMPANY,SALARY) \VALUES (3, '小紅', 28, '騰訊', 9800.00 )"); conn.commit() print("數(shù)據(jù)插入成功.\n")#查詢(xún)操作 cursor = cur.execute("SELECT id, name, age, company, salary from PEOPLE") print("數(shù)據(jù)查詢(xún)成功.") print("序號(hào)", "姓名", "年齡", "公司", "薪水") for row in cursor:print(row[0], row[1], row[2], row[3], row[4]) print('')#更新操作 cur.execute("UPDATE PEOPLE set COMPANY = '華為' where ID=2") conn.commit() print("數(shù)據(jù)更新成功.") cursor = cur.execute("SELECT id, name, company from PEOPLE") for row in cursor:print(row[0], row[1], row[2]) print('')#刪除操作 cur.execute("DELETE from PEOPLE where COMPANY='華為';") conn.commit() print("數(shù)據(jù)刪除成功.") cursor = cur.execute("SELECT id, name, company from PEOPLE") for row in cursor:print(row[0], row[1], row[2]) print('')#關(guān)閉連接 conn.close()輸出結(jié)果如圖31所示：
其步驟如下：
- 1.首先在本地創(chuàng)建了一個(gè)test6.db的數(shù)據(jù)庫(kù)文件。
- 2.執(zhí)行游標(biāo)中的execute()函數(shù)，創(chuàng)建了PEOPLE表，包括編號(hào)、姓名、年齡、公司、薪水等字段，字段涉及各種數(shù)據(jù)類(lèi)型。
- 3.執(zhí)行插入數(shù)據(jù)庫(kù)操作，注意需要調(diào)用conn.commit()函數(shù)提交執(zhí)行。
- 4.執(zhí)行查詢(xún)操作，SQL語(yǔ)句為“SELECT id, name, age, company, salary from PEOPLE”，然后通過(guò)for循環(huán)獲取查詢(xún)的結(jié)果，顯示“小楊”、“小顏”、“小紅”的信息。
- 5.執(zhí)行更新操作并查詢(xún)數(shù)據(jù)結(jié)果，將編號(hào)為“2”的公司信息更改為“華為”。
- 6.執(zhí)行刪除操作，刪除公司名稱(chēng)為“華為”的數(shù)據(jù)，最后剩下“小紅”的信息。
更多數(shù)據(jù)庫(kù)實(shí)際操作將在后面的數(shù)據(jù)分析章節(jié)和爬蟲(chóng)部分進(jìn)行詳細(xì)介紹，同時(shí)推薦讀者深入研究Python操作數(shù)據(jù)庫(kù)的知識(shí)，包括事務(wù)、存儲(chǔ)過(guò)程、觸發(fā)器等內(nèi)容。
四.本章小結(jié)
數(shù)據(jù)庫(kù)是按照數(shù)據(jù)結(jié)構(gòu)來(lái)組織、存儲(chǔ)和管理數(shù)據(jù)的倉(cāng)庫(kù)，用戶(hù)可以通過(guò)數(shù)據(jù)庫(kù)來(lái)存儲(chǔ)和管理所需的數(shù)據(jù)，包括簡(jiǎn)單的數(shù)據(jù)表格、海量數(shù)據(jù)等。數(shù)據(jù)庫(kù)被廣泛應(yīng)用于各行各業(yè)，比如信息管理系統(tǒng)、辦公自動(dòng)化系統(tǒng)、各種云信息平臺(tái)等。
本章為什么要介紹Python操作數(shù)據(jù)庫(kù)知識(shí)呢？
- 一方面，數(shù)據(jù)爬取、數(shù)據(jù)存儲(chǔ)、數(shù)據(jù)分析、數(shù)據(jù)可視化是密不可分的四個(gè)部分，當(dāng)我們爬取了相關(guān)數(shù)據(jù)之后，需要將其存儲(chǔ)至數(shù)據(jù)庫(kù)中，這能夠更加標(biāo)準(zhǔn)化、智能化、自動(dòng)化、便捷地管理數(shù)據(jù)，也為后續(xù)的數(shù)據(jù)分析提供強(qiáng)大的技術(shù)支撐，能夠自定義提取所需數(shù)據(jù)塊進(jìn)行分析；
- 另一方面，數(shù)據(jù)庫(kù)為數(shù)據(jù)共享，實(shí)現(xiàn)數(shù)據(jù)集中控制，保證數(shù)據(jù)的一致性和可維性提供保障。所以學(xué)習(xí)Python操作數(shù)據(jù)庫(kù)是非常必要的，也希望讀者認(rèn)真學(xué)習(xí)本章內(nèi)容，掌握Python操作MySQL數(shù)據(jù)庫(kù)、Sqlite3輕量型數(shù)據(jù)庫(kù)知識(shí)，并應(yīng)用到自己的科研項(xiàng)目中去。
該系列所有代碼下載地址：
- https://github.com/eastmountyxz/Python-zero2one
2020年在github的綠瓷磚終于貼完了第一年提交2100余次，獲得1500多+stars，開(kāi)源93個(gè)倉(cāng)庫(kù)，300個(gè)粉絲。挺開(kāi)心的，希望自己能堅(jiān)持在github打卡五年，督促自己不斷前行。當(dāng)然也存在很多不足之處，希望來(lái)年分享更高質(zhì)量的資源，也希望能將安全和AI頂會(huì)論文系列總結(jié)進(jìn)來(lái)，真誠(chéng)的希望它們能幫助到大家，感恩有你，一起加油~
最后，真誠(chéng)地感謝您關(guān)注“娜璋之家”公眾號(hào)，感謝CSDN這么多年的陪伴，會(huì)一直堅(jiān)持分享，希望我的文章能陪伴你成長(zhǎng)，也希望在技術(shù)路上不斷前行。文章如果對(duì)你有幫助、有感悟，就是對(duì)我最好的回報(bào)，且看且珍惜！2020年8月18日建立的公眾號(hào)，再次感謝您的關(guān)注，也請(qǐng)幫忙宣傳下“娜璋之家”，哈哈~初來(lái)乍到，還請(qǐng)多多指教。
(By:娜璋之家 Eastmount 2021-03-03 夜于貴陽(yáng) https://blog.csdn.net/Eastmount )
參考文獻(xiàn)如下：
- [1] 作者書(shū)籍《Python網(wǎng)絡(luò)數(shù)據(jù)爬取及分析從入門(mén)到精通》
- [2] MySQL官網(wǎng). MySQL[EB/OL]. （2017）[2017-09-23]. https://www.mysql.com/.
- [3] 楊秀璋. [python] 專(zhuān)題九.Mysql數(shù)據(jù)庫(kù)編程基礎(chǔ)知識(shí) - CSDN博客[EB/OL]. （2016-08-10）[2017-09-23]. http://blog.csdn.net/eastmount/article/details/52156383.
- [4] 楊秀璋. 專(zhuān)欄：數(shù)據(jù)庫(kù)實(shí)戰(zhàn)開(kāi)發(fā)設(shè)計(jì)與優(yōu)化 - CSDN博客[EB/OL]. （2016）[2017-09-23]. http://blog.csdn.net/column/details/14842.html.
- [5] runoob.com官網(wǎng). SQLite - Python[EB/OL]. （2017）[2017-09-23]. http://www.runoob.com/sqlite/sqlite-python.html.
- [6] 楊秀璋. [python爬蟲(chóng)] Selenium爬取內(nèi)容并存儲(chǔ)至MySQL數(shù)據(jù)庫(kù) - CSDN博客[EB/OL]. （2017-03-13）[2017-09-23]. http://blog.csdn.net/eastmount/article/details/61914613.
總結(jié)
以上是生活随笔為你收集整理的[Python从零到壹] 八.数据库之MySQL和Sqlite基础知识及操作万字详解的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： [网络安全提高篇] 一〇二.Metasp
- 下一篇： [Python图像处理] 三十八.Ope