# -*- coding: utf-8 -*- # @Author: Gree # @Date: 2020-11-30 09:44:06 # @Last Modified by: StudentCWZ # @Last Modified time: 2020-12-26 08:54:04 import pymysql import configparser from datetime import datetime import re class ConnSql(object): """ 1. 构建一个连接数据库的父类：读取配置文件，连接数据库。 """ def conn_sql(self): """ 1. 读取 sql.conf 配置文件，并利用相关信息，连接数据库 2. 返回值：conn 对象。 """ cf = configparser.ConfigParser() cf.read("sql.conf") # 读取 sql.conf 配置文件(完全路径) conf_dict = { "host": str(cf["log_on"]["host"]), "port": int(cf["log_on"]["port"]), "user": str(cf["log_on"]["user"]), "passwd": str(cf["log_on"]["passwd"]), "db": str(cf["log_on"]["db"]) } # 异常捕获 try: # 连接数据库 conn = pymysql.connect( host = conf_dict["host"], port = conf_dict["port"], user = conf_dict["user"], passwd = conf_dict["passwd"], db = conf_dict["db"] ) print("Database connection is successful!") return conn except Exception as e: print(e) class DbRun(ConnSql): """ 1. 对于父类 ConnSql 的继承，继承了父类的属性和方法，并拥有自己的方法。 2. DbRun 类主要进行数据库的操作。 3. initial_data 方法：获取要自动标注的原始数据，如果输入的时间段不为空，则获取相应时间段的原始数据； 如果输入时间段为空，则获取所有时间段的原始数据。 """ def table_exists(self, table_name): """ 1. 该方法用来判断我们所要的表格是否存在 """ print("Loading the module of table_exists ...") conn = self.conn_sql() # 调用父类方法获取 conn 对象 # 进行异常捕获 try: with conn.cursor() as cursor: sql = "show tables;" cursor.execute(sql) tables = [cursor.fetchall()] table_list = re.findall('(\'.*?\')', str(tables)) table_list = [re.sub("'",'',each) for each in table_list] if table_name in table_list: print("The table of %s is exists!" % table_name) return 1 # 存在返回1 else: print("The table of %s is not exists!" % table_name) return 0 except Exception as e: print(e) finally: conn.close() def new_table(self, table_name): """ 1. 在 mysql 新建一张数据表 """ conn = self.conn_sql() # 调用父类方法获取 conn 对象 # 进行异常捕获 try: """ 1. sql 语句新建一个所需要字段的 data sheet 。 """ with conn.cursor() as cursor: sql = "CREATE TABLE %s (`id` INT PRIMARY KEY AUTO_INCREMENT,`date_time` datetime DEFAULT NULL,`request_id` varchar(50) DEFAULT NULL,`mac_wifi` varchar(50) DEFAULT NULL,`user_id` varchar(50) DEFAULT NULL,`query` varchar(255) DEFAULT NULL,`domain` varchar(50) DEFAULT NULL,`intent` varchar(50) DEFAULT NULL,`response_text` text DEFAULT NULL,`domain_is_right` varchar(50) DEFAULT NULL,`intent_is_right` varchar(50) DEFAULT NULL,`response_is_right` varchar(50) DEFAULT NULL) CHARSET=utf8;" % table_name cursor.execute(sql) conn.commit() # 事务的手动提交 print("Data sheet of %s is established!" % table_name) except Exception as e: print(e) finally: conn.close() def initial_data(self, table_name, time_before = '', time_now = ''): """ 1. 获取需要进行自动化标注的最原始数据。(其中 time_before, time_now 表示一个时间段。) """ print("Loading the module of initial_data ...") conn = self.conn_sql() # 调用父类方法获取 conn 对象 # 进行异常捕获 try: """如果时间段为空，则表示获取数据表 ctoc_tb 中相关字段的全部数据""" if time_before == '': with conn.cursor() as cursor: sql = "select date_time, request_id, mac_wifi, user_id, query, domain, intent, response_text from %s" % table_name cursor.execute(sql) result = cursor.fetchall() print("Get initial data successfully!") return result else: """如果时间段不为空，则表示获取数据表 ctoc_tb 中相关字段在一个时间段的数据""" with conn.cursor() as cursor: sql = "select date_time, request_id, mac_wifi, user_id, query, domain, intent, response_text from %s where date_time BETWEEN %s and %s" % (table_name, time_before, time_now) cursor.execute(sql) result = cursor.fetchall() print("Get initial data between %s and %s" % (time_before, time_now)) return result except Exception as e: print(e) finally: conn.close() # print("Get initial data successfully!") def contrast_data(self, table_name): """ 1. 该方法用于获取数据库中目前已经存在的正确分类数据。 2. 方法目的：后面会将原始数据每一条数据的 query 字段在正确分类数据中进行遍历查找，如果相等，则该条数据不处理； 如果不相等，则该条数据进入自动化标注环节，进行自动化标注处理。 """ print("Loading the module of contrast_data ...") conn = self.conn_sql() # 调用父类方法获取 conn 对象 # 进行异常捕获 try: with conn.cursor() as cursor: sql = "select date_time, query from %s" % table_name cursor.execute(sql) result = cursor.fetchall() print("Get contrast data successfully!") return result except Exception as e: print(e) finally: conn.close() # print("Get contrast data successfully!") def insert_data(self, table_name, df): """ 1. 该方法将自动化分类的最终数据插入相应的数据表中。 """ conn = self.conn_sql() # 调用父类方法获取 conn 对象 sql_lst = [] # 获取插入 sql 语句的各个字段，用于批量插入数据 # 进行异常捕获 try: """ 1. sql 语句插入相对应字段的数据。 """ with conn.cursor() as cursor: sql = "INSERT INTO " + table_name + " (date_time, request_id, mac_wifi, user_id, query, domain, intent, response_text, domain_is_right, intent_is_right, response_is_right) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" # sql = "INSERT INTO final_info (date_time, request_id, mac_wifi, user_id, query, domain, intent, response_text, domain_is_right, intent_is_right, response_is_right) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" for i in range(0, len(df)): # print("Data is inserting:" + str(i + 1)) time = datetime.strftime(df.iloc[i, 0], "%Y-%m-%d %H:%M:%S") # 将时间类型转为字符串 sql_truple = (time, df.iloc[i, 1], df.iloc[i, 2], df.iloc[i, 3], df.iloc[i, 4], df.iloc[i, 5], df.iloc[i, 6], df.iloc[i, 7], df.iloc[i, 8], df.iloc[i, 9], df.iloc[i, 10]) sql_lst.append(sql_truple) # cursor.execute(sql, (time, df.iloc[i, 1], df.iloc[i, 2], df.iloc[i, 3], df.iloc[i, 4], df.iloc[i, 5], df.iloc[i, 6], df.iloc[i, 7], df.iloc[i, 8], df.iloc[i, 9], df.iloc[i, 10])) # conn.commit() # 事务的手动提交 print("Data is inserting...") cursor.executemany(sql, sql_lst) # 批量插入数据 conn.commit() # 事务的手动提交 except Exception as e: print(e) finally: conn.close() print('The inserting of data is finished!')