【python】mysql数据和本地文件对比，并将差集结果输出到csv

最新推荐文章于 2024-07-05 03:33:19 发布

weixin_40293999

最新推荐文章于 2024-07-05 03:33:19 发布

阅读量178

点赞数

分类专栏： python linux 运维文章标签： python mysql pandas

本文链接：https://blog.csdn.net/weixin_40293999/article/details/129086032

版权

linux 同时被 3 个专栏收录

57 篇文章 2 订阅

订阅专栏

python

40 篇文章 2 订阅

订阅专栏

运维

13 篇文章 1 订阅

订阅专栏

该脚本读取camera.txt文件中的IP地址，然后连接到MySQL数据库，比较并找出不在数据库记录中的IP，将结果存储为db.csv。相机IP地址与数据库中设备IP不匹配的部分被标识出来。

摘要由CSDN通过智能技术生成

import pymysql
from pymysql import cursors

camera_ftp_list = []
with open("./camera.txt",'r') as f:
    raw_camera_ftp_list = f.readlines()

for camera_row in raw_camera_ftp_list:
    camera_ftp_list.append(camera_row.strip().split(" ")[-1])

camera_db_list = []
conn = db_conn = pymysql.connect(
host='10.10.xxx.xxx',
port=3306,
user='root',
password='11xxxxx',
database='digital_city',
charset='utf8',
cursorclass=cursors.DictCursor)
cursor = conn.cursor()
sql = "select ident,dev_code,device_ip,dev_name  from digital_city.ai_device";
cursor.execute(sql)
db_camera_list = cursor.fetchall()
only_db = []
only_ftp = []
import pandas as pd

db_camera_ip_list = []

for row in db_camera_list:
    if row['device_ip'] not in camera_ftp_list:
        only_db.append(row)
    db_camera_ip_list.append(row['device_ip'])

for ftp_camera in camera_ftp_list:
    if ftp_camera not in db_camera_ip_list:
        only_ftp.append(ftp_camera)




pd.DataFrame(only_db).to_csv('db.csv',index=False,sep=',')

本地文件 camera.txt

drwxr-xr-x.  2 camera camera 1163264 2月  17 10:04 10.10.60.50
drwxr-xr-x.  2 camera camera 1380352 2月  17 10:04 10.10.60.51
drwxr-xr-x.  2 camera camera  663552 2月  17 10:04 10.10.60.52
drwxr-xr-x.  2 camera camera  950272 2月  17 10:04 10.80.191.50
drwxr-xr-x.  2 camera camera 1568768 2月  17 10:04 10.80.191.51
drwxr-xr-x.  2 camera camera  364544 2月  17 10:04 10.80.191.52
drwxr-xr-x.  2 camera camera 1998848 2月  17 10:04 10.80.192.50
drwxr-xr-x.  2 camera camera  286720 2月  17 10:04 10.80.192.51
drwxr-xr-x.  2 camera camera 1646592 2月  17 10:04 10.80.192.52
drwxr-xr-x.  2 camera camera  458752 2月  17 10:04 10.80.192.53
drwxr-xr-x.  2 camera camera  372736 2月  17 10:04 10.80.192.54
drwxr-xr-x.  2 camera camera  360448 2月  17 10:04 10.80.192.55
drwxr-xr-x.  2 camera camera  634880 2月  17 10:04 10.80.192.56
drwxr-xr-x.  2 camera camera  462848 2月  17 10:04 10.80.192.57
drwxr-xr-x.  2 camera camera 1748992 2月  17 10:04 10.80.192.58
drwxr-xr-x.  2 camera camera  929792 2月  17 10:04 10.80.192.59
drwxr-xr-x.  2 camera camera 1024000 2月  17 10:04 10.80.192.60
drwxr-xr-x.  2 camera camera  282624 2月  17 10:04 10.80.193.50
drwxr-xr-x.  2 camera camera  536576 2月  17 10:04 10.80.193.51
drwxr-xr-x.  2 camera camera  532480 2月  17 10:04 10.80.193.52
drwxr-xr-x.  2 camera camera  393216 2月  17 10:04 10.80.193.53
drwxr-xr-x   2 camera camera  434176 2月  17 10:04 10.80.193.54
drwxr-xr-x   2 camera camera 1036288 2月  17 10:04 10.80.193.55
drwxr-xr-x.  2 camera camera  270336 2月  17 10:04 10.80.193.56
drwxr-xr-x.  2 camera camera  380928 2月  17 10:04 10.80.193.57
drwxr-xr-x.  2 camera camera  421888 2月  17 10:04 10.80.193.58
drwxr-xr-x.  2 camera camera  450560 2月  17 10:04 10.80.193.59
drwxr-xr-x.  2 camera camera  356352 2月  17 10:04 10.80.193.60
drwxr-xr-x.  2 camera camera  368640 2月  17 10:04 10.80.193.61
drwxr-xr-x.  2 camera camera  999424 2月  17 10:04 10.80.194.50
drwxr-xr-x.  2 camera camera 1179648 2月  17 10:04 10.80.194.51
drwxr-xr-x.  2 camera camera 1110016 2月  17 10:04 10.80.194.52
drwxr-xr-x.  2 camera camera 1642496 2月  17 10:04 10.80.194.54
drwxr-xr-x.  2 camera camera 2490368 2月  17 10:04 10.80.194.55
drwxr-xr-x.  2 camera camera 1208320 2月  17 10:04 10.80.194.57
drwxr-xr-x.  2 camera camera 1318912 2月  17 10:04 10.80.194.58
drwxr-xr-x.  2 camera camera 2240512 2月  17 10:04 10.80.194.59
drwxr-xr-x.  2 camera camera 1372160 2月  17 10:04 10.80.194.60
drwxr-xr-x.  2 camera camera 1310720 2月  17 10:04 10.80.194.61
drwxr-xr-x.  2 camera camera 1126400 2月  17 10:04 10.80.194.62
drwxr-xr-x.  2 camera camera  823296 2月  17 10:04 10.80.194.63
drwxr-xr-x.  2 camera camera  458752 2月  17 10:04 10.80.195.50
drwxr-xr-x.  2 camera camera  385024 2月  17 10:04 10.80.195.51
drwxr-xr-x.  2 camera camera  712704 2月  17 10:04 10.80.195.53
drwxr-xr-x.  2 camera camera  708608 2月  17 10:04 10.80.195.54
drwxr-xr-x.  2 camera camera 1421312 2月  17 10:04 10.80.195.55
drwxr-xr-x.  2 camera camera  294912 2月  17 10:04 10.80.195.56
drwxr-xr-x.  2 camera camera 1191936 2月  17 10:04 10.80.196.50
drwxr-xr-x.  2 camera camera 2019328 2月  17 10:04 10.80.196.51
drwxr-xr-x.  2 camera camera 2052096 2月  17 10:04 10.80.197.50

数据库读取出来的数据

[{'ident': '09b7c8855ebe1fdec2fd0d7767127cfb', 'dev_code': 'bdc7832c8f47494d85ddd311b4b75e78', 'device_ip': '10.80.190.244', 'dev_name': '凤凰城二期楼顶'}, {'ident': 'd5f9e7dd911d32df7b9b3bd01157b822', 'dev_code': '5fbb4df1fe3843ea8ad76162ed233b72', 'device_ip': '10.80.190.248', 'dev_name': '北方学院西校区东门枪机'}, {'ident': '', 'dev_code': 'c8c2d3322214407eaa13a3b4cc3dc787', 'device_ip': None, 'dev_name': '站前大街建院南门井盖'}, {'ident': '', 'dev_code': '52ea77fa103b4c44afab23c938c9a850', 'device_ip': None, 'dev_name': '站前大街铁道桥东井盖'}, {'ident': '', 'dev_code': '6505050f60c847f5a592b32e333182f3', 'device_ip': None, 'dev_name': '垃圾中转站西南垃圾桶1'}, {'ident': '', 'dev_code': 'c41d31b163264026a9903351209e2839', 'device_ip': None, 'dev_name': '垃圾中转站西南垃圾桶2'}, {'ident': '', 'dev_code': '68082fa244884625aafdbab9ca4c5bd3', 'device_ip': None, 'dev_name': '站前大街高铁站地下车库入口垃圾桶1'}, {'ident': '', 'dev_code': '27cb1f76d99c4c00a2ac3b8d36aa1f16', 'device_ip': None, 'dev_name': '站前大街高铁站地下车库入口垃圾桶2'}, {'ident': '', 'dev_code': '973eba9a32fa4dcb9b2a67fd6c392fcf', 'device_ip': None, 'dev_name': '站前大街高铁站公交停车场入口垃圾桶1'}, {'ident': '', 'dev_code': '2a5d3708ba4440248e93fa70e5122b81', 'device_ip': None, 'dev_name': '站前大街高铁站公交停车场入口垃圾桶2'}, {'ident': '', 'dev_code': '84c100432d5c4a5dbd59231c28571841', 'device_ip': None, 'dev_name': '文华苑西门土壤'}, {'ident': '', 'dev_code': 'dd6ca7455c314b3994a864cfdc1e6c59', 'device_ip': None, 'dev_name': '张家口站西土壤'}, {'ident': '', 'dev_code': 'c9f02dad2e7447de951db617aaafe767', 'device_ip': None, 'dev_name': '四医院门口土壤'}, {'ident': '', 'dev_code': 'f6079f3a366a4a7ebda17dc4e90cfd24', 'device_ip': None, 'dev_name': '亚朵酒店路西土壤'}, {'ident': '', 'dev_code': '46b1d0d755e2455c8b857e3351ec4d07', 'device_ip': None, 'dev_name': '站前大街铁道桥雨量'}, {'ident': '', 'dev_code': '47c9af74e59b4f61a89c6d13f186dc21', 'device_ip': '10.80.194.53', 'dev_name': '音柱_经开区一中'}, {'ident': '', 'dev_code': '90ebc7465c0c476ca801ab379301afbf', 'device_ip': '10.80.194.64', 'dev_name': '音柱_四医院门口'}, {'ident': '', 'dev_code': '561af6663c5f413583f802345150ca97', 'device_ip': '10.80.192.61', 'dev_name': '音柱_华誉酒店'}, {'ident': '', 'dev_code': 'e8a0c15f98a242d39432040b4a0e0eda', 'device_ip': '10.80.196.52', 'dev_name': '音柱_白云路与站前大街交汇口西北角'}, {'ident': '', 'dev_code': 'd1d7cc3a3f7143da93bd71b905246cb6', 'device_ip': '10.80.193.69', 'dev_name': '音柱_九鼎小区东门'}, {'ident': '', 'dev_code': 'acc7dc81f03d42f29b14d39d189ba58c', 'device_ip': None, 'dev_name': '读写器_站前大街垃圾中转站'}, {'ident': '1971a777f9af003520764cc9eae7dd6c', 'dev_code': '0e0779417e2e40bfb2d1eb14270ce93e', 'device_ip': '10.80.195.52', 'dev_name': '长途汽车站-北'}, {'ident': '5c4e04c9f556da992fa4f99ba7362ef8', 'dev_code': 'ab30e3fdc290425887a0b52b6c25bbec', 'device_ip': '10.80.195.57', 'dev_name': '世纪路与长宁街路口'}, {'ident': 'd3c6db65e5fc395f3ae4dd9ac93dca72', 'dev_code': 'c0e213f82bcd46d4929d7b7f2eb0915f', 'device_ip': '10.80.192.66', 'dev_name': '中兴北路与站前大街路口'}, {'ident': '93c45e83d2a2c760f8160a68c5bcff76', 'dev_code': '7d38a95aeced4e61bb7cc3df89fe79f5', 'device_ip': '10.80.193.69', 'dev_name': '球机_亚朵酒店路西'}, {'ident': '278e1e317dbf97fb2ad5c736572e3206', 'dev_code': 'c55c09f4e92346aaaf450d9836103226', 'device_ip': '10.80.195.61', 'dev_name': '世纪路与纬三路'}, {'ident': '', 'dev_code': '0ceee04af1714323925a668ceaec6a44', 'device_ip': '10.80.194.98', 'dev_name': '音柱_市民广场南门_02'}, {'ident': '1f041e761e1e8d151edf228758cd0268', 'dev_code': 'accbbd053a7f48c493ea326bb4005ab0', 'device_ip': '10.80.194.252 ', 'dev_name': '格林豪泰楼顶'}, {'ident': 'be88dae6482a3b8b2d07009178132419', 'dev_code': 'f1165f65d4704ea693ee3482af442687', 'device_ip': '10.80.195.252 ', 'dev_name': '市林草局楼顶'}]