《Python程序設(shè)計基礎(chǔ)（微課版）》數(shù)據(jù)庫程序設(shè)計2/22知識與能力目標知識目標數(shù)據(jù)庫類型掌握關(guān)系數(shù)據(jù)庫與非關(guān)系數(shù)據(jù)庫的異同點MySQL語法掌握在MySQL中創(chuàng)建和管理數(shù)據(jù)庫的語法數(shù)據(jù)管理掌握在MySQL中管理數(shù)據(jù)的語法能力目標創(chuàng)建數(shù)據(jù)庫能夠根據(jù)實際需求創(chuàng)建數(shù)據(jù)庫數(shù)據(jù)庫維護能夠進行基本的數(shù)據(jù)庫維護工作通過掌握這些目標，您將能夠開發(fā)和維護電子檔案管理系統(tǒng)的數(shù)據(jù)庫部分數(shù)據(jù)庫的組成數(shù)據(jù)庫系統(tǒng)（DatabaseSystem，DBS）主要由數(shù)據(jù)庫（Database，DB），數(shù)據(jù)庫管理系統(tǒng)（DatabaseManagementSystem，DBMS）和應(yīng)用程序組成。數(shù)據(jù)庫是按照數(shù)據(jù)結(jié)構(gòu)來組織、存儲和管理數(shù)據(jù)的倉庫。數(shù)據(jù)庫中的數(shù)據(jù)為眾多用戶共享信息而建立，它擺脫了具體程序的限制和制約，不同用戶可以按照各自的方法使用數(shù)據(jù)庫中的數(shù)據(jù)，多個用戶可以同時共享數(shù)據(jù)庫中的數(shù)據(jù)資源。數(shù)據(jù)庫管理系統(tǒng)是對數(shù)據(jù)庫進行有效管理和操作的軟件，是用戶與數(shù)據(jù)庫之間的接口。

數(shù)據(jù)庫分為層次數(shù)據(jù)庫、網(wǎng)狀數(shù)據(jù)庫和關(guān)系數(shù)據(jù)庫。在這3種類型的數(shù)據(jù)庫中，層次數(shù)據(jù)庫的查詢速度最快；網(wǎng)狀數(shù)據(jù)庫建庫最靈活；關(guān)系數(shù)據(jù)庫最簡單，也是使用最廣泛的數(shù)據(jù)庫類型。層次數(shù)據(jù)庫和網(wǎng)狀數(shù)據(jù)庫很容易與實際問題建立關(guān)聯(lián)，可以很好地解決數(shù)據(jù)的集中和共享問題，但是用戶對這兩種數(shù)據(jù)庫進行數(shù)據(jù)存取時，需要指出數(shù)據(jù)的存儲結(jié)構(gòu)和存取路徑，而關(guān)系數(shù)據(jù)庫則較好地解決了這些問題。關(guān)系數(shù)據(jù)庫關(guān)系數(shù)據(jù)庫建立在關(guān)系模型基礎(chǔ)之上，數(shù)據(jù)以表格形式存儲。ID姓名檔案號部門001張三A10023技術(shù)部002李四B20045財務(wù)部特點：代表數(shù)據(jù)庫：結(jié)構(gòu)化數(shù)據(jù)存儲使用SQL語言查詢強一致性適合復(fù)雜查詢MySQLOracleSQLServerSQLiteAccessFoxProSQLite數(shù)據(jù)庫技術(shù)

SQLite占用的資源少，處理速度快。它支持Windows、Linux、Android等主流操作系統(tǒng)同時能夠與Python、Java、PHP、C等程序語言結(jié)合使用。由于SQLite的體積較小，所以經(jīng)常被集成到各種應(yīng)用程序中。例如，Python2.5之后的版本中，就內(nèi)置了SQLite3模塊，這省去了數(shù)據(jù)庫安裝配置過程。在智能手機的Android系統(tǒng)中，也內(nèi)置了SQLite數(shù)據(jù)庫。SQLite數(shù)據(jù)庫的優(yōu)點（1）操作簡單SQLite本身不需要任何初始化配置文件，也沒有安裝和卸載的過程，這減少了大量的系統(tǒng)部署時間。SQLite不存在服務(wù)器的啟動和停止，在使用過程中，無須創(chuàng)建用戶和劃分權(quán)限。在系統(tǒng)出現(xiàn)災(zāi)難時（如宕機），無須對SQLite進行任何操作。（2）運行效率高SQLite運行時占用的資源很少（只需要數(shù)百K內(nèi)存），而且無須任何管理開銷，因此對PDA、智能手機等移動設(shè)備來說，SQLite的優(yōu)勢毋庸置疑。為了達到快速和高可靠性這一目標，SQLite取消了一些數(shù)據(jù)庫的功能，如高并發(fā)、記錄行級鎖、豐富的內(nèi)置函數(shù)、復(fù)雜的SQL語句等。正是這些功能的犧牲才換來了SQLite的簡單性，而簡單又帶來了高效性和高可靠性。（3）直接備份SQLite的數(shù)據(jù)庫就是一個文件，只要權(quán)限允許便可隨意訪問和復(fù)制，這樣做的優(yōu)點是便于備份、攜帶和共享，且數(shù)據(jù)庫備份更方便。SQLite數(shù)據(jù)庫操作

插入記錄（INSERT）>>>fromsqlite3importdbapi2#導(dǎo)入內(nèi)置SQLite3模塊>>>conn=dbapi2.connect('d:/test/mytest.db')

#建立或打開mytest.db數(shù)據(jù)庫>>>cur=conn.cursor()

#定義一個游標對象>>>sql_create='CREATETABLEIFNOTEXISTSmytb(xmchar,cjreal,kctext)'#定義SQL語句>>>conn.execute(sql_create)#執(zhí)行SQL語句，創(chuàng)建數(shù)據(jù)表>>>sql_insert='INSERTINTOmytb(xm,cj,kc)values(?,?,?)'#定義SQL語句，？為占位符>>>cur.execute(sql_insert)#執(zhí)行SQL語句，創(chuàng)建mytb數(shù)據(jù)表>>>cur.execute(sql_insert,('寶玉',85,'計算機'))#插入記錄>>>cur.execute(sql_insert,('黛玉',92,'計算機'))>>>cur.execute(sql_insert,('寶釵',80,'數(shù)據(jù)庫'))>>>cur.rowcount#通過rowcount獲得插入的行數(shù)>>>mit()#提交事務(wù)，將數(shù)據(jù)寫入文件，并保存到磁盤中SQLite數(shù)據(jù)庫操作

查詢記錄（SELECT）

>>>cur.execute('SELECT*FROMmytb')#取出所有記錄

>>>recs=cur.fetchall()#將所有元素賦值給recs列表，每個元素代表一條記錄

>>>print('共',len(recs),'條記錄')#顯示記錄數(shù)

共3條記錄

#輸出

>>>print(recs)#顯示所有記錄內(nèi)容

[('寶玉',86.0,'計算機'),('黛玉',92.0,'計算機'),('寶釵',70.0,'數(shù)據(jù)庫')]#輸出

>>>cur.execute('SELECTname,sqlFROMsqlite_masterWHEREtype='table'')#查詢事務(wù)

>>>recs=cur.fetchall()#將所有數(shù)據(jù)賦值給recs列表

>>>print(recs)#顯示數(shù)據(jù)表名稱，字段名稱和數(shù)據(jù)類型

[('mytb','CREATETABLEmytb(xmchar,cjreal,kctext)')]#輸出

SQLite數(shù)據(jù)庫操作

更新/刪除記錄（UPDATE/DELETE）

>>>fromsqlite3importdbapi2#導(dǎo)入內(nèi)置SQLite3模塊

>>>conn=dbapi2.connect('d:/test/mytest.db')#打開數(shù)據(jù)庫

>>>cur.execute('UPDATEmytbSETxm=?WHEREcj=?',('寶玉',0))#更新記錄

>>>cur.execute('DELETEFROMmytbWHEREcj>90')#刪除記錄

>>>cur.execute('DELETEFROMmytb')#刪除mytb表中的所有記錄

>>>cur.execute('DROPTABLEmytb')#刪除數(shù)據(jù)表和整個數(shù)據(jù)庫

>>>mit()#提交事務(wù)

>>>cur.close()#關(guān)閉游標

>>>conn.close()#關(guān)閉數(shù)據(jù)庫連接

MySQL數(shù)據(jù)庫技術(shù)

MySQL是一款安全、跨平臺、高效的開源數(shù)據(jù)庫，被廣泛應(yīng)用于互聯(lián)網(wǎng)中小型網(wǎng)站。安全性提供多層次的安全防護機制，確保數(shù)據(jù)安全存儲和訪問，支持加密連接和訪問控制?？缙脚_支持多種操作系統(tǒng)，包括Windows、Linux、MacOS等，可以輕松部署在不同環(huán)境中。高效性體積小、速度快，具有優(yōu)秀的性能和較低的資源消耗，適合各類應(yīng)用場景。開源特性開放源代碼，允許自由使用和修改，擁有活躍的社區(qū)支持和持續(xù)的更新迭代。應(yīng)用場景電子商務(wù)內(nèi)容管理社交網(wǎng)絡(luò)數(shù)據(jù)分析檔案管理"由于其體積小、速度快、開放源碼，因此很多公司都采用MySQL"PyMySQL模塊安裝為了使Python連接MySQL數(shù)據(jù)庫，需要安裝PyMySQL模塊，它是一個用于與數(shù)據(jù)庫交互的驅(qū)動庫。以下是兩種安裝方式：使用pip命令安裝1打開命令提示符或終端2輸入以下pip命令：pipinstallpymysql3等待安裝完成，出現(xiàn)成功提示優(yōu)點：簡單快捷，適用于所有Python環(huán)境通過PyCharm安裝1打開PyCharmIDE2依次點擊：File→Settings→Project→PythonInterpreter3點擊"+"按鈕，搜索"PyMySQL"4選擇PyMySQL并點擊"InstallPackage"優(yōu)點：圖形界面操作，適合初學(xué)者使用安裝完成后，在Python中導(dǎo)入：importpymysqlPyMySQL連接數(shù)據(jù)庫連接參數(shù)host=None數(shù)據(jù)庫連接地址user=None數(shù)據(jù)庫用戶名password=''數(shù)據(jù)庫用戶密碼database=None要連接的數(shù)據(jù)庫port=3306端口，默認為3306charset=''連接數(shù)據(jù)庫的字符編碼connect_timeout=10連接數(shù)據(jù)庫超時時間，默認為10autocommit=False是否自動提交事務(wù)連接示例importpymysqlconn=pymysql.connect(host='localhost',user='root',password='password',database='archive_system',port=3306,charset='utf8mb4')

pymysql常用對象

1．Connection對象

功能：用于與MySQL數(shù)據(jù)庫建立連接和管理連接。

常用方法如下。

（1）cursor()：創(chuàng)建并返回游標對象，用于執(zhí)行SQL語句。

（2）commit()：提交事務(wù)，保存對數(shù)據(jù)庫的修改。

（3）rollback()：回滾事務(wù)，撤銷未提交的修改。

（4）close()：關(guān)閉數(shù)據(jù)庫連接。

importpymysql#創(chuàng)建數(shù)據(jù)庫連接

connection=pymysql.connect(host='localhost',user='root',password='yourpassword',database='testdb')#使用連接

mit()#提交事務(wù)connection.close()#關(guān)閉連接

pymysql常用對象

2．Cursor對象

功能：用于執(zhí)行SQL語句并獲取結(jié)果集。

常用方法如下。

（1）execute(sql,params=None)：執(zhí)行SQL語句，可以包含參數(shù)。

（2）fetchone()：獲取結(jié)果集中的下一行數(shù)據(jù)，返回單個記錄。

（3）fetchall()：獲取結(jié)果集中的所有行數(shù)據(jù)，返回列表。

（4）fetchmany(size)：獲取指定數(shù)量的行。（5）close()：關(guān)閉游標對象。

cursor=connection.cursor()#創(chuàng)建游標對象

#執(zhí)行查詢

cursor.execute("SELECT*FROMusers")#獲取結(jié)果

rows=cursor.fetchall()#遍歷結(jié)果

forrowinrows:print(row)cursor.close()#關(guān)閉游標

pymysql常用對象

3．Error對象

功能：用于處理數(shù)據(jù)庫操作中可能發(fā)生的異常。

常見異常如下。

（1）pymysql.MySQLError：所有數(shù)據(jù)庫異常的基類。

（2）pymysql.OperationalError：連接或操作失敗等操作系統(tǒng)錯誤。

（3）pymysql.ProgrammingError：SQL語法錯誤或參數(shù)錯誤。

（4）pymysql.IntegrityError：違反數(shù)據(jù)完整性約束（如唯一鍵沖突）。

try:cursor.execute("SELECT*FROMnon_existing_table")exceptpymysql.ProgrammingErrorase:print("SQL錯誤：",e)pymysql常用對象

4．事務(wù)控制

功能：通過Connection對象的commit()和rollback()方法來控制事務(wù)。

常用操作如下。

（1）commit()：提交當前事務(wù)。

（2）rollback()：回滾當前事務(wù)。

try:cursor.execute("INSERTINTOusers(name,age)VALUES(%s,%s)",('Alice',30))mit()#提交事務(wù)

except:connection.rollback()#出錯時回滾事務(wù)pymysql數(shù)據(jù)庫操作

數(shù)據(jù)信息是保存在數(shù)據(jù)表中的。在創(chuàng)建完數(shù)據(jù)庫后，需要根據(jù)信息的類別建立不同的數(shù)據(jù)表以保存數(shù)據(jù)。數(shù)據(jù)庫中關(guān)于數(shù)據(jù)表的操作包括：創(chuàng)建表使用CREATETABLE語句創(chuàng)建數(shù)據(jù)表，定義表結(jié)構(gòu)、字段類型和約束條件修改表結(jié)構(gòu)使用ALTERTABLE語句增加、修改或刪除表中的字段修改表名使用RENAMETABLE語句更改數(shù)據(jù)表的名稱異?？刂圃谛薷谋頃r加入異常處理，防止操作失敗導(dǎo)致系統(tǒng)崩潰刪除表使用DROPTABLE語句刪除不再需要的數(shù)據(jù)表SQL實現(xiàn)在Python中使用PyMySQL模塊執(zhí)行SQL語句進行表的管理IDNameTypeDate1文件APDF2023-01-012文件BDOC2023-02-15創(chuàng)建數(shù)據(jù)表表創(chuàng)建基礎(chǔ)數(shù)據(jù)庫中的數(shù)據(jù)信息是保存在數(shù)據(jù)表中的。創(chuàng)建表是進行數(shù)據(jù)操作的第一步，需要定義表結(jié)構(gòu)、字段類型和約束條件。MySQL創(chuàng)建表語法CREATETABLE表名(

字段名1數(shù)據(jù)類型[約束條件],

字段名2數(shù)據(jù)類型[約束條件],...

字段名n數(shù)據(jù)類型[約束條件],[PRIMARYKEY(字段名)]);關(guān)鍵要素字段定義：字段名、數(shù)據(jù)類型、長度限制約束條件：NOTNULL、UNIQUE、DEFAULT等主鍵設(shè)置：確保數(shù)據(jù)唯一性和完整性表結(jié)構(gòu)可視化檔案信息表(archives_info)字段名數(shù)據(jù)類型約束條件說明archive_idINTPRIMARYKEY檔案IDtitleVARCHAR(100)NOTNULL檔案標題create_dateDATE創(chuàng)建日期categoryVARCHAR(50)DEFAULT'general'檔案類別創(chuàng)建表示例CREATETABLEarchives_info(archive_idINTPRIMARYKEY,titleVARCHAR(100)NOTNULL,create_dateDATE,categoryVARCHAR(50)DEFAULT'general',descriptionTEXT,is_confidentialBOOLEANDEFAULTFALSE);

執(zhí)行上述SQL語句后，會在當前數(shù)據(jù)庫中創(chuàng)建一個名為archives_info的表數(shù)據(jù)操作概述在MySQL中，關(guān)于數(shù)據(jù)的操作主要包括增刪改查四種基本功能，這些操作是數(shù)據(jù)庫應(yīng)用的核心。插入數(shù)據(jù)向數(shù)據(jù)表中添加新的數(shù)據(jù)記錄，是數(shù)據(jù)輸入的基本方式。INSERTINTOtable_name(列1,列2,...)VALUES(值1,值2,...);查詢數(shù)據(jù)從數(shù)據(jù)表中檢索數(shù)據(jù)，可以使用各種條件篩選所需信息。SELECT列1,列2,...FROMtable_nameWHERE條件;修改數(shù)據(jù)更新數(shù)據(jù)表中已有的數(shù)據(jù)記錄，可以修改一個或多個字段的值。UPDATEtable_nameSET列1=值1,列2=值2,...WHERE條件;刪除數(shù)據(jù)從數(shù)據(jù)表中移除不需要的數(shù)據(jù)記錄，可以根據(jù)條件刪除特定數(shù)據(jù)。DELETEFROMtable_nameWHERE條件;插入數(shù)據(jù)SQL插入語句使用INSERTINTO語句向表中添加新記錄：INSERTINTOtable_name(column1,column2,column3,...)VALUES(value1,value2,value3,...);示例：INSERTINTOarchives_info(id,title,create_date)VALUES(1001,'財務(wù)報表','2023-05-20');

插入多條記錄：INSERTINTOarchives_info(id,title,create_date)VALUES(1002,'會議紀要','2023-05-21'),(1003,'合同文本','2023-05-22');Python實現(xiàn)使用PyMySQL模塊執(zhí)行插入操作：importpymysql#建立數(shù)據(jù)庫連接conn=pymysql.connect(host='localhost',user='root',password='password',database='archives_db')#創(chuàng)建游標對象cursor=conn.cursor()#執(zhí)行SQL插入語句sql="INSERTINTOarchives_info(title,create_date)VALUES(%s,%s)"val=("工作計劃","2023-06-01")cursor.execute(sql,val)#提交事務(wù)conn.commit()#關(guān)閉連接cursor.close()conn.close()SQ