Python程序实现向MySQL存放图片

目录

• 环境
• 读取图片
• 创建存放图片的表
• 存入MySQL
• 保存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')

测试结果