SQLite 資料函式庫操作

– 8 分鐘閱讀

SQLite 是一種輕量級嵌入式資料函式庫，廣泛應用於各種應用程式開發。本文涵蓋了 SQLite 的基礎操作，從連線、建立表格到資料的增刪改查，也包含了事務處理、SQL 注入防護等重要觀念。此外，文章也說明瞭如何使用 Python 操作 SQLite，包含 WHERE 子句篩選、LIKE 模糊查詢、ORDER BY 排序、LIMIT 限制筆數等進階查詢技巧。同時也介紹了索引建立與刪除、資料函式庫備份與還原、表格結構修改、多表 JOIN 操作，以及在記憶體中建立與操作 SQLite 資料函式庫的方法，提供開發者更全面的 SQLite 使用。

資料函式庫基礎操作

資料函式庫的基本操作包括建立、讀取、更新和刪除（CRUD）。這些操作是資料函式倉管理的基礎。

插入資料

要將資料插入資料函式庫，可以使用INSERT陳述式。例如，建立了一個名為cats的表格，現在要插入一筆新的資料：

INSERT INTO cats VALUES ("Zophie", "2021-01-24", "black", 5.6);

這個INSERT陳述式將一筆新的資料插入cats表格中。注意，括號內的值必須按照表格的欄位順序排列。

###事務（Transaction） 當執行INSERT陳述式時，資料函式庫會開始一個事務（Transaction）。事務是一個單位的工作，必須滿足ACID原則，即：

  • Atomic：事務是不可分割的，全部完成或全部不完成。
  • Consistent：事務不會違反資料函式庫的約束條件。
  • Isolated：事務不會影響其他事務。
  • Durable：事務完成後，結果會被寫入永久儲存。

SQLite是一個符合ACID原則的資料函式庫，因此可以確保事務的完整性。

SQL注入攻擊

SQL注入攻擊是一種駭客技術，可以修改SQL陳述式以達到惡意目的。為了防止這種攻擊，應該使用?問號語法來參照變數。例如：

cat_name = 'Zophie'
cat_bday = '2021-01-24'
fur_color = 'black'
cat_weight = 5.6

conn.execute('INSERT INTO cats VALUES (?,?,?,?)', (cat_name, cat_bday, fur_color, cat_weight))

這樣可以防止SQL注入攻擊。

讀取資料

要讀取資料，可以使用SELECT陳述式。例如：

SELECT * FROM cats;

這個SELECT陳述式會讀取cats表格中的所有資料。

迴圈讀取查詢結果

可以使用迴圈來讀取查詢結果。例如：

for row in conn.execute('SELECT * FROM cats'):
    print(row)

這個迴圈會讀取查詢結果中的每一筆資料，並將其印出。

內容解密：

上述程式碼示範瞭如何使用Python連線SQLite資料函式庫，並執行INSERT和SELECT陳述式。同時，也介紹了事務和SQL注入攻擊的概念。瞭解這些基礎操作，可以幫助您更好地管理您的資料函式庫。

圖表翻譯：

這個流程圖示範了上述程式碼的執行流程。

使用SQLite進行資料函式庫查詢和更新

資料函式庫連線和查詢

首先，我們需要連線到SQLite資料函式庫。假設我們有一個名為sweigartcats.db的資料函式庫檔案，以下是如何連線和執行查詢的步驟：

import sqlite3

# 連線到資料函式庫
conn = sqlite3.connect('sweigartcats.db')
cur = conn.cursor()

# 執行查詢
cur.execute('SELECT * FROM cats')

# 取得查詢結果
rows = cur.fetchall()

# 關閉連線
conn.close()

WHERE子句

WHERE子句用於篩選資料。例如，以下查詢會取得所有毛色為黑色的貓：

SELECT * FROM cats WHERE fur = "black"

LIKE運算元

LIKE運算元用於模糊查詢。例如，以下查詢會取得所有名字以"y"結尾的貓：

SELECT * FROM cats WHERE name LIKE "%y"

ORDER BY子句

ORDER BY子句用於排序查詢結果。例如，以下查詢會取得所有貓按照名字排序的結果：

SELECT * FROM cats ORDER BY name

LIMIT子句

LIMIT子句用於限制查詢結果的數量。例如，以下查詢會取得前3條資料：

SELECT * FROM cats LIMIT 3

更新資料

UPDATE陳述式用於更新資料。例如，以下陳述式會更新ID為1的貓的毛色為灰色：

UPDATE cats SET fur = "gray" WHERE rowid = 1

建立索引

索引可以加速查詢速度。以下陳述式會建立一個名為idx_name的索引在name欄位上：

CREATE INDEX idx_name ON cats (name)

刪除索引

如果索引不再需要，可以使用DROP INDEX陳述式刪除：

DROP INDEX idx_name

這些是使用SQLite進行資料函式庫查詢和更新的基本步驟。根據具體需求，可以使用不同的陳述式和函式來實作更多功能。

更新資料函式庫內容

更新資料函式庫內容可以使用UPDATE關鍵字，語法如下：

UPDATE 表名 SET 列1 = 值1, 列2 = 值2 WHERE 條件;

例如，更新cats表中name為Zophie的資料：

UPDATE cats SET fur = "black", weight_kg = 6 WHERE rowid = 1;

這會更新cats表中rowid為1的資料，將fur欄位更新為"black"，weight_kg欄位更新為6。

刪除資料函式庫內容

刪除資料函式庫內容可以使用DELETE關鍵字，語法如下：

DELETE FROM 表名 WHERE 條件;

例如，刪除cats表中name為Zophie的資料：

DELETE FROM cats WHERE rowid = 1;

這會刪除cats表中rowid為1的資料。

回復交易

如果您想要執行多個查詢，並且在執行完畢後決定是否要提交或回復，可以使用BEGIN、COMMIT和ROLLBACK關鍵字。 例如：

import sqlite3

conn = sqlite3.connect("sweigartcats.db")
cursor = conn.cursor()

cursor.execute("BEGIN")
cursor.execute("INSERT INTO cats VALUES ('Socks', '2022-04-04', 'white', 4.2)")
cursor.execute("INSERT INTO cats VALUES ('Fluffy', '2022-04-04', 'gray', 3.5)")

# 如果想要提交交易
cursor.execute("COMMIT")

# 如果想要回復交易
cursor.execute("ROLLBACK")

這會開始一個新交易，執行兩個INSERT查詢，如果想要提交交易，可以使用COMMIT關鍵字，如果想要回復交易，可以使用ROLLBACK關鍵字。

備份資料函式庫

如果您想要備份資料函式庫，可以使用backup()方法：

import sqlite3

conn = sqlite3.connect("sweigartcats.db")
backup_conn = sqlite3.connect("backup.db")
conn.backup(backup_conn)

這會備份sweigartcats.db資料函式庫到backup.db資料函式庫。

內容解密：

上述程式碼示範瞭如何更新、刪除和回復資料函式庫內容，以及如何備份資料函式庫。UPDATE關鍵字可以用來更新資料函式庫內容，DELETE關鍵字可以用來刪除資料函式庫內容，而BEGIN、COMMIT和ROLLBACK關鍵字可以用來控制交易。backup()方法可以用來備份資料函式庫。

圖表翻譯：

這個流程圖示範了更新、刪除、回復和備份資料函式庫的流程。

修改和刪除表格

在 SQLite 中，您可以使用 ALTER TABLE 查詢來修改表格結構，例如重新命名錶格、新增或刪除欄位。以下是幾個範例：

重新命名錶格

ALTER TABLE cats RENAME TO felines;

重新命名欄位

ALTER TABLE felines RENAME COLUMN fur TO description;

新增新欄位

ALTER TABLE felines ADD COLUMN is_loved INTEGER DEFAULT 1;

刪除欄位

ALTER TABLE felines DROP COLUMN is_loved;

刪除表格

DROP TABLE felines;

請注意，在修改表格結構時，必須小心以免造成資料損失或不一致。

Joining 多個表格

在 SQLite 中，您可以使用 JOIN 查詢來連線多個表格。以下是幾個範例：

內部連線（Inner Join）

SELECT * FROM cats INNER JOIN vaccinations ON cats.rowid = vaccinations.cat_id;

這個查詢會傳回 cats 表格和 vaccinations 表格中相關的資料。

在記憶體中建立資料函式庫

您可以使用 :memory: 來建立一個在記憶體中儲存的資料函式庫。以下是幾個範例：

建立在記憶體中的資料函式庫

import sqlite3
conn = sqlite3.connect(':memory:')

將資料函式庫儲存到檔案中

conn.backup('test.db')

請注意，在記憶體中建立的資料函式庫會在程式結束時自動刪除。

複製資料函式庫

您可以使用 iterdump() 方法來複製資料函式庫。以下是幾個範例：

複製資料函式庫到檔案中

with open('sweigartcats-queries.txt', 'w', encoding='utf-8') as fileObj:
    for line in conn.iterdump():
        fileObj.write(line + '\n')

這個範例會將資料函式庫的查詢語法儲存到檔案中。

圖表翻譯：

SQLite 資料函式庫入門

SQLite 是一種輕量級的關聯式資料函式庫，廣泛用於各種應用程式中。它的優點包括易於使用、佔用資源少、不需要專門的伺服器等。

連線 SQLite 資料函式庫

要連線 SQLite 資料函式庫，可以使用 Python 的 sqlite3 模組。以下是連線資料函式庫的基本步驟：

  1. 匯入 sqlite3 模組。
  2. 使用 connect() 函式連線資料函式庫，傳入資料函式庫檔案的路徑作為引數。
import sqlite3

# 連線資料函式庫
conn = sqlite3.connect('example.db')

建立表格

建立表格可以使用 CREATE TABLE SQL 指令。以下是建立一個名為 cats 的表格的範例：

CREATE TABLE cats (
    name TEXT,
    birthdate TEXT,
    fur TEXT,
    weight_kg REAL
);

執行 SQL 指令

可以使用 execute() 方法執行 SQL 指令。以下是插入一筆資料到 cats 表格的範例：

# 建立一個指令物件
cursor = conn.cursor()

# 執行 SQL 指令
cursor.execute('''
    INSERT INTO cats (name, birthdate, fur, weight_kg)
    VALUES ('Zophie', '2021-01-24', 'gray tabby', 4.7)
''')

# 提交變更
conn.commit()

查詢資料

可以使用 SELECT SQL 指令查詢資料。以下是查詢所有 cats 表格資料的範例：

SELECT * FROM cats;

更新資料

可以使用 UPDATE SQL 指令更新資料。以下是更新 cats 表格中一筆資料的範例：

UPDATE cats SET weight_kg = 5.0 WHERE name = 'Zophie';

刪除資料

可以使用 DELETE SQL 指令刪除資料。以下是刪除 cats 表格中一筆資料的範例：

DELETE FROM cats WHERE name = 'Zophie';

練習題

  1. 如何取得一個 SQLite 資料函式庫的連線物件？
  2. 如何建立一個名為 students 的表格，具有 first_name、last_name 和 favorite_color 欄位？
  3. 如何連線 SQLite 資料函式庫並啟用自動提交模式？
  4. INTEGER 和 REAL 資料型別在 SQLite 中有何不同？
  5. 嚴格模式（STRICT）對表格有何影響？
  6. 在查詢中，星號（*）代表什麼？
  7. CRUD 是什麼意思？
  8. ACID 是什麼意思？
  9. 如何新增記錄到表格中？
  10. 如何刪除表格中的記錄？

內容解密：

以上程式碼示範瞭如何連線 SQLite 資料函式庫、建立表格、執行 SQL 指令、查詢資料、更新資料和刪除資料。這些基本操作是使用 SQLite 資料函式庫的基礎。

圖表翻譯：

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title SQLite 資料函式庫操作

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

此圖表展示了使用 SQLite 資料函式庫的基本流程，從連線資料函式庫開始，到建立表格、執行 SQL 指令、查詢資料、更新資料和刪除資料。

從資料函式庫應用開發的角度來看，本文深入淺出地介紹了SQLite的基礎操作，涵蓋了資料的增刪改查、事務處理、SQL注入防護以及資料函式庫的備份和修改等關鍵環節。透過實際程式碼範例和流程圖解說，清晰地展現了SQLite的實用性和易用性。然而，文章並未深入探討SQLite在高併發場景下的效能瓶頸以及與其他資料函式庫系統的比較分析，這也是未來進一步研究的方向。對於小型應用或嵌入式系統而言，SQLite無疑是一個輕量且高效的選擇。技術團隊應關注資料函式庫設計的合理性以及SQL陳述式的最佳化，才能最大限度地發揮SQLite的效能優勢。展望未來，隨著物聯網和邊緣計算的興起，SQLite在資源受限的環境中將扮演更重要的角色。

玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。