Python程序設(shè)計-第8章-數(shù)據(jù)庫的訪問與操作

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

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

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

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

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

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

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

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

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

10、on連接MySQL數(shù)據(jù)庫的接口，它實現(xiàn)了 Python 數(shù)據(jù)庫 API 規(guī)范 V2.0。為了能夠訪問MySQL數(shù)據(jù)，需要下載MySQL數(shù)據(jù)庫模塊并安裝此模塊。安裝MySQLdb，請訪問/projects/MySQL-python，(Linux平臺可以訪問：/pypi/MySQL-python)從這里可選擇適合平臺的安裝包，安裝包分為預(yù)編譯的二進制文件和源代碼安裝包。8.3MySQL數(shù)據(jù)庫導(dǎo)入如果選擇二進制文件發(fā)行版本的話，安裝過程基本安裝提示即可完成。如果從源代碼進行安裝的話，則需要切換到MySQLdb發(fā)行版本的頂級目錄，并鍵入下列命令：$ 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ù)庫導(dǎo)入使用Python DB API訪問數(shù)據(jù)庫的流程圖如下圖所示本章內(nèi)容8.1數(shù)據(jù)庫8.2 SQL語言8.3 MySQL數(shù)據(jù)庫導(dǎo)入8.4數(shù)據(jù)庫連接8.5數(shù)據(jù)庫的操作8.6執(zhí)行事務(wù)8.7處理異常8.8本章總結(jié)8.4數(shù)據(jù)庫連接在Python語言中，在連接數(shù)據(jù)庫前，編程人員首先需要確認以下事項：1）在程序中是否已經(jīng)創(chuàng)建了數(shù)據(jù)庫 TESTDB。2）在TESTDB數(shù)據(jù)

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

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

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

15、中插入新記錄。INSERT INTO 語句可以有兩種編寫形式。第一種形式無需指定要插入數(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 語句用于更新表中已經(jīng)存在的記錄，UPDATE語法如下：UPDATEtable_name SETcolumn1

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

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

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

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

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