本文介绍了如何使用Python的pymysql、pymssql和cx_Oracle库进行MySQL、SQLServer和Oracle数据库的批量操作，包括连接配置、SQL执行、结果获取、数据提交和批量执行。此外，还提到了利用tkinter创建数据库管理工具并用Pyinstaller打包的应用场景。
平时需要对数据库进行批量的重复性操作，mysq，sqlserver，oracle，所以就需要使用到Python的三个连接的库，pymysql，pymssql，cx_Oracle
三个数据库的包的操作方法基本一致
Mysql的连接
端口一定为整型 charset=“utf8”，编码不要写成"utf-8"
db = pymysql.connect(host=ip, port=int(port), user=user, passwd=password, db=dbname) Sqlserver的连接，端口一定为整型
db = pymssql.connect(ip, user, password, port=int(port), database=dbname)Oracle的连接，端口是字符类型
db = pyoracle.connect(user,password,ip + ':' + str(port) + '/' + dbname)语句的执行
sql="select * from table"cursor = db.cursor()cursor.execute(sql)结果的获取，获取所有输出
info = cursor.fetchall(结果的获取，获取一条输出
cursor.fetchone()获取影响行
cursor.rowcount数据的提交
db.commit()数据库连接的关闭
db.close()批量sql执行
sql = "INSERT INTO USER1(name, age) VALUES (%s, %s);"data = [("Alex", 18), ("Egon", 20), ("Yuan", 21)]cursor.executemany(sql, data)db.commit()使用tkinter做成了一个数据库相关的工具，然后再用Pyinstaller打包一下就可以单独运行了
#coding:utf-8import pymysql,pymssql,requestsimport cx_Oracle as pyoracleimport time,hashlib,jsonfrom tkinter.scrolledtext import ScrolledTextfrom tkinter import *from tkinter import ttkfrom threading import Threadfrom uuid import *requests.packages.urllib3.disable_warnings()def clear_log(): def clear_log_task(): while 1: if int(log_text.index('end').split('.')[0])>=5000: clear_log_text() time.sleep(3) T = Thread(target=clear_log_task) T.start()def connet_database(): global mysql_sql,table_number_start, table_number_end,sqlserver_sql,oracle_sql run_select() log_text.mark_set('insert', 'end') db_type = database_typeChosen.get() ip, port, user, password, charset, dbname = e_ip.get(), database_portChosen.get(), e_user_name.get(), e_pasowrd.get(), e_char.get(), db_nameChosen.get() table_index, table_number_start, table_number_end = tables_typeChosen.get(), int( tables_start_typeChosen.get()), int(tables_end_typeChosen.get()) action, checkblob = action_typeChosen.get(), int(checkVar.get()) check_srcdb,check_dstdb = check_src_dbChosen.get(),check_dst_dbChosen.get() insert_number,insert_for,insert_time = int(insert_numberChosen.get()),int(insert_forChosen.get()),int(insert_timeChosen.get()) update_time,delete_time=int(update_timeChosen.get()),int(delete_timeChosen.get()) mysql_list = [(mysql_insert_sql,insert_time,'insert'),(mysql_update_sql,update_time,'update'),(mysql_delete_sql,delete_time,'delete')] sqlserver_list = [(sqlserver_insert_sql,insert_time,'insert'),(sqlserver_update_sql,update_time,'update'),(sqlserver_delete_sql,delete_time,'delete')] oracle_list = [(oracle_insert_sql,insert_time,'insert'),(oracle_update_sql,update_time,'update'),(oracle_delete_sql,delete_time,'delete')] def run(): def mysql_run(task_id): def mysql_task(task_id, info): db = pymysql.connect(host=ip, port=int(port), user=user, passwd=password, db=dbname) index = 1 if info[2] == 'insert': for t in range(0, insert_for): if run_flag != 'run': break try: for i in range(1, insert_number + 1): try: if run_flag != 'run': break mysql_sql = info[0] cursor = db.cursor() mysql_sql = mysql_sql.replace('?', dbname, 1) mysql_sql = mysql_sql.replace('?', '%s%d' % (table_index, task_id), 1) mysql_sql = mysql_sql.replace('int_number', '%d' % (index)) mysql_sql = mysql_sql.replace('float_number', '%5f' % (float(index))) mysql_sql = mysql_sql.replace('uuid', "'%s'" % uuid1()) mysql_sql = mysql_sql.replace('insert_time', "'%s'" % ( time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))) cursor.execute(mysql_sql) except Exception as e: write_log_text('ERROR：%s' % e) finally: index += 1 write_log_text('(%s%d)插入%d条数据' % ( table_index, task_id, insert_number)) db.commit() log_text.see(END) time.sleep(float(info[1])) except Exception as s: write_log_text('ERROR：%s' % s) else: while 1: if run_flag != 'run': break try: time.sleep(float(info[1])) mysql_sql = info[0] cursor = db.cursor() mysql_sql = mysql_sql.replace('?', dbname, 1) mysql_sql = mysql_sql.replace('?', '%s%d' % (table_index, task_id), 1) cursor.execute(mysql_sql) except Exception as e: write_log_text('ERROR：%s' % e) finally: if info[2] == 'update': write_log_text('(%s%d).更新%s条数据' % ( table_index, task_id, cursor.rowcount)) if info[2] == 'delete': write_log_text('(%s%d).删除%s条数据' % ( table_index, task_id, cursor.rowcount)) db.commit() log_text.see(END) for x in mysql_list: run_task = Thread(target=mysql_task, args=(task_id, x)) run_task.start() def sqlserver_run(task_id): def sqlserver_task(task_id, info): db = pymssql.connect(ip, user, password, port=int(port), database=dbname) index = 1 if info[2] == 'insert': for t in range(0, insert_for): try: for i in range(1, insert_number + 1): try: sqlserver_sql = info[0] cursor = db.cursor() sqlserver_sql = sqlserver_sql.replace('?', '%s%d' % (table_index, task_id), 1) sqlserver_sql = sqlserver_sql.replace('?', '%d' % (index), 1) sqlserver_sql = sqlserver_sql.replace('int_number', '%d' % (index)) sqlserver_sql = sqlserver_sql.replace('float_number', '%5f' % (float(index))) sqlserver_sql = sqlserver_sql.replace('uuid', "'%s'" % uuid1()) sqlserver_sql = sqlserver_sql.replace('insert_time', "'%s'" % ( time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))) cursor.execute(sqlserver_sql) pass except Exception as e: write_log_text('ERROR：%s' % e) finally: index += 1 write_log_text('(%s%d)插入%d条数据' % (table_index, task_id, insert_number)) db.commit() time.sleep(float(info[1])) except Exception as s: write_log_text('ERROR：%s' % s) else: while 1: try: time.sleep(float(info[1])) sqlserver_sql = info[0] cursor = db.cursor() sqlserver_sql = sqlserver_sql.replace('?', '%s%d' % (table_index, task_id), 1) # write_log_text(sqlserver_sql) cursor.execute(sqlserver_sql) except Exception as e: write_log_text('ERROR：%s' % e) finally: if info[2] == 'update': write_log_text('(%s%d).更新%s条数据' % (table_index, task_id, cursor.rowcount)) if info[2] == 'delete': write_log_text('(%s%d).删除%s条数据' % (table_index, task_id, cursor.rowcount)) db.commit() for x in sqlserver_list: run_task = Thread(target=sqlserver_task, args=(task_id, x)) run_task.start() def oracle_run(task_id): def oracle_task(task_id, info): db = pyoracle.connect(user,password,ip + ':' + str(port) + '/' + dbname) index = 1 if info[2] == 'insert': for t in range(0, insert_for): try: for i in range(1, insert_number + 1): try: oracle_sql = info[0] cursor = db.cursor() oracle_sql = oracle_sql.replace('?', user, 1) oracle_sql = oracle_sql.replace('?', '%s%d' % (table_index, task_id), 1) oracle_sql = oracle_sql.replace('int_number', '%d' % (index)) oracle_sql = oracle_sql.replace('float_number', '%5f' % (float(index))) oracle_sql = oracle_sql.replace('uuid', "'%s'" % uuid1()) oracle_sql = oracle_sql.replace('insert_time', "'%s'" % ( time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))) cursor.execute(oracle_sql) except Exception as e: write_log_text('ERROR：%s' % e) finally: index += 1 write_log_text('(%s%d)插入%d条数据' % (table_index, task_id, insert_number)) db.commit() time.sleep(float(info[1])) except Exception as s: write_log_text('ERROR：%s' % s) else: while 1: try: time.sleep(float(info[1])) oracle_sql = info[0] cursor = db.cursor() oracle_sql = oracle_sql.replace('?', user, 1) oracle_sql = oracle_sql.replace('?', '%s%d' % (table_index, task_id), 1) # write_log_text(oracle_sql) cursor.execute(oracle_sql) except Exception as e: write_log_text('ERROR：%s' % e) finally: if info[2] == 'update': write_log_text('(%s%d).更新%s条数据' % (table_index, task_id, cursor.rowcount)) if info[2] == 'delete': write_log_text('(%s%d).删除%s条数据' % (table_index, task_id, cursor.rowcount)) db.commit() for x in oracle_list: run_task = Thread(target=oracle_task, args=(task_id, x)) run_task.start() global mysql_sql,table_number_start, table_number_end,sqlserver_sql,oracle_sql,index if db_type == 'Mysql': index = 0 try: db = pymysql.connect(host=ip, port=int(port), user=user, passwd=password, db=dbname, charset=charset) if action == 'truncate_tmp': table_number_end = table_number_start for i in range(table_number_start, table_number_end + 1): try: index_tmp_all = 0 index_tmp_1 = 0 index_tmp_2 = 0 index_tmp_3 = 0 index_tmp_2_i = 0 index_tmp_2_u = 0 index_tmp_2_d = 0 cursor = db.cursor() if action == 'touch': if checkblob == 1: mysql_sql = mysql_sql elif checkblob == 0: mysql_sql = mysql_sql_no_blob touchsql = mysql_sql.replace('?', dbname, 1) touchsql = touchsql.replace('?', '%s%d' % (table_index, i), 1) cursor.execute(touchsql) write_log_text('正在创建表 -- `%s`.`%s%d`' % (dbname, table_index, i)) elif action == 'drop': sql = """DROP TABLE `%s`.`%s%d`""" % (dbname, table_index, i) cursor.execute(sql) write_log_text('正在删除表 -- `%s`.`%s%d`' % (dbname, table_index, i)) elif action == 'delete': sql = """delete from `%s`.`%s%d`""" % (dbname, table_index, i) cursor.execute(sql) write_log_text('正在清空表 -- `%s`.`%s%d`' % (dbname, table_index, i)) elif action == 'truncate': sql = """TRUNCATE TABLE `%s`.`%s%d`""" % (dbname, table_index, i) cursor.execute(sql) write_log_text('正在重建表 -- `%s`.`%s%d`' % (dbname, table_index, i)) elif action == 'select': sql = """select count(*) from `%s`.`%s%d`""" % (dbname, table_index, i) cursor.execute(sql) info = cursor.fetchall() index = index + int(info[0][0]) write_log_text('正在查询表 -- `%s`.`%s%d` 数据量:%s' % (dbname, table_index, i, info[0][0])) elif action == 'truncate_tmp': sql = """show tables""" cursor.execute(sql) info = cursor.fetchall() for i in info: if 'ds_' in i[0] or 'DS_' in i[0]: sql = """truncate table %s""" % i[0] cursor.execute(sql) write_log_text('重建临时表 -- `%s`.`%s`' % (dbname, i[0])) elif action == 'select_tmp': sql = """show tables""" cursor.execute(sql) info = cursor.fetchall() for i in info: if 'ds_' in i[0] or 'DS_' in i[0]: sql = """select count(*) from `%s`.`%s`""" % (dbname, i[0]) cursor.execute(sql) info = cursor.fetchall() index_tmp_all = index_tmp_all + int(info[0][0]) sql = """select count(*) from `%s`.`%s` where DS_runStatus='1'""" % (dbname, i[0]) cursor.execute(sql) info = cursor.fetchall() index_tmp_1 = index_tmp_1 + int(info[0][0]) sql = """select count(*) from `%s`.`%s` where DS_runStatus='2'""" % (dbname, i[0]) cursor.execute(sql) info = cursor.fetchall() index_tmp_2 = index_tmp_2 + int(info[0][0]) sql = """select count(*) from `%s`.`%s` where DS_runStatus='3'""" % (dbname, i[0]) cursor.execute(sql) info = cursor.fetchall() index_tmp_3 = index_tmp_3 + int(info[0][0]) sql = """select count(*) from `%s`.`%s` where DS_runStatus='2' and DS_ACTIONE='I'""" % ( dbname, i[0]) cursor.execute(sql) info = cursor.fetchall() index_tmp_2_i = index_tmp_2_i + int(info[0][0]) sql = """select count(*) from `%s`.`%s` where DS_runStatus='2' and DS_ACTIONE='U'""" % ( dbname, i[0]) cursor.execute(sql) info = cursor.fetchall() index_tmp_2_u = index_tmp_2_u + int(info[0][0]) sql = """select count(*) from `%s`.`%s` where DS_runStatus='2' and DS_ACTIONE='D'""" % ( dbname, i[0]) cursor.execute(sql) info = cursor.fetchall() index_tmp_2_d = index_tmp_2_d + int(info[0][0]) # write_log_text('正在查询临时表 -- `%s`.`%s` 数据量:%s' % (dbname, i[0], info[0][0])) elif action == 'checkdata': src_list = [] dst_list = [] src_sql = """select id from %s.%s%d""" % (check_srcdb,table_index, i) cursor.execute(src_sql) src_info = cursor.fetchall() for t in src_info: src_list.append(t[0]) dst_sql = """select id from %s.%s%d""" % (check_dstdb,table_index, i) cursor.execute(dst_sql) dst_info = cursor.fetchall() for t in dst_info: dst_list.append(t[0]) if len(src_list) != len(dst_list): in_src = [x for x in src_list if x not in dst_list] # 在src列表中而不在dst列表中 in_dst = [y for y in dst_list if y not in src_list] if len(in_src)!= 0: write_log_text('%s.%s%d 总量:%d %s.%s%d 总量:%d\n%s.%s%d缺失数据id为:%s' %(check_srcdb,table_index, i,len(src_list),check_dstdb,table_index, i,len(dst_list),check_dstdb,table_index, i,str(in_src))) if len(in_dst)!= 0: write_log_text('%s.%s%d 总量:%d %s.%s%d 总量:%d\n%s.%s%d缺失数据id为:%s' %(check_dstdb,table_index, i,len(dst_list),check_dstdb,table_index, i,len(src_list),check_srcdb,table_index, i,str(in_dst))) else: write_log_text('%s.%s%d -- %s.%s%d 数据一致 总量:%d' %(check_srcdb,table_index, i,check_dstdb,table_index, i,len(dst_list))) elif action == 'stability_test': task = Thread(target=mysql_run, args=(i,)) task.start() db.commit() except Exception as e: write_log_text('ERROR：%s' % e) except Exception as e: write_log_text('ERROR：%s' % e) if 'select' == action: write_log_text('数据总量:%d' % index) if 'select_tmp' == action: write_log_text('临时表数据总量:%d 状态1:%d 状态2:%d 状态3:%d 2_I:%d 2_U:%d 2_D:%d' % (index_tmp_all, index_tmp_1, index_tmp_2, index_tmp_3,index_tmp_2_i, index_tmp_2_u, index_tmp_2_d)) elif database_typeChosen.get() == 'Sqlserver': try: db = pymssql.connect(ip, user, password, port=int(port), database=dbname, charset=charset) if action == 'truncate_tmp': table_number_end = table_number_start index = 0 for i in range(table_number_start, table_number_end + 1): try: index_tmp_all = 0 index_tmp_1 = 0 index_tmp_2 = 0 index_tmp_3 = 0 index_tmp_2_i = 0 index_tmp_2_u = 0 index_tmp_2_d = 0 cursor = db.cursor() if action == 'touch': if checkblob == 1: touchsql = sqlserver_sql elif checkblob == 0: touchsql = sqlserver_sql_no_blob touchsql = touchsql.replace('?', '%s%d' % (table_index, i), 1) cursor.execute(touchsql) write_log_text('正在创建表 -- %s.%s%d' % (dbname, table_index, i)) elif action == 'drop': sql = """DROP TABLE %s%d""" % (table_index, i) cursor.execute(sql) write_log_text('正在删除表 -- %s.%s%d' % (dbname, table_index, i)) elif action == 'truncate': sql = """TRUNCATE TABLE %s%d""" % (table_index, i) cursor.execute(sql) write_log_text('正在重建表 -- %s.%s%d' % (dbname, table_index, i)) elif action == 'delete': sql = """delete from %s%d""" % (table_index, i) cursor.execute(sql) write_log_text('正在清空表 -- %s.%s%d' % (dbname, table_index, i)) elif action == 'select': sql = """select count(*) from %s%d""" % (table_index, i) cursor.execute(sql) info = cursor.fetchall() index = index + int(info[0][0]) write_log_text('正在查询表 -- %s.%s%d 数据量:%s' % (dbname,table_index, i, info[0][0])) elif action == 'truncate_tmp': sql = """select name from sysobjects where xtype='u'""" cursor.execute(sql) info = cursor.fetchall() for i in info: if 'ds_' in i[0] or 'DS_' in i[0]: sql = """truncate table %s""" % i[0] cursor.execute(sql) write_log_text('重建临时表 -- %s' % (i[0])) elif action == 'select_tmp': sql = """select name from sysobjects where xtype='u'""" cursor.execute(sql) info = cursor.fetchall() for i in info: if 'ds_' in i[0] or 'DS_' in i[0]: sql = """select count(*) from %s""" % (i[0]) cursor.execute(sql) info = cursor.fetchall() index_tmp_all = index_tmp_all + int(info[0][0]) sql = """select count(*) from %s where DS_runStatus='1'""" % (i[0]) cursor.execute(sql) info = cursor.fetchall() index_tmp_1 = index_tmp_1 + int(info[0][0]) sql = """select count(*) from %s where DS_runStatus='2'""" % (i[0]) cursor.execute(sql) info = cursor.fetchall() index_tmp_2 = index_tmp_2 + int(info[0][0]) sql = """select count(*) from %s where DS_runStatus='3'""" % (i[0]) cursor.execute(sql) info = cursor.fetchall() index_tmp_3 = index_tmp_3 + int(info[0][0]) sql = """select count(*) from %s where DS_runStatus='2' and DS_ACTIONE='I'""" % (i[0]) cursor.execute(sql) info = cursor.fetchall() index_tmp_2_i = index_tmp_2_i + int(info[0][0]) sql = """select count(*) from %s where DS_runStatus='2' and DS_ACTIONE='U'""" % (i[0]) cursor.execute(sql) info = cursor.fetchall() index_tmp_2_u = index_tmp_2_u + int(info[0][0]) sql = """select count(*) from %s where DS_runStatus='2' and DS_ACTIONE='D'""" % i[0] cursor.execute(sql) info = cursor.fetchall() index_tmp_2_d = index_tmp_2_d + int(info[0][0]) # write_log_text('正在查询临时表 -- `%s`.`%s` 数据量:%s' % (dbname, i[0], info[0][0])) elif action == 'checkdata': src_list = [] dst_list = [] src_sql = """select id from %s.%s%d""" % (check_srcdb,table_index, i) cursor.execute(src_sql) src_info = cursor.fetchall() for t in src_info: src_list.append(t[0]) dst_sql = """select id from %s.%s%d""" % (check_dstdb,table_index, i) cursor.execute(dst_sql) dst_info = cursor.fetchall() for t in dst_info: dst_list.append(t[0]) if len(src_list) != len(dst_list): in_src = [x for x in src_list if x not in dst_list] # 在src列表中而不在dst列表中 in_dst = [y for y in dst_list if y not in src_list] if len(in_src)!= 0: write_log_text('%s.%s%d 总量:%d %s.%s%d 总量:%d\n%s.%s%d缺失数据id为:%s' %(check_srcdb,table_index, i,len(src_list),check_dstdb,table_index, i,len(dst_list),check_dstdb,table_index, i,str(in_src))) if len(in_dst)!= 0: write_log_text('%s.%s%d 总量:%d %s.%s%d 总量:%d\n%s.%s%d缺失数据id为:%s' %(check_dstdb,table_index, i,len(dst_list),check_dstdb,table_index, i,len(src_list),check_srcdb,table_index, i,str(in_dst))) else: write_log_text('%s.%s%d -- %s.%s%d 数据一致 总量:%d' %(check_srcdb,table_index, i,check_dstdb,table_index, i,len(dst_list))) elif action == 'stability_test': task = Thread(target=sqlserver_run, args=(i,)) task.start() db.commit() except Exception as e: write_log_text('ERROR：%s' % e) if 'select' == action: write_log_text('数据总量:%d' % index) if 'select_tmp' == action: write_log_text('临时表数据总量:%d 状态1:%d 状态2:%d 状态3:%d 2_I:%d 2_U:%d 2_D:%d' % ( index_tmp_all, index_tmp_1, index_tmp_2, index_tmp_3, index_tmp_2_i, index_tmp_2_u, index_tmp_2_d)) except Exception as e: write_log_text('ERROR：%s' % e) elif database_typeChosen.get() == 'Oracle': try: db = pyoracle.connect(user, password, ip + ':' + str(port) + '/' + dbname) index = 0 if action == 'truncate_tmp': table_number_end = table_number_start for i in range(table_number_start, table_number_end + 1): try: index_tmp_all = 0 index_tmp_1 = 0 index_tmp_2 = 0 index_tmp_3 = 0 index_tmp_2_i = 0 index_tmp_2_u = 0 index_tmp_2_d = 0 cursor = db.cursor() if action == 'touch': touchsql = oracle_sql.replace('?', user, 1) touchsql = touchsql.replace('?', '%s%d' % (table_index, i), 1) cursor.execute(touchsql) write_log_text('正在创建表 -- %s.%s%d' % (user, table_index, i)) elif action == 'drop': sql = """DROP TABLE %s.%s%d""" % (user, table_index, i) cursor.execute(sql) write_log_text('正在删除表 -- %s.%s%d' % (user, table_index, i)) elif action == 'truncate': sql = """TRUNCATE TABLE %s.%s%d""" % (user, table_index, i) cursor.execute(sql) write_log_text('正在重建表 -- %s.%s%d' % (user, table_index, i)) elif action == 'delete': sql = """delete from %s.%s%d""" % (user, table_index, i) cursor.execute(sql) write_log_text('正在清空表 -- %s.%s%d' % (user, table_index, i)) elif action == 'truncate_tmp': sql = """select table_name from all_tables where owner='%s'"""%user.upper() cursor.execute(sql) info = cursor.fetchall() for i in info: if 'ds_' in i[0] or 'DS_' in i[0]: sql = """truncate table %s.%s""" % (user.upper(),i[0]) cursor.execute(sql) write_log_text('重建临时表 -- %s.%s' % (user, i[0])) elif action == 'select_tmp': sql = """select table_name from all_tables where owner='%s'"""%user.upper() cursor.execute(sql) info = cursor.fetchall() for i in info: if 'ds_' in i[0] or 'DS_' in i[0]: sql = """select count(*) from %s.%s""" % (user, i[0]) cursor.execute(sql) info = cursor.fetchall() index_tmp_all = index_tmp_all + int(info[0][0]) sql = """select count(*) from %s.%s where DS_runStatus='1'""" % (user, i[0]) cursor.execute(sql) info = cursor.fetchall() index_tmp_1 = index_tmp_1 + int(info[0][0]) sql = """select count(*) from %s.%s where DS_runStatus='2'""" % (user, i[0]) cursor.execute(sql) info = cursor.fetchall() index_tmp_2 = index_tmp_2 + int(info[0][0]) sql = """select count(*) from %s.%s where DS_runStatus='3'""" % (user, i[0]) cursor.execute(sql) info = cursor.fetchall() index_tmp_3 = index_tmp_3 + int(info[0][0]) sql = """select count(*) from %s.%s where DS_runStatus='2' and DS_ACTIONE='I'""" % ( user, i[0]) cursor.execute(sql) info = cursor.fetchall() index_tmp_2_i = index_tmp_2_i + int(info[0][0]) sql = """select count(*) from %s.%s where DS_runStatus='2' and DS_ACTIONE='U'""" % ( user, i[0]) cursor.execute(sql) info = cursor.fetchall() index_tmp_2_u = index_tmp_2_u + int(info[0][0]) sql = """select count(*) from %s.%s where DS_runStatus='2' and DS_ACTIONE='D'""" % ( user, i[0]) cursor.execute(sql) info = cursor.fetchall() index_tmp_2_d = index_tmp_2_d + int(info[0][0]) # write_log_text('正在查询临时表 -- `%s`.`%s` 数据量:%s' % (dbname, i[0], info[0][0])) elif action == 'checkdata': src_list = [] dst_list = [] src_sql = """select id from %s.%s%d""" % (check_srcdb, table_index, i) cursor.execute(src_sql) src_info = cursor.fetchall() for t in src_info: src_list.append(t[0]) dst_sql = """select id from %s.%s%d""" % (check_dstdb, table_index, i) cursor.execute(dst_sql) dst_info = cursor.fetchall() for t in dst_info: dst_list.append(t[0]) if len(src_list) != len(dst_list): in_src = [x for x in src_list if x not in dst_list] # 在src列表中而不在dst列表中 in_dst = [y for y in dst_list if y not in src_list] if len(in_src) != 0: write_log_text('%s.%s%d 总量:%d %s.%s%d 总量:%d\n%s.%s%d缺失数据id为:%s' % ( check_srcdb, table_index, i, len(src_list), check_dstdb, table_index, i, len(dst_list), check_dstdb, table_index, i, str(in_src))) if len(in_dst) != 0: write_log_text('%s.%s%d 总量:%d %s.%s%d 总量:%d\n%s.%s%d缺失数据id为:%s' % ( check_dstdb, table_index, i, len(dst_list), check_dstdb, table_index, i, len(src_list), check_srcdb, table_index, i, str(in_dst))) else: write_log_text('%s.%s%d -- %s.%s%d 数据一致 总量:%d' % ( check_srcdb, table_index, i, check_dstdb, table_index, i, len(dst_list))) elif action == 'select': sql = """select count(*) from %s.%s%d""" % (user, table_index, i) cursor.execute(sql) info = cursor.fetchall() index = index + int(info[0][0]) write_log_text('正在查询表 -- %s.%s%d 数据量:%s' % (user, table_index, i, info[0][0])) elif action == 'stability_test': task = Thread(target=oracle_run, args=(i,)) task.start() db.commit() except Exception as e: write_log_text('ERROR：%s' % e) except Exception as e: write_log_text('ERROR：%s' % e) if 'select' == action: write_log_text('数据总量:%d' % index) if 'select_tmp' == action: write_log_text('临时表数据总量:%d 状态1:%d 状态2:%d 状态3:%d 2_I:%d 2_U:%d 2_D:%d' % ( index_tmp_all, index_tmp_1, index_tmp_2, index_tmp_3, index_tmp_2_i, index_tmp_2_u, index_tmp_2_d)) log_text.see(END) if 'select_tmp' in action: def task(): while 1: run() time.sleep(1) if run_flag == 'stop': break T = Thread(target=task) T.start() else: T = Thread(target=run) T.start()def select_port(*args): #自动选择对应端口 if database_typeChosen.get() == 'Mysql': database_portChosen.current(0) elif database_typeChosen.get() == 'Sqlserver': database_portChosen.current(1) elif database_typeChosen.get() == 'Oracle': database_portChosen.current(2) db_nameChosen.current(2)def show_win(*args): if action_typeChosen.get() == 'touch': blob_show.pack() sql_windows.pack() l_check_db.pack_forget() check_src_dbChosen.pack_forget() check_dst_dbChosen.pack_forget() stability_windows.pack_forget() stabilityconfig_insert.pack_forget() stabilityconfig_update.pack_forget() stabilityconfig_delete.pack_forget() elif action_typeChosen.get() == 'checkdata': l_check_db.pack() check_src_dbChosen.pack(side=LEFT, padx=12) check_dst_dbChosen.pack(side=LEFT, padx=12) blob_show.pack_forget() sql_windows.pack_forget() stability_windows.pack_forget() stabilityconfig_insert.pack_forget() stabilityconfig_update.pack_forget() stabilityconfig_delete.pack_forget() elif action_typeChosen.get() == 'stability_test': log_info.pack_forget() stabilityconfig_insert.pack(expand=YES) stabilityconfig_update.pack(expand=YES) stabilityconfig_delete.pack(expand=YES) log_info.pack(padx=25, expand=YES, fill=X) stability_windows.pack() l_check_db.pack_forget() check_src_dbChosen.pack_forget() check_dst_dbChosen.pack_forget() blob_show.pack_forget() sql_windows.pack_forget() else: blob_show.pack_forget() sql_windows.pack_forget() l_check_db.pack_forget() check_src_dbChosen.pack_forget() check_dst_dbChosen.pack_forget() stability_windows.pack_forget() stabilityconfig_insert.pack_forget() stabilityconfig_update.pack_forget() stabilityconfig_delete.pack_forget()def write_log_text(info): log_text.insert('insert','(%s)'%time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) +str(info)+'\n')def clear_log_text(): log_text.delete("0.0", "end")def stop_select(): def stop_task(): global run_flag for i in range(500): run_flag = 'stop' time.sleep(0.01) T = Thread(target=stop_task) T.start()def run_select(): global run_flag run_flag = 'run'def touch_windows(): global mysql_sql,mysql_sql_no_blob,sqlserver_sql_no_blob,sqlserver_sql,oracle_sql_no_blob,oracle_sql touch_windows = Tk() touch_windows.title('SQL语句框') touch_windows.geometry('400x830') touch_windows.maxsize(400, 830) touch_windows.minsize(400, 830) def save_sql(): global mysql_sql, mysql_sql_no_blob, sqlserver_sql_no_blob, sqlserver_sql, oracle_sql_no_blob, oracle_sql mysql_sql_no_blob = mysql_sql_noblog_text.get('1.0','end-1c') mysql_sql = mysql_sql_noblog_text.get('1.0','end-1c') sqlserver_sql_no_blob = sqlserver_sql_noblob_text.get('1.0','end-1c') sqlserver_sql = sqlserver_sql_text.get('1.0','end-1c') oracle_sql_no_blob = oracle_sql_noblob_text.get('1.0','end-1c') oracle_sql = oracle_sql_text.get('1.0','end-1c') def resrt_sql(): mysql_sql_bak = """CREATE TABLE `?`.`?` ( `id` bigint(20) NOT NULL, `floatnumbers` float(10,5) NULL, `names` varchar(100) NULL, `namevchars` varchar(100) NULL, `times` datetime NULL, `payloads` longblob NULL, PRIMARY KEY (`id`) )""" """ sqlserver创表的语句 其中数据库名称和表名称使用?代替即可 """ sqlserver_sql_bak = """CREATE TABLE ? ( id numeric(20) not NULL, floatnumbers float(10) NULL, names char(100) NULL, namevchars varchar(100) NULL, times datetime NULL, payloads varbinary(max) NULL, PRIMARY KEY (id) )""" """ oracle创表的语句 其中数据库名称和表名称使用?代替即可 """ oracle_sql_bak = """CREATE TABLE ?.? ( id numeric(20) not NULL, floatnumbers NUMBER NULL, names char(100) NULL, namevchars varchar2(100) NULL, times date NULL, payloads blob NULL, PRIMARY KEY (id) )""" mysql_sql_no_blob_bak = """CREATE TABLE `?`.`?` ( `id` bigint(20) NOT NULL, `floatnumbers` float(10,5) NULL, `names` varchar(100) NULL, `namevchars` varchar(100) NULL, `times` datetime NULL, PRIMARY KEY (`id`) )""" sqlserver_sql_no_blob_bak = """CREATE TABLE ? ( id numeric(20) not NULL, floatnumbers float(10) NULL, names char(100) NULL, namevchars varchar(100) NULL, times datetime NULL, PRIMARY KEY (id) )""" oracle_sql_no_blob_bak = """CREATE TABLE ?.? ( id numeric(20) not NULL, floatnumbers NUMBER NULL, names char(100) NULL, namevchars varchar2(100) NULL, times date NULL, PRIMARY KEY (id) )""" mysql_sql_noblog_text.delete('1.0','end-1c') mysql_sql_text.delete('1.0','end-1c') sqlserver_sql_noblob_text.delete('1.0','end-1c') sqlserver_sql_text.delete('1.0','end-1c') oracle_sql_noblob_text.delete('1.0','end-1c') oracle_sql_text.delete('1.0','end-1c') mysql_sql_noblog_text.insert('insert',mysql_sql_no_blob_bak) mysql_sql_text.insert('insert', mysql_sql_bak) sqlserver_sql_noblob_text.insert('insert', sqlserver_sql_no_blob_bak) sqlserver_sql_text.insert('insert', sqlserver_sql_bak) oracle_sql_noblob_text.insert('insert', oracle_sql_no_blob_bak) oracle_sql_text.insert('insert', oracle_sql_bak) save_sql() button_frame = Frame(touch_windows) Button(button_frame, text='保存SQL', width=10,command=save_sql).pack(side=LEFT) Button(button_frame, text='恢复SQL', width=10,command=resrt_sql).pack(side=LEFT) button_frame.pack() sql_frame = Frame(touch_windows) l_mysql_sql_noblog = Label(sql_frame, text="Mysql无大字段创表语句:", width=30, height=1) l_mysql_sql_noblog.pack() mysql_sql_noblog_text = ScrolledText(sql_frame, width=50, height=8) mysql_sql_noblog_text.insert(INSERT, mysql_sql_no_blob) mysql_sql_noblog_text.pack() sql_frame.pack() l_mysql_sql = Label(sql_frame, text="Mysql大字段创表语句:", width=40, height=1) l_mysql_sql.pack() mysql_sql_text = ScrolledText(sql_frame, width=50, height=8) mysql_sql_text.insert(INSERT, mysql_sql) mysql_sql_text.pack() sql_frame.pack() l_sqlserver_sql_noblob = Label(sql_frame, text="Sqlserver无大字段创表语句:", width=40, height=1) l_sqlserver_sql_noblob.pack() sqlserver_sql_noblob_text = ScrolledText(sql_frame, width=50, height=8) sqlserver_sql_noblob_text.insert(INSERT, sqlserver_sql_no_blob) sqlserver_sql_noblob_text.pack() sql_frame.pack() l_sqlserver_sql = Label(sql_frame, text="Sqlserver大字段创表语句:", width=40, height=1) l_sqlserver_sql.pack() sqlserver_sql_text = ScrolledText(sql_frame, width=50, height=8) sqlserver_sql_text.insert(INSERT, sqlserver_sql) sqlserver_sql_text.pack() sql_frame.pack() l_oracle_sql_noblob = Label(sql_frame, text="Oracle无大字段创表语句:", width=40, height=1) l_oracle_sql_noblob.pack() oracle_sql_noblob_text = ScrolledText(sql_frame, width=50, height=8) oracle_sql_noblob_text.insert(INSERT, oracle_sql_no_blob) oracle_sql_noblob_text.pack() sql_frame.pack() l_oracle_sql = Label(sql_frame, text="Oracle大字段创表语句:", width=40, height=1) l_oracle_sql.pack() oracle_sql_text = ScrolledText(sql_frame, width=50, height=8) oracle_sql_text.insert(INSERT, oracle_sql) oracle_sql_text.pack() sql_frame.pack() touch_windows.mainloop() def stability_windows(): stability_windows = Tk() stability_windows.title('SQL语句框') stability_windows.geometry('1200x400') stability_windows.maxsize(1200, 400) stability_windows.minsize(1200, 400) def save_sql(): global mysql_insert_sql,mysql_update_sql,mysql_delete_sql global sqlserver_insert_sql,sqlserver_update_sql,sqlserver_delete_sql global oracle_delete_sql,oracle_update_sql,oracle_insert_sql mysql_insert_sql = mysql_insert_text.get('1.0','end-1c') mysql_update_sql = mysql_update_text.get('1.0','end-1c') mysql_delete_sql = mysql_delete_text.get('1.0','end-1c') sqlserver_insert_sql = sqlserver_insert_text.get('1.0', 'end-1c') sqlserver_update_sql = sqlserver_update_text.get('1.0', 'end-1c') sqlserver_delete_sql = sqlserver_delete_text.get('1.0', 'end-1c') oracle_insert_sql = oracle_insert_text.get('1.0', 'end-1c') oracle_update_sql = oracle_update_text.get('1.0', 'end-1c') oracle_delete_sql = oracle_delete_text.get('1.0', 'end-1c') def reset_sql(): global mysql_insert_sql,mysql_update_sql,mysql_delete_sql global sqlserver_insert_sql,sqlserver_update_sql,sqlserver_delete_sql global oracle_delete_sql,oracle_update_sql,oracle_insert_sql mysql_insert_sql = """INSERT INTO `?`.`?` VALUES (int_number,float_number,uuid,uuid,insert_time)""" mysql_update_sql = """update `?`.`?` set `names`='updated' where ID%2=0 and `names` !='updated' and `times` > (select date_sub(now(), interval 60 second))""" mysql_delete_sql = """delete from `?`.`?` where `times` < (select date_sub(now(), interval 180 second))""" sqlserver_insert_sql = """INSERT INTO ? VALUES (int_number,float_number,uuid,uuid,insert_time,0x1234567890)""" sqlserver_update_sql = """update ? set names='updated' where ID%2=0 and names !='updated' and times > (select DATEADD(second, -60, GETDATE()))""" sqlserver_delete_sql = """delete from ? where times < (select DATEADD(second, -60, GETDATE()))""" oracle_insert_sql = """INSERT INTO ?.? VALUES (int_number,float_number,uuid,uuid,to_date (insert_time,'yyyy-mm-dd hh24:mi:ss'),HEXTORAW(2345678987654))""" oracle_update_sql = """update ?.? set NAMES='updated' where mod(ID,2) = 0 and NAMES !='updated' and TIMES > (select sysdate-60/86400 from dual)""" oracle_delete_sql = """delete from ?.? where times < (select sysdate-60/86400 from dual)""" mysql_insert_text.delete('1.0', 'end-1c') mysql_update_text.delete('1.0', 'end-1c') mysql_delete_text.delete('1.0', 'end-1c') sqlserver_insert_text.delete('1.0', 'end-1c') sqlserver_update_text.delete('1.0', 'end-1c') sqlserver_delete_text.delete('1.0', 'end-1c') oracle_insert_text.delete('1.0', 'end-1c') oracle_update_text.delete('1.0', 'end-1c') oracle_delete_text.delete('1.0', 'end-1c') mysql_insert_sql = mysql_insert_text.insert('insert',mysql_insert_sql) mysql_update_sql = mysql_update_text.insert('insert',mysql_update_sql) mysql_delete_sql = mysql_delete_text.insert('insert',mysql_delete_sql) sqlserver_insert_sql = sqlserver_insert_text.insert('insert',sqlserver_insert_sql) sqlserver_update_sql = sqlserver_update_text.insert('insert',sqlserver_update_sql) sqlserver_delete_sql = sqlserver_delete_text.insert('insert',sqlserver_delete_sql) oracle_insert_sql = oracle_insert_text.insert('insert',oracle_insert_sql) oracle_update_sql = oracle_update_text.insert('insert',oracle_update_sql) oracle_delete_sql = oracle_delete_text.insert('insert',oracle_delete_sql) save_sql() sql_frame = Frame(stability_windows) l_mysql_insert = Label(sql_frame, text="Mysql插入语句:", width=20, height=1) l_mysql_insert.pack() mysql_insert_text = Text(sql_frame, width=1200, height=1) mysql_insert_text.insert(INSERT,mysql_insert_sql) mysql_insert_text.pack() l_mysql_update = Label(sql_frame, text="Mysql更新语句:", width=20, height=1) l_mysql_update.pack() mysql_update_text = Text(sql_frame, width=1200, height=1) mysql_update_text.insert(INSERT, mysql_update_sql) mysql_update_text.pack() l_mysql_delete = Label(sql_frame, text="Mysql删除语句:", width=20, height=1) l_mysql_delete.pack() mysql_delete_text = Text(sql_frame, width=1200, height=1) mysql_delete_text.insert(INSERT, mysql_delete_sql) mysql_delete_text.pack() l_sqlserver_insert = Label(sql_frame, text="Sqlserver插入语句:", width=20, height=1) l_sqlserver_insert.pack() sqlserver_insert_text = Text(sql_frame, width=1200, height=1) sqlserver_insert_text.insert(INSERT,sqlserver_insert_sql) sqlserver_insert_text.pack() l_sqlserver_update = Label(sql_frame, text="Sqlserver更新语句:", width=20, height=1) l_sqlserver_update.pack() sqlserver_update_text = Text(sql_frame, width=1200, height=1) sqlserver_update_text.insert(INSERT, sqlserver_update_sql) sqlserver_update_text.pack() l_sqlserver_delete = Label(sql_frame, text="Sqlserver删除语句:", width=20, height=1) l_sqlserver_delete.pack() sqlserver_delete_text = Text(sql_frame, width=1200, height=1) sqlserver_delete_text.insert(INSERT, sqlserver_delete_sql) sqlserver_delete_text.pack() l_oracle_insert = Label(sql_frame, text="Oracle插入语句:", width=20, height=1) l_oracle_insert.pack() oracle_insert_text = Text(sql_frame, width=1200, height=1) oracle_insert_text.insert(INSERT, oracle_insert_sql) oracle_insert_text.pack() l_oracle_update = Label(sql_frame, text="Oracle更新语句:", width=20, height=1) l_oracle_update.pack() oracle_update_text = Text(sql_frame, width=1200, height=1) oracle_update_text.insert(INSERT, oracle_update_sql) oracle_update_text.pack() l_oracle_delete = Label(sql_frame, text="Oracle删除语句:", width=20, height=1) l_oracle_delete.pack() oracle_delete_text = Text(sql_frame, width=1200, height=1) oracle_delete_text.insert(INSERT, oracle_delete_sql) oracle_delete_text.pack() button_frame = Frame(stability_windows) Button(button_frame, text='保存SQL', width=10,command=save_sql).pack(side=LEFT) Button(button_frame, text='恢复SQL', width=10,command=reset_sql).pack(side=LEFT) button_frame.pack() sql_frame.pack() stability_windows.mainloop()def add_rule(): def add_task(): global src_db_type,dst_db_type,tab_name_src,tab_name_dst ip,user,pwd = dut_ip_typeChosen.get(),dut_user_typeChosen.get(),dut_password_typeChosen.get() rule_number,table_number,rule_name = int(rule_number_typeChosen.get()),int(talebs_number_typeChosen.get()),rule_name_typeChosen.get() src_ip,dst_ip = src_ip_typeChosen.get(),dst_ip_typeChosen.get() src_port,dst_port = src_port_typeChosen.get(),dst_port_typeChosen.get() src_user,dst_user = src_user_typeChosen.get(),dst_user_typeChosen.get() src_pwd,dst_pwd = src_password_typeChosen.get(),dst_password_typeChosen.get() tab_name_src,tab_name_dst = src_tabelsinfo_typeChosen.get(),dst_tabelsinfo_typeChosen.get() src_dbname,dst_dbname = src_dbinfo_typeChosen.get(),dst_dbinfo_typeChosen.get() src_db_type,dst_db_type = src_dbtype_typeChosen.get(),dst_dbtype_typeChosen.get() if src_pwd == 'test': src_pwd = 'QzhlbDNVRGlZWEVDWWRHVkJIQ0gwemRBZUpUTkxUSVhtT2da' elif src_pwd == 'zss': src_pwd = 'RFg3YmJKT1JCaU9pVWVuTmhTUTRyelVaYlZRTElYeENrYQ' elif src_pwd == 'Anmit_2005': src_pwd = 'ZERVYVZTUHFpUkltd1FXNVVhYk1LdGFYUmZNakF3TlFOSmVMRldXVW5oSlo' if dst_pwd == 'test': dst_pwd = 'QzhlbDNVRGlZWEVDWWRHVkJIQ0gwemRBZUpUTkxUSVhtT2da' elif dst_pwd == 'zss': dst_pwd = 'RFg3YmJKT1JCaU9pVWVuTmhTUTRyelVaYlZRTElYeENrYQ' elif dst_pwd == 'Anmit_2005': dst_pwd = 'ZERVYVZTUHFpUkltd1FXNVVhYk1LdGFYUmZNakF3TlFOSmVMRldXVW5oSlo' if '内到外' == direction_typeChosen.get(): direction = 0 elif '外到内' == direction_typeChosen.get(): direction = 1 else:direction = 0 class Add_DataBase(): def __init__(self): self.ip = ip self.user = user self.pwd = pwd self.agent = 'Mozilla/5.0 (Windows NT 6.3; Win64; x64; rv:77.0) Gecko/20100101 Firefox/77.0' url_mainbody = 'https://%s/login/mainbody' % self.ip headers = {'User-Agent': self.agent} try: rsp = requests.get(url_mainbody, headers=headers, verify=False) mytoken = re.findall('\w{32}', rsp.text) mytoken = mytoken[0].encode('utf-8') login_cookie = re.findall(r'(?:\s)su.*?(?:;)', rsp.headers['Set-Cookie'])[-1].strip(';').strip() headers = {'User-Agent': self.agent, 'Cookie': login_cookie} url_login = 'https://%s/login/login_confirm' % self.ip pwd1 = hashlib.md5(self.pwd.encode('utf-8')).hexdigest() pwd2 = hashlib.sha1(pwd1.encode('utf-8')) pwd2 = pwd2.hexdigest() file_list = {"mytoken": (None, mytoken), "user": (None, self.user), "textname": (None, ''), "passwd": (None, pwd2), "cerfile": ('', '', 'application/octet-stream')} requests.post(url=url_login, headers=headers, files=file_list, verify=False, allow_redirects=False) check_url = "https://%s/status" % self.ip check_rsp = requests.get(url=check_url, headers=headers, verify=False) cookie = re.findall(r'(?:)(sugap6_session=.*)(?:;)', str(check_rsp.headers))[0] token = re.findall(r'\w{32}', check_rsp.text)[0] self.token = token self.cookie = cookie self.headers = {'User-Agent': self.agent, 'Cookie': self.cookie, 'Host': self.ip, 'x-csrf-token': self.token} except Exception as e: write_log_text('ERROR:%s'%e) def connet_database(self, table_name): self.headers['Content-type'] = 'application/x-www-form-urlencoded' if src_db_type == 'MYSQL': src_charset = 'utf-8' elif src_db_type == 'ORACLE': src_charset = 'AL32UTF8' elif src_db_type == 'SQLSERVER': src_charset = 'GBK' if dst_db_type == 'MYSQL': dst_charset = 'utf-8' elif dst_db_type == 'ORACLE': dst_charset = 'AL32UTF8' elif dst_db_type == 'SQLSERVER': dst_charset = 'GBK' data = {"tokenid": self.token, "limit": "10", "page": "1", "t_id": "0", "name": table_name, "area": direction, "in_dbms": src_db_type, "in_dbver": "2008", "out_dbms": dst_db_type, "out_dbver": "2008", "in_dbip": src_ip, "out_dbip": dst_ip, "in_dbport": src_port, "out_dbport": dst_port, "in_dbname": src_dbname, "out_dbname": dst_dbname, "in_user": src_user, "out_user": dst_user, "in_pwd": src_pwd, "out_pwd": dst_pwd, "in_charset": src_charset, "out_charset": dst_charset} url = 'https://%s/dbsync/connect_base' % self.ip rsp = requests.post(url=url, headers=self.headers, data=data, verify=False).text t_id = re.findall(r'"t_id":(\d{13}),', rsp)[0] return t_id def select_table(self, t_id, info): self.headers['Content-type'] = 'application/json; charset=utf-8' self.headers['X-Requested-With'] = 'XMLHttpRequest' self.headers['Referer'] = 'https://%s/dbsync/new_rule' % self.ip url = 'https://%s/dbsync/select_tab_name' % self.ip data = {"t_id": t_id, "src_tab": info['src_table'], "val": info['dst_table']} requests.post(url=url, headers=self.headers, data=json.dumps(data), verify=False).text def start_table(self, t_id, info): self.headers['Content-type'] = 'application/json; charset=utf-8' url = 'https://%s/dbsync/multiple_enable_tab' % self.ip data = {"t_id": t_id, "src_tab": [info['src_table']], "status": "on"} requests.post(url=url, headers=self.headers, data=json.dumps(data), verify=False).text def change_table_info(self, t_id, info): self.headers['Content-type'] = 'application/json; charset=utf-8' self.headers['X-Requested-With'] = 'XMLHttpRequest' self.headers['Referer'] = 'https://%s/dbsync/new_rule' % self.ip url = 'https://%s/dbsync/change_tab_info' % self.ip data = {"t_id": t_id, "src_tab": info['src_table'], "tr_name": "is_refresh_trigger", "val": "on"} requests.post(url=url, headers=self.headers, data=json.dumps(data), verify=False).text def add_rule(self, t_id, table_name): self.headers['Content-type'] = 'application/x-www-form-urlencoded' if src_db_type == 'MYSQL': src_charset = 'utf-8' elif src_db_type == 'ORACLE': src_charset = 'AL32UTF8' elif src_db_type == 'SQLSERVER': src_charset = 'GBK' if dst_db_type == 'MYSQL': dst_charset = 'utf-8' elif dst_db_type == 'ORACLE': dst_charset = 'AL32UTF8' elif dst_db_type == 'SQLSERVER': dst_charset = 'GBK' data = {"tokenid": self.token, "limit": "10", "page": "1", "t_id": t_id, "name": table_name, "area": direction, "in_dbms": src_db_type, "in_dbver": "2008", "out_dbms": dst_db_type, "out_dbver": "2008", "in_dbip": src_ip, "out_dbip": dst_ip, "in_dbport": src_port, "out_dbport": dst_port, "in_dbname": src_dbname, "out_dbname": dst_dbname, "in_user": src_user, "out_user": dst_user, "in_pwd": src_pwd, "out_pwd": dst_pwd, "in_charset": src_charset, "out_charset": dst_charset} url = 'https://%s/dbsync/add_rule' % self.ip rsp = requests.post(url=url, headers=self.headers, data=data, verify=False).text write_log_text(re.findall(r'msg":"(.*)"', rsp)[0].encode('utf8').decode('unicode_escape'), ) if src_db_type.upper() == 'ORACLE': tab_name_src = tab_name_src.upper() if dst_db_type.upper() == 'ORACLE': tab_name_dst = tab_name_dst.upper() if direction == '1': tab_name_src,tab_name_dst=tab_name_dst,tab_name_src add_rule = Add_DataBase() index = 1 for i in range(1, rule_number + 1): try: databaserule_name = rule_name + '%d' % i t_id = add_rule.connet_database(databaserule_name) write_log_text('添加同步策略:%s' % databaserule_name) for t in range(1, table_number + 1): src_base = tab_name_src + '%d' % index dst_base = tab_name_dst + '%d' % index add_rule.select_table(t_id,{'src_table': src_base, 'dst_table':dst_base}) add_rule.change_table_info(t_id,{'src_table': src_base, 'dst_table':dst_base}) write_log_text('添加表策略:%s-->%s' %(src_base,dst_base)) log_text.mark_set('insert', 'end') add_rule.start_table(t_id, {'src_table': src_base}) index += 1 add_rule.add_rule(t_id, databaserule_name) except Exception as e: write_log_text('ERROR:%s'%e) T= Thread(target=add_task) T.start()def database_rule_windows(): global dut_ip_typeChosen,dut_user_typeChosen,dut_password_typeChosen,direction_typeChosen global rule_number_typeChosen,talebs_number_typeChosen,rule_name_typeChosen global src_dbinfo_typeChosen,src_dbtype_typeChosen,src_ip_typeChosen,src_password_typeChosen,src_port_typeChosen,src_tabelsinfo_typeChosen,src_user_typeChosen global dst_dbinfo_typeChosen,dst_dbtype_typeChosen,dst_ip_typeChosen,dst_password_typeChosen,dst_port_typeChosen,dst_tabelsinfo_typeChosen,dst_user_typeChosen database_rule_windows = Tk() database_rule_windows.title('数据库同步规则配置') database_rule_windows.geometry('770x450') database_rule_windows.maxsize(770, 450) database_rule_windows.minsize(770, 450) dut_info = Frame(database_rule_windows) l_dut_ip = Label(dut_info, text="设备IP:", width=8) l_dut_ip.pack(side=LEFT) dut_ip_typeChosen = ttk.Combobox(dut_info, width=15) dut_ip_typeChosen['values'] = ['192.168.1.254', '10.6.5.251', '10.6.5.240'] dut_ip_typeChosen.pack(side=LEFT) dut_ip_typeChosen.current(1) l_dut_user = Label(dut_info, text="登陆账号:", width=8) l_dut_user.pack(side=LEFT) dut_user_typeChosen = ttk.Combobox(dut_info, width=15) dut_user_typeChosen['values'] = ['superman', 'admin'] dut_user_typeChosen.pack(side=LEFT) dut_user_typeChosen.current(0) l_dut_password = Label(dut_info, text="登陆密码:", width=8) l_dut_password.pack(side=LEFT) dut_password_typeChosen = ttk.Combobox(dut_info, width=15) dut_password_typeChosen['values'] = ['talent', 'talent123','talent123!','admin'] dut_password_typeChosen.pack(side=LEFT) dut_password_typeChosen.current(2) dut_info.pack(pady=20) rule_info = Frame(database_rule_windows) l_rule_name = Label(rule_info, text="规则名称前缀:", width=15) l_rule_name.pack(side=LEFT) rule_name_typeChosen = ttk.Combobox(rule_info, width=5) rule_name_typeChosen['values'] = ['zss', 'test', 'rule'] rule_name_typeChosen.pack(side=LEFT,padx=10) rule_name_typeChosen.current(2) l_rule_number = Label(rule_info, text="创建的规则总数:", width=15) l_rule_number.pack(side=LEFT) rule_number_typeChosen = ttk.Combobox(rule_info, width=5) rule_number_typeChosen['values'] = [i for i in range(1,51)] rule_number_typeChosen.pack(side=LEFT,padx=10) rule_number_typeChosen.current(0) l_talebs_number = Label(rule_info, text="单个规则的表策略总数:", width=18) l_talebs_number.pack(side=LEFT) talebs_number_typeChosen = ttk.Combobox(rule_info, width=5) talebs_number_typeChosen['values'] = [i for i in range(1,101)] talebs_number_typeChosen.pack(side=LEFT,padx=10) talebs_number_typeChosen.current(49) l_direction = Label(rule_info, text="同步方向:", width=8) l_direction.pack(side=LEFT) direction_typeChosen = ttk.Combobox(rule_info, width=5) direction_typeChosen['values'] = ['内到外','外到内'] direction_typeChosen.pack(side=LEFT,padx=10) direction_typeChosen.current(0) rule_info.pack(pady=5) rule_fenge = Frame(database_rule_windows) l_src_fenge = Label(rule_fenge, text="----------------------源数据库信息---------------------- ----------------------目的数据库信息----------------------", width=100) l_src_fenge.pack(side=LEFT) rule_fenge.pack(pady=15) rule_dbtype = Frame(database_rule_windows) l_src_dbtype = Label(rule_dbtype, text="源库类型:", width=15) l_src_dbtype.pack(side=LEFT) src_dbtype_typeChosen = ttk.Combobox(rule_dbtype, width=15) src_dbtype_typeChosen['values'] = ['MYSQL', 'SQLSERVER', 'ORACLE'] src_dbtype_typeChosen.pack(side=LEFT,padx=10) src_dbtype_typeChosen.current(0) l_dst_dbtype = Label(rule_dbtype, text="目的库类型:", width=15) l_dst_dbtype.pack(side=LEFT,padx=10) dst_dbtype_typeChosen = ttk.Combobox(rule_dbtype, width=15) dst_dbtype_typeChosen['values'] = ['MYSQL', 'SQLSERVER', 'ORACLE'] dst_dbtype_typeChosen.pack(side=LEFT) dst_dbtype_typeChosen.current(0) rule_dbtype.pack(pady=5) rule_ip = Frame(database_rule_windows) l_src_ip = Label(rule_ip, text="源IP:", width=15) l_src_ip.pack(side=LEFT) src_ip_typeChosen = ttk.Combobox(rule_ip, width=15) src_ip_typeChosen['values'] = ['10.6.3.11', '10.6.3.222', '192.168.66.215','192.168.66.214'] src_ip_typeChosen.pack(side=LEFT,padx=10) src_ip_typeChosen.current(0) l_dst_ip = Label(rule_ip, text="目的IP:", width=15) l_dst_ip.pack(side=LEFT,padx=10) dst_ip_typeChosen = ttk.Combobox(rule_ip, width=15) dst_ip_typeChosen['values'] = ['10.6.4.11', '10.6.4.222','192.168.66.215','192.168.66.214'] dst_ip_typeChosen.pack(side=LEFT) dst_ip_typeChosen.current(0) rule_ip.pack(pady=5) rule_port = Frame(database_rule_windows) l_src_port = Label(rule_port, text="源端口:", width=15) l_src_port.pack(side=LEFT) src_port_typeChosen = ttk.Combobox(rule_port, width=15) src_port_typeChosen['values'] = ['3306','1433','1521'] src_port_typeChosen.pack(side=LEFT,padx=10) src_port_typeChosen.current(0) l_dst_port = Label(rule_port, text="目的端口:", width=15) l_dst_port.pack(side=LEFT,padx=10) dst_port_typeChosen = ttk.Combobox(rule_port, width=15) dst_port_typeChosen['values'] = ['3306','1433','1521'] dst_port_typeChosen.pack(side=LEFT) dst_port_typeChosen.current(0) rule_port.pack(pady=5) rule_user = Frame(database_rule_windows) l_src_user = Label(rule_user, text="源账号:", width=15) l_src_user.pack(side=LEFT) src_user_typeChosen = ttk.Combobox(rule_user, width=15) src_user_typeChosen['values'] = ['zss','test','root'] src_user_typeChosen.pack(side=LEFT,padx=10) src_user_typeChosen.current(1) l_dst_user = Label(rule_user, text="目的账号:", width=15) l_dst_user.pack(side=LEFT,padx=10) dst_user_typeChosen = ttk.Combobox(rule_user, width=15) dst_user_typeChosen['values'] = ['zss','test','root'] dst_user_typeChosen.pack(side=LEFT) dst_user_typeChosen.current(1) rule_user.pack(pady=5) rule_password = Frame(database_rule_windows) l_src_password = Label(rule_password, text="源密码:", width=15) l_src_password.pack(side=LEFT) src_password_typeChosen = ttk.Combobox(rule_password, width=15) src_password_typeChosen['values'] = ['zss','test','Anmit_2005'] src_password_typeChosen.pack(side=LEFT,padx=10) src_password_typeChosen.current(1) l_dst_password = Label(rule_password, text="目的密码:", width=15) l_dst_password.pack(side=LEFT,padx=10) dst_password_typeChosen = ttk.Combobox(rule_password, width=15) dst_password_typeChosen['values'] = ['zss','test','Anmit_2005'] dst_password_typeChosen.pack(side=LEFT) dst_password_typeChosen.current(1) rule_password.pack(pady=5) rule_dbinfo = Frame(database_rule_windows) l_src_dbinfo = Label(rule_dbinfo, text="源库名(实例名):", width=15) l_src_dbinfo.pack(side=LEFT) src_dbinfo_typeChosen = ttk.Combobox(rule_dbinfo, width=15) src_dbinfo_typeChosen['values'] = ['src','test','mysqlssd','mysqlhdd','oracle','orcl'] src_dbinfo_typeChosen.pack(side=LEFT,padx=10) src_dbinfo_typeChosen.current(0) l_dst_dbinfo = Label(rule_dbinfo, text="目的库名(实例名):", width=15) l_dst_dbinfo.pack(side=LEFT,padx=10) dst_dbinfo_typeChosen = ttk.Combobox(rule_dbinfo, width=15) dst_dbinfo_typeChosen['values'] = ['dst','test','mysqlssd','mysqlhdd','oracle','orcl'] dst_dbinfo_typeChosen.pack(side=LEFT) dst_dbinfo_typeChosen.current(0) rule_dbinfo.pack(pady=5) rule_tabelsinfo = Frame(database_rule_windows) l_src_tabelsinfo = Label(rule_tabelsinfo, text="源表名前缀:", width=15) l_src_tabelsinfo.pack(side=LEFT) src_tabelsinfo_typeChosen = ttk.Combobox(rule_tabelsinfo, width=15) src_tabelsinfo_typeChosen['values'] = ['zss_', 'test_', 'src_','dst_'] src_tabelsinfo_typeChosen.pack(side=LEFT,padx=10) src_tabelsinfo_typeChosen.current(0) l_dst_tabelsinfo = Label(rule_tabelsinfo, text="目的表名前缀:", width=15) l_dst_tabelsinfo.pack(side=LEFT,padx=10) dst_tabelsinfo_typeChosen = ttk.Combobox(rule_tabelsinfo, width=15) dst_tabelsinfo_typeChosen['values'] = ['zss_', 'test_', 'src_','dst_'] dst_tabelsinfo_typeChosen.pack(side=LEFT) dst_tabelsinfo_typeChosen.current(0) rule_tabelsinfo.pack(pady=5) rule_run = Frame(database_rule_windows) Button(rule_run, text='运行', width=10,command=add_rule).pack(side=LEFT) Button(rule_run, text='停止', width=10,command=select_port).pack(side=LEFT) rule_run.pack(pady=15) database_rule_windows.mainloop()if __name__ == '__main__': mysql_sql = """CREATE TABLE `?`.`?` ( `id` bigint(20) NOT NULL, `floatnumbers` float(10,5) NULL, `names` varchar(100) NULL, `namevchars` varchar(100) NULL, `times` datetime NULL, `payloads` longblob NULL, PRIMARY KEY (`id`))""" """ sqlserver创表的语句 其中数据库名称和表名称使用?代替即可 """ sqlserver_sql = """CREATE TABLE ? ( id numeric(20) not NULL, floatnumbers float(10) NULL, names char(100) NULL, namevchars varchar(100) NULL, times datetime NULL, payloads varbinary(max) NULL, PRIMARY KEY (id) )""" """ oracle创表的语句 其中数据库名称和表名称使用?代替即可 """ oracle_sql = """CREATE TABLE ?.? ( id numeric(20) not NULL, floatnumbers NUMBER NULL, names char(100) NULL, namevchars varchar2(100) NULL, times date NULL, payloads blob NULL, PRIMARY KEY (id) )""" mysql_sql_no_blob = """CREATE TABLE `?`.`?` ( `id` bigint(20) NOT NULL, `floatnumbers` float(10,5) NULL, `names` varchar(100) NULL, `namevchars` varchar(100) NULL, `times` datetime NULL, PRIMARY KEY (`id`) )""" sqlserver_sql_no_blob = """CREATE TABLE ? ( id numeric(20) not NULL, floatnumbers float(10) NULL, names char(100) NULL, namevchars varchar(100) NULL, times datetime NULL, PRIMARY KEY (id) )""" oracle_sql_no_blob = """CREATE TABLE ?.? ( id numeric(20) not NULL, floatnumbers NUMBER NULL, names char(100) NULL, namevchars varchar2(100) NULL, times date NULL, PRIMARY KEY (id) )""" mysql_insert_sql = """INSERT INTO `?`.`?` VALUES (int_number,float_number,uuid,uuid,insert_time)""" mysql_update_sql = """update `?`.`?` set `names`='updated' where ID%2=0 and `names` !='updated' and `times` > (select date_sub(now(), interval 60 second))""" mysql_delete_sql = """delete from `?`.`?` where `times` < (select date_sub(now(), interval 180 second))""" sqlserver_insert_sql = """INSERT INTO ? VALUES (int_number,float_number,uuid,uuid,insert_time,0x1234567890)""" sqlserver_update_sql = """update ? set names='updated' where ID%2=0 and names !='updated' and times > (select DATEADD(second, -60, GETDATE()))""" sqlserver_delete_sql = """delete from ? where times < (select DATEADD(second, -60, GETDATE()))""" oracle_insert_sql = """INSERT INTO ?.? VALUES (int_number,float_number,uuid,uuid,to_date (insert_time,'yyyy-mm-dd hh24:mi:ss'),HEXTORAW(2345678987654))""" oracle_update_sql = """update ?.? set NAMES='updated' where mod(ID,2) = 0 and NAMES !='updated' and TIMES > (select sysdate-60/86400 from dual)""" oracle_delete_sql = """delete from ?.? where times < (select sysdate-60/86400 from dual)""" root = Tk() root.title('数据库工具') root.geometry('800x650') root.maxsize(800, 650) root.minsize(800, 650) run_flag = 'run' database_info = Frame(root) l_database_type = Label(database_info, text="数据库类型:", width=8) l_database_type.pack(side=LEFT) database_typeChosen = ttk.Combobox(database_info, width=8) database_typeChosen['values'] = list(['Mysql','Sqlserver','Oracle']) # 设置下拉列表的值 database_typeChosen.pack(side=LEFT) database_typeChosen.current(0) database_typeChosen.bind("<>", select_port) ip_value = StringVar() ip_value.set('10.6.3.11') l_ip = Label(database_info,text="地址:", width=5) l_ip.pack(side=LEFT) e_ip = Entry(database_info, textvariable=ip_value,width=15) # 输入框 e_ip.pack(side=LEFT) l_dtabase_port = Label(database_info, text="端口:") l_dtabase_port.pack(side=LEFT) database_portChosen = ttk.Combobox(database_info, width=5) database_portChosen['values'] = list(['3306','1433','1521']) # 设置下拉列表的值 database_portChosen.pack(side=LEFT) database_portChosen.current(0) char_value = StringVar() char_value.set('utf8') l_char = Label(database_info,text="字符集:", width=5) l_char.pack(side=LEFT) e_char = Entry(database_info, textvariable=char_value,width=5) # 输入框 e_char.pack(side=LEFT) user_name_value = StringVar() user_name_value.set('test') l_user_name = Label(database_info, text="账号:", width=5) l_user_name.pack(side=LEFT) e_user_name = Entry(database_info, textvariable=user_name_value, width=10) # 输入框 e_user_name.pack(side=LEFT) pasowrd_value = StringVar() pasowrd_value.set('test') l_pasowrd = Label(database_info, text="密码:", width=5) l_pasowrd.pack(side=LEFT) e_pasowrd = Entry(database_info, textvariable=pasowrd_value, width=10) # 输入框 e_pasowrd.pack(side=LEFT) database_info.pack(expand=YES) tables_info = Frame(root) l_db_name = Label(tables_info, text="库名(实例名):") l_db_name.pack(side=LEFT) db_nameChosen = ttk.Combobox(tables_info, width=5) db_nameChosen['values'] = list(['src', 'dst', 'oracle','orcl']) # 设置下拉列表的值 db_nameChosen.pack(side=LEFT) db_nameChosen.current(0) l_tables_type = Label(tables_info, text="表名前缀:", width=8) l_tables_type.pack(side=LEFT) tables_typeChosen = ttk.Combobox(tables_info, width=8) tables_typeChosen['values'] = list(['zss_', 'test_', 'src_','dst_']) # 设置下拉列表的值 tables_typeChosen.pack(side=LEFT) tables_typeChosen.current(0) l_tables_start = Label(tables_info, text="表名后缀起始值:", width=15) l_tables_start.pack(side=LEFT) tables_start_typeChosen = ttk.Combobox(tables_info, width=5) tables_start_typeChosen['values'] = list(['0','1', '11', '101']) # 设置下拉列表的值 tables_start_typeChosen.pack(side=LEFT) tables_start_typeChosen.current(1) l_tables_end = Label(tables_info, text="表名后缀结束值:", width=15) l_tables_end.pack(side=LEFT) tables_end_typeChosen = ttk.Combobox(tables_info, width=5) tables_end_typeChosen['values'] = list(['10','20', '50', '100','200']) # 设置下拉列表的值 tables_end_typeChosen.pack(side=LEFT) tables_end_typeChosen.current(2) tables_info.pack(expand=YES) run = Frame(root) l_action = Label(run, text="动作:", width=5) l_action.pack(side=LEFT) action_typeChosen = ttk.Combobox(run, width=12) action_typeChosen['values'] = list(['select','select_tmp','truncate','truncate_tmp','touch','delete', 'drop','checkdata','stability_test']) # 设置下拉列表的值 action_typeChosen.pack(side=LEFT) action_typeChosen.current(0) checkVar = StringVar(value="0") blob_show = Checkbutton(run, text="是否创建大字段",variable=checkVar,onvalue = 1, offvalue = 0,width = 15) action_typeChosen.bind("<>", show_win) Button(run, text='运行', width=10,command=connet_database).pack(side=LEFT) Button(run, text='停止', width=10,command=stop_select).pack(side=LEFT) Button(run, text='清理', width=10,command=clear_log_text).pack(side=LEFT) Button(run, text='添加数据库同步规则', width=18,command=database_rule_windows).pack(side=LEFT) sql_windows = Button(run, text='自定义创建表语句框', width=16,command=touch_windows) stability_windows = Button(run, text='自定义测试语句框', width=16,command=stability_windows) l_check_db = Label(run, text="源库名(所有者)/目的库名(所有者):") check_src_dbChosen = ttk.Combobox(run, width=5) check_src_dbChosen['values'] = list(['src', 'dst', 'oracle', 'orcl']) # 设置下拉列表的值 check_src_dbChosen.current(0) check_dst_dbChosen = ttk.Combobox(run, width=5) check_dst_dbChosen['values'] = list(['src', 'dst', 'oracle', 'orcl']) # 设置下拉列表的值 check_dst_dbChosen.current(1) run.pack() stabilityconfig_insert = Frame(root) l_insert_number = Label(stabilityconfig_insert, text="单次插入数据量") l_insert_number.pack(side=LEFT) insert_numberChosen = ttk.Combobox(stabilityconfig_insert, width=5) insert_numberChosen['values'] = list(['10', '50', '100', '150', '200', '250', '300','400','500','1000']) # 设置下拉列表的值 insert_numberChosen.pack(side=LEFT) insert_numberChosen.current(2) l_insert_time = Label(stabilityconfig_insert, text="单次插入间隔时间") l_insert_time.pack(side=LEFT) insert_timeChosen = ttk.Combobox(stabilityconfig_insert, width=5) insert_timeChosen['values'] = list(['1', '2', '5','10']) # 设置下拉列表的值 insert_timeChosen.pack(side=LEFT) insert_timeChosen.current(2) l_insert_for = Label(stabilityconfig_insert, text="插入总次数") l_insert_for.pack(side=LEFT) insert_forChosen = ttk.Combobox(stabilityconfig_insert, width=5) insert_forChosen['values'] = list(['1000', '5000', '10000','20000']) # 设置下拉列表的值 insert_forChosen.pack(side=LEFT) insert_forChosen.current(2) stabilityconfig_update = Frame(root) l_update_time = Label(stabilityconfig_update, text="单次更新间隔时间") l_update_time.pack(side=LEFT) update_timeChosen = ttk.Combobox(stabilityconfig_update, width=5) update_timeChosen['values'] = list(['1', '2', '5','10']) # 设置下拉列表的值 update_timeChosen.pack(side=LEFT) update_timeChosen.current(2) stabilityconfig_delete = Frame(root) l_delete_time = Label(stabilityconfig_delete, text="单次删除间隔时间") l_delete_time.pack(side=LEFT) delete_timeChosen = ttk.Combobox(stabilityconfig_delete, width=5) delete_timeChosen['values'] = list(['1', '2', '5','10']) # 设置下拉列表的值 delete_timeChosen.pack(side=LEFT) delete_timeChosen.current(2) log_info = Frame(root) l_log = Label(log_info, text="输出如下:", width=10, height=1) l_log.pack() log_text = ScrolledText(log_info,width=300,height=30) #汇总框 log_text.pack() log_info.pack(padx=25,expand=YES, fill=X) clear_log() root.mainloop()
扫一扫
5238
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
