这是针对SQLite数据库的Python编程教程。它涵盖了使用Python语言进行SQLite编程的基础知识。ZetCode有一本针对PythonSQLite的完整电子书：PythonSQLite电子书。
示例的源代码可在作者的https://github.com/janbodnar/Python-SQLite-Examplesrepository中找到。
要使用本教程，我们必须在系统上安装Python语言、SQLite数据库、pysqlite语言绑定和sqlite3命令行工具。
要使用SQLite数据库，我们可以安装sqlite3或SQLite浏览器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版本。在我们的例子中，它是Python3.9.0。sqlite.version是pysqlite2.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命令行工具的交互式会话。lsUnix命令显示当前工作目录的内容。我们可以看到ydb.db文件。所有数据都将存储在这个文件中。
PythonSQLite版本示例
在第一个代码示例中，我们将获取SQLite数据库的版本。
#!/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数据库。我们执行一条返回SQLite数据库版本的SQL语句。
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关键字。
#!/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
PythonSQLite执行
我们创建一个cars表并向其中插入几行。我们使用execute。
#!/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表的数据。
PythonSQLiteexecutemany
我们将创建同一个表。这次使用方便的executemany方法。
#!/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语句创建汽车表。
cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars)
我们使用方便的executemany方法将八行插入表中。该方法的第一个参数是参数化的SQL语句。第二个参数是数据，以元组的元组形式。
PythonSQLite执行脚本
我们提供了另一种方法来使用executescript创建我们的cars表。我们手动提交更改并提供我们自己的错误处理。
#!/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)
如果出现错误，将回滚更改并将错误消息打印到终端。
PythonSQLite检查数据库是否存在
无法使用connect方法检查数据库文件是否存在。如果给定的文件存在，该方法只是连接到数据库。如果不存在，则创建数据库文件。可以使用标准的os.path.exist函数检查数据库文件是否存在。
#!/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表中获取并打印两行。
PythonSQLitelastrowid
有时，我们需要确定最后插入的行的id。在PythonSQLite中，我们使用游标对象的lastrowid属性。
#!/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中，INTEGERPRIMARYKEY列是自动递增的。还有一个AUTOINCREMENT关键字。在INTEGERPRIMARYKEYAUTOINCREMENT中使用时，会使用略微不同的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
我们看到程序的输出。
PythonSQLite使用fetchall检索数据
fetchall方法获取查询结果集的所有（或所有剩余）行并返回元组列表。
#!/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
PythonSQLitefetchone
fetchone返回查询结果集的下一行，返回单个元组，或者当没有更多数据可用时返回None。
#!/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
PythonSQLite字典游标
默认游标以元组的元组形式返回数据。当我们使用字典游标时，数据以Python字典的形式发送。这样我们就可以通过列名来引用数据。
#!/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']}")
通过列名访问数据。
PythonSQLite参数化语句
现在我们将关注参数化查询。当我们使用参数化查询时，我们使用占位符而不是直接将值写入语句。参数化查询提高了安全性和性能。
Pythonsqlite3模块支持两种类型的占位符：问号和命名占位符。
带问号的参数化语句
在第一个示例中，我们使用问号的语法。
#!/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属性返回更新的行数。在我们的例子中，更新了一行。
带有命名占位符的参数化语句
第二个示例使用带命名占位符的参数化语句。
#!/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})
命名占位符以冒号字符开头。
PythonSQLite插入图片
在本节中，我们将向SQLite数据库中插入一个图像。请注意，有些人反对将图像放入数据库。这里我们只展示如何去做。我们不讨论是否将图像保存在数据库中的技术问题。
sqlite> CREATE TABLE images(id INTEGER PRIMARY KEY, data BLOB);
对于这个例子，我们创建一个名为images的新表。对于图像，我们使用BLOB数据类型，它代表二进制大对象。
#!/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()
在此脚本中，我们从当前工作目录读取图像并将其写入SQLiteydb.db数据库的images表。
try:
fin = open("sid.jpg", "rb")
img = fin.read()
return img
我们从文件系统中读取二进制数据。我们有一个名为sid.jpg的JPG图片。
binary = sqlite3.Binary(data)
数据使用SQLiteBinary对象进行编码。
cur.execute("INSERT INTO images(data) VALUES (?)", (binary,) )
此SQL语句用于将图像插入数据库。
PythonSQLite读取图像
在本节中，我们将执行反向操作：我们从数据库表中读取图像。
#!/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表中选择并获取数据。我们从第一行获取二进制数据。
PythonSQLite元数据
元数据是有关数据库中数据的信息。SQLite中的元数据包含有关我们存储数据的表和列的信息。受SQL语句影响的行数是元数据。结果集中返回的行数和列数也属于元数据。
SQLite中的元数据可以使用PRAGMA命令获取。SQLite对象可能具有属性，这些属性是元数据。最后，我们还可以通过查询SQLite系统sqlite_master表来获取具体的元数据。
#!/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]}")
在此示例中，我们发出PRAGMAtable_info(tableName)命令，以获取有关我们的cars表的一些元数据信息。
cur.execute('PRAGMA table_info(cars)')
PRAGMAtable_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表中的所有行及其列名。
#!/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数据库中的所有表。
#!/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
这些是我们系统上的表。
PythonSQLite数据导出
我们可以转储SQL格式的数据，为我们的数据库表创建一个简单的备份。
#!/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;
dumpedin-memorycars表的内容。
PythonSQLite导入数据
现在我们要进行反向操作。我们会将转储的表重新导入内存。
#!/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)
输出表明我们已经成功地重新创建了保存的汽车表。
PythonSQLite事务
事务是对一个或多个数据库中的数据进行数据库操作的原子单元。一个事务中所有SQL语句的效果可以全部提交到数据库，也可以全部回滚。
Pythonsqlite3模块默认在数据修改语言(DML)语句（即INSERT/UPDATE）之前隐式发出一个BEGIN语句/DELETE/REPLACE).
sqlite3用于在DDL语句之前隐式提交打开的事务。情况已不再如此。
手动事务以BEGINTRANSACTION语句开始，以COMMIT或ROLLBACK语句结束。
SQLite支持三种非标准事务级别：DEFERRED、IMMEDIATE和EXCLUSIVE。PythonSQLite模块还支持自动提交模式，其中对表的所有更改都会立即生效。
#!/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>
表已创建，但数据未写入表。
PythonSQLite自动提交
在自动提交模式下，立即执行一条SQL语句。
#!/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表。
这是PythonSQLite教程。
列出所有Python教程。
