Python SQLite 教程 · ZetCode 中文系列教程

# Python SQLite 教程 > 原文： [http://zetcode.com/python/sqlite/](http://zetcode.com/python/sqlite/) 這是用于 SQLite 數據庫的 Python 編程教程。它涵蓋了使用 Python 語言進行 SQLite 編程的基礎。 ZetCode 擁有用于 Python SQLite 的完整電子書： [Python SQLite 電子書](/ebooks/sqlitepython/)。要使用本教程，我們必須在系統上安裝 Python 語言，SQLite 數據庫，`pysqlite`語言綁定和`sqlite3`命令行工具。為了使用 SQLite 數據庫，我們可以安裝`sqlite3`或 SQLite 瀏覽器 GUI。 ```py $ python Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 27 2018, 04:59:51) [MSC v.1914 64 bit (AMD64)] on win32 Type "help", "copyright", "credits" or "license" for more information. >>> import sqlite3 >>> sqlite3.version '2.6.0' >>> sqlite3.sqlite_version '3.21.0' ``` 在外殼程序中，我們啟動 Python 交互式解釋器。我們可以看到 Python 版本。在我們的例子中是 Python 3.7.0。 `sqlite.version`是`pysqlite`（2.6.0）的版本，它是 Python 語言與 SQLite 數據庫的綁定。 `sqlite3.sqlite_version`為我們提供了 SQLite 數據庫庫的版本。在我們的例子中，版本是 3.21.0。 ## SQLite SQLite 是嵌入式關系數據庫引擎。該文檔稱其為自包含，無服務器，零配置和事務型 SQL 數據庫引擎。如今，它在全球使用著數億冊，非常受歡迎。幾種編程語言都內置了對 SQLite 的支持，包括 Python 和 PHP。 ## 創建 SQLite 數據庫現在，我們將使用`sqlite3`命令行工具創建一個新數據庫。 ```py $ sqlite3 ydb.db SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. sqlite> ``` 我們為`sqlite3 tool`提供了一個參數； `ydb.db`是數據庫名稱。這是我們磁盤上的文件。如果存在，則將其打開。如果不是，則創建它。 ```py sqlite> .tables sqlite> .exit $ ls ydb.db ``` `.tables`命令提供`ydb.db`數據庫中的表的列表。當前沒有表格。 `.exit`命令終止`sqlite3`命令行工具的交互式會話。 `ls` Unix 命令顯示當前工作目錄的內容。我們可以看到`ydb.db`文件。所有數據將存儲在該單個文件中。 ## Python SQLite 版本示例在第一個代碼示例中，我們將獲得 SQLite 數據庫的版本。 `version.py` ```py #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlite3 as sqlite import sys con = None try: con = sqlite.connect('ydb.db') cur = con.cursor() cur.execute('SELECT SQLITE_VERSION()') data = cur.fetchone()[0] print(f"SQLite version: {data}") except sqlite.Error as e: print(f"Error {e.args[0]}") sys.exit(1) finally: if con: con.close() ``` 在上面的 Python 腳本中，我們連接到先前創建的`ydb.db`數據庫。我們執行一條 SQL 語句，該語句返回 SQLite 數據庫的版本。 ```py import sqlite3 as sqlite ``` 我們導入`sqlite3`并為其命名。 ```py con = None ``` 我們將`con`變量初始化為`None`。如果無法創建與數據庫的連接（例如磁盤已滿），則不會定義連接變量。這將導致`finally`子句中的錯誤。 ```py con = sqlite.connect('ydb.db') ``` 我們連接到`ydb.db`數據庫。 `connect()`方法返回一個連接對象。 ```py cur = con.cursor() cur.execute('SELECT SQLITE_VERSION()') ``` 從連接中，我們得到游標對象。游標用于遍歷結果集中的記錄。我們調用游標的`execute()`方法并執行 SQL 語句。 ```py data = cur.fetchone()[0] ``` 我們獲取數據。由于只檢索一條記錄，因此我們稱為`fetchone()`方法。 ```py print(f"SQLite version: {data}") ``` 我們將檢索到的數據打印到控制臺。 ```py except sqlite.Error as e: print(f"Error {e.args[0]}") sys.exit(1) ``` 如果發生異常，我們將輸出一條錯誤消息，并以錯誤代碼 1 退出腳本。 ```py finally: if con: con.close() ``` 在最后一步，我們釋放資源。在第二個示例中，我們再次獲得 SQLite 數據庫的版本。這次我們將使用`with`關鍵字。 `version2.py` ```py #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlite3 as sqlite con = sqlite.connect('ydb.db') with con: cur = con.cursor() cur.execute('SELECT SQLITE_VERSION()') data = cur.fetchone()[0] print(f"SQLite version: {data}") ``` 該腳本返回 SQLite 數據庫的當前版本。通過使用`with`關鍵字。代碼更緊湊。 ```py with con: ``` 使用`with`關鍵字，Python 解釋器會自動釋放資源。它還提供錯誤處理。 ```py $ ./version.py SQLite version: 3.21.0 ``` 這是輸出。 ## Python SQLite `execute` 我們創建一個`cars`表并在其中插入幾行。我們使用`execute()`。 `create_table.py` ```py #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlite3 as sqlite con = sqlite.connect('ydb.db') with con: cur = con.cursor() cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)") cur.execute("INSERT INTO cars VALUES(1,'Audi',52642)") cur.execute("INSERT INTO cars VALUES(2,'Mercedes',57127)") cur.execute("INSERT INTO cars VALUES(3,'Skoda',9000)") cur.execute("INSERT INTO cars VALUES(4,'Volvo',29000)") cur.execute("INSERT INTO cars VALUES(5,'Bentley',350000)") cur.execute("INSERT INTO cars VALUES(6,'Citroen',21000)") cur.execute("INSERT INTO cars VALUES(7,'Hummer',41400)") cur.execute("INSERT INTO cars VALUES(8,'Volkswagen',21600)") ``` 上面的腳本創建一個`cars`表，并將 8 行插入到該表中。 ```py cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)") ``` 該 SQL 語句創建一個新的`cars`表。該表有三列。 ```py cur.execute("INSERT INTO cars VALUES(1,'Audi',52642)") cur.execute("INSERT INTO cars VALUES(2,'Mercedes',57127)") ``` 這兩行將兩輛車插入表。使用`with`關鍵字，更改將自動提交。否則，我們將不得不手動提交它們。 ```py sqlite> .mode column sqlite> .headers on ``` 我們使用`sqlite3`工具驗證寫入的數據。首先，我們修改數據在控制臺中的顯示方式。我們使用列模式并打開標題。 ```py sqlite> select * from cars; id name price ---------- ---------- ---------- 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600 ``` 這是我們已寫入`cars`表的數據。 ## Python SQLite `executemany` 我們將創建相同的表。這次使用便捷的`executemany()`方法。 `create_table2.py` ```py #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlite3 as sqlite cars = ( (1, 'Audi', 52642), (2, 'Mercedes', 57127), (3, 'Skoda', 9000), (4, 'Volvo', 29000), (5, 'Bentley', 350000), (6, 'Hummer', 41400), (7, 'Volkswagen', 21600) ) con = sqlite.connect('ydb.db') with con: cur = con.cursor() cur.execute("DROP TABLE IF EXISTS cars") cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)") cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars) ``` 程序將刪除`cars`表（如果存在）并重新創建它。 ```py cur.execute("DROP TABLE IF EXISTS cars") cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)") ``` 如果存在，則第一個 SQL 語句將刪除`cars`表。第二條 SQL 語句創建`cars`表。 ```py cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars) ``` 我們使用便捷的`executemany()`方法將 8 行插入到表中。此方法的第一個參數是參數化的 SQL 語句。第二個參數是數據，以元組的元組的形式。 ## Python SQLite `executescript` 我們提供了另一種使用`executescript()`創建`cars`表的方法。我們手動提交更改并提供我們自己的錯誤處理。 `create_table3.py` ```py #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlite3 as sqlite import sys con = None try: con = sqlite.connect('ydb.db') cur = con.cursor() cur.executescript(""" DROP TABLE IF EXISTS cars; CREATE TABLE cars(id INT, name TEXT, price INT); INSERT INTO cars VALUES(1,'Audi',52642); INSERT INTO cars VALUES(2,'Mercedes',57127); INSERT INTO cars VALUES(3,'Skoda',9000); INSERT INTO cars VALUES(4,'Volvo',29000); INSERT INTO cars VALUES(5,'Bentley',350000); INSERT INTO cars VALUES(6,'Citroen',21000); INSERT INTO cars VALUES(7,'Hummer',41400); INSERT INTO cars VALUES(8,'Volkswagen',21600); """) con.commit() except sqlite.Error as e: if con: con.rollback() print(f"Error {e.args[0]}") sys.exit(1) finally: if con: con.close() ``` 在上面的腳本中，我們使用`executescript()`方法（重新）創建`cars`表。 ```py cur.executescript(""" DROP TABLE IF EXISTS cars; CREATE TABLE cars(id INT, name TEXT, price INT); INSERT INTO cars VALUES(1,'Audi',52642); INSERT INTO cars VALUES(2,'Mercedes',57127); ... ``` `executescript()`方法允許我們一步執行整個 SQL 代碼。 ```py con.commit() ``` 如果沒有`with`關鍵字，則必須使用`commit()`方法來提交更改。 ```py except sqlite.Error as e: if con: con.rollback() print(f"Error {e.args[0]}") sys.exit(1) ``` 發生錯誤時，所做的更改將回滾，并在終端上顯示一條錯誤消息。 ## Python SQLite `lastrowid` 有時，我們需要確定最后插入的行的 ID。在 Python SQLite 中，我們使用游標對象的`lastrowid`屬性。 `lastrowid.py` ```py #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlite3 as sqlite con = sqlite.connect(':memory:') with con: cur = con.cursor() cur.execute("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT);") cur.execute("INSERT INTO friends(name) VALUES ('Tom');") cur.execute("INSERT INTO friends(name) VALUES ('Rebecca');") cur.execute("INSERT INTO friends(name) VALUES ('Jim');") cur.execute("INSERT INTO friends(name) VALUES ('Robert');") last_row_id = cur.lastrowid print(f"The last Id of the inserted row is {last_row_id}") ``` 我們在內存中創建一個`friends`表。 ID 會自動遞增。 ```py cur.execute("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT);") ``` 在 SQLite 中，`INTEGER PRIMARY KEY`列自動增加。還有一個`AUTOINCREMENT`關鍵字。在`INTEGER PRIMARY KEY AUTOINCREMENT`中使用時，會使用稍微不同的 ID 創建算法。 ```py cur.execute("INSERT INTO friends(name) VALUES ('Tom');") cur.execute("INSERT INTO friends(name) VALUES ('Rebecca');") cur.execute("INSERT INTO friends(name) VALUES ('Jim');") cur.execute("INSERT INTO friends(name) VALUES ('Robert');") ``` 使用自動增量時，我們必須明確聲明列名，而忽略自動增量的列名。這四個語句在`friends`表中插入四行。 ```py last_row_id = cur.lastrowid ``` 使用`lastrowid`獲得最后插入的行 ID。 ```py $ ./lastrowid.py The last Id of the inserted row is 4 ``` 我們看到了程序的輸出。 ## Python SQLite 使用`fetchall`檢索數據 `fetchall()`方法獲取查詢結果集的所有（或所有剩余）行，并返回一個元組列表。 `fetch_all.py` ```py #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlite3 as sqlite con = sqlite.connect('ydb.db') with con: cur = con.cursor() cur.execute("SELECT * FROM cars") rows = cur.fetchall() for row in rows: print(f"{row[0]} {row[1]} {row[2]}") ``` 在此示例中，我們從`cars`表中檢索所有數據。 ```py cur.execute("SELECT * FROM cars") ``` 該 SQL 語句從`cars`表中選擇所有數據。 ```py rows = cur.fetchall() ``` `fetchall()`方法獲取所有記錄。它返回一個結果集。從技術上講，它是一個元組的元組。每個內部元組代表表中的一行。 ```py for row in rows: print(f"{row[0]} {row[1]} {row[2]}") ``` 我們將數據逐行打印到控制臺。 ```py $ ./fetch_all.py 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600 ``` 這是示例的輸出。 ## Python SQLite `fetchone` `fetchone()`返回查詢結果集的下一行，返回單個元組，或者在沒有更多數據可用時返回`None`。 `fetch_one.py` ```py #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlite3 as sqlite con = sqlite.connect('ydb.db') with con: cur = con.cursor() cur.execute("SELECT * FROM cars") while True: row = cur.fetchone() if row == None: break print(f"{row[0]} {row[1]} {row[2]}") ``` 在此腳本中，我們連接到數據庫，并逐個讀取`cars`表的行。 ```py while True: ``` 我們從`while`循環訪問數據。當我們讀取最后一行時，循環終止。 ```py row = cur.fetchone() if row == None: break ``` `fetchone()`方法返回表的下一行。如果沒有剩余數據，則返回`None`。在這種情況下，我們打破了循環。 ```py print(f"{row[0]} {row[1]} {row[2]}") ``` 數據以元組的形式返回。在這里，我們從元組中選擇記錄。第一個是 ID，第二個是汽車名稱，第三個是汽車的價格。 ```py $ ./fetch_one.py 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600 ``` 這是示例的輸出。 ## Python SQLite 字典游標默認游標以元組的元組返回數據。當我們使用字典游標時，數據以 Python 字典的形式發送。這樣，我們可以通過列名稱來引用數據。 `dictionary_cursor.py` ```py #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlite3 as sqlite con = sqlite.connect('ydb.db') with con: con.row_factory = sqlite.Row cur = con.cursor() cur.execute("SELECT * FROM cars") rows = cur.fetchall() for row in rows: print(f"{row['id']} {row['name']} {row['price']}") ``` 在此示例中，我們使用字典游標打印`cars`表的內容。 ```py con.row_factory = sqlite.Row ``` 我們選擇一個字典游標。現在，我們可以按列名訪問記錄。 ```py for row in rows: print(f"{row['id']} {row['name']} {row['price']}") ``` 通過列名訪問數據。 ## Python SQLite 參數化語句現在，我們將關注參數化查詢。當使用參數化查詢時，我們使用占位符，而不是直接將值寫入語句。參數化查詢可提高安全性和性能。 Python `sqlite3`模塊支持兩種類型的占位符：問號和命名占位符。 ### 帶問號的參數化語句在第一個示例中，我們使用問號的語法。 `parameterized_query.py` ```py #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlite3 as sqlite uId = 1 uPrice = 62300 con = sqlite.connect('ydb.db') with con: cur = con.cursor() cur.execute("UPDATE cars SET price=? WHERE id=?", (uPrice, uId)) print(f"Number of rows updated: {cur.rowcount}") ``` 我們更新了一輛車的價格。在此代碼示例中，我們使用問號占位符。 ```py cur.execute("UPDATE cars SET price=? WHERE id=?", (uPrice, uId)) ``` 問號`?`是值的占位符。這些值將添加到占位符。 ```py print(f"Number of rows updated: {cur.rowcount}") ``` `rowcount`屬性返回更新的行數。在我們的情況下，一行已更新。 ### 具有命名占位符的參數化語句第二個示例使用帶有命名占位符的參數化語句。 `named_placeholders.py` ```py #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlite3 as sqlite uId = 4 con = sqlite.connect('ydb.db') with con: cur = con.cursor() cur.execute("SELECT name, price FROM cars WHERE Id=:Id", {"Id": uId}) row = cur.fetchone() print(f"{row[0]}, {row[1]}") ``` 我們使用命名的占位符選擇汽車的名稱和價格。 ```py cur.execute("SELECT name, price FROM cars WHERE Id=:Id", {"Id": uId}) ``` 命名的占位符以冒號開頭。 ## Python SQLite 插入圖片在本節中，我們將圖像插入到 SQLite 數據庫中。請注意，有些人反對將圖像放入數據庫。在這里，我們只展示如何做。我們不討論是否將圖像保存在數據庫中的技術問題。 ```py sqlite> CREATE TABLE images(id INTEGER PRIMARY KEY, data BLOB); ``` 對于此示例，我們創建一個名為`Images`的新表。對于圖像，我們使用`BLOB`數據類型，表示二進制大型對象。 `insert_image.py` ```py #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlite3 as sqlite import sys def readImage(): fin = None try: fin = open("sid.jpg", "rb") img = fin.read() return img except IOError as e: print(e) sys.exit(1) finally: if fin: fin.close() con = None try: con = sqlite.connect('ydb.db') cur = con.cursor() data = readImage() binary = sqlite.Binary(data) cur.execute("INSERT INTO images(data) VALUES (?)", (binary,) ) con.commit() except sqlite.Error as e: if con: con.rollback() print(e) sys.exit(1) finally: if con: con.close() ``` 在此腳本中，我們從當前工作目錄中讀取圖像，并將其寫入 SQLite `ydb.db`數據庫的`images`表中。 ```py try: fin = open("sid.png", "rb") img = fin.read() return img ``` 我們從文件系統讀取二進制數據。我們有一個名為`sid.png`的 JPG 圖像。 ```py binary = sqlite.Binary(data) ``` 使用 SQLite `Binary`對象對數據進行編碼。 ```py cur.execute("INSERT INTO images(data) VALUES (?)", (binary,) ) ``` 該 SQL 語句用于將映像插入數據庫。 ## Python SQLite 讀取圖像在本節中，我們將執行相反的操作：我們從數據庫表中讀取一個圖像。 `read_image.py` ```py #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlite3 as sqlite import sys def writeImage(data): fout = None try: fout = open('sid2.png','wb') fout.write(data) except IOError as e: print(e) sys.exit(1) finally: if fout: fout.close() con = None try: con = sqlite.connect('ydb.db') cur = con.cursor() cur.execute("SELECT data FROM images LIMIT 1") data = cur.fetchone()[0] writeImage(data) except sqlite.Error as e: print(e) sys.exit(1) finally: if con: con.close() ``` 我們從`Images`表中讀取圖像數據，并將其寫入另一個文件`woman2.jpg`中。 ```py try: fout = open('sid2.png','wb') fout.write(data) ``` 我們以寫入模式打開一個二進制文件。來自數據庫的數據被寫入文件。 ```py cur.execute("SELECT data FROM images LIMIT 1") data = cur.fetchone()[0] ``` 這兩行從`images`表中選擇并獲取數據。我們從第一行獲取二進制數據。 ## Python SQLite 元數據元數據是有關數據庫中數據的信息。 SQLite 中的元數據包含有關表和列的信息，我們在其中存儲數據。受 SQL 語句影響的行數是元數據。結果集中返回的行數和列數也屬于元數據。可以使用`PRAGMA`命令獲取 SQLite 中的元數據。 SQLite 對象可能具有屬性，即元數據。最后，我們還可以通過查詢 SQLite 系統`sqlite_master`表來獲取特定的元數據。 `column_names.py` ```py #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlite3 as sqlite con = sqlite.connect('ydb.db') with con: cur = con.cursor() cur.execute('PRAGMA table_info(cars)') data = cur.fetchall() for d in data: print(f"{d[0]} {d[1]} {d[2]}") ``` 在此示例中，我們發出`PRAGMA table_info(tableName)`命令，以獲取有關`cars`表的一些元數據信息。 ```py cur.execute('PRAGMA table_info(cars)') ``` `PRAGMA table_info(tableName)`命令為`cars`表中的每一列返回一行。結果集中的列包括列順序號，列名稱，數據類型，該列是否可以為`NULL`以及該列的默認值。 ```py for d in data: print(f"{d[0]} {d[1]} {d[2]}") ``` 根據提供的信息，我們打印列順序號，列名稱和列數據類型。 ```py $ ./column_names.py 0 id INT 1 name TEXT 2 price INT ``` 這是示例的輸出。在下面的示例中，我們打印`cars`表中的所有行及其列名。 `column_names2.py` ```py #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlite3 as sqlite con = sqlite.connect('ydb.db') with con: cur = con.cursor() cur.execute('SELECT * FROM cars') col_names = [cn[0] for cn in cur.description] rows = cur.fetchall() print(f"{col_names[0]:3} {col_names[1]:10} {col_names[2]:7}") for row in rows: print(f"{row[0]:<3} {row[1]:<10} {row[2]:7}") ``` 我們將`cars`表的內容打印到控制臺。現在，我們也包括列的名稱。記錄與列名對齊。 ```py col_names = [cn[0] for cn in cur.description] ``` 我們從游標對象的`description`屬性獲得列名。 ```py print(f"{col_names[0]:3} {col_names[1]:10} {col_names[2]:7}") ``` 此行打印`cars`表的三個列名。 ```py for row in rows: print(f"{row[0]:<3} {row[1]:<10} {row[2]:7}") ``` 我們使用`for`循環打印行。數據與列名對齊。 ```py $ ./column_names2.py id name price 1 Audi 62300 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Hummer 41400 7 Volkswagen 21600 ``` 這是輸出。在與元數據有關的最后一個示例中，我們將列出`ydb.db`數據庫中的所有表。 `list_tables.py` ```py #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlite3 as sqlite con = sqlite.connect('ydb.db') with con: cur = con.cursor() cur.execute("SELECT name FROM sqlite_master WHERE type='table'") rows = cur.fetchall() for row in rows: print(row[0]) ``` 該代碼示例將當前數據庫中的所有可用表打印到終端。 ```py cur.execute("SELECT name FROM sqlite_master WHERE type='table'") ``` 表名存儲在系統`sqlite_master`表中。 ```py $ ./list_tables.py cars images ``` 這些是我們系統上的表。 ## Python SQLite 數據導出我們可以轉儲 SQL 格式的數據以創建數據庫表的簡單備份。 `export_table.py` ```py #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlite3 as sqlite cars = ( (1, 'Audi', 52643), (2, 'Mercedes', 57642), (3, 'Skoda', 9000), (4, 'Volvo', 29000), (5, 'Bentley', 350000), (6, 'Hummer', 41400), (7, 'Volkswagen', 21600) ) def writeData(data): f = open('cars.sql', 'w') with f: f.write(data) con = sqlite.connect(':memory:') with con: cur = con.cursor() cur.execute("DROP TABLE IF EXISTS cars") cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)") cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars) cur.execute("DELETE FROM cars WHERE price < 30000") data = '\n'.join(con.iterdump()) writeData(data) ``` 在上面的示例中，我們在內存中重新創建了`cars`表。我們從表中刪除一些行，并將表的當前狀態轉儲到`cars.sql`文件中。該文件可以用作表的當前備份。 ```py def writeData(data): f = open('cars.sql', 'w') with f: f.write(data) ``` 表中的數據正在寫入文件。 ```py con = sqlite.connect(':memory:') ``` 我們在內存中創建一個臨時表。 ```py cur.execute("DROP TABLE IF EXISTS cars") cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)") cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars) cur.execute("DELETE FROM cars WHERE price < 30000") ``` 這些行創建`cars`表，插入值并刪除行，其中`price`小于 30000 單位。 ```py data = '\n'.join(con.iterdump()) ``` `con.iterdump()`返回一個迭代器，以 SQL 文本格式轉儲數據庫。內置的`join()`函數采用迭代器，并將迭代器中的所有字符串連接在一起，并用新行分隔。此數據將寫入`writeData()`函數中的`cars.sql`文件中。 ```py $ cat cars.sql BEGIN TRANSACTION; CREATE TABLE cars(id INT, name TEXT, price INT); INSERT INTO "cars" VALUES(1,'Audi',52643); INSERT INTO "cars" VALUES(2,'Mercedes',57642); INSERT INTO "cars" VALUES(5,'Bentley',350000); INSERT INTO "cars" VALUES(6,'Hummer',41400); COMMIT; ``` 廢棄的內存車表中的內容。 ## Python SQLite 導入數據現在，我們將執行反向操作。我們將轉儲的表導入回內存。 `import_table.py` ```py #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlite3 as sqlite def readData(): f = open('cars.sql', 'r') with f: data = f.read() return data con = sqlite.connect(':memory:') with con: cur = con.cursor() sql = readData() cur.executescript(sql) cur.execute("SELECT * FROM cars") rows = cur.fetchall() for row in rows: print(row) ``` 在此腳本中，我們讀取`cars.sql`文件的內容并執行它。這將重新創建轉儲的表。 ```py def readData(): f = open('cars.sql', 'r') with f: data = f.read() return data ``` 在`readData()`方法內部，我們讀取`cars.sql`表的內容。 ```py cur.executescript(sql) ``` 我們調用`executescript()`方法來啟動 SQL 腳本。 ```py cur.execute("SELECT * FROM cars") rows = cur.fetchall() for row in rows: print(row) ``` 我們驗證數據。 ```py $ ./import_table.py (1, u'Audi', 52643) (2, u'Mercedes', 57642) (5, u'Bentley', 350000) (6, u'Hummer', 41400) ``` 輸出顯示我們已經成功地重新創建了保存的汽車表。 ## Python SQLite 事務事務是針對一個或多個數據庫中數據的數據庫操作的基本單位。事務中所有 SQL 語句的影響可以全部提交給數據庫，也可以全部回滾。默認情況下，Python `sqlite3`模塊在數據修改語言（DML）語句（即`INSERT`/`UPDATE`/`DELETE`/`REPLACE`）之前隱式發出`BEGIN`語句。 `sqlite3`用于在 DDL 語句之前隱式提交一個打開的事務。這已不再是這種情況。手動事務以`BEGIN TRANSACTION`語句開始，并以`COMMIT`或`ROLLBACK`語句結束。 SQLite 支持三種非標準事務級別：`DEFERRED`，`IMMEDIATE`和`EXCLUSIVE`。 Python SQLite 模塊還支持自動提交模式，該模式下對表的所有更改均立即生效。 `no_commit.py` ```py #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlite3 as sqlite import sys con = None try: con = sqlite.connect('ydb.db') cur = con.cursor() cur.execute("DROP TABLE IF EXISTS friends") cur.execute("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT)") cur.execute("INSERT INTO friends(name) VALUES ('Tom')") cur.execute("INSERT INTO friends(name) VALUES ('Rebecca')") cur.execute("INSERT INTO friends(name) VALUES ('Jim')") cur.execute("INSERT INTO friends(name) VALUES ('Robert')") #con.commit() except sqlite.Error as e: if con: con.rollback() print(e) sys.exit(1) finally: if con: con.close() ``` 我們創建一個`friends`表，并嘗試用數據填充它。但是，正如我們將看到的，數據未提交。 ```py #con.commit() ``` 注釋`commit()`方法。如果我們取消注釋該行，則數據將被寫入表中。 ```py sqlite> .tables cars friends images sqlite> SELECT Count(id) FROM friends; Count(id) ---------- 0 sqlite> ``` 表已創建，但數據未寫入表中。 ### Python SQLite 自動提交在自動提交模式下，將立即執行一條 SQL 語句。 `autocommit.py` ```py #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlite3 as sqlite import sys con = None try: con = sqlite.connect('ydb.db', isolation_level=None) cur = con.cursor() cur.execute("DROP TABLE IF EXISTS friends") cur.execute("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT)") cur.execute("INSERT INTO friends(name) VALUES ('Tom')") cur.execute("INSERT INTO friends(name) VALUES ('Rebecca')") cur.execute("INSERT INTO friends(name) VALUES ('Jim')") cur.execute("INSERT INTO friends(name) VALUES ('Robert')") except sqlite.Error as e: print(e) sys.exit(1) finally: if con: con.close() ``` 在此示例中，我們以自動提交模式連接到數據庫。 ```py con = sqlite.connect('ydb.db', isolation_level=None) ``` 當將`isolation_level`設置為`None`時，我們具有自動提交模式。 ```py $ ./autocommit.py sqlite> SELECT * FROM friends; Id Name ---------- ---------- 1 Tom 2 Rebecca 3 Jim 4 Robert ``` 數據已成功提交到`friends`表。這是 Python SQLite 教程。您可能還想查看 ZetCode 上的 [Python 教程](/lang/python/)， [PyMySQL 教程](/python/pymysql/)或 [Python PostgreSQL 教程](/python/psycopg2/)。