第一步 读取图片文件:
with open("书籍.png", "rb") as f:
# 转为二进制格式,并且使用base64进行加密
base64_data = base64.b64encode(f.read())
MySQL_connect(1,'书籍', base64_data)
第二步 编写MySQL_connect,向mysql存储文件
def MySQL_connect(picture_id, picture_name, picture_binary_system):
# 打开数据库连接
connection = pymysql.connect(host="127.0.0.1", user="root", password="111111", port=3306, database="test",
charset='utf8')
# MySQL语句
sql = 'INSERT INTO photo(id,name,photo) VALUES (%s,%s,%s)'
# 获取标记
cursor = connection.cursor()
try:
# 执行SQL语句
cursor.execute(sql, [picture_id, picture_name, picture_binary_system])
# 提交事务
connection.commit()
except Exception as e:
print(str(e))
# 有异常,回滚事务
connection.rollback()
# 释放内存
cursor.close()
connection.close()
第三步 读取图片文件
def getFile():
# 打开数据库连接
connection = pymysql.connect(host="127.0.0.1", user="root", password="111111", port=3306, database="test",
charset='utf8')
cursor = connection.cursor()
sql = 'SELECT * FROM photo where id = 2'
cursor.execute(sql)
data = cursor.fetchone()
print(data)
#with open('存放文件的位置/文件名.扩展名', 'wb') as f:
# f.write(base64.b64decode(data[2]))
#例如下面
with open('./获取图片.png', 'wb') as f:
f.write(base64.b64decode(data[2]))#解密即可
# 释放内存
cursor.close()
connection.close()
整体如下:
import pymysql
import base64
def MySQL_connect(picture_id, picture_name, picture_binary_system):
# 打开数据库连接
connection = pymysql.connect(host="127.0.0.1", user="root", password="111111", port=3306, database="test",
charset='utf8')
# MySQL语句
sql = 'INSERT INTO photo(id,name,photo) VALUES (%s,%s,%s)'
# 获取标记
cursor = connection.cursor()
try:
# 执行SQL语句
cursor.execute(sql, [picture_id, picture_name, picture_binary_system])
# 提交事务
connection.commit()
except Exception as e:
print(str(e))
# 有异常,回滚事务
connection.rollback()
# 释放内存
cursor.close()
connection.close()
def getFile():
# 打开数据库连接
connection = pymysql.connect(host="127.0.0.1", user="root", password="111111", port=3306, database="test",
charset='utf8')
cursor = connection.cursor()
sql = 'SELECT * FROM photo where id = 2'
cursor.execute(sql)
data = cursor.fetchone()
print(data)
with open('获取文件.docx', 'wb') as f:
f.write(base64.b64decode(data[2]))
# 释放内存
cursor.close()
connection.close()
with open("./photo/书籍.png", "rb") as f:
# 转为二进制格式,并且使用base64进行加密
base64_data = base64.b64encode(f.read())
MySQL_connect(1,'书籍', base64_data)
getFile()
# with open("软件开发流程.docx", "rb") as f:
# # 转为二进制格式,并且使用base64进行加密
# base64_data = base64.b64encode(f.read())
# MySQL_connect(2,'文件', base64_data)
# getFile()
mysql数据库如下:
当然在数据库设计表的时候,你可以设计存储文件扩展名之类的。我这里主要用图片示范一下,已明知文件的扩展名了。