🍉一、录入sql和批量上传minIo图片，
录入sql（掠过）...,
一个上传，一个下载，只需上传，找到
def upload():
# NOTE：Step:3 把拉下来的图片传上去给XXX服务器的minio
up_data_minio("test", image_Urls_path='res')修改image_Urls_path，为批量上传图片目录位置。
找到
# 上传图片到minio
def up_data_minio(bucket: str, image_Urls_path='imageUrlFromminIO'):
# TODO：minio_conf唯一要修改的地方！
minio_conf = {
'endpoint': '10.168.1.96:9000',
# 'endpoint': '172.20.0.67:9000',
'access_key': 'minioadmin',
# 'access_key': 'root',
'secret_key': 'minioadmin',
# 'secret_key': 'Lidar4d@#321',
'secure': False
}修改minio_conf为需要上传到指定minIO的绑定信息。
import minio
import pymysql
import openpyxl
import os
def get_data_from_mysql():
# 连接数据库-
conn = pymysql.connect(host="10.168.1.94", user="root", passwd="root", db="RemotePatrolDB", port=3369, charset="utf8")
cur = conn.cursor() # 创建游标对象
# 查询表中数据
cur.execute("SELECT * FROM CorrectPoint_fushun;")
df = cur.fetchall() # 获取所有数据
imageUrls = []
for data in df:
imageUrls.append(data[15])
cur.close()
conn.close()
return imageUrls
def save_for_excel(df):
wb = openpyxl.Workbook()
ws = wb.active
for row in df:
ws.append(row)
wb.save("文件名.xlsx")
# 从minio上面拉取图片
def load_data_minio(bucket: str, imageUrls):
minio_conf = {
'endpoint': '10.168.1.96:9000',
'access_key': 'minioadmin',
'secret_key': 'minioadmin',
'secure': False
}
client = minio.Minio(**minio_conf)
if not client.bucket_exists(bucket):
return None
root_path = os.path.join("imageUrlFromminIO")
for imageUrl in imageUrls:
imageUrl = imageUrl.split('/')[-1]
data = client.get_object(bucket, imageUrl)
save_path = os.path.join(root_path, imageUrl)
with open(save_path, 'wb') as file_data:
for d in data.stream(32 * 1024):
file_data.write(d)
return data.data
# 上传图片到minio
def up_data_minio(bucket: str, image_Urls_path='imageUrlFromminIO'):
# TODO：minio_conf唯一要修改的地方！
minio_conf = {
'endpoint': '10.168.1.96:9000',
# 'endpoint': '172.20.0.67:9000',
'access_key': 'minioadmin',
# 'access_key': 'root',
'secret_key': 'minioadmin',
# 'secret_key': 'Lidar4d@#321',
'secure': False
}
for im_name in os.listdir(image_Urls_path):
client = minio.Minio(**minio_conf)
print(im_name, "load sucessed!")
'''
client.fput_object('mybucket', 'myobject.jpg', '/path/to/myobject.jpg', content_type='image/jpeg')
'''
client.fput_object(bucket_name=bucket, object_name=im_name,
file_path=os.path.join(image_Urls_path, im_name),
content_type='image/jpeg'
)
def download():
# NOTE：Step:1 拉取数据库信息
imageUrls = get_data_from_mysql()
print(imageUrls)
# NOTE：Step:2 把图片从96的minio上面拉下来
print(type(load_data_minio("test", imageUrls)))
def upload():
# NOTE：Step:3 把拉下来的图片传上去给XXX服务器的minio
up_data_minio("test", image_Urls_path='res')
if __name__ == "__main__":
# 子良使用
# download()
# 豪哥使用
upload()
'''
用于批量修改数据库ImagePath字段信息，替换为自己的ip。
---
UPDATE CorrectPoint SET ImagePath=REPLACE(ImagePath, '192.168.120.188', '10.168.1.96');
UPDATE CorrectPoint SET ImagePath=REPLACE(ImagePath, '10.168.1.96', '192.168.120.188');
UPDATE CorrectPoint_lanjiang SET ImagePath=(REPLACE(ImagePath,"/intelligentremotepatrol/obj/","/test/"));
'''
🍉二、自动分区指针、数显、油面、状态数据
刚拿到数据的时候，数据目录是这样的：
可以看到，里面有数显、也有指针类表计，当然还有更多的没有展示出来：油面表和状态类。
这样就很痛苦，因为每个类别对应的算法不同，想要批量测试，必须将他们分区，所以我们可以运行下面的代码：
1. 连接数据库。
2.找到数据库中对应点位的算法类别标签。
3.根据标签将每一个数据分到指定分区。
import minio
import pymysql
import openpyxl
import os
import shutil
def get_data_from_mysql():
# 连接数据库-
conn = pymysql.connect(host="10.168.1.94", user="root", passwd="root", db="RemotePatrolDB", port=3369, charset="utf8")
cur = conn.cursor() # 创建游标对象
# 查询表中数据
cur.execute("SELECT * FROM CorrectPoint_fushun;")
df = cur.fetchall() # 获取所有数据
imageUrls = {}
for data in df:
imageUrls[data[1]] = data[5]
cur.close()
conn.close()
return imageUrls
if __name__ == "__main__":
img_list = os.listdir('./')
id_list = [temp.split('.')[0].split('_')[-1] for temp in img_list]
meter_type = get_data_from_mysql()
is_null_ids = []
is_v5 = []
is_paddleocr = []
is_ywj = []
if not os.path.exists('meter_ywj'):
os.makedirs('meter_ywj')
if not os.path.exists('meter_v5'):
os.makedirs('meter_v5')
if not os.path.exists('paddleocr'):
os.makedirs('paddleocr')
if not os.path.exists('zt_shibie'):
os.makedirs('zt_shibie')
for ind, i in enumerate(id_list):
if i == 'not':
continue
if i not in meter_type:
is_null_ids.append(i)
continue
if meter_type[i] == 'meter_v5':
shutil.move(img_list[ind], 'meter_v5/' + img_list[ind])
elif meter_type[i] == 'paddleocr':
shutil.move(img_list[ind], 'paddleocr/' + img_list[ind])
elif meter_type[i] == 'meter_ywj':
shutil.move(img_list[ind], 'meter_ywj/' + img_list[ind])
print("未标定的点位：", is_null_ids)结果如下：
🍉三、自动筛选出未识别出的点位，并且放到一个单独的分区，可以逐个测试，找到每一个测试失败的原因
import os
import shutil
def find(dir_path):
ori_data = set(os.listdir(dir_path))
res_data = set(os.listdir(dir_path + '/' + 'res'))
results = []
for ori in ori_data:
if ori == 'res':
continue
ori_id = ori.split('_')[0] + '.jpg'
if not ori_id in res_data and not ori in res_data:
results.append(ori)
# shutil.move(os.path.join(dir_path, ori), ori)
# shutil.copyfile(os.path.join(dir_path, ori), ori)
print(results)
print("len(results):", len(results))
if __name__ == "__main__":
find('lanjiang_meter1226')
🍉四、 找到数据库中录入失败的点位
import minio
import pymysql
import openpyxl
import os
import shutil
def get_column_from_mysql():
# 连接到MySQL数据库
connection = pymysql.connect(
host="10.168.1.94",
user="root",
password="root",
database="RemotePatrolDB",
port=3369,
charset="utf8",
cursorclass=pymysql.cursors.DictCursor
)
try:
# 创建游标对象
with connection.cursor() as cursor:
# 执行查询表结构的SQL语句
table_name = 'CorrectPoint_lanjiang'
query = f"DESCRIBE {table_name}"
cursor.execute(query)
# 获取查询结果
result = cursor.fetchall()
# 打印表的所有字段
for i,row in enumerate(result):
print(i, row['Field'])
finally:
# 关闭数据库连接
connection.close()
def get_data_from_mysql():
get_column_from_mysql()
# 连接数据库-
conn = pymysql.connect(
host="10.168.1.94",
user="root",
password="root",
database="RemotePatrolDB",
port=3369,
charset="utf8",
cursorclass=pymysql.cursors.DictCursor
)
cur = conn.cursor() # 创建游标对象
# 查询表中数据
cur.execute("SELECT * FROM CorrectPoint_lanjiang;")
df = cur.fetchall() # 获取所有数据
imageUrls = []
ids = []
for data in df:
ids.append(data['PointPositionId'])
if len(data['ImagePath']) > 0:
continue
imageUrls.append(data['PointPositionId'])
cur.close()
conn.close()
return ids, imageUrls
def find_notLoad_id(ids, image_paths='lanjiang_meter1226'):
img_list = os.listdir(image_paths)
results = []
for im in img_list:
if im == 'res':
continue
id = im.split('_')[0]
if not id in ids:
results.append(im)
return results
if __name__ == "__main__":
ids, imageUrls = get_data_from_mysql()
print('***************************')
print("【警告】minIO为空的图片：")
print('***************************')
print(imageUrls)
print("len(imageUrls):", len(imageUrls))
print('***************************')
print("【警告】未录入点位的图片：")
print('***************************')
ids_error = find_notLoad_id(ids, image_paths='lanjiang_meter1226')
print(ids_error)
print("len(ids_error):", len(ids_error))
0 Id
1 PointPositionId
2 Position
3 MinValue
4 MaxValue
5 AlgorithmType
6 CreatorId
7 CreatorName
8 CreatorTime
9 UpdateId
10 UpdateName
11 UpdateTime
12 IsDelete
13 ImagePath
14 FrameValue
15 MarkType
16 Type
17 RegionCoordinate
18 RowNum
19 ColumnNum
20 Time
21 Tags
***************************
【警告】minIO为空的图片：
***************************
['3a0ce56d-2746-2f58-46f2-aa56f24b2324', '3a0ce56d-2746-4ca1-cb92-9a8446883521', '3a0ce56d-2746-36c7-2b65-24159b4bcab8', '3a0ce56d-2746-c90c-dac5-47fca3e28ee5', '3a0ce56d-2747-9700-e48c-b771397bb369', '3a0d7153-5342-1877-54e7-98a79fe1a23f', '3a0d7153-5343-5750-300f-3d379c106df2', '3a0d7153-5343-6063-9c42-a9ca6af7c961', '3a0d7153-5344-6f60-3753-15e9f6d2f35b', '3a0d7153-5343-f060-b02f-6f15924d27d4', '3a0d7153-5344-babb-fafd-71e4a7148114', '3a0d7153-5344-d702-763e-1800629853f3', '3a0d7153-5344-46fb-bc2f-0ce786508e4b', '3a0d7153-5344-bde3-31c8-f421afb3ad6b', '3a0d7153-5344-1f4c-de63-97fbdf9c33fb', '3a0d7153-5344-c48a-c61a-6d5ed9180d9d', '3a0d7153-5344-d35a-0482-25a5cc66e46f', '3a0d7153-5344-e983-3246-9ca182933208', '3a0d7153-5344-d41a-51ff-1609dbb9af79', '3a0d7153-5344-c173-c0ef-59e0eb3b24aa', '3a0ee406-d07b-9c19-9270-32c2f02befa8', '3a0ee406-d07c-021f-97ad-652b00de6102', '3a0ee406-d07c-0247-7c15-057b9407fb0e', '3a0ee406-d07c-27a4-876f-6dac0d737f68', '3a0ee406-d07d-4b54-6931-072ac4cf7538', '3a0ee406-d07f-6421-43c7-77f6c8d8ff1f', '3a0f0427-d532-0530-134f-a8eb01640ec8', '3a0f0427-d533-254f-6a87-b4eb500c4581', '3a0f0427-d533-8b2e-2279-a6f38f592545', '3a0f0427-d532-a738-89a6-af4af7328446', '3a0f02fd-a129-cde9-6e89-58662a3c6cf4', '3a0f0427-d536-1f1f-0ad7-14fc9a6c0094', '3a0f0427-d536-4b05-6640-791df6aa7c3b', '3a0f64db-4e12-af0a-5fa8-65100a5fddd9', '3a0f06ed-daa4-99e2-e3fa-b925ba024921']
len(imageUrls): 35
***************************
【警告】未录入点位的图片：
***************************
['3a0efe65-3f13-42a3-6098-aebbc07e9a88_3a0fb216-76d7-3764-295b-dc7093a55ef6_20231225590349.jpg', '3a0efe65-3f14-5f10-d5c0-ba72d79b6893_3a0fb216-76d8-7a46-7b6c-58821c0595b2_20231225060447.jpg', '3a0efe65-3f19-678d-edf4-a804c300a51a_3a0fb216-76d7-f405-57fa-fb0999b37238_20231225070450.jpg', '3a0f0211-b2d8-61e5-5533-764e2ead9f2c_3a0fb216-76d5-a2e8-4992-62c198d56478_20231225040411.jpg', '3a0f0211-b2d8-648a-15cb-6465c1d4cb91_3a0fb216-76d5-a2e8-4992-62c198d56478_20231225040446.jpg', '3a0f0211-b2d8-7ff2-afbb-ac10399d0e54_3a0fb216-76d5-a2e8-4992-62c198d56478_20231225040423.jpg', '3a0f0211-b2d8-9184-80fc-a62f23fe5975_3a0fb216-76db-8134-20c1-1f3960819ff3_20231225060411.jpg', '3a0f0211-b2d8-af28-3ef8-4a17141059b4_3a0fb216-76db-8134-20c1-1f3960819ff3_20231225050426.jpg', '3a0f0211-b2d8-bcf1-f581-d7b774248d92_3a0fb216-76d5-a2e8-4992-62c198d56478_20231225040400.jpg', '3a0f0211-b2d8-bf27-af5d-f17e7a1bcdd7_3a0fb216-76db-8134-20c1-1f3960819ff3_20231225060400.jpg', '3a0f0211-b2d8-e2af-32d8-2c049edea555_3a0fb216-76d5-a2e8-4992-62c198d56478_20231225030447.jpg', '3a0f0211-b2d8-f5d4-4959-8bcad63ca3ea_3a0fb216-76d5-a2e8-4992-62c198d56478_20231225040434.jpg', '3a0f0211-b2da-0781-6d31-b4cdb8b9df02_3a0fb216-76d5-40e9-8f4d-ef8d9dedeb45_20231225000449.jpg', '3a0f0211-b2db-1967-de35-079f3765b631_3a0fb216-76db-5277-bfbd-b6583952ce68_20231225060423.jpg', '3a0f0211-b2db-2dcd-585a-2abad0c31b1b_3a0fb216-76db-e24d-d80b-02206c47655c_20231225590326.jpg', '3a0f0211-b2db-3a64-4a09-ad834ff42571_3a0fb216-76db-e24d-d80b-02206c47655c_20231225580352.jpg', '3a0f0211-b2db-4377-69ba-557d58642223_3a0fb216-76db-e24d-d80b-02206c47655c_20231225590338.jpg', '3a0f0211-b2db-60f0-c6ac-c796bbb144fe_3a0fb216-76db-e24d-d80b-02206c47655c_20231225590315.jpg', '3a0f0211-b2db-81a5-446b-4a98683da222_3a0fb216-76db-e24d-d80b-02206c47655c_20231225590303.jpg', '3a0f0427-d52d-a45c-cdb4-1a2010190084_3a0fb216-76d7-e192-15be-5e7718b8eced_20231225070445.jpg', '3a0f06ed-daa4-3209-c85d-438478893957_3a0fb216-76da-c262-5fc3-d091cd39c381_20231225590304.jpg', '3a0f06ed-daa4-47b4-7d04-5afb9db6f351_3a0fb216-76d4-5546-c271-517f7f4bd2b9_20231225090428.jpg', '3a0f06ed-daa4-e789-2cb1-be0df84590af_3a0fb216-76d4-5546-c271-517f7f4bd2b9_20231225090439.jpg', '3a0f0716-eedd-0665-6153-2bfd4752bd5f_3a0fb216-76d5-c63c-f2e5-85d5a4ba55c3_20231225100418.jpg', '3a0f0716-eedd-8084-fb19-05aeb9e9703f_3a0fb216-76d5-c63c-f2e5-85d5a4ba55c3_20231225100406.jpg', '3a0f0716-eedd-9599-b03f-7abc46a0f550_3a0fb216-76d5-c63c-f2e5-85d5a4ba55c3_20231225090442.jpg', '3a0f0716-eedd-ac1a-d551-5c56abdef0a4_3a0fb216-76d5-c63c-f2e5-85d5a4ba55c3_20231225090455.jpg', '3a0f0716-eedd-ade1-670b-64dc55474505_3a0fb216-76d5-c63c-f2e5-85d5a4ba55c3_20231225090418.jpg', '3a0f5a44-a13a-17ee-980f-3915ddf9bbbb_3a0fb216-76d0-a4d5-9563-58bb7e7e5792_20231225080429.jpg', '3a0f5a44-a13b-0a71-398f-f8a8ff4eea9f_3a0fb216-76cd-dfbb-4281-0d3ebc3fc6e8_20231225050450.jpg', '3a0f5a44-a13b-1215-c2ad-955100c6d158_3a0fb216-76d7-e192-15be-5e7718b8eced_20231225090428.jpg', '3a0f5a44-a13b-24c4-f5a3-744a21e7f7b6_3a0fb216-76d9-0ed1-b5ae-0165bd60b3d4_20231225090448.jpg', '3a0f5a44-a13b-34ca-f87d-c7d429e110c9_3a0fb216-76d9-149d-6b9e-9aeca820cc2d_20231225080414.jpg', '3a0f5a44-a13b-3ae8-8cad-b1e69b2614b1_3a0fb216-76d9-349f-ca2a-72cd65115f4b_20231225080441.jpg', '3a0f5a44-a13b-3ba0-1c5a-cce36dfbc102_3a0fb216-76d9-149d-6b9e-9aeca820cc2d_20231225080403.jpg', '3a0f5a44-a13b-4e81-43ee-34cd314c64eb_3a0fb216-76cd-e824-740f-4606943d467d_20231225090428.jpg', '3a0f5a44-a13b-512f-8108-341614c4ed3e_3a0fb216-76cd-aa8b-4c95-f02ac94b54d8_20231225050403.jpg', '3a0f5a44-a13b-6d85-d787-9fd616ece5a5_3a0fb216-76d9-9c4e-1fce-6ea5caeae160_20231225080423.jpg', '3a0f5a44-a13b-7131-c9d1-c97292fdc1b8_3a0fb216-76cd-aa8b-4c95-f02ac94b54d8_20231225040452.jpg', '3a0f5a44-a13b-8680-b129-83f98a812630_3a0fb216-76cd-2c11-2c21-e64fe4d1ccec_20231225090407.jpg', '3a0f5a44-a13b-9c22-0cdb-1ef88fbd354b_3a0fb216-76cd-381f-4bee-fe16451c5bf6_20231225090451.jpg', '3a0f5a44-a13b-a8e6-03b3-d3c88e3292c7_3a0fb216-76ce-4ed2-2287-6542e89f5eb4_20231225030427.jpg', '3a0f5a44-a13b-b050-6a53-095e5bb3b392_3a0fb216-76d7-e192-15be-5e7718b8eced_20231225080443.jpg', '3a0f5a44-a13b-bc14-31d1-3edcbf856018_3a0fb216-76cd-8c02-86e9-dd0c5b430b87_20231225080446.jpg', '3a0f5a44-a13b-bc5a-7e9c-2932fe3bb57f_3a0fb216-76d9-349f-ca2a-72cd65115f4b_20231225080452.jpg', '3a0f5a44-a13b-c68e-1038-d6e6800716d0_3a0fb216-76cd-2c11-2c21-e64fe4d1ccec_20231225080455.jpg', '3a0f5a44-a13b-e2f6-5670-bbda4f0f72eb_3a0fb216-76d7-e192-15be-5e7718b8eced_20231225080408.jpg', '3a0f5a44-a13b-e6ff-8941-9fa869a6ed53_3a0fb216-76cd-e650-cbdc-939260d52add_20231225090412.jpg', '3a0f5a44-a13b-ede6-8763-6fe52ced2337_3a0fb216-76d9-9c4e-1fce-6ea5caeae160_20231225080434.jpg', '3a0f5abf-3643-4933-e894-84e2f91dc390_3a0fb216-76d1-19fe-948f-3e929b4f7cd6_20231225080443.jpg', '3a0f5abf-3643-6006-797f-b4aa26fe9a24_3a0fb216-76d2-2625-f8b5-4ccbfcac02dc_20231225080434.jpg']
len(ids_error): 51
