sqlite3 本地数据库

SQLite 是一个C语言库，它可以提供一种轻量级的基于磁盘的数据库。这种数据库不需要独立的服务器进程，也允许使用一种非标准的 SQL 查询语言来访问它。一些应用程序可以使用 SQLite 作为内部数据存储。可以用它来创建一个应用程序原型，然后再迁移到更大的数据库，比如 PostgreSQL 或 Oracle。

如果想了解更多，可访问：https://www.sqlite.org/index.html

本节内容假定对数据基本概念有所了解，包括 游标 和 事务.

创建连接

import sqlite3

conn = sqlite3.connect("sqlite_test.db")

调用 sqlite3.connect() 来创建与当前工作目录下 sqlite_test.db 数据库的连接，如果它不存在则会隐式地创建它。

conn = sqlite3.connect(":memory:")

也可以完全在内存中创建数据库。

为了执行 SQL 语句并且从 SQL 查询中取得结果，你需要使用游标 (cursor) 。 在下面的代码中，我们调用函数 con.cursor() 创建了一个游标 Curosr：

cur = conn.cursor()

创建表

SQLite has a flexing typing feature that makes specifying the data types optional.

通过上面的操作，已经得到了与数据库的连接 (connection) 与游标 (cursor) ，现在便可以在数据库中创建一张名为 album 的表了，它包括 title，release year，singer 这三个字段。 出于简洁的考虑，我们在创建表的 SQL 语句声明中只列出表头名。这一点得益于 SQLite 的 flexing typing 特性，它使得 SQLite 中创建表时数据类型可选。

cur.execute("CREATE TABLE album(title, year, singer)")

你可以通过查询 SQLite 内置的 sqlite_matser 表以验证新表是否已经创建，本例中，此时该表应该已经包括了一条 album 的表定义。

result = cur.execute("SELECT name FROM sqlite_master")
result.fetchone() # ('album',)

如果检查内存表，则查询 sqlite_temp_master 而不是 sqlite_master。

query = "SELECT name FROM sqlite_temp_master WHERE " \
        "type='table' AND name='album'"
result = cur.execute(query)

插入数据

现在，通过 INSERT 语句加入一些数据。

cur.execute("""INSERT INTO album VALUES
    ('Music', 2000, 'Madonna'),
    ('Justified', 2002, 'Justin Timberlake')
""")

INSERT 语句将隐式地创建一个事务，事务需要在将更改保存到数据库前提交。可以通过在连接对象上调用 conn.commit() 提交事务：

conn.commit()

你也可以调用 cursor.executemany(...) 来插入更多数据。

data = [
    ("21", 2011, "Adele"),
    ("Man of the Woods", 2018, "Justin Timberlake"),
    ("folklore", 2020, "Taylor Swift")
]
cur.executemany("INSERT INTO album VALUES(?, ?, ?)", data)
conn.commit() # Remember to commit the transaction

请注意，占位符 ? 是用来在语句中绑定数据 data 的。 记得使用占位符取代字符串格式化来防止SQL注入攻击。

查询数据

你可以使用 SELECT 查询来验证之前插入的数据。

res = cur.execute("SELECT year FROM album")
res.fetchall() # [(2000,), (2002,), (2011,), (2018,), (2020,)]

删除数据

你也可使用 DELETE 语句来删除不需要的数据。

cur.execute("DELETE FROM album WHERE year > 2012")
print("Deleted", cur.rowcount, "records.") # Deleted 2 records.
conn.commit()

关闭连接

最后，关闭数据库连接。

conn.close()

代码挑战

测试修改编辑器中代码来操作 sqlite 并输出 [('21', 2011, 'Adele'), ('reputation', 2017, 'Taylor Swift')]。