AWS Lambda中Python操作Postgres报UnboundLocalError问题求助

• 变量作用域问题：post_connection仅在try代码块内部完成赋值，如果psycopg2.connect()执行前就触发异常（比如VaultService初始化失败、数据库账号密码获取报错、环境变量读取异常等），该变量不会被创建，finally块中执行if post_connection判断时就会触发未绑定错误。
• 额外代码错误：run_bc函数中你定义的SQL查询变量名为bc_qry，但构造COPY语句时错误调用了不存在的bc_flowdown_qry变量，这会提前触发异常，导致post_connection还未完成赋值就进入finally逻辑。
• 补充说明：run_bo函数中的SQL语句也存在语法错误，缺少FROM关键字、字段间缺逗号，建议同步修正避免执行报错。

1. 在try块前提前将post_connection和cursor初始化为None，保证finally块访问时变量一定存在
2. 修正run_bc中的变量名拼写错误
3. 优化资源关闭逻辑，分别判断变量是否存在再执行关闭操作，避免二次报错

修正后代码示例（以run_bc为例，run_bo按相同逻辑修改即可） #

def run_bc(event):
    if 'resources' in event and "psql_billing" in event['resources'][0]:
        # 提前初始化连接和游标变量
        post_connection = None
        cursor = None
        try:
            config = VaultService()
            s3_resource = boto3.resource('s3')
            idv_endpoint = os.getenv('DB_ENDPOINT')
            idv_database = os.getenv("DB_NAME")
            filename = 'staging/billing_bc'
            bucket = os.getenv('BILLING_ETL')
            idv_username = config.get_postgres_username()
            idv_password = config.get_postgres_password()

            post_connection = psycopg2.connect(
                user = idv_username,
                password = idv_password,
                host = idv_endpoint,
                port = "5432",
                database = idv_database
            )
        
            cursor = post_connection.cursor()
            bc_qry = "select id, uuid, document_type, image_id, document_id from public.bc"
            # 修复变量名错误
            query = """COPY ({}) TO STDIN WITH (FORMAT csv, DELIMITER '|', QUOTE '"', HEADER TRUE)""".format(bc_qry)
            file = StringIO()
            cursor.copy_expert(query, file)
            s3_resource.Object(bucket, f'{filename}.csv').put(Body=file.getvalue())
            cursor.close()
        except(Exception, psycopg2.Error) as error:
            print("Error connecting to postgres instance", error)

        finally:
            # 分别判断变量状态再关闭，避免二次报错
            if cursor:
                cursor.close()
            if post_connection:
                post_connection.close()
    else:
        print("Cannot make a solid connection to psql instance. Please check code configuration")

内容的提问来源于stack exchange，提问作者pass_the_kavasier