psycopg2.errors.UndefinedColumn: column

I’m getting this error message when trying to fetch all the data

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])

Code

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
                                           """
                                           )

Answer

Did you really think that was going to work? SQL doesn’t know anything about your Python variables. Fortunately, with f-strings it’s an easy fix:

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
                                           """
                                           )