0

I cannot figure out why this is not working. I'm trying to pass a list of strings as part of a loop inside a string that is used as database query. This is the error I'm getting:

\porfiler03.ar.local\gtdshare\vortex\monthly_variability\scripts\wind_index_chinook_region_3plots_working_monthly.py:71 in get_db_data
    crs_obj.execute(query)

UndefinedColumn: column "east" does not exist
LINE 5: ...s_name = 'UNITED STATES'  AND maint_region_name = EAST  AND ...

Here is the code:

regions = ['EAST','CENTRAL']

for x in regions:
    randall_query = f"""select plant_mos_adj_wind.plant_name, plant_mos_adj_wind.business_name,plant_mos_adj_wind.business_code,plant_mos_adj_wind.wind_speed_ms,
    plant_mos_adj_wind.maint_region_name, plant_mos_adj_wind.wind_direction_deg, plant_mos_adj_wind.mos_time, plant_mos_adj_wind.total_current_capacity,
    plant_mos_adj_wind.dataset
    from vortex.plant_mos_adj_wind
    where business_name = 'UNITED STATES'  AND maint_region_name = {x}  AND dataset = 'ERA5'"""
    try:
       connection = psycopg2.connect(user="cms_app",
                                      password="xxxxx",
                                      host="frupaapg02.ar.local",
                                      port="xxxx",
                                      database="xxxx")
       print("Selecting rows from vortex plant_mos_adj_wind table using cursor.fetchmany")
       cursor = connection.cursor()
       cdata = get_db_data(cursor, randall_query, False, True).round(1) #panda df
       postgreSQL_select_Query = f"""select plant_name, business_name, maint_region_name, total_current_capacity, mos_time, wind_speed_ms, wind_direction_deg, dataset
       from vortex.plant_mos_adj_wind
       where business_name = 'UNITED STATES' AND maint_region_name = {x}  AND dataset = 'ERA5'"""
    
       cursor.execute(postgreSQL_select_Query)
       chinookmos_records = cursor.fetchall()
    finally:
        #closing database connection.
        if(connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")
    
    #iterate the columns
            print("Column headers for cdata are:")
            for col in cdata.columns:
                print(col)

This should work using the f string function and substituting for x in regions but you can see that it fails because of the EAST argument and it clearly should be 'EAST' passes as a string is my thought.

mkrieger1
  • 19,194
  • 5
  • 54
  • 65
user2100039
  • 1,280
  • 2
  • 16
  • 31
  • Does this answer your question? [How to use variables in SQL statement in Python?](https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python) – mkrieger1 Mar 14 '23 at 21:44
  • The f-string just doesn't work this way. The actual string is `EAST` (no quotes) and is inserted as it is. – Michael Butscher Mar 14 '23 at 21:45
  • 2
    try adding quotes around `{x}` , like this `... AND maint_region_name = '{x}' ...` So when `EAST` gets inserted into the string it looks like `'EAST'` on the SQL Query. – Bas van der Linden Mar 14 '23 at 21:46
  • `EAST` looks like a column name at the moment as it is not in quotes – JonSG Mar 14 '23 at 21:47
  • yes!!! '{x}' works by adding the single quotes. thank you, – user2100039 Mar 14 '23 at 22:18

1 Answers1

0

I recommend 2 solutions:

  1. Remove tabs of for loop in query line after 1st query line

randall_query = f"""select plant_mos_adj_wind.plant_name, plant_mos_adj_wind.business_name,plant_mos_adj_wind.business_code,plant_mos_adj_wind.wind_speed_ms, plant_mos_adj_wind.maint_region_name, plant_mos_adj_wind.wind_direction_deg, plant_mos_adj_wind.mos_time, plant_mos_adj_wind.total_current_capacity, plant_mos_adj_wind.dataset from vortex.plant_mos_adj_wind where business_name = 'UNITED STATES' AND maint_region_name = {x} AND dataset = 'ERA5'"""

  1. Check whether column name EAST exists or not in database.
OM VASTRE
  • 11
  • 2