Python程序实现向MySQL存放图片

环境

Python 3.7.4
pymysql
8.0.11 MySQL Community Server

读取图片

以二进制格式读取图片

with open("./test.jpg", "rb") as file:
image = file.read()

创建存放图片的表

存放图片字段的属性为longblog，即long binary large object

def create_image_table(self):
sql = 'create table if not exists picture ( \
       image longblob);'

try:
       self.cursor.execute(sql)

self.connection.commit()

except pymysql.Error:
       print(pymysql.Error)

存入MySQL

将二进制格式的图片数据存入MySQL

def insert_image(self, image):
   sql = "insert into picture(image) values(％s)"
   self.cursor.execute(sql, image)
   self.connection.commit()

保存MySQL查询得到的图片数据为图片

以二进制的格式写出图片

def get_image(self, path):
   sql = 'select * from picture'
   try:
       self.cursor.execute(sql)
       image = self.cursor.fetchone()[0]
       with open(path, "wb") as file:
           file.write(image)
   except pymysql.Error:
       print(pymysql.Error)
   except IOError:
       print(IOError)

实现代码

import pymysql

class Database():

'''
Description:
database demo to store image in MySQL RDBMS
Attributes:
None
'''

def __init__(self):
       self.connection = pymysql.connect(host='<host name>',user='<user name>',passwd='<password>',db='<database name>',charset='utf8')
       self.cursor = self.connection.cursor()

'''
Description:
create table to store images
Args:
None
Return:
None
'''

def create_image_table(self):
       sql = 'create table if not exists picture ( \
           image longblob);'

try:
           self.cursor.execute(sql)

self.connection.commit()

except pymysql.Error:
           print(pymysql.Error)

'''
Description:
insert image into table
Args:
image:
image to store
Returns:
None
'''

def insert_image(self, image):
       sql = "insert into picture(image) values(％s)"
       self.cursor.execute(sql, image)
       self.connection.commit()

'''
Description:
get image from database
Args:
path:
path to save image
Returns:
None
'''

def get_image(self, path):
       sql = 'select * from picture'
       try:
           self.cursor.execute(sql)
           image = self.cursor.fetchone()[0]
           with open(path, "wb") as file:
               file.write(image)
       except pymysql.Error:
           print(pymysql.Error)
       except IOError:
           print(IOError)

'''
Description:
destruction method
Args:
None
Returns:
None
'''

def __del__(self):
       self.connection.close()
       self.cursor.close()

if __name__ == "__main__":
   database = Database()
   # read image from current directory
   with open("./test.jpg", "rb") as file:
       image = file.read()

database.create_image_table()
   database.insert_image(image)

database.get_image('./result.jpg')

测试结果

来源：https://blog.csdn.net/qq_44486439/article/details/109003727