Python SQLite BLOB ：在数据库里 存入和取回 文件与图片

    本文将使用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盘，发现多出了保存的文件，如图：

翻译文章

原文链接