python批量插入同一文件夹中图片

文章目录

目录

文章目录

一、写入函数

二、同一文件夹中批量插入

三、完整代码

一、写入函数

读文件操作

filename = path.split('/')[-1]
    try:
        with open(path, 'rb') as f:
            img = f.read()
    except:
        print('读取失败')
        # sys.exit(1)
        return

数据库操作

    try:
        conn = pymysql.connect(host=config['host'],
                               port=config['port'],
                               user=config['user'],
                               passwd=config['password'],
                               db=config['db'],
                               charset='utf8',
                               use_unicode=True)
        cursor = conn.cursor()

		# 注意一下这里的 {0} 的引号，可以试一下去掉引号会提醒没有者找到该字段
        sql = "INSERT INTO images (data, date,path,name) VALUES (%s,%s,%s, '{0}')".format(filename)
        now = datetime.datetime.now()
        now = now.strftime("%Y-%m-%d %H:%M:%S")
        values = (img, now, path)
        cursor.execute(sql, values)
        conn.commit()
        cursor.close()
        conn.close()
        print('写入 {} 成功'.format(filename))

    except Exception as e:
        print(e)
        print('写入失败')

写入数据库

def write_pic2mysql(path, config):
    """
    读取图片写入数据库
    :param path: 读取的图片的路径
    :param config: 数据库连接配置信息
    :return: None
    """
    filename = path.split('/')[-1]
    try:
        with open(path, 'rb') as f:
            img = f.read()
    except:
        print('读取失败')
        # sys.exit(1)
        return
    try:
        conn = pymysql.connect(host=config['host'],
                               port=config['port'],
                               user=config['user'],
                               passwd=config['password'],
                               db=config['db'],
                               charset='utf8',
                               use_unicode=True)
        cursor = conn.cursor()

		# 注意一下这里的 {0} 的引号，可以试一下去掉引号会提醒没有者找到该字段
        sql = "INSERT INTO images (data, date,path,name) VALUES (%s,%s,%s, '{0}')".format(filename)
        now = datetime.datetime.now()
        now = now.strftime("%Y-%m-%d %H:%M:%S")
        values = (img, now, path)
        cursor.execute(sql, values)
        conn.commit()
        cursor.close()
        conn.close()
        print('写入 {} 成功'.format(filename))

    except Exception as e:
        print(e)
        print('写入失败')

二、同一文件夹中批量插入

#批量遍历存储
    for path in glob.glob('D:\\sources\\images\\*.png'):
        write_pic2mysql(path,my_config)

三、完整代码

import pymysql
import os
import traceback
import datetime
import os
import glob

def write_pic2mysql(path, config):
    """
    读取图片写入数据库
    :param path: 读取的图片的路径
    :param config: 数据库连接配置信息
    :return: None
    """
    filename = path.split('/')[-1]
    try:
        with open(path, 'rb') as f:
            img = f.read()
    except:
        print('读取失败')
        # sys.exit(1)
        return
    try:
        conn = pymysql.connect(host=config['host'],
                               port=config['port'],
                               user=config['user'],
                               passwd=config['password'],
                               db=config['db'],
                               charset='utf8',
                               use_unicode=True)
        cursor = conn.cursor()

		# 注意一下这里的 {0} 的引号，可以试一下去掉引号会提醒没有者找到该字段
        sql = "INSERT INTO images (data, date,path,name) VALUES (%s,%s,%s, '{0}')".format(filename)
        now = datetime.datetime.now()
        now = now.strftime("%Y-%m-%d %H:%M:%S")
        values = (img, now, path)
        cursor.execute(sql, values)
        conn.commit()
        cursor.close()
        conn.close()
        print('写入 {} 成功'.format(filename))

    except Exception as e:
        print(e)
        print('写入失败')


def read_mysql2pic(path, filename, config):
    """
    从数据库中读取图片
    :param path: 你要保存的图片的路径
    :param filename:你要从数据库读取的名字，在本例子相当于数据库中的name字段
    :param config: 数据库连接配置信息
    :return: None
    """
    try:
        conn = pymysql.connect(host=config['host'],
                               port=config['port'],
                               user=config['user'],
                               passwd=config['password'],
                               db=config['db'],
                               charset='utf8',
                               use_unicode=True)
        cursor = conn.cursor()
        cursor.execute("select data from images where name = '{}'".format(filename))
        res = cursor.fetchone()[0]
        with open(path, 'wb') as f:
            f.write(res)
        print('从数据库中读取 {} 成功'.format(filename))
    except Exception as e:
        print(e)
        print('读取数据库中的图片失败')


if __name__ == '__main__':
    my_config = {'host': 'localhost', 'port': 3306, 'user': 'root',
                 'password': '123', 'db': 'django'}
    #批量遍历存储
    for path in glob.glob('D:\\sources\\images\\*.png'):
        write_pic2mysql(path,my_config)
    #write_pic2mysql('D:\\sources\\images\\DJI_0004_01_04.png',my_config)
    #print(' 写入后再读取 '.center(50, '*'))
    #read_mysql2pic('D:\\sources\\read_images\\DJI_0004_01_04.png', 'D:DJI_0004_01_04.png', my_config)