python3 从kafka获取数据，并解析为json格式，写入到mysql中

项目需求：将kafka解析来的日志获取到数据库的变更记录，按照订单的级别和订单明细级别写入数据库，一条订单的所有信息包括各种维度信息均保存在一条json中，写入mysql5.7中。
配置信息：

[Global]
kafka_server=xxxxxxxxxxx:9092
kafka_topic=mes
consumer_group=test100
passwd = tracking
port = 3306
host = xxxxxxxxxx
user = track
schema = track
dd_socket =
dd_host = xxxxxxxxxxxx
dd_port = 3306
dd_user = xxxxxxxxx
dd_passwd = xxxxxxxx

代码又长又丑，半吊子，只完成了面向过程的编程，没做到对象，将就看，有问题可以联系我
代码：

#encoding=utf-8
import datetime
import configparser
import re
import pymysql
from vertica_python import connect
import vertica_python
import json
from confluent_kafka import Consumer, KafkaError
import csv
import logging
import os
import time
import signal
import sys

#写日志
logging.basicConfig(filename=os.path.join(os.getcwd(), 'log_tracking.txt'), level=logging.WARN, filemode='a',format='%(asctime)s - %(levelname)s: %(message)s')

def writeErrorLog(errSrc, errType, errMsg):
    try:
        v_log_file = 'err_tracking.log';
        v_file = open(v_log_file, 'a')
        v_file.write(datetime.datetime.strftime(datetime.datetime.now(),"%Y-%m-%d %H:%M:%S") + " - " + errSrc + " - " + errType +" : " + errMsg + '\n')
        v_file.flush()
    except Exception as data:
        v_err_file = open('err_tracking.log', 'a')
        v_err_file.write(str(data) + '\n')
        v_err_file.write(datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d %H:%M:%S") + " - " + errSrc + " - " + errType + " : " + errMsg + '\n')
        v_err_file.flush()
        v_err_file.close()
    finally:
        v_file.close()

class RH_Consumer:
#读取配置文件的配置信息，并初始化一些类需要的变量
    def __init__(self):
        self.config = configparser.ConfigParser()
        self.config.read('config.ini')
        self.host = self.config.get('Global', 'host')
        self.user = self.config.get('Global', 'user')
        self.passwd = self.config.get('Global', 'passwd')
        self.schema = self.config.get('Global', 'schema')
        self.port = int(self.config.get('Global', 'port'))
        self.kafka_server = self.config.get('Global', 'kafka_server')
        self.kafka_topic = self.config.get('Global', 'kafka_topic')
        self.consumer_group = self.config.get('Global', 'consumer_group')
        self.dd_host = self.config.get('Global', 'dd_host')
        self.dd_user = self.config.get('Global', 'dd_user')
        self.dd_passwd = self.config.get('Global', 'dd_passwd')
        self.dd_port = int(self.config.get('Global', 'dd_port'))
        self.dd_socket = self.config.get('Global', 'dd_socket')
        self.operation_time = datetime.datetime.now()
        self.stop_flag = 0
        self.src_table_name = []
        self.__init_db()
        self.__init_mes_db()
        self._get_all_src_table()
#连接写入目标数据库
    def __init_db(self):
        try:
            self.conn_info = {'host': self.host,'port': self.port,'user': self.user,'password': self.passwd,'db': 'tracking'}
            self.mysql_db = pymysql.connect(**self.conn_info, charset="utf8" )
            self.mysql_cur = self.mysql_db.cursor()
        except Exception as data:
            writeErrorLog('__init_db', 'Error', str(data))
#连接生产数据库，用于获取相关维度信息
    def __init_mes_db(self):
        try:
            self.mes_mysql_db = pymysql.connect(host=self.dd_host, user=self.dd_user, passwd=self.dd_passwd,port=self.dd_port, unix_socket=self.dd_socket, charset="utf8")
            self.mes_mysql_cur = self.mes_mysql_db.cursor()
        except Exception as data:
            writeErrorLog('__init_db', 'Error', str(data))

#关闭数据库
    def _release_db(self):
            self.mysql_cur.close()
            self.mysql_db.close()
            self.mes_mysql_cur.close()
            self.mes_mysql_db.close()
#获取所有的配置表信息（需要获取的表）
    def _get_all_src_table(self):
        try:
            # 获取table的信息
            select_src_table_names = "select distinct src_table_name from tracking.tracking_table_mapping_rule"
            self.mysql_cur.execute(select_src_table_names)
            rows = self.mysql_cur.fetchall()
            for item in rows:
                self.src_table_name.append(item[0])
            return self.src_table_name
        except Exception as data:
            writeErrorLog('_get_all_src_table', 'Error', str(data))
            logging.error('_get_all_src_table: ' + str(data))
#获取src表的目标表信息
    def _get_tgt_table_name(self,table_name,table_schema):
        try:
            # 获取table的信息（table_name是schema|tablename）
            select_tgt_table_names = "select distinct tgt_table_name from tracking.tracking_table_mapping_rule where src_table_name = '%s' and src_table_schema = '%s'" %(table_name,table_schema)
            self.mysql_cur.execute(select_tgt_table_names)
            rows = self.mysql_cur.fetchall()
            tgt_table_names=[]
            for item in rows:
                tgt_table_names.append(item[0])
            return tgt_table_names
        except Exception as data:
            writeErrorLog('_get_tgt_table_name', 'Error', str(data))
            logging.error('_get_tgt_table_name: ' + str(data))
# 根据获取到输入的table_name,读取表的配置信息 会以json格式返回获取到的数据
    def _get_config(self,table_name,tgt_table_name,table_schema):
        try:
            # 获取table的信息（table_name是schema|tablename）
            select_table_config = "select coalesce( src_system, '' ) as src_system,coalesce ( src_table_schema, '' ) as src_table_schema,coalesce ( src_table_name, '' ) as src_table_name,coalesce ( tgt_operation, '{}' ) as tgt_operation,active_flag,coalesce ( tgt_system, '' ) as tgt_system,coalesce ( tgt_table_schema, '' ) as tgt_table_schema,coalesce ( tgt_table_name, '' ) as tgt_table_name from tracking.tracking_table_mapping_rule where src_table_name = '%s' and tgt_table_name='%s'  and src_table_schema = '%s' " %(table_name,tgt_table_name,table_schema)
            self.mysql_cur.execute(select_table_config)
            rows = self.mysql_cur.fetchall()
            for item in rows:
                self.src_system = item[0]
                self.src_table_schema = item[1]
                self.src_table_name = item[2]
                self.tgt_operation = item[3]
                self.active_flag = item[4]
                self.tgt_system = item[5]
                self.tgt_table_schema = item[6]
                self.tgt_table_name = item[7]
            #解析出self.tgt_operation 中以后所需要的数据
            self.tgt_operation = eval(self.tgt_operation)
            result_data = {'src_system':self.src_system,
                           'src_table_schema':self.src_table_schema,
                           'src_table_name':self.src_table_name,
                           'tgt_operation':self.tgt_operation,
                           'active_flag':self.active_flag,
                           'tgt_system': self.tgt_system,
                           'tgt_table_schema': self.tgt_table_schema,
                           'tgt_table_name': self.tgt_table_name,
                           #解析出来的self.tgt_operation里的信息
                           'source_primary_key': self.tgt_operation['source_primary_key'],
                           'source_all_column': self.tgt_operation['source_all_column'],
                           'target_primary_key': self.tgt_operation['target_primary_key'],
                           'target_column': self.tgt_operation['target_column'],
                           'source_level': self.tgt_operation['source_level']   }
            return  result_data
        except Exception as data:
            writeErrorLog('_get_config', 'Error', str(data)+':table is not available')
            logging.error('_get_config: ' + str(data))


#主方法的入口
    def _do(self):
        try:
            #配置consumer的信息，可以配置很多其他信息
            c = Consumer({
                'bootstrap.servers': self.kafka_server,
                'group.id': self.consumer_group,
                'default.topic.config': {
                    'auto.offset.reset': 'smallest',
                    'enable.auto.commit': False}
            })
            #定义消费kafka中的主题
            c.subscribe([self.kafka_topic])
            while True:
                msg = c.poll(1.0)
                if msg is None:
                    continue
                if msg.error():
                    if msg.error().code() == KafkaError._PARTITION_EOF:
                        continue
                    else:
                        print(msg.error())
                        break
                text = msg.value().decode(encoding="utf-8")
           
            # kfk_text = eval(text)
                kfk_text = json.loads(text)
            #此处判断kfk数据是否在配置表中，如果在则进行下一步，如果不在则忽略
                #添加异常处理目的是为了如果这条数据写入有问题，就不commit，方便下次处理还可以继续消费
                try:
                    kfk_table = kfk_text['table']
                    if kfk_table in ['order_mails'] :
                        print(type(text),text)
                        logging.warning('-------------- start exec table time : ' + str(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))+'---------------------')
                        kfk_text = str(kfk_text)
                        kfk_text = kfk_text.replace(": None",": ''")
                        kfk_text = eval(kfk_text)
                        kfk_datas = kfk_text['data']
                        kfk_type = kfk_text['type']
                        kfk_old = kfk_text['old']
                        logging.warning(' table_name: '+ str(kfk_table)+ ' table_type : ' + kfk_type)
                        if kfk_type == 'UPDATE':
                            continue
                            print('update')
                            for i,data in enumerate(kfk_datas):
                                kfk_text['data'] = eval("["+str(data)+"]")
                                kfk_text['old'] = eval("[" + str(kfk_old[i]) + "]")
                                self._get_rh_from_kafka(kfk_text)
                        else:
                            print('insert')
                            for data in kfk_datas:
                                kfk_text['data'] = eval("["+str(data)+"]")
                                print(type(kfk_text), kfk_text)
                                self._get_rh_from_kafka(kfk_text)