【Web_接口测试_Python3_Mysql_数据库基础操作】（数据库）自动重连、获取库名、获取表名、批量生成sql语句，自动化实战案例

#!/usr/bin/env/python3
# -*- coding:utf-8 -*-
'''
Author:leo
Date&Time:2020/02/15 and 12:30
Project:Python3
FileName:demo_mysql.py
Description：
1.数据库增删查改
2.数据库获取库名
3.数据库获取表名
4.数据库批量生成sql语句
5.数据库批量读取sql文件
'''
import pymysql,MySQLdb,sys,time,re,pymysql,os
sys.path.append(r'...')
from hb_config import Deve_envirment as de
import MySQLdb.cursors
delete_nowTime = time.strftime('%Y-%m-%d %H%M%S', time.localtime(time.time())).split(" ")[0]  # 公共参数
mysql_time = time.strftime('%Y%m%d_%H%M%S', time.localtime(time.time()))
class hb_mysql():

    def create_mysql_conn(self, database_name='mysql'):
        print("\n----开始连接数据库----\n")
        conn_status, conn_retries_count, max_retries_count = True, 0, 5
        while conn_status == True and conn_retries_count < max_retries_count:
            try:
                conn = pymysql.connect(de.database['host'], de.database['username'], de.database['password'], database_name, charset='utf8', connect_timeout=10)
                conn_status = False
                print(f"\n----成功连接数据库：类型-{type(conn)} 名称-{conn}\n")
                return conn
            except Exception as e:
                conn_retries_count += 1
                print("\n----数据库连接失败，10s后自动重连，第{}次重连开始：{}".format(conn_retries_count, e))
                time.sleep(10)
                continue

    def get_database_name(self, database_name='mysql'):
        print("\n----获取数据库：库名称----\n")
        conn = pymysql.connect(de.database['host'], de.database['username'], de.database['password'], database_name, charset='utf8')
        cursor = conn.cursor()
        cursor.execute('show databases;')
        DISABLED_DATABASES = ['information_schema', 'mysql']
        db0 = str(re.sub(r'[()\' ]', '', str(cursor.fetchall()))).split(",")
        dbs_list = [i for i in db0 if i != '' and i not in DISABLED_DATABASES]
        print(f"{database_name}-数据库统计：{len(dbs_list)},{len(DISABLED_DATABASES)}\n业务库：{dbs_list}\n原始库：{DISABLED_DATABASES}")
        cursor.close()
        conn.close()
        return dbs_list

    def get_tables_name(self, database_name='mysql'):
        print("\n----获取数据库：表名称----\n")
        conn = pymysql.connect(de.database['host'], de.database['username'], de.database['password'], database_name, charset='utf8')
        cursor = conn.cursor()
        cursor.execute('show tables;')
        db0 = str(re.sub(r'[()\' ]', '', str(cursor.fetchall()))).split(",")
        tbs_list = [i for i in db0 if i != '']
        print(f"{database_name}-数据表统计：{len(tbs_list)}\n{tbs_list}")
        cursor.close()
        conn.close()
        return tbs_list

    def get_col_name(self, database_name='mysql', sql=""):
        print("\n----获取数据库：列名称----\n")
        conn = pymysql.connect(de.database['host'], de.database['username'], de.database['password'], database_name, charset='utf8')
        cursor = conn.cursor()
        cursor.execute(sql)
        col = cursor.description
        col_list = []
        for field in col:
            col_list.append(field[0])
        print(col_list)
        conn.close()
        return col_list

    def select_info(self, database_name, select_sql):
        db = MySQLdb.connect(de.database['host'], de.database['username'], de.database['password'], database_name, charset='utf8')
        cursor = db.cursor()
        try:
            print("\n----查询数据----\n")
            print("sql:", select_sql)
            cursor.execute(select_sql)
        except Exception as e:
            print(f"Exception:{e}")
        data_all = cursor.fetchall()
        print(f"全部数据：{type(data_all)} {data_all}")
        cursor.close()
        db.close()
        for data in data_all[:]:
            str_data = str(data).replace('"', " ").replace("'", ' ').replace(" ", "")
        return str_data

    def delete_info(self, database_name, table, delete_sql, delete_time=delete_nowTime):
        db = MySQLdb.connect(de.database['host'], de.database['username'], de.database['password'], database_name, charset='utf8')
        cursor = db.cursor()
        try:
            print("\n----删除数据----\n")
            cursor.execute(f"select * from {table} where create_time LIKE'{delete_time} %';")  # 改日期！！！！
            data_all = cursor.fetchall()
            for data in data_all[:]:
                print(f"获取全部数据:\n{type(data)} {data}")
            cursor.execute(delete_sql)
            db.commit()
        except Exception as e:
            print(f"Exception:{e}")
            db.rollback()
        cursor.close()
        db.close()
        return data_all

    def execute_mysql(self, database_name, execute_sql):
        db = MySQLdb.connect(de.database['host'], de.database['username'], de.database['password'], database_name, charset='gbk', connect_timeout=10)
        cursor = db.cursor()
        execute_sql = execute_sql.lower()
        try:
            if "insert" in execute_sql.lower():
                print("\n----开始执行insert语句----\n")
                cursor.execute(execute_sql)
                db.commit()
                print("insert-执行成功")
                print("\n--查询数据\n")
                select_tablename = str(execute_sql.split(" ")[2]).split("(")[0].rstrip(";")
                insert_key, insert_value = str(execute_sql.split("(")[1]).split(",")[0], str(execute_sql.split("(")[2]).split(",")[0]
                where_sql = f" where {insert_key}={insert_value}"
                select_excute_sql = f"select * from {select_tablename}{where_sql};"
                print(f"原始sql：{execute_sql}\n查询sql：{select_excute_sql}")
                cursor.execute(select_excute_sql)
                # 获取全部数据
                select_all_data, select_all_data_list = cursor.fetchall(), []
                print(f"\n查询全部数据(类型、统计、详情）:{select_tablename} {len(select_all_data)} {type(select_all_data)} {select_all_data}")
                for one_data in select_all_data[:]:
                    str_data = str(one_data).replace('"', " ").replace("'", ' ').replace(" ", "")
                    select_all_data_list.append(str_data)
                    print(str_data)
                return select_all_data_list
            elif "delete" in execute_sql.lower():
                print("\n----开始执行delete语句----\n")
                select_tablename = execute_sql.split(" ")[2].rstrip(";")
                if 'where' in execute_sql.lower():
                    where_sql = " where" + execute_sql.split("where")[1].rstrip(";")
                else:
                    where_sql = ""
                print("\n--查询数据\n")
                select_excute_sql = f"select * from {select_tablename}{where_sql};"
                print(f"原始sql：{execute_sql}\n查询sql：{select_excute_sql}")
                cursor.execute(select_excute_sql)
                # 获取全部数据
                select_all_data, select_all_data_list = cursor.fetchall(), []
                print(f"\n查询全部数据(类型、统计、详情）:{select_tablename} {len(select_all_data)} {type(select_all_data)} {select_all_data}")
                for one_data in select_all_data[:]:
                    str_data = str(one_data).replace('"', " ").replace("'", ' ').replace(" ", "")
                    select_all_data_list.append(str_data)
                    print(str_data)
                print("\n--执行SQL\n")
                print("delete-执行成功")
                cursor.execute(execute_sql)
                db.commit()
                return select_all_data_list
            elif "select" in execute_sql.lower():
                print("\n----开始执行select语句----\n")
                select_tablename = execute_sql.split(" ")[3].rstrip(";")
                if 'where' in execute_sql.lower():
                    where_sql = " where" + execute_sql.split("where")[1].rstrip(";")  # where id='123' where条件id='123'不能有空格
                else:
                    where_sql = ""
                    print("select-执行成功")
                print("\n--查询数据\n")
                select_excute_sql = f"select * from {select_tablename}{where_sql};"
                print(f"原始sql：{execute_sql}\n查询sql：{select_excute_sql}")
                cursor.execute(select_excute_sql)  # 改日期！！！！
                select_all_data, select_all_data_list = cursor.fetchall(), []
                print(f"\n查询全部数据(类型、统计、详情）:{select_tablename} {len(select_all_data)} {type(select_all_data)} {select_all_data}")
                for one_data in select_all_data[:]:
                    str_data = str(one_data).replace('"', " ").replace("'", ' ').replace(" ", "")
                    select_all_data_list.append(str_data)
                    print(str_data)
                return select_all_data_list
            elif "update" in execute_sql.lower():
                print("\n----开始执行update语句----\n")
                cursor.execute(execute_sql)
                db.commit()
                print("update-执行成功")
                select_tablename = execute_sql.split(" ")[1].rstrip(";")
                if 'where' in execute_sql.lower():
                    where_sql = " where" + execute_sql.split("where")[1].rstrip(";")  # where id='123' where条件id='123'不能有空格
                else:
                    where_sql = ""
                print("\n--查询数据\n")
                select_excute_sql = f"select * from {select_tablename}{where_sql};"
                print(f"原始sql：{execute_sql}\n查询sql：{select_excute_sql}")
                cursor.execute(select_excute_sql)
                select_all_data, select_all_data_list = cursor.fetchall(), []
                print(f"\n查询全部数据(类型、统计、详情）:{select_tablename} {len(select_all_data)} {type(select_all_data)} {select_all_data}")
                for one_data in select_all_data[:]:
                    str_data = str(one_data).replace('"', " ").replace("'", ' ').replace(" ", "")
                    select_all_data_list.append(str_data)
                    print(str_data)
                return select_all_data_list
        except (Exception, IndexError) as e:
            print(f"Exception info:{e}")
            print('Exception line:{}'.format(sys.exc_info()[-1].tb_lineno))
            db.rollback()
        finally:
            cursor.close()
            db.close()
        return

    def mkdir_if_not_exists(self, path=f"{mysql_time}"):
        """
        新建存放文件夹：判断给定目录是否存在，不存在则创建它
        Args:
            path: 带创建目录名称（后续写入路径需要{BACKUP_PATH}+"\\"+{sonfile_name})
        """
        path = path.rstrip("\\")  
        if not os.path.exists(path):
            os.mkdir(path)

    def write_mysql(self, mfile_path = f"{mysql_time}"):
        hb_mysql().mkdir_if_not_exists(mfile_path)
        database_name = hb_mysql().get_database_name("mysql")
        for db_name in database_name:
            try:
                if db_name not in ['mysql']:
                    mysql_list = []
                    tables_name = hb_mysql().get_tables_name(db_name)
                    for t_name in tables_name[:]:
                        data = f"DELETE FROM {db_name}.{t_name} where create_time > @create_time;\n"
                        mysql_list.append(data)
                    with open(mfile_path + "\\" + f'{db_name}_{len(mysql_list)}.sql', mode="w+", encoding="utf8") as mfile:
                        mfile.writelines(mysql_list)
                    print(f"--成功生成sql条数:{db_name}  {len(mysql_list)}")
                else:
                    print(f"\n----非业务相关数据库:{db_name}")
            except Exception as e:
                print(e)
        return None
    # apollo 数据库密码解密
    def mysql_password_Decrypt(self, new_password = "znf2AMF3zsQKYT4ucHBYQ2RPpLXvSPht"):
        import subprocess,os
        # os.system_34("cmd")
        # time.sleep(1)
        # os.system_34("exit")
        # input("exit")

        # 解密正文
        password_path = r"jasypt-1.9.2\bin"
        with open(password_path + r"\win_decrypt1.bat", "w+") as m:
            password_info = f"""@echo off\n@call decrypt.bat input="{new_password}" password="Oat4jkamo$dK"\npause;"""
            m.write(password_info)
            m.seek(0, 0)
            print("\n----文件地址----：" + f"D:\Mytest\Python3\Python3\...\...sit\mysql\jasypt-1.9.2\\bin")
            print("\n----修改成功----\n", m.read())  # 生成的文件右下角改成lf保存，才可以执行成功
            a1 = os.system("d: && cd D:\Mytest\Python3\Python3\...\...sit\mysql\jasypt-1.9.2\\bin && win_decrypt1.bat")
            print("1.返回0成功；2.返回1失败；3.返回2系统错误；\n--当前结果：", a1)
            print("after")
            print("---- 2.加start关键字，非阻塞式调用 ----")
            testFile = os.system("start for %i in (1, 2, 3) do @echo %i")
            print("1.返回0成功；2.返回1失败；3.返回2系统错误；\n--当前结果：", testFile)

if __name__ == "__main__":
    pass