SQLite 是一款最受欢迎的關係型數據庫管理系统（RDBMS）。它輕量级，意味著它在您的系統上佔用很少空間。它最出色的特點之一是無服務器，所以您不需要安裝或管理一個獨立的服务器來使用它。
取而代之的是，它將所有內容存儲在您電腦上的一個簡單文件中。它還需要零配置，所以沒有複雜的設置過程，這使它成為初学者和小型項目的完美選擇。
SQLite 是小型至中型應用程序的首選，因為它易於使用，快速，並且可以處理大部分大型數據庫可以完成的任務，而無需管理額外的軟件。無論您正在建立一個個人項目還是原型一个新的應用程序，SQLite 都是迅速開始工作的重要選擇。
在這個教程中，您將學習如何使用 Python 與 SQLite 合作。我們將要在這個教程中涵蓋以下內容：
這份教學對於想要開始使用數據庫但不想陷入複雜設置的人來說非常完美。
如何設定Python環境
在使用SQLite之前，讓我們確保您的Python環境已經準備好。以下是設定一切的步驟。
安裝Python
如果您的系統還沒有安裝Python，您可以從官方Python網站下載。跟隨您的作業系統（Windows、macOS或Linux）的安裝說明。
要檢查是否已安裝Python，請打開終端機（或命令提示符）並輸入：
python --version
這應該會顯示目前安裝的Python版本。如果還沒有安裝，請遵循Python網站的指示。
安裝SQLite3模組
好消息是，SQLite3 已內建在 Python 中！您不需要分開安裝它，因為它包含在標準的 Python 函式庫中。這意味著您可以立即開始使用它，而無需進行任何額外的設置。
如何創建一個虛擬環境（可選但建議）
為每個專案創建一個虛擬環境是一個好主意，這樣可以讓您的依賴關係組織得更有條理。虛擬環境就像一塊乾淨的石板，您可以在不影響您的全域 Python 安裝的情況下安裝套件。
要創建虛擬環境，請按照以下步驟操作：
-
首先，打開您的終端機或命令提示符，並導航到您想要創建專案的目錄。
-
運行以下命令以創建虛擬環境：
python -m venv env
在這裡，env 是虛擬環境的名稱。您可以隨意命名。
- 啟用虛擬環境：
# 使用 Windows 的命令
env\Scripts\activate
# 使用 macOS/Linux 的命令：
env/bin/activate
啟用虛擬環境後，您會注意到您的終端機提示變了，顯示了虛擬環境的名稱。這意味著您現在正在這個環境中工作。
安裝必要的函式庫
我們將需要一些額外的函式庫來進行這個專案。具體來說，我們將使用：
-
pandas：這是一個選用的函式庫，用於處理和顯示以表格格式呈現的數據，對於進階使用情境非常有用。 -
faker：這個函式庫將幫助我們生成假數據，例如隨機的名稱和地址，我們可以將這些數據插入到資料庫中進行測試。
要安裝 pandas 和 faker，只需運行以下命令：
pip install pandas faker
這會將 pandas 和 faker 一起安裝到您的虛擬環境中。這樣一來，您的開發環境就設置完成了，您可以開始在Python中創建和管理SQLite資料庫了！
如何創建SQLite資料庫
資料庫是一種結構化的方式來存儲和管理數據，以便能夠輕鬆訪問、更新和組織數據。它就像是一個數字化的檔案系統，讓您可以有效地存儲大量數據，無論是用於簡單應用還是更複雜的系統。資料庫使用表格來組織數據，使用行和列來表示個別記錄及其屬性。
SQLite資料庫的工作原理
與大多數其他數據庫系統不同，SQLite是一個無服務器的數據庫。這意味著它不需要設置或管理服務器，使其輕量級且易於使用。所有數據都存儲在電腦上的單個文件中，您可以輕鬆地移動、分享或備份它。儘管它的設計簡單，但SQLite足夠強大，能夠處理許多常見的數據庫任務，並廣泛用於移動應用、嵌入式系統和小型至中型項目。
如何創建新的SQLite數據庫
讓我們創建一個新的SQLite數據庫，並學習如何使用Python的sqlite3庫與其互動。
連接到數據庫
由於sqlite3已經預安裝，您只需要在Python腳本中導入它。為了創建新的數據庫或連接到現有的數據庫，我們使用sqlite3.connect()方法。這個方法接受數據庫文件的名稱作為參數。如果文件不存在，SQLite將自動創建它。
import sqlite3
# 連接到SQLite數據庫（如果不存在則創建它）
connection = sqlite3.connect('my_database.db')
在這個例子中，會在腳本同一個目錄下創建一個名為my_database.db的文件。如果文件已經存在，SQLite將只會打開與它的連接。
創建游標
當您有了連接之後，下一步是創建一個游標對象。游標負責在數據庫上執行SQL命令和查詢。
# 創建一個游標對象
cursor = connection.cursor()
關閉連接
完成資料庫操作後，關閉連線以釋放資源非常重要。您可以使用以下命令來關閉連線：
# 關閉數據庫連線
connection.close()
然而，您應該在所有操作完成後才關閉連線。
當您運行Python脚本時，一個名稱為my_database.db的文件將在我的当前工作目錄中被創建。您已經成功地創建了您的第一個SQLite數據庫！
使用上下文管理器打開和關閉連線的方法
Python通過with語句（亦稱為上下文管理器）提供了處理數據庫連線更高效、更 cleaner 的方式。with語句會自動打開和關閉連線，確保即使在數據庫操作過程中發生錯誤時，連線也能被正確關閉。這消除了手動調用connection.close()的需要。
以下是您可以使用with語句來處理數據庫連線的方法：
import sqlite3
# 步驟1：使用'with'連接到數據庫（或創建一個）並在完成時自動關閉
with sqlite3.connect('my_database.db') as connection:
# 步驟2：創建一個游標物件來與數據庫互動
cursor = connection.cursor()
print("Database created and connected successfully!")
# 無需調用connection.close(); 它已經被自動完成！
從現在開始，我們在將來的代碼示例中將使用 `with` 語句來高效地管理數據庫連接。這將使代碼更簡潔且易于維護。
如何創建數據庫表
既然我們已經創建了一個 SQLite 數據庫並與之連接，下一步是者在數據庫內創建表。表是我們將數據組織成行（記錄）和列（屬性）的地方。為這個示例，我們將創建一個稱為 `Students` 的表，以存放關於學生的信息，我們将在未來的節段中重用。
要創建表，我們使用 SQL 的 `CREATE TABLE` 語句。這個命令定義了表結構，包括每列的列名和每列的數據類型。
以下是一個簡單的 SQL 命令，用於創建具有以下字段的 `Students` 表：
-
id：每個學生的唯一識別符（整數）。 -
name：學生姓名（文字）。
-
age：學生的年齡（整數）。
-
email：學生的電子郵件地址（文字）。
創建此表的 SQL 命令如下：
CREATE TABLE Students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER,
email TEXT
);
我們可以在 Python 中使用 sqlite3 函式庫執行這個 CREATE TABLE SQL 命令。讓我們來看看該如何進行。
import sqlite3
# 使用 'with' 關鍵字來連接到 SQLite 數據庫，並在操作完成後自動關閉連接
with sqlite3.connect('my_database.db') as connection:
# 創建一個游標物件
cursor = connection.cursor()
# 寫入創建 Students 表的 SQL 命令
create_table_query = '''
CREATE TABLE IF NOT EXISTS Students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER,
email TEXT
);
'''
# 執行 SQL 命令
cursor.execute(create_table_query)
# 提交更改
connection.commit()
# 打印一条確認信息
print("Table 'Students' created successfully!")
-
IF NOT EXISTS：這確保了只有當表不存在時才創建表，防止如果在之前已經創建過表而產生錯誤。 -
connection.commit()：這將更改保存（提交）到數據庫中。
當您運行上面提供的 Python 代碼時，它將在 my_database.db 數據庫文件中創建一個 Students 表。您還將看到終端中有一条消息，確認表已成功創建。
如果你使用的是 Visual Studio Code，你可以安裝 SQLite Viewer 這個擴展來查看 SQLite 數據庫。
SQLite 中的數據類型及其與 Python 的映射
SQLite 支援多種數據類型，我們在定義我們的表格時需要了解它們。以下是常見的 SQLite 數據類型以及它們如何映射到 Python 類型的一個快速概覽：
| SQLite 數據類型 | 描述 | Python 等效類型 |
| INTEGER | 整數 | int |
| TEXT | 文本字符串 | str |
| REAL | 浮點數 | float |
| BLOB | 二進制數據（例如圖片、文件） | bytes |
| NULL | 表示沒有值或缺失數據 | None |
在我們的 Students 表中：
-
id是INTEGER類型，它映射到 Python 的int。 -
name和email是TEXT類型，它們映射到 Python 的str。 -
age也是INTEGER類型，對應到 Python 的int。
如何將資料插入到表格中
既然我們已經創建了 Students 表格，現在是時候開始將資料插入到數據庫中了。在本節中，我們將介紹如何使用 Python 和 SQLite 插入單個和多個記錄，並使用參數化查詢來避免如 SQL 注入這樣的常見安全問題。
如何插入單個記錄
要將資料插入到數據庫中，我們使用 INSERT INTO SQL 命令。讓我們先將單個記錄插入到 Students 表格中。
以下是插入單個記錄的基本 SQL 語法：
INSERT INTO Students (name, age, email)
VALUES ('John Doe', 20, '[email protected]');
然而，我們不會直接在 Python 腳本中寫入含有硬編碼值的 SQL，我們將使用參數化查詢來使我們的代碼更加安全和靈活。參數化查詢有助於防止 SQL 注入，這是一種常見的攻擊，惡意用戶可以通過傳遞有害輸入來操縱 SQL 查詢。
以下是我們使用參數化查詢將單個記錄插入到 Students 表格中的方法：
import sqlite3
使用 'with' 来自動開啟和關閉連接
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
向 Students 表中插入記錄
insert_query = '''
INSERT INTO Students (name, age, email)
VALUES (?, ?, ?);
'''
student_data = ('Jane Doe', 23, '[email protected]')
cursor.execute(insert_query, student_data)
自訂 changes
connection.commit()
無需調用 connection.close(); 它會自動完成!
print("Record inserted successfully!")
`?` 占位符代表要插入表中的值。實際值作為元組 (`student_data`) 在 `cursor.execute()` 方法中傳遞。
如何插入多条記錄
如果您想一次插入多条記錄，可以在 Python 中使用 `executemany()` 方法。此方法接受一個元組列表，每個元組代表一個記錄。
為了使我們的示例更具動 Dynamic，我們可以使用 `Faker` 庫來生成隨機學生數據。這對於測試和模擬真實情境很有用。
from faker import Faker
import sqlite3
初始化 Faker
fake = Faker(['en_IN'])
使用 'with' 來自動開啟和關閉連接
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
向 Students 表中插入記錄
insert_query = '''
INSERT INTO Students (name, age, email)
VALUES (?, ?, ?);
'''
students_data = [(fake.name(), fake.random_int(
min=18, max=25), fake.email()) for _ in range(5)]
執行多条記錄的查詢
cursor.executemany(insert_query, students_data)
提交更改
connection.commit()
打印確認消息
print("Fake student records inserted successfully!")
在這個代碼中:
-
FAKER()
生成學生隨機的名字、年齡和電子郵件。傳遞地域（
[‘en_IN’] -
cursor.executemany()：這個方法讓我們能一次插入多条記錄，讓程式更有效率。 -
students_data：一個元組列表，每個元組代表一個學生的數據。
如何處理常見問題：SQL 注入
SQL 注入是一種安全性漏洞， attackers 可以通过提供有害的输入来插入或操纵 SQL 查询。例如，攻击者可能会试图注入像 '; DROP TABLE Students; -- 的代碼来删除表。
通过使用参数化查询（如上所示），我们避免了这个问题。参数化查询中的 ? 占位符确保输入值被视为数据，而不是 SQL 命令的一部分。这使得恶意代码无法执行。
如何查閱數據
既然我們已經將一些數據插入到我們的 Students 表中，讓我們來學習如何從表中检索數據。我們將探索在Python中獲取數據的不同方法，包括 fetchone()，fetchall() 和 fetchmany()。
要從表中查詢數據，我們使用 SELECT 語句。以下是從 Students 表選擇所有列的一個簡單SQL命令：
SELECT * FROM Students;
此命令從 Students 表中检索所有記錄和列。我們可以在Python中執行這個 SELECT 查詢並獲取結果。
如何獲取所有記錄
以下是從 Students 表中獲取所有記錄的方法：
import sqlite3
# 使用 'with' 來連接到SQLite數據庫
with sqlite3.connect('my_database.db') as connection:
# 創建一個游標物件
cursor = connection.cursor()
# 寫入SQL命令以從Students表選擇所有記錄
select_query = "SELECT * FROM Students;"
# 執行SQL命令
cursor.execute(select_query)
# 獲取所有記錄
all_students = cursor.fetchall()
# 在終端器中顯示結果
print("All Students:")
for student in all_students:
print(student)
在這個例子中，fetchall() 方法將查詢返回的所有行作為一個元組列表检索。
All Students:
(1, 'Jane Doe', 23, '[email protected]')
(2, 'Bahadurjit Sabharwal', 18, '[email protected]')
(3, 'Zayyan Arya', 20, '[email protected]')
(4, 'Hemani Shukla', 18, '[email protected]')
(5, 'Warda Kara', 20, '[email protected]')
(6, 'Mitali Nazareth', 19, '[email protected]')
如何獲取單個記錄
如果你只想檢索一個記錄，你可以使用 fetchone() 方法：
import sqlite3
# 使用 'with' 連接到 SQLite 數據庫
with sqlite3.connect('my_database.db') as connection:
# 創建一個游標物件
cursor = connection.cursor()
# 寫入 SQL 命令以選擇學生表格中的所有記錄
select_query = "SELECT * FROM Students;"
# 執行 SQL 命令
cursor.execute(select_query)
# 提取一條記錄
student = cursor.fetchone()
# 顯示結果
print("First Student:")
print(student)
輸出:
First Student:
(1, 'Jane Doe', 23, '[email protected]')
如何提取多條記錄
要提取特定數量的記錄，可以使用 fetchmany(size):
import sqlite3
# 使用 'with' 連接到 SQLite 數據庫
with sqlite3.connect('my_database.db') as connection:
# 創建一個游標物件
cursor = connection.cursor()
# 寫入 SQL 命令以選擇學生表格中的所有記錄
select_query = "SELECT * FROM Students;"
# 執行 SQL 命令
cursor.execute(select_query)
# 提取三條記錄
three_students = cursor.fetchmany(3)
# 顯示結果
print("Three Students:")
for student in three_students:
print(student)
輸出:
Three Students:
(1, 'Jane Doe', 23, '[email protected]')
(2, 'Bahadurjit Sabharwal', 18, '[email protected]')
(3, 'Zayyan Arya', 20, '[email protected]')
如何使用 pandas 進行更好的數據呈現
為了更好的數據呈現，我們可以使用 pandas 庫從查詢結果創建一個 DataFrame。這樣更容易操作和視覺化數據。
以下是提取所有記錄並將其顯示為 pandas DataFrame 的方法：
import sqlite3
import pandas as pd
# 使用 'with' 來連接到 SQLite 數據庫
with sqlite3.connect('my_database.db') as connection:
# 寫 SQL 命令來從 Students 表中選擇所有記錄
select_query = "SELECT * FROM Students;"
# 使用 pandas 直接將 SQL 查詢讀取為 DataFrame
df = pd.read_sql_query(select_query, connection)
# 顯示 DataFrame
print("All Students as DataFrame:")
print(df)
輸出:
All Students as DataFrame:
id name age email
0 1 Jane Doe 23 [email protected]
1 2 Bahadurjit Sabharwal 18 [email protected]
2 3 Zayyan Arya 20 [email protected]
3 4 Hemani Shukla 18 [email protected]
4 5 Warda Kara 20 [email protected]
5 6 Mitali Nazareth 19 [email protected]
pd.read_sql_query() 函數執行 SQL 查詢，並直接將結果返回為 pandas DataFrame。
如何更新和刪除數據
在這一節中，我們將學習如何使用 Python 中的 SQL 命令來更新 Students 表中的现有記錄和刪除記錄。這對於有效地管理和工作數據非常重要。
更新现有記錄
要修改數據庫中的现有記錄，我們使用 SQL UPDATE 命令。該命令讓我們能夠根據指定條件更改一行或多行中特定列的值。
例如，如果我們想要更新一名學生的年齡，SQL 命令看起來像這樣：
UPDATE Students
SET age = 21
WHERE name = 'Jane Doe';
現在，讓我們寫 Python 代碼來更新我們 Students 表中特定學生的年齡。
import sqlite3
＃ 使用'with'連接到SQLite數據庫
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
＃ 更新學生年齡的SQL命令
update_query = '''
UPDATE Students
SET age = ?
WHERE name = ?;
'''
＃ 更新的數據
new_age = 21
student_name = 'Jane Doe'
＃ 使用數據執行SQL命令
cursor.execute(update_query, (new_age, student_name))
＃ 提交更改以保存更新
connection.commit()
＃ 打印確認消息
print(f"Updated age for {student_name} to {new_age}.")
在此示例中，我們使用帶有參數的查詢來防止SQL注入。
如何從表中刪除記錄
要從數據庫中刪除記錄，我們使用SQL DELETE 命令。 這個命令允許我們根據指定的條件刪除一行或多行。
例如，如果我們想刪除名為’Jane Doe’的學生，SQL命令如下：
DELETE FROM Students
WHERE name = 'Jane Doe';
讓我們編寫Python代碼，使用 with 語句從我們的 Students 表中刪除特定學生。
import sqlite3
＃ 使用'with'連接到SQLite數據庫
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
＃ 刪除學生的SQL命令
delete_query = '''
DELETE FROM Students
WHERE name = ?;
'''
＃ 要刪除的學生的名稱
student_name = 'Jane Doe'
＃ 使用數據執行SQL命令
cursor.execute(delete_query, (student_name,))
＃ 提交更改以保存刪除
connection.commit()
＃ 打印確認消息
print(f"Deleted student record for {student_name}.")
重要考慮事項
-
條件: 在更新或刪除記錄時，始終使用
WHERE子句，以避免修改或刪除表中的所有行。如果沒有WHERE子句，該命令將影響表中的每一行。
-
備份: 在執行更新或刪除操作之前，特別是在生產環境中，備份您的數據庫是一種良好的實踐。
如何使用事務
事務是一個或多個 SQL 操作的序列，被視為一個單獨的工作單位。在數據庫的上下文中，事務允許您執行多個操作，要麼全部成功，要麼全部失敗。這確保您的數據庫保持一致的狀態，即使出現錯誤或意外問題。
例如，如果您在兩個銀行賬戶之間轉賬，您希望從一個賬戶的借方和另一個賬戶的貸方都成功或一起失敗。如果一個操作失敗，則不應執行另一個操作以保持一致性。
為什麼要使用事務？
-
原子性：事務確保一系列操作被視為單個單元。如果一個操作失敗，則不會應用任何操作到數據庫。
-
一致性：事務通過確保遵循所有規則和約束來幫助維護數據庫的完整性。
-
隔離性：每個事務獨立運行，防止意外干擾。
-
持久性：一旦事務提交，變更將是永久的，即使系統失敗。
何時使用事務？
應使用事務時機：
-
執行必須一起成功或失敗的多個相關操作。
-
修改需要一致性和完整性的重要數據。
-
進行可能失敗的操作，例如財務交易或數據遷移。
如何在Python中管理交易
在SQLite中，交易是使用BEGIN、COMMIT和ROLLBACK命令管理的。然而，在使用Python中的sqlite3模組時，您通常通過連接物件管理交易。
開始交易
當您執行任何SQL語句時，交易會隐式地開始。要显式地開始交易，您可以使用BEGIN命令：
cursor.execute("BEGIN;")
然而，通常不需要手動開始交易，因為SQLite在您執行SQL語句時會自動開始交易。
如何提交交易
要保存交易期间所做的所有更改，您使用commit()方法。這使得所有修改在數據庫中永久有效。
connection.commit()
我們在上面提供的示例中已經使用了commit()方法。
回滚交易
如果發生問題，您想要回退交易期间所做的更改，您可以使用rollback()方法。這將撤消自交易開始以來所做的所有更改。
connection.rollback()
在Python中使用交易的示例。
在實際世界中的交易使用案例，我們將建立一個名為 Customers 的新表格來管理客戶帳戶。在這個例子中，我們假設每位客戶都有一個 balance。我們將在這個表格中加入兩位客戶，並在他們之間進行資金轉帳操作。
首先，讓我們建立 Customers 表格並插入兩位客戶：
import sqlite3
# 創建 Customers 表格並添加兩位客戶
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 創建 Customers 表格
create_customers_table = '''
CREATE TABLE IF NOT EXISTS Customers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
balance REAL NOT NULL
);
'''
cursor.execute(create_customers_table)
# 插入兩位客戶
cursor.execute(
"INSERT INTO Customers (name, balance) VALUES (?, ?);", ('Ashutosh', 100.0))
cursor.execute(
"INSERT INTO Customers (name, balance) VALUES (?, ?);", ('Krishna', 50.0))
connection.commit()
現在，讓我們在 Ashutosh 和 Krishna 間進行資金轉帳操作：
import sqlite3
def transfer_funds(from_customer, to_customer, amount):
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
try:
# 開始一個交易
cursor.execute("BEGIN;")
# 從發送者那裡扣除金額
cursor.execute(
"UPDATE Customers SET balance = balance - ? WHERE name = ?;", (amount, from_customer))
# 將金額加入接收者
cursor.execute(
"UPDATE Customers SET balance = balance + ? WHERE name = ?;", (amount, to_customer))
# 提交更改
connection.commit()
print(
f"Transferred {amount} from {from_customer} to {to_customer}.")
except Exception as e:
# 如果發生錯誤，則回滚交易
connection.rollback()
print(f"Transaction failed: {e}")
# 使用示例
transfer_funds('Ashutosh', 'Krishna', 80.0)
在這個示例中，我們首先創建了一個Customers表，並插入兩位客戶，Ashutosh avec une balance de ₹100，以及Krishna avec une balance de ₹50。然後我們進行了80₹從Ashutosh到Krishna的资金轉移。通過使用事務，我們確保從Ashutosh的賬戶中扣款以及向Krishna的賬戶中加分是作為一個原子操作來執行的，這樣在發生任何錯誤時都能保持數據完整性。如果轉移失敗（例如，由於資金不足），事務將回滚，使兩個賬戶都不變。
如何通過索引優化SQLite查詢性能
索引是一種在數據庫中提高查詢性能的强大技術。索引基本上是一個數據結構，它根據特定的列值存儲行的位置，就像書的索引能帮助你快速找到一個主題一樣。
沒有索引，SQLite必須逐行扫瞄整個表以找到相關數據，當數據集增長時，這種做法變得效率低下。通過使用索引，SQLite可以直通到你需要的行，顯著加快查詢執行速度。
如何用假數據填充數據庫
為了有效地測試索引的影響，我們需要一個大作Dataset。而不是手動添加記錄，我們可以使用faker庫快速生成假數據。在這一節中，我們將生成10,000個假記錄並將它們插入到我們的Students表中。這將模擬一個 realistic world scenario，在這個情況下，數據庫變得越来越大，查询性能變得重要。
我們將使用 executemany() 方法來插入記錄，如下所示：
import sqlite3
from faker import Faker
# 初始化 Faker 庫
fake = Faker(['en_IN'])
def insert_fake_students(num_records):
"""Generate and insert fake student data into the Students table."""
fake_data = [(fake.name(), fake.random_int(min=18, max=25),
fake.email()) for _ in range(num_records)]
# 使用 'with' 來處理資料庫連接
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 將假資料插入 Students 表
cursor.executemany('''
INSERT INTO Students (name, age, email)
VALUES (?, ?, ?);
''', fake_data)
connection.commit()
print(f"{num_records} fake student records inserted successfully.")
# 插入 10,000 筆假記錄到 Students 表
insert_fake_students(10000)
運行這個腳本後，將會新增 10,000 筆假學生記錄到 Students 表。在下一節中，我們將查詢資料庫，並比較有無索引的查詢效能差異。
如何在沒有索引的情況下查詢
在本節中，我們將在沒有任何索引的情況下查詢 Students 表，以觀察 SQLite 在沒有優化措施時的表現。這將作為基線，以便在我們稍後新增索引時進行效能比較。
沒有索引時，SQLite 會進行全表掃描，這意味著它必須檢查表中的每一行以找到匹配的結果。對於小型數據集，這是可管理的，但隨著記錄數量的增加，搜尋所需的時間會顯著增加。讓我們通過運行一個基本的 SELECT 查詢來搜尋特定學生的名字，並測量所需時間，看看這一過程的實際情況。
首先，我們將通過查詢特定名字的學生來查詢 Students 表。我們將使用 Python 的 time 模組來記錄查詢執行所需的時間，以測量效能。
import sqlite3
import time
def query_without_index(search_name):
"""Query the Students table by name without an index and measure the time taken."""
# 使用 'with' 連接到數據庫
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 計量開始時間
start_time = time.perf_counter_ns()
# 執行 SELECT 查詢以透過姓名找到學生
cursor.execute('''
SELECT * FROM Students WHERE name = ?;
''', (search_name,))
# 獲取所有結果（實際上應該只有一個或幾個）
results = cursor.fetchall()
# 計量結束時間
end_time = time.perf_counter_ns()
# 計算總耗時
elapsed_time = (end_time - start_time) / 1000
# 顯示結果及耗時
print(f"Query completed in {elapsed_time:.5f} microseconds.")
print("Results:", results)
# 示例：透過姓名搜索學生
query_without_index('Ojasvi Dhawan')
以下是輸出：
Query completed in 1578.10000 microseconds.
Results: [(104, 'Ojasvi Dhawan', 21, '[email protected]')]
運行上述腳本，你會看到在沒有索引的情況下搜索 Students 表需要多長時間。例如，如果表中有一萬條記錄，根據表的大小和硬體配置，查詢可能需要1000-2000微秒。對於小型數據集來說，這可能不會太慢，但隨著記錄的增加，性能將會降低。
我們使用 time.perf_counter_ns() 來計量查詢執行的時間，以納秒為單位。這種方法對於對小時間間隔進行基準測試非常準確。我們將時間轉換為微秒(us)，以便更容易閱讀。
介紹查詢計畫
當處理資料庫時，了解查詢如何被执行可以帮助您識別性能瓶頸並優化您的程式碼。SQLite提供了一個有用的工具稱為EXPLAIN QUERY PLAN，它讓您能夠分析SQLite取回數據的步驟。
在這一節中，我們將介紹如何使用EXPLAIN QUERY PLAN來視覺化和理解查询的內部工作机制—特別是，當沒有索引存在時SQLite如何執行完整表掃瞄。
讓我們使用EXPLAIN QUERY PLAN來查看SQLite從Students表中取回數據的情況，當沒有任何索引時。我們將通過姓名查找學生，查詢計劃將顯示SQLite採取哪些步驟來找到匹配的行。
import sqlite3
def explain_query(search_name):
"""Explain the query execution plan for a SELECT query without an index."""
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 使用 EXPLAIN QUERY PLAN 分析查询的执行情况
cursor.execute('''
EXPLAIN QUERY PLAN
SELECT * FROM Students WHERE name = ?;
''', (search_name,))
# 获取并显示查询计划
query_plan = cursor.fetchall()
print("Query Plan:")
for step in query_plan:
print(step)
# 示例：分析按姓名搜索的查询计划
explain_query('Ojasvi Dhawan')
當您運行此代碼時，SQLite將返回一個關於它如何計劃執行查询的分解。這裡是一個例子，顯示輸出可能的外观：
Query Plan:
(2, 0, 0, 'SCAN Students')
這意味著SQLite正在扫瞄整個Students表（完整表扫瞄）以找到name列與提供值（Ojasvi Dhawan）匹配的行。由於name列上沒有索引，SQLite必須檢查表中的每一行。
如何創建索引
建立索引在 SQLite 內存中找尋行的速度會更快，尤其在進行查詢操作時。SQLite 會使用索引跳 directly 到相關的行，而不需要扫瞄整個表，這樣可以大幅提高查詢速度—特別是對於大型數據集的查询。
要建立索引，可以使用以下 SQL 命令：
CREATE INDEX IF NOT EXISTS index-name ON table (column(s));
在這個示例中，我們将在 `Students` 表的 `name` 列上建立索引。以下是如何使用 Python 來進行：
import sqlite3
import time
def create_index():
"""Create an index on the name column of the Students table."""
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 在 name 列上建立索引的 SQL 命令
create_index_query = '''
CREATE INDEX IF NOT EXISTS idx_name ON Students (name);
'''
# 量度起始時間
start_time = time.perf_counter_ns()
# 執行建立索引的 SQL 命令
cursor.execute(create_index_query)
# 量度起始時間
end_time = time.perf_counter_ns()
# 提交更改
connection.commit()
print("Index on 'name' column created successfully!")
# 計算總共花費的時間
elapsed_time = (end_time - start_time) / 1000
# 顯示結果和所用時間
print(f"Query completed in {elapsed_time:.5f} microseconds.")
# 呼叫建立索引的函數
create_index()
輸出：
Index on 'name' column created successfully!
Query completed in 102768.60000 microseconds.
儘管建立索引花費了这么長的時間 (102768.6 微秒)，這是一個一次性的操作。當你執行多個查詢時，你仍然會獲得顯著的性能提升。在以下的節段中，我們將再次查询數據庫以觀察這種索引所帶來的性能改善。
如何使用索引查詢
這段內容我們將執行稍早前執行的同樣SELECT查詢，但這次我們將利用我們在Students表的name欄位上創建的索引。我們將測量並記錄執行時間，以觀察索引提供的性能改善。
import sqlite3
import time
def query_with_index(student_name):
"""Query the Students table using an index on the name column."""
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 根據姓名選擇學生的SQL命令
select_query = 'SELECT * FROM Students WHERE name = ?;'
# 測量執行時間
start_time = time.perf_counter_ns() # 開始計時
# 使用提供的學生姓名執行查詢
cursor.execute(select_query, (student_name,))
result = cursor.fetchall() # 獲取所有結果
end_time = time.perf_counter_ns() # 結束計時
# 計算經過的時間（微秒）
execution_time = (end_time - start_time) / 1000
# 顯示結果及執行時間
print(f"Query result: {result}")
print(f"Execution time with index: {execution_time:.5f} microseconds")
# 示例：透過姓名搜索學生
query_with_index('Ojasvi Dhawan')
以下是輸出的內容：
Query result: [(104, 'Ojasvi Dhawan', 21, '[email protected]')]
Execution time with index: 390.70000 microseconds
我們可以觀察到與沒有索引時執行查詢相比，執行時間顯著縮短。
讓我們分析一下對Students表的name欄位上具有索引的查詢執行計劃。如果你再次執行相同的腳本以解釋查詢，你將得到以下的輸出：
Query Plan:
(3, 0, 0, 'SEARCH Students USING INDEX idx_name (name=?)')
這個計劃現在顯示查詢使用了索引idx_name，大幅減少了需要掃描的行數，從而導致查詢執行速度更快。
性能結果比較
現在，讓我們總結在有索引和無索引的情況下取得的性能結果。
執行時間比較
| 查詢類型 | 執行時間（微秒） |
| 無索引 | 1578.1 |
| 有索引 | 390.7 |
性能提升總結
-
有索引的查詢比無索引的查詢快約4.04倍。
-
加入索引後，執行時間提高了約75.24%。
使用索引的最佳實踐
索引可以顯著提高您的SQLite數據庫的性能，但應該謹慎使用。在處理索引時，請考慮以下一些最佳實踐：
使用索引的時機和原因
-
頻繁查詢的列：在 frequently used in
SELECTqueries 的列上使用索引，特別是那些在WHERE、JOIN和ORDER BY子句中使用的列。这是因为索引这些列可以大大减少查询执行时间。 -
唯一性約束: 当你有必须持唯一值columns （如用戶名或電子郵件地址）时，建立索引可以有效强制这个约束。
-
大數據集: 对于有大量记录的表，索引变得越来越有价值。它们实现了快速查找，这对于数据增长时维持性能至关重要。
-
复合索引: 考虑为过滤或按多个columns 排序的查询创建复合索引。例如，如果你经常根据
name和age搜索学生，这两个columns 的索引可以优化此类查询。
索引的潛在缺點
雖然索引提供了顯著的優勢，但也存在一些潛在的缺點：
-
插入/更新操作變慢：當你在具有索引的表中插入或更新記錄時，SQLite也必須更新索引，這可能會放慢這些操作。這是因為每次插入或更新都需要額外的開銷來維護索引結構。
-
增加的儲存需求：索引會消耗額外的磁碟空間。對於大型表來說，儲存成本可能會相當可觀。在設計數據庫結構時應考慮這一點，尤其是對於存儲資源有限的系統。
-
複合索引管理: 索引太多會使資料庫管理變得複雜。這可能會導致重複索引的狀況，這會降低效能，而不是提高效能。定期檢視和優化索引是一種良 好做法。
索引是優化資料庫查詢的强大工具，但它需要謹慎考慮。在提高讀取性能和潛在的寫操作開銷之間保持平衡是關鍵。以下是達到平衡的一些策略：
-
監控查詢性能: 使用 SQLite 的
EXPLAIN QUERY PLAN來分析帶有和沒有索引的查詢效能。這可以幫助確定哪些索引有益，哪些可能 不必要。 -
定期維護: 定期檢視你的索引並評估是否仍然需要。移除重複的或很少使用的索引以簡化資料庫操作。
-
測試與評估: 在生產環境中實例化索引之前，進行彻底的測試以了解它們對讀取和寫入操作的影響。
遵循這些最佳實踐，您可以 leverate 索引的利益同時最小化潛在的缺點，從而提高您的 SQLite 數據庫的性能和效率。
處理錯誤和異常
在這一節中，我們將討論當您在 Python 中與 SQLite 合作時，如何處理錯誤和異常。正確的錯誤處理對於維護您的數據庫完整性至关重要，並確保您的應用程序可預測地行為。
SQLite 操作中的常見錯誤
當與 SQLite 數據庫互動時，可能會出現幾種常見錯誤：
-
約束違反: 當您嘗試插入或更新違反數據庫約束的數據時發生，例如主鍵唯一性或外部鍵約束。例如，嘗試插入重複的主鍵將觸發錯誤。
-
數據類型不匹配：試圖插入錯誤類型的數據（例如，在期望數字的情況下插入字串）可能會導致錯誤。
-
數據庫鎖定錯誤：如果數據庫正在被另一個進程或連接寫入，試圖訪問它可能會導致“數據庫已被鎖定”的錯誤。
-
語法錯誤：在您的SQL語法中犯錯會導致在執行命令時出現錯誤。
如何使用Python的異常處理
Python 內建的例外處理機制（try 與 except）對於管理 SQLite 操作中的錯誤至關重要。透過使用這些結構，您可以捕獲例外並恰當地回應，而不使您的程式崩潰。
以下是在數據庫中插入數據時處理錯誤的基本示例：
import sqlite3
def add_customer_with_error_handling(name, balance):
"""Add a new customer with error handling."""
try:
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
cursor.execute(
"INSERT INTO Customers (name, balance) VALUES (?, ?);", (name, balance))
connection.commit()
print(f"Added customer: {name} with balance: {balance}")
except sqlite3.IntegrityError as e:
print(f"Error: Integrity constraint violated - {e}")
except sqlite3.OperationalError as e:
print(f"Error: Operational issue - {e}")
except Exception as e:
print(f"An unexpected error occurred: {e}")
# 使用範例
add_customer_with_error_handling('Vishakha', 100.0) # 有效
add_customer_with_error_handling('Vishakha', 150.0) # 重複條目
在此示例中：
-
我們捕獲
IntegrityError，當違反獨一性約束等規範時會引發此錯誤。 -
我們捕獲
OperationalError來處理一般的數據庫相關問題（如數據庫鎖定錯誤）。 -
我們還有一個泛用的
except區塊來處理任何未預期的例外。
輸出：
Added customer: Vishakha with balance: 100.0
Error: Integrity constraint violated - UNIQUE constraint failed: Customers.name
確保數據庫完整性的最佳實踐
-
使用交易
: 當進行多個相關操作時，总是使用交易（如前所述）。這有助於確保所有操作成功或全部失敗，以保持一致性。
-
驗證輸入數據: 在執行SQL命令之前，驗證輸入數據以確保它符合预期的標準（例如，正確的類型，在允許的範圍內）。
-
捕獲特定異常: 總是捕獲特定異常以適當處理不同類型的錯誤。這有助於更清晰的錯誤處理和除錯。
-
記錄錯誤: 不僅僅是把錯誤打印到控制台，考慮將它們記錄到文件或監控系統中。這將幫助你在生產環境中追蹤問題。
- 優雅降級：設計你的應用程式以優雅的方式處理錯誤。如果一個操作失敗，向用戶提供有意義的反饋，而不是讓應用程式崩潰。
-
定期備份資料：定期備份你的數據庫，以防止在嚴重故障或損壞情況下資料丟失。
-
使用預準備語句：預準備語句有助於防止SQL注入攻擊，並且還可以提高重複查詢的性能。
如何導出和導入資料 [附帶章節]
在本節中，我們將學習如何使用Python將SQLite數據庫中的資料導出為常見格式如CSV和JSON，以及如何從這些格式將資料導入SQLite。這對於資料共享、備份和與其他應用程式的整合非常有用。
將SQLite資料導出至CSV
使用Python的內建庫將資料導出至CSV（逗號分隔值）文件是相當直觀的。CSV文件廣泛用於資料存儲和交換，使它成為導出資料的方便格式。
這是將SQLite表中的數據匯出到CSV文件的方法：
import sqlite3
import csv
def export_to_csv(file_name):
"""Export data from the Customers table to a CSV file."""
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 執行SQL查询以獲取所有客戶數據
cursor.execute("SELECT * FROM Customers;")
customers = cursor.fetchall()
# 將數據寫入CSV文件
with open(file_name, 'w', newline='') as csv_file:
csv_writer = csv.writer(csv_file)
csv_writer.writerow(['ID', 'Name', 'Balance']) # 寫入標題行
csv_writer.writerows(customers) # 寫入數據行
print(f"Data exported successfully to {file_name}.")
# 示例用法
export_to_csv('customers.csv')
如何將數據匯出到JSON格式
similarly, you can export data to a JSON (JavaScript Object Notation) file, which is a popular format for data interchange, especially in web applications.
以下是將數據匯出到JSON格式的示例：
import json
import sqlite3
def export_to_json(file_name):
"""Export data from the Customers table to a JSON file."""
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 執行SQL查询以獲取所有客戶數據
cursor.execute("SELECT * FROM Customers;")
customers = cursor.fetchall()
#將數據轉化為字典列表
customers_list = [{'ID': customer[0], 'Name': customer[1],
'Balance': customer[2]} for customer in customers]
#將數據寫入JSON文件
with open(file_name, 'w') as json_file:
json.dump(customers_list, json_file, indent=4)
print(f"Data exported successfully to {file_name}.")
# 示例用法
export_to_json('customers.json')
如何從CSV文件將數據匯入SQLite
您還可以將CSV文件中的數據匯入SQLite數據庫。這對於用現有的數據集填充您的數據庫很有用。
以下是從CSV文件導入數據的方法：
import csv
import sqlite3
def import_from_csv(file_name):
"""Import data from a CSV file into the Customers table."""
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 打開 CSV 檔案以供讀取
with open(file_name, 'r') as csv_file:
csv_reader = csv.reader(csv_file)
next(csv_reader) # 跳過標題列
# 將每一列插入 Customers 表中
for row in csv_reader:
cursor.execute(
"INSERT INTO Customers (name, balance) VALUES (?, ?);", (row[1], row[2]))
connection.commit()
print(f"Data imported successfully from {file_name}.")
# 範例用法
import_from_csv('customer_data.csv')
如何從 JSON 導入數據到 SQLite
同樣地，從 JSON 檔案導入數據是簡單的。您可以讀取 JSON 檔案並將數據插入到您的 SQLite 表中。
以下是操作步驟：
import json
import sqlite3
def import_from_json(file_name):
"""Import data from a JSON file into the Customers table."""
with sqlite3.connect('my_database.db') as connection:
cursor = connection.cursor()
# 打開 JSON 檔案以供讀取
with open(file_name, 'r') as json_file:
customers_list = json.load(json_file)
# 將每位客戶插入 Customers 表中
for customer in customers_list:
cursor.execute("INSERT INTO Customers (name, balance) VALUES (?, ?);", (customer['Name'], customer['Balance']))
connection.commit()
print(f"Data imported successfully from {file_name}.")
# 範例用法
import_from_json('customer_data.json')
總結
就是這樣了！本指南向您介紹了在 Python 中使用 SQLite 的基本知識，從設置環境到查詢和操作數據，以及導出和導入信息等方面。希望您找到了有用的信息，並且激發了您對在項目中使用 SQLite 的興趣。
現在是時候將您新學到的知識付諸實踐了！我鼓勵您使用 SQLite 和 Python 創建您的項目。無論是用於管理您的圖書館的簡單應用程序，預算工具，還是其他獨特的應用，可能性是無限的。
完成項目後，請在 Twitter 上分享並標記我！我很樂意看到您創建的內容並慶祝您的成就。
您可以在 GitHub 上找到本教學中的所有代碼。感謝您的跟隨，祝您編程愉快！
免費使用 TOC 生成器 工具為您的 freeCodeCamp 文章生成目錄。
Source:
https://www.freecodecamp.org/news/work-with-sqlite-in-python-handbook/