返回文章列表
玄貓（BlackCat）

SQLite 資料函式庫操作

本文介紹 SQLite 資料函式庫的基本操作，包含 CRUD、事務、SQL 注入防護、迴圈讀取、WHERE、LIKE、ORDER BY、LIMIT 子句應用，以及索引、資料函式庫備份、表格修改、JOIN 語法與記憶體資料函式庫應用等，提供 Python 程式碼範例與流程圖解說。

資料函式庫 後端開發
SQLite SQL 資料函式庫操作 CRUD Python 資料函式庫連線

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在資源受限的環境中將扮演更重要的角色。