这篇“怎么用Python程序实现向MySQL存放图片”文章的知识点大部分人都不太理解,所以小编给大家总结了以下内容,内容详细,步骤清晰,具有一定的借鉴价值,希望大家阅读完这篇文章能有所收获,下面我们一起来看看这篇“怎么用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')
测试结果