第8章Python數(shù)據(jù)庫應(yīng)用

1、第第8 8章章 PythonPython數(shù)據(jù)庫應(yīng)用數(shù)據(jù)庫應(yīng)用8.1 數(shù)據(jù)庫基礎(chǔ)u 數(shù)據(jù)庫(Database)數(shù)據(jù)庫是數(shù)據(jù)的集合，數(shù)據(jù)庫能將大量數(shù)據(jù)按照一定的方式組織并存儲起來，方便的進(jìn)行管理和維護(hù)。u 數(shù)據(jù)庫的特征 以一定的方式組織、存儲數(shù)據(jù)能為多個用戶共享具有盡可能少的冗余代碼與程序彼此獨(dú)立8.1.1 數(shù)據(jù)庫基本概念u 數(shù)據(jù)庫管理系統(tǒng)（DBMS）數(shù)據(jù)庫管理系統(tǒng)（database management system）是一種操縱和管理數(shù)據(jù)庫的大型軟件，是用于建立、使用和維護(hù)數(shù)據(jù)庫。它對數(shù)據(jù)庫進(jìn)行統(tǒng)一的管理和控制，以保證數(shù)據(jù)庫的安全性和完整性。它的功能包括： （1）數(shù)據(jù)定義功能。 （2）數(shù)據(jù)存取功

2、能。 （3）數(shù)據(jù)庫運(yùn)行管理功能。 （4）數(shù)據(jù)庫的建立和維護(hù)功能。 （5）數(shù)據(jù)庫的傳輸。常用的數(shù)據(jù)庫管理系統(tǒng)有MS SQL、 SYBASE、DB2、ORACLE、MySQL等。u 數(shù)據(jù)庫系統(tǒng)（DBS）數(shù)據(jù)庫可分為層次型數(shù)據(jù)庫、對象型數(shù)據(jù)庫和關(guān)系型數(shù)據(jù)庫。數(shù)據(jù)庫可分為層次型數(shù)據(jù)庫、對象型數(shù)據(jù)庫和關(guān)系型數(shù)據(jù)庫。關(guān)系型數(shù)據(jù)庫是目前的主流數(shù)據(jù)庫類型。關(guān)系型數(shù)據(jù)庫不僅描關(guān)系型數(shù)據(jù)庫是目前的主流數(shù)據(jù)庫類型。關(guān)系型數(shù)據(jù)庫不僅描述數(shù)據(jù)本身，而且對數(shù)據(jù)之間的關(guān)系進(jìn)行描述。述數(shù)據(jù)本身，而且對數(shù)據(jù)之間的關(guān)系進(jìn)行描述。u常用術(shù)語關(guān)系：關(guān)系：可以理解為一張二維表，每一個關(guān)系都有一個關(guān)系名，也就是表名。屬性屬性: : 可以

3、理解為二維表中的一列，在數(shù)據(jù)庫中稱為字段。元組：元組：可以理解為二維表中的一行，在數(shù)據(jù)庫中稱為記錄。域：域：屬性的取值范圍，也就是數(shù)據(jù)庫中某一列的取值范圍。關(guān)鍵字：關(guān)鍵字：一組可以唯一標(biāo)識元組的屬性，數(shù)據(jù)庫中稱為主鍵，可以由一個或者多個列組成。 8.1.2 關(guān)系型數(shù)據(jù)庫u關(guān)系型數(shù)據(jù)庫實體間的聯(lián)系uDB-APIDB-API是一個規(guī)范。它定義了一系列必需的對象和數(shù)據(jù)庫存取方式，以便為各種各樣的底層數(shù)據(jù)庫系統(tǒng)和多種多樣的數(shù)據(jù)庫接口程序提供一致的訪問接口。從Python中訪問數(shù)據(jù)庫需要接口程序，接口程序是一個Python模塊，它提供數(shù)據(jù)庫客戶端（通常是C語言編寫）的接口以供訪問，所有的Python接口

4、程序都一定程度上遵守Python DB-API規(guī)范。8.2 結(jié)構(gòu)化查詢語言SQL數(shù)據(jù)庫命令和查詢操作需要通過SQL語言來執(zhí)行，SQL（Structured Query Language“結(jié)構(gòu)化查詢語言”）是通用的關(guān)系型數(shù)據(jù)庫操作語言?？梢圆樵?、定義、操縱和控制數(shù)據(jù)庫。它是一種非過程化語言。用于創(chuàng)建數(shù)據(jù)庫中的表。它的語法格式為：CREATE TABLE 表名稱(列名稱1 數(shù)據(jù)類型,列名稱2 數(shù)據(jù)類型,列名稱3 數(shù)據(jù)類型,.）u例CREATE TABLE students(stuNumber varchar(12),stuName varchar(255),age integer(2),sex v

5、archar(2),score integer(4),Address varchar(255),) 8.2.1 數(shù)據(jù)表的建立（CREATE TABLE）和刪除（DROP）uCREATE TABLE uDROP用于刪除表（表的結(jié)構(gòu)、屬性以及索引也會被刪除）,它的語法格式為：DROP TABLE 表名稱DROP TABLE studentsu例用于從表中選取數(shù)據(jù)。結(jié)果被存儲在一個結(jié)果表中（稱為結(jié)果集）。查詢語句語法如下所示：SELECT 字段表 FROM 表名 WHERE 查詢條件 GROUP BY 分組字段 ORDER BY 字段ASC|DESCu 字段表8.2.2 查詢語句SELECT指出所查

6、詢列，它可以是一組列名、星號、表達(dá)式、變量等構(gòu)成。例：查詢students表中所有列的數(shù)據(jù)。 SELECT * FROM students例：查詢表students中所有記錄的stuName, stuNumber字段內(nèi)容。 SELECT stuName, stuNumber FROM studentsu WHERE子句8.2.2 查詢語句SELECTWHERE子句設(shè)置查詢條件，過濾掉不需要的數(shù)據(jù)行。WHERE子句可包括各種條件運(yùn)算符（1）比較運(yùn)算符：；、=、=、；、=、；、?。?、！18 AND sex=女（5）模式匹配符：LIKE、NOT LIKE例：SELECT * FROM student

7、s WHERE name like “周%”例：SELECT * FROM students WHERE score like 80-90 說明：指定一個字符、字符串或范圍，要求所匹配對象為它們中的任一個。則要求所匹配對象為指定字符以外的任一個字符。u數(shù)據(jù)分組Group By8.2.2 查詢語句SELECTGROUP BY 子句用于結(jié)合聚合函數(shù)，根據(jù)一個或多個列對結(jié)果集進(jìn)行分組SELECT sex，avg(score) as 平均成績 FROM students Group By sex u例u常用的聚合函數(shù)u查詢結(jié)果排序Order By8.2.2 查詢語句SELECT使用ORDER BY子句

8、對查詢返回的結(jié)果按一列或多列排序。SELECT stuName，stuNumber FROM students ORDER BY stuSCORE DESEu例說明：ASC表示升序，為默認(rèn)值，DESC為降序INSERT INTO 語句用于向表格中插入新的行。它的語法格式為：INSERT INTO 數(shù)據(jù)表 (字段1,字段2,字段3 ) VALUES (值1,值2,值3 ) u 例8.2.3 添加記錄語句INSERT INTOINSERT INTO students (stuNumber，stuName，age，sex，score，address，city) VALUES(2010005，李帆，1

9、9，男，92，Changjiang 12，Zhengzhou)或：INSERT INTO students VALUES(2010005，李帆，19，男，92，Changjiang 12，Zhengzhou)。Update 語句用于修改表中的數(shù)據(jù)。語法格式為：UPDATE 表名 SET 列名 = 新值 WHERE 列名 = 某值u 例8.2.4 更新語句UPDATEUPDATE students SET age=age+1 WHERE sex=女UPDATE students SET Address = Zhongyuanlu 41, City = Zhengzhou WHERE stuNam

10、e = 李四DELETE 語句用于刪除表中的行。它的語法格式為：DELETE FROM 表名稱 WHERE 列名 = 值u 例8.2.5 刪除記錄語句DELETEDELETE FROM students WHERE stuName = 張三說明：DELETE FROM students表示刪除表中所有記錄。8.3 SQLite數(shù)據(jù)庫簡介Python自帶一個輕量級的關(guān)系型數(shù)據(jù)庫SQLite。SQLite是一種嵌入式關(guān)系型數(shù)據(jù)庫，它的數(shù)據(jù)庫就是一個文件。8.3.1 SQLite數(shù)據(jù)庫每個存放在SQLite3數(shù)據(jù)庫中的值，都具有下表中的一種存儲類型：8.3.2 SQLite3的數(shù)據(jù)類型存儲類型存儲類

11、型說明說明NULL 空值INTEGER 帶符號整數(shù)，根據(jù)存入的數(shù)值的大小占據(jù)1,2,3,4,6或者8個字節(jié)REAL浮點數(shù)，采用8byte（即雙精度）的IEEE格式表示TEXT 字符串文本，采用數(shù)據(jù)庫的編碼（UTF-8，UTF-16BE 或者UTF- 16LE）BLOB 無類型，可用于保存二進(jìn)制文件1. SQLite時間時間/日期函數(shù)日期函數(shù)：datetime()：產(chǎn)生日期和時間date()：產(chǎn)生日期time()：產(chǎn)生時間 strftime()：對以上三個函數(shù)產(chǎn)生的日期和時間進(jìn)行格式化8.3.3 SQLite3的函數(shù)2. SQlite算術(shù)函數(shù)算術(shù)函數(shù)：abs(X)：返回絕對值max(X,Y,.)

12、：返回最大值min(X,Y,.)：返回最小值random(*)：返回隨機(jī)數(shù)round(X,Y)： 四舍五入3. SQLite字符串處理函數(shù)length(x) ：返回字符串字符個數(shù)lower(x) ：大寫轉(zhuǎn)小寫upper(x)：小寫轉(zhuǎn)大寫substr(x,y,Z)：截取子串like(A,B)：確定給定的字符串與指定的模式是否匹配8.3.3 SQLite3的函數(shù)4.其它函數(shù)：typeof(x)：返回數(shù)據(jù)的類型last_insert_rowid()：返回最后插入的數(shù)據(jù)的IDPython標(biāo)準(zhǔn)模塊sqlite3使用C語言實現(xiàn)，提供訪問和操作數(shù)據(jù)庫SQLite的各種功能。Sqlite3模塊主要包括下列常量

13、、函數(shù)和對象：8.3.4 SQLite3的模塊Sqlite3.Version：常量，版本號。Sqlite3.Connect（database）：）：函數(shù)，鏈接到數(shù)據(jù)庫，返回Connect對象。Sqlite3.Connect：數(shù)據(jù)庫連接對象。Sqlite3.Cursor：游標(biāo)對象。Sqlite3.Row：行對象。8.4 Python的SQLite3數(shù)據(jù)庫編程Python2.5版本以上就內(nèi)置了SQLite3，所以，在Python中使用SQLite，不需要安裝任何東西，直接使用。SQLite3數(shù)據(jù)庫使用SQL語言。SQLite作為后端數(shù)據(jù)庫，可以制作有數(shù)據(jù)存儲需求的工具。Python標(biāo)準(zhǔn)庫中的SQL

14、ite3提供該數(shù)據(jù)庫的接口。Python的數(shù)據(jù)庫模塊有統(tǒng)一的接口標(biāo)準(zhǔn)，所以數(shù)據(jù)庫操作都有統(tǒng)一的模式，操作數(shù)據(jù)庫SQLite3 主要分為以下幾步：8.4.1 訪問數(shù)據(jù)庫的步驟1.導(dǎo)入導(dǎo)入Python SQLite數(shù)據(jù)庫模塊數(shù)據(jù)庫模塊 import sqlite32. 建立數(shù)據(jù)庫連接，返回建立數(shù)據(jù)庫連接，返回Connection對象對象 con = sqlite3.connect(connectstring) 例：con=sqlite3.connect(“E:test.db”)3. 創(chuàng)建游標(biāo)對象創(chuàng)建游標(biāo)對象 cur=con.cursor() 4. 使用使用Cursor對象的對象的execute執(zhí)行

15、執(zhí)行SQL命令返回結(jié)果集命令返回結(jié)果集 cur.execute(sql)：執(zhí)行SQL語句。 cur.execute(sql，parameters):執(zhí)行代參數(shù)的SQL語句。 cur.executemany(sql，seq_of_pqrameters)：根據(jù)參數(shù)執(zhí)行多次SQL語句。 cur.executescript(sql_script)：執(zhí)行SQL腳本。 例：cur.execute(CREATE TABLE category(id primary key，sort，name)5. 獲取游標(biāo)的查詢結(jié)果集獲取游標(biāo)的查詢結(jié)果集 cur.fetchone()：返回結(jié)果集的下一行（Row對象）；無數(shù)據(jù)

16、時，返回None。 cur.fetchall()：返回結(jié)果集的剩余行（Row對象列表），無數(shù)據(jù)時，返回空List。 cur.fetchmany()：返回結(jié)果集的多行（Row對象列表），無數(shù)據(jù)時，返回空List。 例： print cur.fetchall() #提取查詢到的數(shù)據(jù) 也可以直接使用循環(huán)輸出結(jié)果，例如： For row in cur.execute(select * from catagory): Print(row0,row1)6. 數(shù)據(jù)庫的提交和回滾數(shù)據(jù)庫的提交和回滾 mit()：事務(wù)提交 con.rollback()：事務(wù)回滾 7. 關(guān)閉關(guān)閉Cursor對象和對象和Connec

17、tion對象對象 cur.close()：關(guān)閉 Cursor對象 con.close()：關(guān)閉Connection對象8.4.1 訪問數(shù)據(jù)庫的步驟【例8-17】：創(chuàng)建數(shù)據(jù)庫sales，并在其中創(chuàng)建表book，表中包含三列，id、price和name，其中id為主鍵（primary key）。8.4.2 創(chuàng)建數(shù)據(jù)庫和表#導(dǎo)入Python SQLite數(shù)據(jù)庫模塊import sqlite3#創(chuàng)建SQLite數(shù)據(jù)庫con=sqlite3.connect(E:sales.db)#創(chuàng)建表book：包含三個列，id（主鍵）、price和namecon.execute（create table book(i

18、d primary key，price，name)）說明：connection對象的execute方法是Cursor對象對應(yīng)方法的快捷方式，系統(tǒng)會創(chuàng)建一個臨時Cursor對象，然后調(diào)用對應(yīng)的方法，并返回Cursor對象。在數(shù)據(jù)庫表中插入、更新、刪除記錄的一般步驟為：1.建立數(shù)據(jù)庫連接；2.創(chuàng)建游標(biāo)對象cur，使用cur.execute(sql)執(zhí)行SQL的insert、Update、delete等語句完成數(shù)據(jù)庫記錄的插入、更新、刪除操作，并根據(jù)返回值判斷操作結(jié)果；3.提交操作；4.關(guān)閉數(shù)據(jù)庫。8.4.3 數(shù)據(jù)庫的插入、更新和刪除操作import sqlite3books=(021,25,大學(xué)計

19、算機(jī)),(022,30, 大學(xué)英語),(023,18, 藝術(shù)欣賞 ) ,( 024,35, 高級語言程序設(shè)計)#打開數(shù)據(jù)庫Con=sqlite3.connect(E:sales.db)#創(chuàng)建游標(biāo)對象Cur=Con.cursor()#插入一行數(shù)據(jù)Cur.execute(insert into book(id,price,name) values (001,33,大學(xué)計算機(jī)多媒體)Cur.execute(insert into book(id,price,name) values (?,?,?) ,(002,28,數(shù)據(jù)庫基礎(chǔ))8.4.3 數(shù)據(jù)庫的插入、更新和刪除操作#插入多行數(shù)據(jù)Cur.executeman