Getting unexpected result when trying to update a cell in SQLite database using Python-CodePudding

I am trying to update a cell in an existing SQLite database. The aim is to replace a string with an id.

import sqlite3

TABLES = ['table1', 'table2', ...]
PAR_TYPES_STRINGS = ['foo', 'bar', ...]
PAR_TYPES_ID = [1, 2, ...]

def upateTable(table, parTypeId, parTypeString):
  return f"UPDATE {table} SET par_type = {parTypeId} WHERE par_type = {parTypeString};"

conn = sqlite3.connect('existing_db.db')
cursor = conn.cursor()

for table in TABLES:
  for index, parTypeString in enumerate(PAR_TYPES_STRINGS):
    parTypeId = PAR_TYPES_ID[index]
    cursor.execute(upateTable(table, parTypeId, parTypeString))
conn.commit()
cursor.close()

This gives me

sqlite3.OperationalError: no such column: foo

Which I do not understand. I am using par_type statically as a column name, and comparing it to parTypeString (which then has foo) in it dynamically.

WHERE par_type = {parTypeString} < dynamicall passed variable
      ^
      hardcoded column name

Yet, it is saying "no such column foo", which is the first element the for loop takes out of PAR_TYPES_STRINGS. Why is that?

CodePudding user response：

In the function definition, you need to put {parTypeString} in quotes: '{parTypeString}', because you want to check where the hardcoded column matches that string. Otherwise the expression is interpreted to check equality between two columns, hence the error message.

CodePudding user response：

A string value in a column must be wrapped into ''

Thus

def upateTable(table, parTypeId, parTypeString):
  return f"UPDATE {table} SET par_type = {parTypeId} WHERE par_type = '{parTypeString}';"
                                                                      ^               ^
                                                                      Add '           Add '