Python程序設(shè)計(jì)-第8章-數(shù)據(jù)庫(kù)的訪(fǎng)問(wèn)與操作

1、Python程序設(shè)計(jì)8數(shù)據(jù)庫(kù)的訪(fǎng)問(wèn)與操作第章在Python語(yǔ)言中，存儲(chǔ)數(shù)據(jù)訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)采用標(biāo)準(zhǔn)數(shù)據(jù)庫(kù)接口：Python DB-API，Python DB-API為編程人員提供數(shù)據(jù)庫(kù)應(yīng)用編程接口。Python 數(shù)據(jù)庫(kù)接口支持非常多的數(shù)據(jù)庫(kù)，編程者可以選擇適合自己項(xiàng)目的數(shù)據(jù)庫(kù)Python的DB-API，為大多數(shù)的數(shù)據(jù)庫(kù)實(shí)現(xiàn)了接口，使用它連接各數(shù)據(jù)庫(kù)后，就可以用相同的方式操作各數(shù)據(jù)庫(kù)。本章將以開(kāi)源數(shù)據(jù)庫(kù)-MySQL數(shù)據(jù)庫(kù)為例進(jìn)行詳細(xì)講解數(shù)據(jù)庫(kù)的訪(fǎng)問(wèn)和操作。前言本章內(nèi)容8.1數(shù)據(jù)庫(kù)8.2 SQL語(yǔ)言8.3 MySQL數(shù)據(jù)庫(kù)導(dǎo)入8.4數(shù)據(jù)庫(kù)連接8.5數(shù)據(jù)庫(kù)的操作8.6執(zhí)行事務(wù)8.7處理異常8.8本章總結(jié)8

2、.1數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)通常采用文件系統(tǒng)作為基本的持久化存儲(chǔ)，數(shù)據(jù)庫(kù)存儲(chǔ)可以采用普通的操作系統(tǒng)文件、專(zhuān)用的操作系統(tǒng)文件，甚至是原始的磁盤(pán)分區(qū)。數(shù)據(jù)庫(kù)存儲(chǔ)一般抽象為一張表，每行數(shù)據(jù)都有一些字段對(duì)應(yīng)數(shù)據(jù)庫(kù)的列，每一列的表定義的集合以及每個(gè)表的數(shù)據(jù)類(lèi)型放到一起定義了數(shù)據(jù)庫(kù)的模式。數(shù)據(jù)庫(kù)可以創(chuàng)建和刪除，表也一樣。當(dāng)查詢(xún)一個(gè)數(shù)據(jù)庫(kù)的時(shí)候，可以一次性取回一次性結(jié)果，也可以逐條遍歷每個(gè)結(jié)果行，一些數(shù)據(jù)庫(kù)使用游標(biāo)的概念來(lái)提交SQL命令，查詢(xún)以及獲取結(jié)果，不管是一次性獲取還是逐行獲取都可以使用這個(gè)概念。8.1.1數(shù)據(jù)庫(kù)訪(fǎng)問(wèn)在Python語(yǔ)言中，數(shù)據(jù)庫(kù)的訪(fǎng)問(wèn)是通過(guò)適配器的方式進(jìn)行訪(fǎng)問(wèn)，適配器是一個(gè)Python模塊，使用它

3、可以與關(guān)系數(shù)據(jù)庫(kù)的客戶(hù)端庫(kù)（通常是使用C語(yǔ)言編寫(xiě)的）接口相連。一般情況下會(huì)推薦所有的Python適配器應(yīng)當(dāng)符合Python數(shù)據(jù)庫(kù)特殊興趣小組的API標(biāo)準(zhǔn)。Python數(shù)據(jù)庫(kù)應(yīng)用結(jié)構(gòu)如圖8-1所示，Python數(shù)據(jù)庫(kù)適配器（DB-API）是連接到數(shù)據(jù)庫(kù)客戶(hù)端的C語(yǔ)言庫(kù)接口。8.1.1數(shù)據(jù)庫(kù)訪(fǎng)問(wèn)8.1.2DB-API接口屬性描述apilevel需要適配器兼容的DB-API版本threadsafety本模塊線(xiàn)程安全級(jí)別paramstyle本模塊sql語(yǔ)句參數(shù)風(fēng)格connect()Connect()函數(shù)8.1.2DB-API接口下面將依次介紹這四個(gè)屬性：（1）apilevel：該字符串指明了模塊要兼容

4、的DB-API最高版本，比如，1.0,2.0等，默認(rèn)值是1.0（2）threadsafety：這是一個(gè)整型值，可選值如下：0：不支持線(xiàn)程安全，線(xiàn)程間不能共享模塊。1：最小化線(xiàn)程安全支持，線(xiàn)程間可以共享模塊，但是不能共享連接。2：適度的線(xiàn)程安全支持，線(xiàn)程間可以共享模塊和連接，但是不能共享游標(biāo)。3：完整的線(xiàn)程安全支持，線(xiàn)程間可以共享模塊，連接和游標(biāo)。（3） paramstyle：該參數(shù)是一個(gè)字符串，用于指定構(gòu)建查詢(xún)行或命令時(shí)使用的字符串替代形式（4）函數(shù)屬性：connect()函數(shù)通過(guò)Connection對(duì)象訪(fǎng)問(wèn)數(shù)據(jù)本章內(nèi)容8.1數(shù)據(jù)庫(kù)8.2 SQL語(yǔ)言8.3 MySQL數(shù)據(jù)庫(kù)導(dǎo)入8.4數(shù)據(jù)庫(kù)連接

5、8.5數(shù)據(jù)庫(kù)的操作8.6執(zhí)行事務(wù)8.7處理異常8.8本章總結(jié)8.2.1SQL簡(jiǎn)介SQL是結(jié)構(gòu)化查詢(xún)語(yǔ)言，全稱(chēng)是 Structured Query Language ，是用于訪(fǎng)問(wèn)和處理數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)的計(jì)算機(jī)語(yǔ)言。盡管SQL 是一門(mén) ANSI（American National Standards Institute 美國(guó)國(guó)家標(biāo)準(zhǔn)化組織）標(biāo)準(zhǔn)的計(jì)算機(jī)語(yǔ)言，但是仍然存在著多種不同版本的 SQL 語(yǔ)言。然而，為了與 ANSI 標(biāo)準(zhǔn)相兼容，它們必須以相似的方式共同支持一些主要的命令（比如 SELECT、UPDATE、DELETE、INSERT、WHERE 等等）8.2.1SQL簡(jiǎn)介采用SQL語(yǔ)言，能夠?qū)崿F(xiàn)

6、數(shù)據(jù)庫(kù)的以下操作：（1）SQL 面向數(shù)據(jù)庫(kù)執(zhí)行查詢(xún)（2）SQL 可從數(shù)據(jù)庫(kù)取回?cái)?shù)據(jù)（3）SQL 可在數(shù)據(jù)庫(kù)中插入新的記錄（4）SQL 可更新數(shù)據(jù)庫(kù)中的數(shù)據(jù)（5）SQL 可從數(shù)據(jù)庫(kù)刪除記錄（6）SQL 可創(chuàng)建新數(shù)據(jù)庫(kù)（7）SQL 可在數(shù)據(jù)庫(kù)中創(chuàng)建新表（8）SQL 可在數(shù)據(jù)庫(kù)中創(chuàng)建存儲(chǔ)過(guò)程（9）SQL 可在數(shù)據(jù)庫(kù)中創(chuàng)建視圖（10）SQL 可以設(shè)置表、存儲(chǔ)過(guò)程和視圖的權(quán)限8.2.2SQL語(yǔ)法SQL命令描 述SELECT從數(shù)據(jù)庫(kù)中提取數(shù)據(jù)UPDATE更新數(shù)據(jù)庫(kù)中的數(shù)據(jù)DELETE從數(shù)據(jù)庫(kù)中刪除數(shù)據(jù)INSERT INTO向數(shù)據(jù)庫(kù)中插入新數(shù)據(jù)CREATE DATABASE創(chuàng)建新數(shù)據(jù)庫(kù)ALTER DATAB

7、ASE修改數(shù)據(jù)庫(kù)DROP TABLE刪除表CREATE INDEX創(chuàng)建索引（搜索鍵）DROP INDEX刪除索引8.2.3SQL視圖視圖是基于 SQL 語(yǔ)句的結(jié)果集的可視化的表。視圖包含行和列，就像一個(gè)真實(shí)的表。視圖中的字段就是來(lái)自一個(gè)或多個(gè)數(shù)據(jù)庫(kù)中的真實(shí)的表中的字段。我們可以向視圖添加 SQL 函數(shù)、WHERE 以及 JOIN 語(yǔ)句，也可以呈現(xiàn)數(shù)據(jù)，就像這些數(shù)據(jù)來(lái)自于某個(gè)單一的表一樣。（1）創(chuàng)建視圖SQL CREATE VIEW 語(yǔ)法如下所示：CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condit

8、ion8.2.3SQL視圖（2）更新視圖SQL CREATE OR REPLACE VIEW 語(yǔ)法如下所示：CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition（3）撤銷(xiāo)視圖通過(guò) DROP VIEW 命令來(lái)刪除視圖，具體語(yǔ)法如下：DROP VIEW view_name;8.2.4SQL函數(shù)SQL命令描 述AVG()返回平均值COUNT()返回行數(shù)FIRST()返回第一個(gè)記錄的值LAST()返回最后一個(gè)記錄的值MAX()返回最大值MIN()返回最小值SUM()返回總和SQ

9、L Aggregate 函數(shù)8.2.4SQL函數(shù)SQL命令描 述UCASE()將某個(gè)字段轉(zhuǎn)換為大寫(xiě)LCASE() 將某個(gè)字段轉(zhuǎn)換為小寫(xiě)MID() 從某個(gè)文本字段提取字符，MySql 中使用SubString(字段，1，end) 從某個(gè)文本字段提取字符LEN() 返回某個(gè)文本字段的長(zhǎng)度ROUND() 對(duì)某個(gè)數(shù)值字段進(jìn)行指定小數(shù)位數(shù)的四舍五入NOW() 返回當(dāng)前的系統(tǒng)日期和時(shí)間SQL Scalar 函數(shù)本章內(nèi)容8.1數(shù)據(jù)庫(kù)8.2 SQL語(yǔ)言8.3 MySQL數(shù)據(jù)庫(kù)導(dǎo)入8.4數(shù)據(jù)庫(kù)連接8.5數(shù)據(jù)庫(kù)的操作8.6執(zhí)行事務(wù)8.7處理異常8.8本章總結(jié)8.3MySQL數(shù)據(jù)庫(kù)導(dǎo)入MySQLdb 是用于Pyth

10、on連接MySQL數(shù)據(jù)庫(kù)的接口，它實(shí)現(xiàn)了 Python 數(shù)據(jù)庫(kù) API 規(guī)范 V2.0。為了能夠訪(fǎng)問(wèn)MySQL數(shù)據(jù)，需要下載MySQL數(shù)據(jù)庫(kù)模塊并安裝此模塊。安裝MySQLdb，請(qǐng)?jiān)L問(wèn)/projects/MySQL-python，(Linux平臺(tái)可以訪(fǎng)問(wèn)：/pypi/MySQL-python)從這里可選擇適合平臺(tái)的安裝包，安裝包分為預(yù)編譯的二進(jìn)制文件和源代碼安裝包。8.3MySQL數(shù)據(jù)庫(kù)導(dǎo)入如果選擇二進(jìn)制文件發(fā)行版本的話(huà)，安裝過(guò)程基本安裝提示即可完成。如果從源代碼進(jìn)行安裝的話(huà)，則需要切換到MySQLdb發(fā)行版本的頂級(jí)目錄，并鍵入下列命令：$ gunzip MySQL-python-1.2.2.

11、tar.gz$ tar -xvf MySQL-python-1.2.2.tar$ cd MySQL-python-1.2.2$ python setup.py build$ python setup.py install8.3MySQL數(shù)據(jù)庫(kù)導(dǎo)入使用Python DB API訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)的流程圖如下圖所示本章內(nèi)容8.1數(shù)據(jù)庫(kù)8.2 SQL語(yǔ)言8.3 MySQL數(shù)據(jù)庫(kù)導(dǎo)入8.4數(shù)據(jù)庫(kù)連接8.5數(shù)據(jù)庫(kù)的操作8.6執(zhí)行事務(wù)8.7處理異常8.8本章總結(jié)8.4數(shù)據(jù)庫(kù)連接在Python語(yǔ)言中，在連接數(shù)據(jù)庫(kù)前，編程人員首先需要確認(rèn)以下事項(xiàng)：1）在程序中是否已經(jīng)創(chuàng)建了數(shù)據(jù)庫(kù) TESTDB。2）在TESTDB數(shù)據(jù)

12、庫(kù)中是否已經(jīng)創(chuàng)建了表 EMPLOYEE。3）EMPLOYEE表字段為 FIRST_NAME, LAST_NAME, AGE, SEX 和 INCOME。4）是否設(shè)置了連接數(shù)據(jù)庫(kù)TESTDB使用的用戶(hù)名和密碼，當(dāng)然也可以自己設(shè)定或者直接使用root用戶(hù)名及其密碼，MySQL數(shù)據(jù)庫(kù)用戶(hù)授權(quán)請(qǐng)使用Grant命令。5）是否在機(jī)子上已經(jīng)安裝了 Python MySQLdb 模塊。只有在確認(rèn)完成了以上內(nèi)容后，才可以進(jìn)行數(shù)據(jù)庫(kù)連接操作8.4.1創(chuàng)建數(shù)據(jù)庫(kù)編程人員在編程時(shí)建立了數(shù)據(jù)庫(kù)連接后，首先要?jiǎng)?chuàng)建數(shù)據(jù)庫(kù)，創(chuàng)建數(shù)據(jù)庫(kù)需要調(diào)用數(shù)據(jù)庫(kù)API：MySQLdb通過(guò)import MySQLdb調(diào)用數(shù)據(jù)庫(kù)API：MyS

13、QLdb，采用connect（）方法創(chuàng)建數(shù)據(jù)庫(kù)的connect對(duì)象：db，然后使用db對(duì)象的cursor()方法獲取操作游標(biāo)cursor，使用execute方法執(zhí)行SQL語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)，調(diào)用db對(duì)象對(duì)象的select_db（）方法選擇數(shù)據(jù)庫(kù)COMDB，最后關(guān)閉游標(biāo)和數(shù)據(jù)庫(kù)連接。8.4.2創(chuàng)建表用戶(hù)在創(chuàng)建表之前，必須確認(rèn)所在的數(shù)據(jù)庫(kù)連接是否存在。如果數(shù)據(jù)庫(kù)連接存在，則可以使用execute()方法來(lái)為數(shù)據(jù)庫(kù)創(chuàng)建表調(diào)用connect()方法創(chuàng)建connect對(duì)象，打開(kāi)數(shù)據(jù)庫(kù)連接，使用cursor()方法獲取操作游標(biāo)cursor對(duì)象，如果數(shù)據(jù)表已經(jīng)存在使用 execute() 方法刪除表，執(zhí)行創(chuàng)建數(shù)

14、據(jù)表，關(guān)閉游標(biāo)cursor和數(shù)據(jù)庫(kù)連接db對(duì)象。本章內(nèi)容8.1數(shù)據(jù)庫(kù)8.2 SQL語(yǔ)言8.3 MySQL數(shù)據(jù)庫(kù)導(dǎo)入8.4數(shù)據(jù)庫(kù)連接8.5數(shù)據(jù)庫(kù)的操作8.6執(zhí)行事務(wù)8.7處理異常8.8本章總結(jié)8.5數(shù)據(jù)庫(kù)的操作數(shù)據(jù)庫(kù)操作是數(shù)據(jù)庫(kù)訪(fǎng)問(wèn)中最重要的操作，主要操作包括插入、修改、刪除等操作，數(shù)據(jù)庫(kù)操作首先創(chuàng)建數(shù)據(jù)庫(kù)連接對(duì)象，獲取游標(biāo)cursor對(duì)象，調(diào)用游標(biāo)cursor對(duì)象相關(guān)方法執(zhí)行數(shù)據(jù)庫(kù)操作，操作正常提交事務(wù)處理，操作異?；貪L事務(wù)，然后關(guān)閉游標(biāo)cursor對(duì)象，最后關(guān)閉數(shù)據(jù)庫(kù)連接connect對(duì)象，結(jié)束操作，整個(gè)數(shù)據(jù)庫(kù)操作的流程圖如圖8-15所示8.5.1插入數(shù)據(jù)INSERT INTO 語(yǔ)句用于向表

15、中插入新記錄。INSERT INTO 語(yǔ)句可以有兩種編寫(xiě)形式。第一種形式無(wú)需指定要插入數(shù)據(jù)的列名，只需提供被插入的值即可：INSERT INTOtable_name VALUES (value1,value2,value3,.);第二種形式需要指定列名及被插入的值：INSERT INTOtable_name(column1,column2,column3,.) VALUES (value1,value2,value3,.);8.5.2更新數(shù)據(jù)更新操作用于更新數(shù)據(jù)表的的數(shù)據(jù)，UPDATE 語(yǔ)句用于更新表中已經(jīng)存在的記錄，UPDATE語(yǔ)法如下：UPDATEtable_name SETcolumn1

16、=value1,column2=value2,. WHEREsome_column=some_value;8.5.3刪除數(shù)據(jù)刪除操作用于刪除數(shù)據(jù)表中的數(shù)據(jù)，DELETE 語(yǔ)句用于刪除表中的記錄。SQL DELETE 語(yǔ)法如下：DELETE FROMtable_name WHEREsome_column=some_value;8.5.4查詢(xún)數(shù)據(jù)查詢(xún)的結(jié)果將被存儲(chǔ)在一個(gè)結(jié)果表中，稱(chēng)為結(jié)果集。SELECTcolumn_name,column_name FROMtable_name;或SELECT * FROMtable_name;Python查詢(xún)MySQL使用 fetchone() 方法獲取單條數(shù)據(jù)

17、，使用fetchall() 方法獲取多條數(shù)據(jù)。1）fetchone()：該方法獲取下一個(gè)查詢(xún)結(jié)果集，結(jié)果集是一個(gè)對(duì)象。2）fetchall()：接收全部的返回結(jié)果行。3）Rowcount：這是一個(gè)只讀屬性，并返回執(zhí)行execute()方法后影響的行數(shù)。本章內(nèi)容8.1數(shù)據(jù)庫(kù)8.2 SQL語(yǔ)言8.3 MySQL數(shù)據(jù)庫(kù)導(dǎo)入8.4數(shù)據(jù)庫(kù)連接8.5數(shù)據(jù)庫(kù)的操作8.6執(zhí)行事務(wù)8.7處理異常8.8本章總結(jié)8.6執(zhí)行事務(wù)一個(gè)數(shù)據(jù)庫(kù)事務(wù)通常包含對(duì)數(shù)據(jù)庫(kù)進(jìn)行讀或?qū)懙囊粋€(gè)操作序列。它的存在包含有以下兩個(gè)目的：1）為數(shù)據(jù)庫(kù)操作提供了一個(gè)從失敗中恢復(fù)到正常狀態(tài)的方法，同時(shí)提供了數(shù)據(jù)庫(kù)即使在異常狀態(tài)下仍能保持一致性的方法

18、。2）當(dāng)多個(gè)應(yīng)用程序在并發(fā)訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)時(shí)，可以在這些應(yīng)用程序之間提供一個(gè)隔離方法，以防止彼此的操作互相干擾。8.6執(zhí)行事務(wù)但在現(xiàn)實(shí)情況下，失敗的風(fēng)險(xiǎn)很高。在一個(gè)數(shù)據(jù)庫(kù)事務(wù)的執(zhí)行過(guò)程中，有可能會(huì)遇上事務(wù)操作失敗、數(shù)據(jù)庫(kù)系統(tǒng)/操作系統(tǒng)失敗，甚至是存儲(chǔ)介質(zhì)失敗等情況。這便需要DBMS對(duì)一個(gè)執(zhí)行失敗的事務(wù)執(zhí)行恢復(fù)操作，將其數(shù)據(jù)庫(kù)狀態(tài)恢復(fù)到一致?tīng)顟B(tài)（數(shù)據(jù)的一致性得到保證的狀態(tài)）。為了實(shí)現(xiàn)將數(shù)據(jù)庫(kù)狀態(tài)恢復(fù)到一致?tīng)顟B(tài)的功能，DBMS通常需要維護(hù)事務(wù)日志以追蹤事務(wù)中所有影響數(shù)據(jù)庫(kù)數(shù)據(jù)的操作。8.6執(zhí)行事務(wù)事務(wù)機(jī)制可以確保數(shù)據(jù)一致性。事務(wù)必須具有4個(gè)屬性：原子性、一致性、隔離性、持久性。這四個(gè)屬性通常稱(chēng)為ACID

19、特性。原子性（atomicity）。一個(gè)事務(wù)是一個(gè)不可分割的工作單位，事務(wù)中包括的諸操作要么都做，要么都不做。一致性（consistency）。事務(wù)必須是使數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)變到另一個(gè)一致性狀態(tài)。一致性與原子性是密切相關(guān)的。隔離性（isolation）。一個(gè)事務(wù)的執(zhí)行不能被其他事務(wù)干擾。即一個(gè)事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對(duì)并發(fā)的其他事務(wù)是隔離的，并發(fā)執(zhí)行的各個(gè)事務(wù)之間不能互相干擾。持久性（durability）。持續(xù)性也稱(chēng)永久性（permanence），指一個(gè)事務(wù)一旦提交，它對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變就應(yīng)該是永久性的。接下來(lái)的其他操作或故障不應(yīng)該對(duì)其有任何影響。本章內(nèi)容8.1數(shù)據(jù)庫(kù)8.2 SQL語(yǔ)

20、言8.3 MySQL數(shù)據(jù)庫(kù)導(dǎo)入8.4數(shù)據(jù)庫(kù)連接8.5數(shù)據(jù)庫(kù)的操作8.6執(zhí)行事務(wù)8.7處理異常8.8本章總結(jié)8.7處理異常異常描述Warning當(dāng)有嚴(yán)重警告時(shí)觸發(fā)，例如插入數(shù)據(jù)是被截?cái)嗟鹊?。必須?StandardError 的子類(lèi)Error警告以外所有其他錯(cuò)誤類(lèi)。必須是 StandardError 的子類(lèi)InterfaceError當(dāng)有數(shù)據(jù)庫(kù)接口模塊本身的錯(cuò)誤（而不是數(shù)據(jù)庫(kù)的錯(cuò)誤）發(fā)生時(shí)觸發(fā)。 必須是Error的子類(lèi)DatabaseError和數(shù)據(jù)庫(kù)有關(guān)的錯(cuò)誤發(fā)生時(shí)觸發(fā)。 必須是Error的子類(lèi)DataError當(dāng)有數(shù)據(jù)處理時(shí)的錯(cuò)誤發(fā)生時(shí)觸發(fā)，例如：除零錯(cuò)誤，數(shù)據(jù)超范圍等等。 必須是DatabaseError的子類(lèi)OperationalError指