Python SQLite

最后修改于 2024 年 1 月 29 日

这是一个关于 SQLite 数据库的 Python 编程教程。它涵盖了使用 Python 语言进行 SQLite 编程的基础知识。 ZetCode 有一本完整的 Python SQLite *电子书*：Python SQLite 电子书。

示例的源代码可以在作者的 https://github.com/janbodnar/Python-SQLite-Examples 仓库中找到。

要学习本教程，我们必须在系统上安装 Python 语言、SQLite 数据库、pysqlite 语言绑定和 sqlite3 命令行工具。

为了使用 SQLite 数据库，我们可以安装 sqlite3 或 SQLite browser GUI。

$ python
Python 3.9.0 (default, Oct  5 2020, 20:56:51)
[GCC 10.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.33.0'

在 shell 中，我们启动 Python 交互式解释器。我们可以看到 Python 版本。在我们的例子中，它是 Python 3.9.0。sqlite.version 是 pysqlite 2.6.0 的版本，它是 Python 语言到 SQLite 数据库的绑定。 sqlite3.sqlite_version 给出我们 SQLite 数据库库的版本。在我们的例子中，版本是 3.33.0。

SQLite

SQLite 是一种嵌入式关系数据库引擎。该文档称其为自包含、无服务器、零配置和事务性 SQL 数据库引擎。它非常受欢迎，如今全球有数亿份副本正在使用。几种编程语言都内置了对 SQLite 的支持，包括 Python 和 PHP。

创建 SQLite 数据库

现在我们将使用 sqlite3 命令行工具创建一个新数据库。

$ sqlite3 ydb.db
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
sqlite>

我们为 sqlite3 工具 提供一个参数； ydb.db 是一个数据库名称。它是我们磁盘上的一个文件。如果它存在，它将被打开。如果不存在，则会创建它。

sqlite> .tables
sqlite> .exit
$ ls
ydb.db

.tables 命令给出 ydb.db 数据库中的表列表。目前没有表。 .exit 命令终止 sqlite3 命令行工具的交互式会话。 ls Unix 命令显示当前工作目录的内容。我们可以看到 ydb.db 文件。所有数据都将存储在这个单个文件中。

Python SQLite 版本示例

在第一个代码示例中，我们将获取 SQLite 数据库的版本。

version.py

#!/usr/bin/python

import sqlite3
import sys

con = None

try:
    con = sqlite3.connect('ydb.db')

    cur = con.cursor()
    cur.execute('SELECT SQLITE_VERSION()')

    data = cur.fetchone()[0]

    print(f"SQLite version: {data}")

except sqlite3.Error as e:

    print(f"Error {e.args[0]}")
    sys.exit(1)

finally:

    if con:
        con.close()

在上面的 Python 脚本中，我们连接到先前创建的 ydb.db 数据库。我们执行一个 SQL 语句，该语句返回 SQLite 数据库的版本。

import sqlite3

我们导入 sqlite3 模块。

con = None

我们将 con 变量初始化为 None。如果我们无法创建到数据库的连接（例如磁盘已满），我们将不会定义连接变量。这将导致 finally 子句中出现错误。

con = sqlite3.connect('ydb.db')

我们连接到 ydb.db 数据库。 connect 方法返回一个连接对象。

cur = con.cursor()
cur.execute('SELECT SQLITE_VERSION()')

从连接中，我们获取游标对象。游标用于遍历结果集中的记录。我们调用游标的 execute 方法并执行 SQL 语句。

data = cur.fetchone()[0]

我们获取数据。由于我们只检索一条记录，我们调用 fetchone 方法。

print(f"SQLite version: {data}")

我们将检索到的数据打印到控制台。

except sqlite3.Error as e:

    print(f"Error {e.args[0]}")
    sys.exit(1)

如果发生异常，我们打印一条错误消息并以错误代码 1 退出脚本。

finally:

    if con:
        con.close()

在最后一步，我们释放资源。

在第二个例子中，我们再次获取 SQLite 数据库的版本。这次我们将使用 with 关键字。

version2.py

#!/usr/bin/python

import sqlite3

con = sqlite3.connect('ydb.db')

with con:

    cur = con.cursor()
    cur.execute('SELECT SQLITE_VERSION()')

    data = cur.fetchone()[0]

    print(f"SQLite version: {data}")

该脚本返回 SQLite 数据库的当前版本。使用 with 关键字，代码更简洁。

with con:

使用 with 关键字，Python 解释器会自动释放资源。它还提供错误处理。

$ ./version2.py
SQLite version: 3.33.0

Python SQLite 执行

我们创建一个 cars 表并向其中插入多行。我们使用 execute。

create_table.py

#!/usr/bin/python

import sqlite3

con = sqlite3.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.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 表并将八行插入到该表中。

cur.execute("DROP TABLE IF EXISTS cars;")

如果表已存在，我们将删除该表。

cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")

此 SQL 语句创建一个新的 cars 表。该表有三列。

cur.execute("INSERT INTO cars VALUES(1,'Audi',52642)")
cur.execute("INSERT INTO cars VALUES(2,'Mercedes',57127)")

这两行将两辆汽车插入到表中。使用 with 关键字，更改会自动提交。否则，我们将不得不手动提交它们。

sqlite> .mode column
sqlite> .headers on

我们使用 sqlite3 工具来验证写入的数据。首先，我们修改数据在控制台中的显示方式。我们使用列模式并打开表头。

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

#!/usr/bin/python

import sqlite3

cars = (
    (1, 'Audi', 52642),
    (2, 'Mercedes', 57127),
    (3, 'Skoda', 9000),
    (4, 'Volvo', 29000),
    (5, 'Bentley', 350000),
    (6, 'Hummer', 41400),
    (7, 'Volkswagen', 21600)
)

con = sqlite3.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 表并重新创建它。

cur.execute("DROP TABLE IF EXISTS cars")
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")

第一个 SQL 语句删除现有的 cars 表。第二个 SQL 语句创建 cars 表。

cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars)

我们使用方便的 executemany 方法将八行插入到表中。此方法的第一个参数是参数化的 SQL 语句。第二个参数是数据，形式为元组的元组。

Python SQLite executescript

我们提供另一种使用 executescript 创建 cars 表的方法。我们手动提交更改并提供我们自己的错误处理。

create_table3.py

#!/usr/bin/python

import sqlite3
import sys

con = None

try:
    con = sqlite3.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 sqlite3.Error as e:

    if con:
        con.rollback()

    print(f"Error {e.args[0]}")
    sys.exit(1)

finally:

    if con:
        con.close()

在上面的脚本中，我们使用 executescript 方法（重新）创建 cars 表。

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 代码。

con.commit()

如果没有 with 关键字，则必须使用 commit 方法提交更改。

except sqlite.Error as e:

    if con:
        con.rollback()

    print(f"Error {e.args[0]}")
    sys.exit(1)

如果发生错误，更改将被回滚，并且一条错误消息将打印到终端。

Python SQLite 检查数据库是否存在

无法使用 connect 方法检查数据库文件是否存在。如果给定文件存在，该方法只会连接到数据库。如果它不存在，则会创建数据库文件。可以使用标准的 os.path.exist 函数检查数据库文件是否存在。

db_exists.py

#!/usr/bin/python

import os
import sqlite3


if not os.path.exists('ydb.db'):

    con = sqlite3.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.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)")

else:

    con = sqlite3.connect('ydb.db')

    with con:

        cur = con.cursor()
        cur.execute("SELECT * FROM cars")
        rows = cur.fetchmany(2)

        print(rows)

在该脚本中，我们检查 ydb.db 文件是否存在。如果它不存在，则会创建它并生成一个新表。如果它已经存在，我们将从表中检索两行。

if not os.path.exists('test.db'):

  con = sqlite3.connect('test.db')
  ...

我们使用 os.path.exists 方法检查 ydb.db 文件是否存在。如果数据库文件不存在，则会创建它。

else:

  con = sqlite3.connect('ydb.db')
  ...

如果数据库文件已存在，我们连接到它，然后获取一些数据。

$ ls
db_exists.py
$ ./db_exists.py
$ ./db_exists.py
[(1, 'Audi', 52642), (2, 'Mercedes', 57127)]

在一个不包含 ydb.db 文件的目录中，我们两次启动该脚本。首次执行时，会创建数据库并生成一个 cars 表。第二次执行时，我们从 cars 表中获取并打印两行。

Python SQLite lastrowid

有时，我们需要确定最后插入行的 id。在 Python SQLite 中，我们使用游标对象的 lastrowid 属性。

lastrowid.py

#!/usr/bin/python

import sqlite3

con = sqlite3.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 会自动递增。

cur.execute("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT);")

在 SQLite 中，INTEGER PRIMARY KEY 列会自动递增。还有一个 AUTOINCREMENT 关键字。当在 INTEGER PRIMARY KEY AUTOINCREMENT 中使用时，会使用稍微不同的 Id 创建算法。

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 表。

last_row_id = cur.lastrowid

使用 lastrowid，我们可以获得最后插入的行 id。

$ ./lastrowid.py
The last Id of the inserted row is 4

我们看到了程序的输出。

Python SQLite 使用 fetchall 检索数据

fetchall 方法获取查询结果集的所有（或所有剩余）行，并返回一个元组列表。

fetch_all.py

#!/usr/bin/python

import sqlite3

con = sqlite3.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 表中检索所有数据。

cur.execute("SELECT * FROM cars")

此 SQL 语句从 cars 表中选择所有数据。

rows = cur.fetchall()

fetchall 方法获取所有记录。它返回一个结果集。从技术上讲，它是一个元组的元组。每个内部元组代表表中的一行。

for row in rows:
    print(f"{row[0]} {row[1]} {row[2]}")

我们将数据逐行打印到控制台。

$ ./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

#!/usr/bin/python

import sqlite3

con = sqlite3.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 表的行。

while True:

我们从 while 循环访问数据。当我们读取最后一行时，循环终止。

row = cur.fetchone()

if row == None:
    break

fetchone 方法返回表中的下一行。如果没有更多数据，它返回 None。在这种情况下，我们中断循环。

print(f"{row[0]} {row[1]} {row[2]}")

数据以元组的形式返回。在这里我们从元组中选择记录。第一个是 ID，第二个是汽车名称，第三个是汽车价格。

$ ./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

#!/usr/bin/python

import sqlite3

con = sqlite3.connect('ydb.db')

with con:

    con.row_factory = sqlite3.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 表的内容。

con.row_factory = sqlite.Row

我们选择一个字典游标。现在我们可以通过列名访问记录。

for row in rows:
    print(f"{row['id']} {row['name']} {row['price']}")

数据通过列名访问。

Python SQLite 参数化语句

现在我们将关注参数化查询。当我们使用参数化查询时，我们使用占位符而不是直接将值写入语句中。参数化查询提高了安全性和性能。

Python sqlite3 模块支持两种类型的占位符：问号和命名占位符。

带问号的参数化语句

在第一个例子中，我们使用问号的语法。

parameterized_query.py

#!/usr/bin/python

import sqlite3

uId = 1
uPrice = 62300

con = sqlite3.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}")

我们更新一辆汽车的价格。在这个代码示例中，我们使用问号占位符。

cur.execute("UPDATE cars SET price=? WHERE id=?", (uPrice, uId))

问号 ? 是值的占位符。这些值被添加到占位符中。

print(f"Number of rows updated: {cur.rowcount}")

rowcount 属性返回更新的行数。在我们的例子中，更新了一行。

带有命名占位符的参数化语句

第二个示例使用带有命名占位符的参数化语句。

named_placeholders.py

#!/usr/bin/python

import sqlite3

uId = 4

con = sqlite3.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]}")

我们使用命名占位符选择汽车的名称和价格。

cur.execute("SELECT name, price FROM cars WHERE Id=:Id", {"Id": uId})

命名占位符以冒号字符开头。

Python SQLite 插入图像

在本节中，我们将向 SQLite 数据库中插入图像。请注意，有些人反对将图像放入数据库中。在这里，我们只展示如何做到这一点。我们不深入探讨是否将图像保存在数据库中这些技术问题。

sqlite> CREATE TABLE images(id INTEGER PRIMARY KEY, data BLOB);

对于此示例，我们创建一个名为 images 的新表。对于图像，我们使用 BLOB 数据类型，它代表 Binary Large Objects（二进制大对象）。

insert_image.py

#!/usr/bin/python

import sqlite3
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 = sqlite3.connect('ydb.db')

    cur = con.cursor()

    data = readImage()
    binary = sqlite3.Binary(data)
    cur.execute("INSERT INTO images(data) VALUES (?)", (binary,) )

    con.commit()

except sqlite3.Error as e:

    if con:
        con.rollback()

    print(e)
    sys.exit(1)

finally:

    if con:
        con.close()

在此脚本中，我们从当前工作目录读取图像并将其写入 SQLite ydb.db 数据库的 images 表中。

try:
    fin = open("sid.jpg", "rb")
    img = fin.read()
    return img

我们从文件系统读取二进制数据。我们有一个名为 sid.jpg 的 JPG 图像。

binary = sqlite3.Binary(data)

数据使用 SQLite Binary 对象进行编码。

cur.execute("INSERT INTO images(data) VALUES (?)", (binary,) )

这个 SQL 语句用于将图像插入数据库。

Python SQLite 读取图像

在本节中，我们将执行反向操作：我们从数据库表中读取图像。

read_image.py

#!/usr/bin/python

import sqlite3
import sys


def writeImage(data):

    fout = None

    try:
        fout = open('sid2.jpg','wb')
        fout.write(data)

    except IOError as e:

        print(e)
        sys.exit(1)

    finally:

        if fout:
            fout.close()

con = None

try:
    con = sqlite3.connect('ydb.db')

    cur = con.cursor()
    cur.execute("SELECT data FROM images LIMIT 1")
    data = cur.fetchone()[0]

    writeImage(data)


except sqlite3.Error as e:

    print(e)
    sys.exit(1)

finally:

    if con:
        con.close()

我们从 images 表中读取图像数据并将其写入另一个文件，我们称之为 sid2.jpg。

try:
    fout = open('sid2.jpg','wb')
    fout.write(data)

我们以写入模式打开一个二进制文件。来自数据库的数据将被写入该文件。

cur.execute("SELECT data FROM images LIMIT 1")
data = cur.fetchone()[0]

这两行从 images 表中选择并获取数据。我们从第一行获取二进制数据。

Python SQLite 元数据

元数据是关于数据库中数据的信息。 SQLite 中的元数据包含关于表和列的信息，我们在其中存储数据。 SQL 语句影响的行数是元数据。结果集中返回的行数和列数也属于元数据。

SQLite 中的元数据可以使用 PRAGMA 命令获取。SQLite 对象可能具有属性，这些属性就是元数据。最后，我们还可以通过查询 SQLite 系统表 sqlite_master 来获取特定的元数据。

column_names.py

#!/usr/bin/python

import sqlite3

con = sqlite3.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 表的一些元数据信息。

cur.execute('PRAGMA table_info(cars)')

PRAGMA table_info(tableName) 命令为 cars 表中的每一列返回一行。结果集中的列包括列顺序号、列名、数据类型、列是否可以为 NULL 以及列的默认值。

for d in data:
    print(f"{d[0]} {d[1]} {d[2]}")

从提供的信息中，我们打印列顺序号、列名和列数据类型。

$ ./column_names.py
0 id INT
1 name TEXT
2 price INT

在下面的示例中，我们打印 cars 表中的所有行及其列名。

column_names2.py

#!/usr/bin/python

import sqlite3

con = sqlite3.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 表的内容打印到控制台。现在，我们还包括列的名称。记录与列名对齐。

col_names = [cn[0] for cn in cur.description]

我们从游标对象的 description 属性获取列名。

print(f"{col_names[0]:3} {col_names[1]:10} {col_names[2]:7}")

这一行打印了 cars 表的三个列名。

for row in rows:
    print(f"{row[0]:<3} {row[1]:<10} {row[2]:7}")

我们使用 for 循环打印行。数据与列名对齐。

$  ./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

#!/usr/bin/python

import sqlite3

con = sqlite3.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])

该代码示例将当前数据库中所有可用的表打印到终端。

cur.execute("SELECT name FROM sqlite_master WHERE type='table'")

表名存储在系统表 sqlite_master 中。

$ ./list_tables.py
cars
images

这些是我们系统上的表。

Python SQLite 数据导出

我们可以以 SQL 格式转储数据，以创建数据库表的简单备份。

export_table.py

#!/usr/bin/python

import sqlite3

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 = sqlite3.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 文件中。此文件可以用作表的当前备份。

def writeData(data):

    f = open('cars.sql', 'w')

    with f:
        f.write(data)

表中的数据正在写入文件中。

con = sqlite3.connect(':memory:')

我们在内存中创建一个临时表。

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 个单位的行。

data = '\n'.join(con.iterdump())

con.iterdump 返回一个迭代器，以 SQL 文本格式转储数据库。内置的 join 函数接受迭代器并将迭代器中的所有字符串连接起来，并用换行符分隔。此数据写入 writeData 函数中的 cars.sql 文件。

$ 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;

转储在内存中的 cars 表的内容。

Python SQLite 导入数据

现在我们将执行反向操作。我们将把转储的表导回内存。

import_table.py

#!/usr/bin/python

import sqlite3


def readData():

    f = open('cars.sql', 'r')

    with f:

        data = f.read()

        return data


con = sqlite3.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 文件的内容并执行它。这将重新创建转储的表。

def readData():

    f = open('cars.sql', 'r')

    with f:

        data = f.read()

        return data

在 readData 方法中，我们读取 cars.sql 表的内容。

cur.executescript(sql)

我们调用 executescript 方法来启动 SQL 脚本。

cur.execute("SELECT * FROM cars")

rows = cur.fetchall()

for row in rows:
    print(row)

我们验证数据。

$ ./import_table.py
(1, 'Audi', 52643)
(2, 'Mercedes', 57642)
(5, 'Bentley', 350000)
(6, 'Hummer', 41400)

输出显示我们已成功重新创建保存的 cars 表。

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

#!/usr/bin/python

import sqlite3
import sys

con = None

try:
    con = sqlite3.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 sqlite3.Error as e:

    if con:
        con.rollback()

    print(e)
    sys.exit(1)

finally:

    if con:
        con.close()

我们创建一个 friends 表并尝试用数据填充它。然而，正如我们将看到的，数据没有被提交。

#con.commit()

commit 方法被注释掉了。如果我们取消注释该行，数据将被写入表中。

sqlite> .tables
cars     friends  images
sqlite> SELECT COUNT(id) FROM friends;
COUNT(id)
----------
0
sqlite>

表已创建，但数据未写入表中。

Python SQLite 自动提交

在自动提交模式下，SQL 语句会立即执行。

autocommit.py

#!/usr/bin/python

import sqlite3
import sys

con = None

try:
    con = sqlite3.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 sqlite3.Error as e:

    print(e)
    sys.exit(1)

finally:

    if con:
        con.close()

在本例中，我们以自动提交模式连接到数据库。

con = sqlite3.connect('ydb.db', isolation_level = None)

当我们把 isolation_level 设置为 None 时，我们就有了自动提交模式。

$ ./autocommit.py

sqlite> SELECT * FROM friends;
id          name
----------  ----------
1           Tom
2           Rebecca
3           Jim
4           Robert

数据已成功提交到 friends 表中。

来源

Python sqlite3 — 用于 SQLite 的 DB-API 2.0 接口

这是 Python SQLite 教程。

作者

我叫 Jan Bodnar，是一位充满激情的程序员，拥有丰富的编程经验。我自 2007 年以来一直撰写编程文章。到目前为止，我已经撰写了 1,400 多篇文章和 8 本电子书。我拥有超过十年的编程教学经验。

列出所有 Python 教程。