Python SQLite教程
这是SQLite数据库的Python编程教程。它涵盖了使用Python语言编写SQLite的基础知识。ZetCode拥有完整的 电子书为Python SQLite的：Python的SQLite的电子书
要使用本教程，我们必须在系统上安装Python语言，SQLite数据库， pysqlite语言绑定和sqlite3命令行工具。
为了使用SQLite数据库，我们可以安装sqlite3 或SQLite浏览器GUI。
$ 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'
在shell中，我们启动了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命令行工具创建一个新数据库。
$ sqlite3 ydb.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite>
我们提供参数sqlite3 tool; ydb.db是数据库名称。它是我们磁盘上的一个文件。如果存在，则打开。如果没有，则创建它。
sqlite> .tables
sqlite> .exit
$ ls
ydb.db
该.tables命令提供ydb.db 数据库中的表列表。目前没有表格。该.exit命令终止sqlite3命令行工具的交互式会话。在lsUnix命令显示当前工作目录的内容。我们可以看到该ydb.db文件。所有数据都将存储在此单个文件中。
Python SQLite版本示例
在第一个代码示例中，我们将获得SQLite数据库的版本。
#!/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数据库。我们执行一个返回SQLite数据库版本的SQL语句。
import sqlite3 as sqlite
我们导入sqlite3并给它一个别名。
con = None
我们将con变量初始化为None。如果我们无法创建与数据库的连接（例如磁盘已满），我们就不会定义连接变量。这将导致finally子句中的错误。
con = sqlite.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 sqlite.Error as e:
print(f"Error {e.args[0]}")
sys.exit(1)
如果发生异常，我们会打印一条错误消息并退出脚本，错误代码为1。
finally:
if con:
con.close()
在最后一步中，我们释放资源。
在第二个示例中，我们再次获取SQLite数据库的版本。这次我们将使用with关键字。
#!/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关键字。代码更紧凑。
with con:
使用with关键字，Python解释器会自动释放资源。它还提供错误处理。
$ ./version.py
SQLite version: 3.21.0
这是输出。
Python SQLite执行
我们创建一个cars表并向其插入几行。我们用execute()。
#!/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行。
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()方法。
#!/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如果表存在， 程序将删除该表并重新创建它。
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()方法在表中插入8行。此方法的第一个参数是参数化SQL语句。第二个参数是元组元组形式的数据。
Python SQLite执行脚本
我们提供了另一种创建cars表的方法executescript()。我们手动提交更改并提供自己的错误处理。
#!/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()
在上面的脚本中，我们（重新）cars使用该executescript()方法创建表 。
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 lastrowid
有时，我们需要确定最后插入行的id。在Python SQLite中，我们使用lastrowid游标对象的属性。
#!/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自动递增。
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()方法获取查询结果集的所有（或所有剩余）行，并返回元组列表。
#!/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表中检索所有数据。
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时，没有更多的数据是可用的。