杂记——自动化测试连接PG库

最新推荐文章于 2024-09-15 08:03:51 发布

溯堂夜话

最新推荐文章于 2024-09-15 08:03:51 发布

阅读量465

点赞数 8

文章标签：数据库

本文链接：https://blog.csdn.net/jsdtfza/article/details/136184762

版权

文章介绍了在项目部署后遇到的数据库端口号不确定性问题，通过SSH连接远程执行命令，包括使用`paramiko`库进行SSH操作，获取服务的PG_ADDRESS，执行`curl`请求获取publish_port，并处理可能的异常，以实现自动化的数据库端口查找和SQL执行。

摘要由CSDN通过智能技术生成

主要难点问题：

项目部署之后，数据库端口号不确定。

解决方案：

1.知道端口范围，进行遍历（不推荐）

2.通过SSH连接远程执行命令

def get_db_port(service_name):
    ssh = ...
    userName =...
    passWord = ...
    ssh = ...
    ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    try:
        ssh.connect(hostname=ssh, port=22, username=userName, password=passWord)
        shell = ssh.invoke_shell()
        shell.send('sudo su\n')
        time.sleep(2)
        stdin, stdout, stderr = ssh.exec_command('sudo kubectl get pod -n zenap | grep '+service_name)
        output = stdout.read().decode('utf-8')
        if output:
            pod_name = output.split()[0]

            # 获取PG_ADDRESS
            stdin, stdout, stderr = ssh.exec_command('sudo kubectl describe pod -n zenap ' + pod_name + ' | grep PG')
            output = stdout.read().decode('utf-8')
            match = re.search(r'OPENPALETTE_PG_ADDRESS:\s*([^\s]+)', output)
            if match:
                pg_address = match.group(1).split('-opcs')[0]

                # 执行curl请求
                stdin, stdout, stderr = ssh.exec_command('sudo curl -l -g -H "Content-type:application/json" -X GET "http://[0]:10081/api/microservices/v1/services/'+pg_address+'-2/version/v1?namespace=opcs"')
                output = stdout.read().decode('utf-8')

                # 解析JSON
                json_dict = json.loads(output)
                publish_port = json_dict.get('publish_port')

                # 检查publish_port类型
                if isinstance(publish_port, unicode):
                    print publish_port
                    return int(publish_port)
            else:
                # 添加错误处理逻辑
                pass
        else:
            # 添加错误处理逻辑
            pass
    except Exception as e:
        # 添加错误处理逻辑
        print("An error occurred: {}".format(e))
    finally:
        ssh.close()

def execute_sql(service_name,file_name):
    try:
        file_path = path...+'/sql_jsons/'+file_name
        with open(file_path, 'r') as file:
            data = json.load(file)
        sqls = data.get('sqls', [])
        public_port = get_db_port(service_name)
        host=...
        user=...
        dbname=...
        passward=...
        conn = psycopg2.connect(dbname=dbname,
                                user=user,
                                password=passward,
                                host=host,
                                port=public_port)
        cur = conn.cursor()
        for sql in sqls:
            print("Executing SQL:", sql)
            cur.execute(sql)
            # 获取查询结果并打印
            result = cur.fetchall()
            for row in result:
                print(row)
            conn.commit()
        cur.close()
        return result
    except psycopg2.DatabaseError as e:
        print("could not connect to Greenplum server",e)
    except Exception as e:
        print("Error:", e)
    finally:
        # 无论操作成功或者发生异常，都确保关闭数据库连接
        if conn is not None:
            conn.close()