Python程序設計

第十章數據庫操作本章知識點導圖

SQLite是一個軟件庫，實現了自給自足的、無服務器的、零配置的、事務性的SQL數據庫引擎。SQLite是在世界上最廣泛部署的SQL數據庫引擎。SQLite源代碼不受版權限制。SQLite數據庫占用資源非常少，僅需幾百K內存即可，處理速度快，可直接使用C或配合C#、PHP、Java等其他語言使用。SQLite誕生于2000年，2015年發布了新版本SQLite3。

在Python中操作數據庫時，要先導入數據庫對應的驅動，然后通過Connection對象和Cursor對象操作數據。最后要確保打開的Connection對象和Cursor對象都正確地被關閉，否則資源就會泄露。在Python中使用SQLite數據庫，需要以下幾個關鍵步驟：導入sqlite3模塊包：importsqlite3通過sqlite3的connect()函數連接已經存在的或新創建數據庫，獲得操作數據庫的句柄：conn=sqlite3.connect(數據庫名稱)利用句柄執行SQL指令:conn.execute(sql指令串)利用句柄把操作結果提交給數據庫:mit()最后要關閉數據庫的連接:conn.close()10.1創建數據庫

對于數據庫首先要明白一個邏輯關系，有數據庫才有表，一個數據庫文件下可以有多張數據表。類似于一個Excel文件下可以有多張數據表Sheet1、Sheet2、Sheet3等。

創建和鏈接一個已有的數據庫方法一樣。如果數據庫不存在，那么它就會被創建，最后將返回一個數據庫對象。

首先導入SQLite以驅動數據庫模塊，該模塊是Python集成的內置類庫，提供Python操作sqlite3的相關接口，無需安裝。連接到SQlite數據庫，數據庫文件名為test.db，若文件不存在，則自動創建。In[1]:importsqlite3In[2]:conn=sqlite3.connect('test.db')

為了對數據庫中的數據表能夠進行操作，需要創建游標對象conn.cursor()，通過返回的對象c，執行相應的SQL語句。In[3]:c=conn.cursor()

有了游標，我們可以對表進行各種操作，創建表、對表進行增刪改查等。在執行這些操作時，均通過c.execute()執行sql語句。In[4]:c.execute('''CREATETABLEcompany

...:(IDINTPRIMARYKEYNOTNULL,

...:NAMETEXTNOTNULL,

...:AGEINTNOTNULL,

...:ADDRESSCHAR(50),

...:SALARYREAL);

...:''')Out[4]:<sqlite3.Cursorat0x1db5efe6730>上面語句創建了一個叫company的表，它有一個主鍵ID，一個NAME，一個AGE，以及ADDRESS和SALARY，如果NAME是不可以重復的，可以設置NAME為varchar(10)UNIQUE）。這里需要提醒的是，只有提交了之后才能生效。我們使用數據庫連接對象c來進行提交commit和回滾rollback操作。提交之后需要及時關閉游標和數據庫鏈接。In[5]:mit()

...:c.close()

...:conn.close()10.2插入數據在插入記錄（數據）之前需要先連接數據庫，并創建游標。In[6]:conn=sqlite3.connect('test.db')

...:c=conn.cursor()插入記錄用c.execute()句式，并帶上sql插入語句。插入語句句式如下："INSERTINTO***(？,？,？)VALUES(^,^,^)"其中***表示數據表的名稱；？表示數據表中要插入的記錄的相應字段，每個字段之間用逗號分割；^表示？所對應的值。In[7]:c.execute("INSERTINTOcompany(ID,NAME,AGE,ADDRESS,SALARY)\

...:VALUES(1,'Paul',32,'California',20000.00)")

...:

...:c.execute("INSERTINTOcompany(ID,NAME,AGE,ADDRESS,SALARY)\

...:VALUES(2,'Allen',25,'Texas',15000.00)")

...:

...:c.execute("INSERTINTOcompany(ID,NAME,AGE,ADDRESS,SALARY)\

...:VALUES(3,'Teddy',23,'Norway',20000.00)")

...:

...:c.execute("INSERTINTOCOMPANY(ID,NAME,AGE,ADDRESS,SALARY)\

...:VALUES(4,'Mark',25,'Rich-Mond',65000.00)")Out[7]:<sqlite3.Cursorat0x1db6022b9d0>插入記錄后要記得提交和關閉鏈接。In[8]:mit()

...:conn.close()也可以一次性插入多條記錄，用executemany()的方法來執行。如：reco=[(5,'Paul0',31,'California0',20600.00),(6,'Allen0',26,'Texas0',15500.00),(7,'Teddy0',28,'Norway0',27000.00),(8,'Mark0',23,'Rich-Mond0',65500.00)]c.executemany("insertintocompany(ID,NAME,AGE,ADDRESS,SALARY)values(?,?,?,?,?)",reco)SQL語句中的參數，使用"?"作為替代符號，并在后面的參數中給出具體值。這里不能用Python的格式化字符串，如"%s"，因為這一用法容易受到SQL注入攻擊。10.3查詢記錄

在查詢記錄時也需要先鏈接到數據庫，并利用以下句式查詢，把查詢結果賦值給cursor。cursor=c.execute("SELECT?,?,?from*")此處?表示數據表中要查詢的記錄相應的字段，每個字段之間用逗號分割；*表示數據表名稱。In[1]:importsqlite3

In[2]:conn=sqlite3.connect('test.db')

...:c=conn.cursor()

...:

...:cursor=c.execute("SELECTid,name,address,salaryfromCOMPANY")

...:forrowincursor:

...:print("ID=",row[0])

...:print("NAME=",row[1])

...:print("ADDRESS=",row[2])

...:print("SALARY=",row[3],"\n")

...:

...:conn.close()ID=1NAME=PaulADDRESS=CaliforniaSALARY=20000.0

ID=2NAME=AllenADDRESS=TexasSALARY=15000.0

ID=3NAME=TeddyADDRESS=NorwaySALARY=20000.0

ID=4NAME=MarkADDRESS=Rich-MondSALARY=65000.0查詢記錄時不需要提交commit操作。

在執行查詢語句后，Python將返回一個循環器，包含有查詢獲得的多個記錄?？梢匝h讀取，也可以使用sqlite3提供的fetchone()和fetchall()方法讀取記錄.In[3]:conn=sqlite3.connect('test.db')

...:c=conn.cursor()

In[4]:cursor=c.execute("SELECTid,name,address,salaryfromCOMPANY")

...:c.fetchone()#調用一次顯示一條記錄，游標下移Out[4]:(1,'Paul','California',20000.0)

In[5]:c.fetchone()Out[5]:(2,'Allen','Texas',15000.0)

In[6]:c.fetchall()#一次性顯示所有的查詢記錄Out[6]:[(3,'Teddy','Norway',20000.0),(4,'Mark','Rich-Mond',65000.0)]

In[7]:conn.close()在SQLite中查詢一個表是否存在的方法：SELECTnameFROMsqlite_masterWHEREtype=‘table’ANDname=‘table_name’;table_name就是傳進去要查找的表的名字。10.4修改記錄修改記錄也稱之為更新記錄。操作句式如下：c.execute("UPDATE***set?=^whereID=n")此處***為數據表名稱；?為要修改的字段；^表示要改為的值；n表示要修改的記錄序號。In[1]:importsqlite3

...:conn=sqlite3.connect('test.db')

...:c=conn.cursor()

In[2]:c.execute("UPDATECOMPANYsetSALARY=25000.00whereID=1")

...:mit()

In[3]:cursor=conn.execute("SELECTid,name,address,salaryfromCOMPANY")

In[4]:forrowincursor:

...:print("ID=",row[0])

...:print("NAME=",row[1])

...:print("ADDRESS=",row[2])

...:print("SALARY=",row[3],"\n")ID=1NAME=PaulADDRESS=CaliforniaSALARY=25000.0

ID=2NAME=AllenADDRESS=TexasSALARY=15000.0

ID=3NAME=TeddyADDRESS=NorwaySALARY=20000.0

ID=4NAME=MarkADDRESS=Rich-MondSALARY=65000.0

In[5]:conn.close()10.5刪除記錄刪除記錄的句式如下：c.execute("DELETEfrom***whereID=n;")其中***表示數據表的名稱；n表示要刪除的數據記錄序號。In[1]:importsqlite3

...:conn=sqlite3.connect('test.db')

...:c=conn.cursor()

In[2]:c.execute("DELETEfromCOMPANYwhereID=2;")

...:mit()

In[3]:cursor=conn.execute("SELECTid,name,address,salaryfromCOMPANY")In[4]:forrowincursor:

...:print(row)

...:print("ID=",row[0])

...:print("NAME=",row[1])

...:print("ADDRESS=",row[2])

...:print("SALARY=",row[3],"\n")

...:conn.close()(1,'Paul','California',25000.0)ID=1NAME=PaulADDRESS=CaliforniaSALARY=25000.0

(3,'Teddy','Norway',20000.0)ID=3NAME=TeddyADDRESS=NorwaySALARY=20000.0

(4,'Mark','Rich-Mond',65000.0)ID=4NAME=MarkADDRESS=Rich-MondSALARY=65000.0也可以直接刪除整張表：c