前言

由于mysql鏈接超時波動，導(dǎo)致數(shù)據(jù)缺失，需要根據(jù)日志填補數(shù)據(jù)

流程

獲取確實數(shù)據(jù)的訂單列表

搜索日志，獲取請求日志

根據(jù)請求日志拼裝sql

打印sql供修復(fù)數(shù)據(jù)

代碼

因為我們?nèi)罩敬蛴〉挠袉栴}，所以這里用字符串截取獲取入?yún)?。如果日志打印的是?biāo)準(zhǔn)json，直接搞json即可

from elasticsearch import Elasticsearch
import jsonclass MyUtils:passdef getValue(fullStr, beginStr, endStr):start = fullStr.find(beginStr) + len(beginStr)end = fullStr.find(endStr)value = fullStr[start:end]return valuedef setValue(orderInfoExt, columnName, fullStr, beginStr, endStr):value = MyUtils.getValue(fullStr, beginStr, endStr)if value != 'null':orderInfoExt[columnName] = valuees = Elasticsearch(hosts="http://xxx:9200/", http_auth=('xxx', 'xxx'))
scroll_id = None
fileName = "create-order-info" + ".txt"
orderIdList = [74xxxx574,74xxxx822]
orderExtInfoList = []for orderId in orderIdList:query_json = {"_source": ["message", "logger_name", "@timestamp"],"query": {"bool": {"filter":[{"bool":{"filter":[{"multi_match":{"lenient": True,"query": "order/v1/createOrder","type": "phrase"}},{"multi_match":{"lenient": True,"query": orderId,"type": "phrase"}}]}},{"range":{"@timestamp":{"format": "strict_date_optional_time","gte": "2024-11-01T00:00:00.000Z","lte": "2024-11-02T10:00:00.000Z"}}}],"must":[],"must_not":[],"should":[]}}}query = es.search(index='xxxx-pro*', body=query_json, scroll='25m', size=5000,request_timeout=2000000)for k in query['hits']['hits']:timestr = k['_source']['@timestamp']request = k['_source']['message']orderInfoExt = {}#beancopy的字段MyUtils.setValue(orderInfoExt, 'user_device_mac', request, "userDeviceMac=", ", userDeviceImei")MyUtils.setValue(orderInfoExt, 'user_device_imei', request, "userDeviceImei=", ", userDeviceImsi")#特殊的字段MyUtils.setValue(orderInfoExt, 'order_id', request, "orderId=", ", oid")MyUtils.setValue(orderInfoExt, 'user_order_ip', request, "userIpAddr=", ", userPort")#print(orderInfoExt)orderExtInfoList.append(orderInfoExt)
# 假設(shè)表名為 orders
table_name = 'order_info_ext'
for orderInfoExt in orderExtInfoList:# 提取列名columns = ', '.join(orderInfoExt.keys())# 提取值，并處理為適當(dāng)?shù)母袷絭alues = []for key, value in orderInfoExt.items():if value == 'null':values.append('NULL')elif isinstance(value, (int, float)):values.append(str(value))elif isinstance(value, str):values.append("'"+value+"'")else:values.append('NULL')# 構(gòu)建 INSERT 語句sql = f"INSERT INTO {table_name} ({columns}) VALUES ({', '.join(values)});"print(sql)