How to UPDATE table in psycopg2

Question

def GetUpdate():
    enter = input("What attributes do you want to change ? ")
    select,p_id =  input("Please enter your attributes separated by space: ").split()
    try:
        cur.execute("UPDATE  Diary SET %s = %s where diary_id = %s",(enter,select,p_id))
        #cur.execute(sql, (select, p_id))
        con.commit()
        print("Updating in table Diary is completed successfully")
    except (Exception, ps.DatabaseError) as error:
        print(error)

I want to make update function as you see I have 3 input parameters, first is what column in db I want to change .But 2,3 parameters is update parameters. When I'm trying run this code I got error. He takes my first input not as parameter, but as a Variable(string)

Show the full traceback of the error as properly formatted text in the question. Better remove the "try" and the except-block for this. — Michael Butscher, Jan 18 '22 at 06:10

JL Peyret · Accepted Answer · 2022-01-18T06:50:38.910

cur.execute("UPDATE  Diary SET ❌%s❌ = %s where diary_id = %s",(enter,select,p_id))

You can't use a bind variable to set a tablename or column name or the like. I.e. you can't compose/build a query from binds, only use binds to substitute in sql arguments.

For example, see Oracle doc (first one I found citing this):

Bind variables can be used to substitute data, but cannot be used to substitute the text of the statement. You cannot, for example, use a bind variable where a column name or a table name is required. Bind variables also cannot be used in Data Definition Language (DDL) statements, such as CREATE TABLE or ALTER statements.

Sure, you could compose the query as

qry = f"UPDATE  Diary SET {enter} = %s ..."
cur.execute(qry,(select,p_id))

but that's a massive security risk of the sql injection kind and designing around how to safely take in user-supplied params to build into a safe sql query goes beyond answering your asking why this error was happening.

How to UPDATE table in psycopg2

1 Answers1