本文介绍如何使用Python的sqlite3模块进行数据库操作，包括连接、创建表、增删改查等基本操作，以及如何安全地使用占位符来避免SQL注入风险。
sqlite3 Python模块
教程来自：SQLite – Python | 菜鸟教程 (runoob.com)
Python SQLite 教程|极客教程 (geek-docs.com)
SQLite 是一个C语言库，它可以提供一种轻量级的基于磁盘文件的数据库，这种数据库不需要独立的服务器进程，也允许需要使用一种非标准的 SQL 查询语言来访问它。一些应用程序可以使用 SQLite 作为内部数据存储。可以用它来创建一个应用程序原型，然后再迁移到更大的数据库，比如 PostgreSQL 或 Oracle。
您不需要单独安装该模块，因为 Python 2.5.x 以上版本默认自带了该模块。
以下是重要的 sqlite3 模块API，可以满足您在 Python 程序中使用 SQLite 数据库的需求。如果您需要了解更多细节，请查看 Python sqlite3 模块的官方文档。
| 序号 | API & 描述 |
|---|---|
| 1 | sqlite3.connect(database [,timeout ,other optional arguments]) 该 API 打开一个到 SQLite 数据库文件 database 的链接。 |
| 2 | connection.backup(target, *, pages=-1, progress=None, name=“main”, sleep=0.250) 当前连接的数据库备份到 target 。 |
| 3 | connection.total_changes() 该例程返回自数据库连接打开以来被修改、插入或删除的数据库总行数。 |
| 4 | connection.commit() 该方法提交当前的事务。 |
| 5 | connection.rollback() 该方法回滚自上一次调用 commit() 以来对数据库所做的更改。 |
| 6 | connection.close() 该方法关闭数据库连接。 |
| 7 | connection.cursor([cursorClass]) 该例程创建一个 cursor，将在 Python 数据库编程中用到。 |
| 8 | cursor.execute(sql [, optional parameters]) 该例程执行一个 SQL 语句。 |
| 9 | cursor.executemany(sql, seq_of_parameters) 该例程对 seq_of_parameters 中的所有参数或映射执行一个 SQL 命令。 |
| 10 | cursor.executescript(sql_script) 该例程一旦接收到脚本，会执行多个 SQL 语句。 |
| 11 | cursor.fetchone() 该方法获取查询结果集中的下一行，返回一个单一的序列，当没有更多可用的数据时，则返回 None。 |
| 12 | cursor.fetchmany([size=cursor.arraysize]) 该方法获取查询结果集中的下一行组，返回一个列表。 |
| 13 | cursor.fetchall() 该例程获取查询结果集中所有（剩余）的行，返回一个列表。 |
连接数据库
下面的 Python 代码显示了如何连接到一个现有的数据库。如果数据库不存在，那么它就会被创建，最后将返回一个数据库对象。
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully"
在这里，您也可以把数据库名称复制为特定的名称 :memory:，这样就会在 RAM 中创建一个数据库。现在，让我们来运行上面的程序，在当前目录中创建我们的数据库 test.db。您可以根据需要改变路径。保存上面代码到 sqlite.py 文件中，并按如下所示执行。如果数据库成功创建，那么会显示下面所示的消息：
$chmod +x sqlite.py
$./sqlite.py
Open database successfully
数据库备份
-
将现有数据库复制到另一个数据库中：
import sqlite3 def progress(status, remaining, total): print(f'Copied {total-remaining} of {total} pages...') con = sqlite3.connect('existing_db.db') bck = sqlite3.connect('backup.db') with bck: con.backup(bck, pages=1, progress=progress) bck.close() con.close() -
将现有数据库复制到临时副本中：
import sqlite3 source = sqlite3.connect('existing_db.db') dest = sqlite3.connect(':memory:') source.backup(dest)
创建表
下面的 Python 代码段将用于在先前创建的数据库中创建一个表：
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully"
c = conn.cursor()
c.execute('''CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
print "Table created successfully"
conn.commit()
conn.close()
上述程序执行时，它会在 test.db 中创建 COMPANY 表，并显示下面所示的消息：
Opened database successfully
Table created successfully
INSERT 操作
下面的 Python 程序显示了如何在上面创建的 COMPANY 表中创建记录：
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print "Opened database successfully"
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (1, 'Paul', 32, 'California', 20000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (2, 'Allen', 25, 'Texas', 15000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )")
conn.commit()
print "Records created successfully"
conn.close()
上述程序执行时，它会在 COMPANY 表中创建给定记录，并会显示以下两行：
Opened database successfully
Records created successfully
SELECT 操作
下面的 Python 程序显示了如何从前面创建的 COMPANY 表中获取并显示记录：
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print "Opened database successfully"
cursor = c.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print "ID = ", row[0]
print "NAME = ", row[1]
print "ADDRESS = ", row[2]
print "SALARY = ", row[3], "\n"
print "Operation done successfully"
conn.close()
上述程序执行时，它会产生以下结果：
Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000.0
ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000.0
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfully
UPDATE 操作
下面的 Python 代码显示了如何使用 UPDATE 语句来更新任何记录，然后从 COMPANY 表中获取并显示更新的记录：
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print "Opened database successfully"
c.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")
conn.commit()
print "Total number of rows updated :", conn.total_changes
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print "ID = ", row[0]
print "NAME = ", row[1]
print "ADDRESS = ", row[2]
print "SALARY = ", row[3], "\n"
print "Operation done successfully"
conn.close()
上述程序执行时，它会产生以下结果：
Opened database successfully
Total number of rows updated : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000.0
ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000.0
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfully
DELETE 操作
下面的 Python 代码显示了如何使用 DELETE 语句删除任何记录，然后从 COMPANY 表中获取并显示剩余的记录：
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print "Opened database successfully"
c.execute("DELETE from COMPANY where ID=2;")
conn.commit()
print "Total number of rows deleted :", conn.total_changes
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print "ID = ", row[0]
print "NAME = ", row[1]
print "ADDRESS = ", row[2]
print "SALARY = ", row[3], "\n"
print "Operation done successfully"
conn.close()
上述程序执行时，它会产生以下结果：
Opened database successfully
Total number of rows deleted : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000.0
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfully
占位符使用
通常你的 SQL 操作需要使用一些 Python 变量的值。你不应该使用 Python 的字符串操作来创建你的查询语句，因为那样做不安全；
- 使用
?占位符来代替值，然后把对应的值组成的元组，(列表也行)做为execute()方法的第二个参数。
# 千万别这么做——不安全
symbol = 'RHAT'
cur.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)
# 改为这样做
t = ('RHAT',)
cur.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(cur.fetchone())
# 一次插入多条记录的较大示例
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
('2006-04-06', 'SELL', 'IBM', 500, 53.00),
]
cur.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)
- 要在执行 SELECT 语句后获取数据，你可以把游标作为 iterator，然后调用它的
fetchone()方法来获取一条匹配的行，也可以调用fetchall()来得到包含多个匹配行的列表。
下面是一个使用迭代器形式的例子：
>>> for row in cur.execute('SELECT * FROM stocks ORDER BY price'):
print(row)
'''
('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
('2006-04-06', 'SELL', 'IBM', 500, 53.0)
('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)
'''
SQLite 与 Python 类型
概述
SQLite 原生支持如下的类型： NULL，INTEGER，REAL，TEXT，BLOB。
因此可以将以下Python类型发送到SQLite而不会出现任何问题：
| Python 类型 | SQLite 类型 |
|---|---|
None | NULL |
int | INTEGER |
float | REAL |
str | TEXT |
bytes | BLOB |
这是SQLite类型默认转换为Python类型的方式：
| SQLite 类型 | Python 类型 |
|---|---|
NULL | None |
INTEGER | int |
REAL | float |
TEXT | 取决于 text_factory , 默认为 str |
BLOB | bytes |
sqlite3 模块的类型系统可通过两种方式来扩展：你可以通过对象适配将额外的 Python 类型保存在 SQLite 数据库中，你也可以让 sqlite3 模块通过转换器将 SQLite 类型转换为不同的 Python 类型。
额外的 Python 类型
SQLite 只包含对有限类型集的原生支持。 要让 SQLite 能使用其他 Python 类型，你必须将它们 适配 至 sqlite3 模块所支持的 SQLite 类型中的一种：NoneType, int, float, str, bytes。
有两种方式能让 sqlite3 模块将某个定制的 Python 类型适配为受支持的类型。
略。详见 Python 的官档文件
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
