怎么用Python程序实现向MySQL存放图片

时间：2023-04-19 17:28

环境

Python 3.7.4pymysql8.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 pymysqlclass 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')