How to add escape with in python """ quote string?

Question

i am trying to integrate my AWS lambda function to query my postgresql.

sql_update_query = """update  "YP_SUPPLIERS" set %s =%s where "YP_SUPPLIERS"."supplierID"= %s"""
    cursor.execute(sql_update_query, (key[0],value[0],supplierID))

Apparently while creating the table "" where used. This is pretty hectic to work around this.

error

syntax error at or near "'supplierName'"
LINE 1: update  "YP_SUPPLIERS" set 'supplierName'='key' where "YP_SU...

it seems set %s who's value is supplierName should be with in "". Can any one let me know a work around or how can i implement this properly please

Does this answer your question? [Pass column name as parameter to PostgreSQL using psycopg2](https://stackoverflow.com/questions/27289957/pass-column-name-as-parameter-to-postgresql-using-psycopg2) — Mike Organek, Jul 28 '20 at 11:35
@MikeOrganek i am afraid it doesn't have already implemented what was mentioned in there . Thank you though :) — sumanth shetty, Jul 28 '20 at 11:44
Yes. It does. The error you are getting is because `supplierName` is a column name. When the `execute` substitutes it as a parameter, it treats `supplierName` like a literal value. — Mike Organek, Jul 28 '20 at 11:59
@MikeOrganek so how can i have supplierName taken as a column name. i am using this because i will be unaware of the column name to be modified. which will be sent from API query parameter — sumanth shetty, Jul 28 '20 at 13:26

Mike Organek · Accepted Answer · 2020-07-28T15:21:02.373

2

Use the SQL composition feature of psycopg2

from psycopg2 import sql

sql_update_query = sql.SQL(
    """update "YP_SUPPLIERS" 
          set {} = %s,
              {} = %s
        where "YP_SUPPLIERS"."supplierID" = %s""").format(
                                                    sql.Identifier(key[0]),
                                                    sql.Identifier(key[1])
                                                   )

cursor.execute(sql_update_query, (value[0], value[1], supplierID))

edited Jul 28 '20 at 15:21

answered Jul 28 '20 at 13:44

Mike Organek

11,647
3
11
26

This worked suppose if i have multiple column to update ? will i add """update "YP_SUPPLIERS" set {} = %s set{} = %s where "YP_SUPPLIERS"."supplierID" = %s""").format(sql.Identifier(key[0],key[1])) – sumanth shetty Jul 28 '20 at 13:53
1

@sumanthshetty No. You will need to call `sql.Identifier()` once for each format substitution. I will update my answer. If you pass multiple arguments to `sql.Identifier()`, then it constructs an identifier like `"FirstString"."SecondString"` – Mike Organek Jul 28 '20 at 14:09
got it . thank you so much for the explanation it helped me to understand it much clearly. – sumanth shetty Jul 28 '20 at 15:17

Ujjwal Dash · Answer 2 · 2020-07-28T13:32:18.117

0

Use this

sql_update_query = """update  YP_SUPPLIERS set %s =%s where supplierID= %s"""
cursor.execute(sql_update_query%(key[0],value[0],supplierID))

edited Jul 28 '20 at 13:32

answered Jul 28 '20 at 13:11

Ujjwal Dash

767
1
4
8

How to add escape with in python """ quote string?

2 Answers2