分享
三行代码  ›  专栏  ›  技术社区  ›  Mohammad Nurdin

psycopg2.errors.UndefinedColumn:列

  •  0
  • Mohammad Nurdin  · 技术社区  · 5 天前

    我在尝试获取所有数据时收到此错误消息

    Traceback (most recent call last):
      File "/home/user/xxxxx/node_modules/serverless/lib/plugins/aws/invokeLocal/runtimeWrappers/invoke.py", line 86, in <module>
        result = handler(input['event'], context)
      File "./src/handler.py", line 97, in historical
        post_process(db_connection)
      File "./src/post_process.py", line 25, in post_process
        averages = db_connection.fetch_all("""
      File "./src/common/database_helper.py", line 36, in fetch_all
        cursor.execute(query)
    psycopg2.errors.UndefinedColumn: column "col_list" does not exist
    LINE 4:                                            avg(col_list[z])
    

    代码

    col_list = ['A', 'B', 'C']
    
    averages = db_connection.fetch_all("""
                                               SELECT
                                               DATE_TRUNC('month', "DateTime") AS date_time,
                                               avg(col_list[z])
                                               FROM public."price_NA" group by DATE_TRUNC('month', "DateTime")
                                               ORDER BY date_time DESC
                                               """
                                               )
    
    1 回复  |  直到 5 天前
        1
  •  1
  •   Tim Roberts    5 天前

    你真的认为这样行得通吗?SQL对Python变量一无所知。幸运的是,对于f字符串,这是一个简单的解决方法:

    col_list = ['A', 'B', 'C']
    
    averages = db_connection.fetch_all(f"""
                                               SELECT
                                               DATE_TRUNC('month', "DateTime") AS date_time,
                                               avg({col_list[z]})
                                               FROM public."price_NA" group by DATE_TRUNC('month', "DateTime")
                                               ORDER BY date_time DESC
                                               """
                                               )