import pymysql
import pandas as pd
from tqdm import tqdm
# 创建连接对象也就是为了连接到本地的数据库
engine = pymysql.connect(host='xxx', user='xxx', password='xxx', database='xxx', )
regonls = pd.read_csv('xxx.csv')
cursor = engine.cursor()
# 批量查询
for i in tqdm(range(len(regonls))):
Regon = regonls["REGNO"][i]
ImageType = regonls["IMAGETYPE"][i]
sql = "select RegNO, TMImage from lctmimage where RegNO = '{}'".format(Regon)
data = pd.read_sql(sql, engine)
a = data.empty
# 判断为不为空,为空放入数据库表中
if data['TMImage'].empty:
data = {
'RegNO': Regon,
'ImageType': ImageType,
}
table = 'loss_image_lctmimage'
keys = ', '.join(data.keys())
values = ', '.join(['%s'] * len(data))
sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
try:
cursor.execute(sql, tuple(data.values()))
engine.commit()
except:
print('Failed in this Regon:{}'.format(Regon))
engine.rollback()
continue
# 不为空,创建图片,插入数据
pic = data['TMImage'].values[0]
filename = 'image/' + Regon + '.jpg'
with open(filename, 'wb') as fp:
fp.write(pic)
python中mysql 批量查询
最新推荐文章于 2022-07-28 17:51:00 发布