SQL UPDATE in python string substitution partially works

Question

I'm trying to use sqlite3 with python3 to build a rudimentary database system. I've create a database now I want to know how to update it. The following function works and gives the desired output.

def update_db(table_name,column_name):
    con = db_connect()
    cur = con.cursor()
    id_column  ='first_name'
    update_sql = """ UPDATE {}  SET {} = 'hi' where {} = "ID_0-1" """.format(table_name, column_name, id_column)
    cur.execute(update_sql)
    con.commit()

However, if I try to generalise the update_sql statement as such:

def update_db(table_name,column_name):
    con = db_connect()
    cur = con.cursor()
    id_column  ='first_name'
    value = 'hi'
    id_value = "ID_0-1"
    update_sql = """ UPDATE {}  SET {} = {} where {} = {} """.format(table_name, column_name, value, id_column, id_value)
    cur.execute(update_sql)
    con.commit()

I get the following traceback:

Traceback (most recent call last):
  File "database_intialisation.py", line 97, in <module>
    fill_column(table_name, column_name)
  File "database_intialisation.py", line 77, in fill_column
    cur.execute(update_sql)
sqlite3.OperationalError: no such column: hi

I've also tried %s string substitution method and it yields similar results.

Quote at hi, you need to quote the value

Phung Duy Phong
– Phung Duy Phong

2020-01-13 11:48:04 +00:00
Commented Jan 13, 2020 at 11:48 — Phung Duy Phong
– Phung Duy Phong, Commented Jan 13, 2020 at 11:48

zipa · Accepted Answer · 2020-01-13 11:51:36Z

2

Try like this, using the ? placeholder:

update_sql = """ UPDATE {}  SET {} = ? where {} = ? """.format(table_name, column_name, id_column)
cur.execute(update_sql, (value, id_value))

Your 'hi' is inserted into statement as hi and it is trying to set the value of your column to the value the would be in column hi.

answered Jan 13, 2020 at 11:51

zipa

28k6 gold badges45 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Phung Duy Phong · Accepted Answer · 2020-01-13 11:51:15Z

1

Change sql string to:

""" UPDATE {}  SET {} = '{}' where {} = '{}' """

Note: this approach have many problem and you should use parameterized query

answered Jan 13, 2020 at 11:51

Phung Duy Phong

9066 silver badges18 bronze badges

Collectives™ on Stack Overflow

SQL UPDATE in python string substitution partially works

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related