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/)。