本文将使用Python的SQLite3模块,来完成 在SQLite表 里 插入与保存 文件,这里使用的格式是BLOB。
- 使用 SQLite BLOB数据类型保存二进制数据于SQLite 表格中,用Python完成
- 从Sqlite表格 中 读取BLOB二进制数据,用Python完成
前置知识
在执行下面的SQLite BLOB操作前,需要知道插入数据的表名以及列名。
所以我们需要 新建 一个表可以存储数据。
可以参考链接教程:creating an SQLite table from Python
教程里使用了名为 new_employee 的表
你也可以用如下的query语句新建一个表,或则在SQLiteStudio软件里新建。
CREATE TABLE new_employee ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, photo BLOB NOT NULL, resume BLOB NOT NULL);
这个表含有两个BLOB列。
- 一个 photo列用来保存员工的图片
- 一个 resume列用来保存员工的简历
什么是BLOB
BLOB(large binary object) 是一种SQLite的数据类型,用来存储大容量的对象,通常是较大的文件如图片,声音,适配,文档等。
我们需要把文件与图片转化为二进制数据(byte array在Python里),然后再保存在SQLite数据库里。
插入图片与文件
现在我们建立的是一张空表,需完成如下步骤:
- D:\mydatabase.db 自己数据库的位置要放对。要做到与代码相符合。
- D:\zs.jpg 和 D:\zs_resume.txt 和 D:\ls.jpg 和 D:\ls_resume.txt,要有这些文件,位置与名称可以自己定义。要做到与代码相符合。
- SQLite和Python建立连接
- 建立一个cursor对象使用 connection对象
- 写好INSERT SQL语句完成自己需要的功能,相关文档:INSERT Query
- 然后,写一个函数完成读取硬盘上的文件,并转化为二进制数据。
- 用cursor.execute()执行SQL语句
- 执行完成后,commit(提交)你的修改。
- 关闭cursor和connection连接
- 最重要的一定,若有则catch(捕捉)SQLite的exceptions(异常)
import sqlite3
def convertToBinaryData(filename):
# Convert digital data to binary format
with open(filename, 'rb') as file:
blobData = file.read()
return blobData
def insertBLOB(empId, name, photo, resumeFile):
try:
sqliteConnection = sqlite3.connect('D:\mydatabase.db')
cursor = sqliteConnection.cursor()
print("Connected to SQLite")
sqlite_insert_blob_query = """ INSERT INTO new_employee
(id, name, photo, resume) VALUES (?, ?, ?, ?)"""
empPhoto = convertToBinaryData(photo)
resume = convertToBinaryData(resumeFile)
# Convert data into tuple format
data_tuple = (empId, name, empPhoto, resume)
cursor.execute(sqlite_insert_blob_query, data_tuple)
sqliteConnection.commit()
print("Image and file inserted successfully as a BLOB into a table")
cursor.close()
except sqlite3.Error as error:
print("Failed to insert blob data into sqlite table", error)
finally:
if sqliteConnection:
sqliteConnection.close()
print("the sqlite connection is closed")
insertBLOB(1, "张三", "D:\zs.jpg", "D:\zs_resume.txt")
insertBLOB(2, "李四", "D:\ls.jpg", "D:\ls_resume.txt")
输出:
Connected to SQLite
Image and file inserted successfully as a BLOB into a table
the sqlite connection is closed
Connected to SQLite
Image and file inserted successfully as a BLOB into a table
the sqlite connection is closed
再次打开SQLiteStudio,打开对应表。发现表格里多了两行数据。
注意:
我们还可以用参数化的query,链接文章: parameterized query来插入动态的数据
从BLOB格式的数据,保存为图片和文件
假如你现在想要读取保存在SQLite 表格里的 文件或图片,将它们以文件的格式重写写入到磁盘中,方便使用。
下面将完成这个功能。步骤如下:
- D:\mydatabase.db 自己数据库的位置要放对。要做到与代码相符合。
- SQLite和Python建立连接
- 建立一个cursor对象使用 connection对象
- 写好SELECT SQL语句完成自己需要的功能
- 用cursor.execute()执行SQL语句
- 使用 cursor.fetchall() 去取回所有符合要求的行,并遍历取回的数据。
- 然后,写一个函数完成将BLOB数据转化并保存为合适的格式。
- 关闭cursor和connection连接
import sqlite3
def writeTofile(data, filename):
# Convert binary data to proper format and write it on Hard Disk
with open(filename, 'wb') as file:
file.write(data)
print("Stored blob data into: ", filename, "\n")
def readBlobData(empId):
try:
sqliteConnection = sqlite3.connect('D:\mydatabase.db')
cursor = sqliteConnection.cursor()
print("Connected to SQLite")
sql_fetch_blob_query = """SELECT * from new_employee where id = ?"""
cursor.execute(sql_fetch_blob_query, (empId,))
record = cursor.fetchall()
for row in record:
print("Id = ", row[0], "Name = ", row[1])
name = row[1]
photo = row[2]
resumeFile = row[3]
print("Storing employee image and resume on disk \n")
photoPath = "D:\\sqlite_create" + name + ".jpg"
resumePath = "D:\\sqlite_create" + name + "_resume.txt"
writeTofile(photo, photoPath)
writeTofile(resumeFile, resumePath)
cursor.close()
except sqlite3.Error as error:
print("Failed to read blob data from sqlite table", error)
finally:
if sqliteConnection:
sqliteConnection.close()
print("sqlite connection is closed")
readBlobData(1)
readBlobData(2)
输出:
Connected to SQLite
Id = 1 Name = 张三
Storing employee image and resume on disk
Stored blob data into: D:\sqlite_create张三.jpg
Stored blob data into: D:\sqlite_create张三_resume.txt
sqlite connection is closed
Connected to SQLite
Id = 2 Name = 李四
Storing employee image and resume on disk
Stored blob data into: D:\sqlite_create李四.jpg
Stored blob data into: D:\sqlite_create李四_resume.txt
sqlite connection is closed
打开D盘,发现多出了保存的文件,如图: