python将sqlite中blob字段内容存为文件

最新推荐文章于 2025-09-19 07:11:48 发布

原创最新推荐文章于 2025-09-19 07:11:48 发布 · 831 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sqlite #数据库 #python

文章展示了一个Python脚本，用于连接到SQLite数据库，读取指定ID员工的BLOB字段（包含照片和简历），并将这些二进制数据转换为文件存储在硬盘上。函数writeToFile负责将二进制数据写入文件，readBlobData函数则负责查询数据库并调用writeToFile进行数据保存。

有个sqlite库，

blob字段存储的是照片，需要提取字段内容的照片，转存成文件，

示例如下：

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)