Python 資料持久化 - Sqlite3 模組

---

---

CSV、JSON、XML 等檔案的**一個主要缺點**是它們對於隨機訪問和事務處理不是很有用，因為它們本質上是無結構的。因此，修改內容變得非常困難。

這些平面檔案不適合客戶端-伺服器環境，因為它們缺乏非同步處理能力。使用無結構資料檔案會導致資料冗餘和不一致。

這些問題可以透過使用關係資料庫來克服。資料庫是組織好的資料集合，用於消除冗餘和不一致，並維護資料完整性。關係資料庫模型非常流行。

其基本概念是將資料排列在實體表（稱為關係）中。實體表結構提供一個屬性，其值對於每一行都是唯一的。此類屬性稱為**“主鍵”**。

當一個表的主鍵出現在其他表的結構中時，它被稱為**“外部索引鍵”**，這構成了這兩個表之間關係的基礎。基於此模型，目前有許多流行的關係資料庫管理系統（RDBMS）產品可用 -

• GadFly
• mSQL
• MySQL
• PostgreSQL
• Microsoft SQL Server 2000
• Informix
• Interbase
• Oracle
• Sybase
• SQLite

SQLite 是一種輕量級關係資料庫，廣泛用於各種應用程式。它是一個自包含的、無伺服器的、零配置的事務性 SQL 資料庫引擎。整個資料庫是一個單個檔案，可以放置在檔案系統中的任何位置。它是一個開源軟體，佔用空間非常小，並且零配置。它廣泛用於嵌入式裝置、物聯網和移動應用程式。

所有關係資料庫都使用 SQL 來處理表中的資料。但是，早些時候，每個資料庫都透過特定於資料庫型別的 Python 模組連線到 Python 應用程式。

因此，它們之間缺乏相容性。如果使用者想要更改到不同的資料庫產品，將被證明是困難的。此相容性問題透過提出“Python 增強提案 (PEP 248)”來解決，該提案建議對關係資料庫使用一致的介面，稱為 DB-API。最新的建議稱為**DB-API** 版本 2.0。（PEP 249）

Python 的標準庫包含 sqlite3 模組，這是一個符合 DB-API 的模組，用於透過 Python 程式處理 SQLite 資料庫。本章介紹 Python 與 SQLite 資料庫的連線。

如前所述，Python 以 sqlite3 模組的形式對 SQLite 資料庫提供了內建支援。對於其他資料庫，需要使用 pip 實用程式安裝相應的符合 DB-API 的 Python 模組。例如，要使用 MySQL 資料庫，我們需要安裝 PyMySQL 模組。

pip install pymysql

DB-API 中建議以下步驟 -

• 使用**connect()** 函式建立與資料庫的連線並獲取連線物件。

• 呼叫連線物件的**cursor()** 方法獲取遊標物件。

• 形成由要執行的 SQL 語句組成的查詢字串。

• 透過呼叫**execute()** 方法執行所需的查詢。

• 關閉連線。

import sqlite3
db=sqlite3.connect('test.db')

這裡，db 是表示 test.db 的連線物件。請注意，如果資料庫尚不存在，則會建立它。連線物件 db 具有以下方法 -

遊標充當給定 SQL 查詢的控制代碼，允許檢索結果的一行或多行。從連線中獲取遊標物件以使用以下語句執行 SQL 查詢 -

cur=db.cursor()

遊標物件定義了以下方法 -

以下程式碼在 test.db 中建立一個表：-

import sqlite3
db=sqlite3.connect('test.db')
cur =db.cursor()
cur.execute('''CREATE TABLE student (
StudentID INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT (20) NOT NULL,
age INTEGER,
marks REAL);''')
print ('table created successfully')
db.close()

資料庫中所需的資料完整性透過連線物件的**commit()** 和**rollback()** 方法實現。SQL 查詢字串可能包含不正確的 SQL 查詢，這可能會引發異常，應正確處理。為此，execute() 語句放置在 try 塊中，如果成功，則使用 commit() 方法永久儲存結果。如果查詢失敗，則使用 rollback() 方法撤消事務。

以下程式碼在 test.db 中的 student 表上執行 INSERT 查詢。

import sqlite3
db=sqlite3.connect('test.db')
qry="insert into student (name, age, marks) values('Abbas', 20, 80);"
try:
   cur=db.cursor()
   cur.execute(qry)
   db.commit()
print ("record added successfully")
except:
   print ("error in query")
   db.rollback()
db.close()

如果希望 INSERT 查詢的 values 子句中的資料由使用者輸入動態提供，請使用 Python DB-API 中推薦的引數替換。? 字元用作查詢字串中的佔位符，並以元組的形式在 execute() 方法中提供值。以下示例使用引數替換方法插入記錄。姓名、年齡和分數作為輸入。

import sqlite3
db=sqlite3.connect('test.db')
nm=input('enter name')
a=int(input('enter age'))
m=int(input('enter marks'))
qry="insert into student (name, age, marks) values(?,?,?);"
try:
   cur=db.cursor()
   cur.execute(qry, (nm,a,m))
   db.commit()
   print ("one record added successfully")
except:
   print("error in operation")
   db.rollback()
db.close()

sqlite3 模組定義了**executemany()** 方法，該方法能夠一次新增多條記錄。要新增的資料應以元組列表的形式給出，每個元組包含一條記錄。列表物件是 executemany() 方法的引數，以及查詢字串。但是，某些其他模組不支援 executemany() 方法。

**UPDATE** 查詢通常包含由 WHERE 子句指定的邏輯表示式。execute() 方法中的查詢字串應包含 UPDATE 查詢語法。要將“Anil”的“age”值更新為 23，請將字串定義如下

qry="update student set age=23 where name='Anil';"

為了使更新過程更具動態性，我們使用上面描述的引數替換方法。

import sqlite3
db=sqlite3.connect('test.db')
nm=input(‘enter name’)
a=int(input(‘enter age’))
qry="update student set age=? where name=?;"
try:
   cur=db.cursor()
   cur.execute(qry, (a, nm))
   db.commit()
   print("record updated successfully")
except:
   print("error in query")
   db.rollback()
db.close()

類似地，DELETE 操作透過呼叫 execute() 方法來執行，該方法帶有一個包含 SQL 的 DELETE 查詢語法的字串。順便說一句，**DELETE** 查詢也通常包含**WHERE** 子句。

import sqlite3
db=sqlite3.connect('test.db')
nm=input(‘enter name’)
qry="DELETE from student where name=?;"
try:
   cur=db.cursor()
   cur.execute(qry, (nm,))
   db.commit()
   print("record deleted successfully")
except:
   print("error in operation")
   db.rollback()
db.close()

資料庫表上的重要操作之一是從中檢索記錄。SQL 提供**SELECT** 查詢來實現此目的。當將包含 SELECT 查詢語法的字串傳遞給 execute() 方法時，會返回一個結果集物件。遊標物件有兩個重要方法，可以使用它們從結果集中檢索一條或多條記錄。

fetchone()

從結果集中獲取下一條可用記錄。它是一個元組，包含已獲取記錄的每一列的值。

fetchall()

以元組列表的形式獲取所有剩餘的記錄。每個元組對應一條記錄，幷包含表中每一列的值。

以下示例列出 student 表中的所有記錄

import sqlite3
db=sqlite3.connect('test.db')
37
sql="SELECT * from student;"
cur=db.cursor()
cur.execute(sql)
while True:
   record=cur.fetchone()
   if record==None:
      break
   print (record)
db.close()

如果您計劃使用 MySQL 資料庫而不是 SQLite 資料庫，則需要如上所述安裝**PyMySQL** 模組。資料庫連線過程中的所有步驟都相同，因為 MySQL 資料庫安裝在伺服器上，connect() 函式需要 URL 和登入憑據。

import pymysql
con=pymysql.connect('localhost', 'root', '***')

唯一可能與 SQLite 不同的方面是 MySQL 特定的資料型別。類似地，透過安裝 pyodbc 模組，任何與 ODBC 相容的資料庫都可以與 Python 一起使用。