Python SQLite3 multiple string manipulation-CodePudding

Using SQLite3, how do you use multiple variables in one request? Using one works but once you add a second it gives you the syntax error below. I have tried researching this and I cannot find any documentation/other questions in the same situation.

Code:

self.db.execute("INSERT INTO complements (:record) VALUES (:field)", {"record": record, "field": field})

Error:

|Traceback (most recent call last):
  File "(path)", line 22, in <module>
    db.add("Adjective", "adjective")
  File "(path)", line 17, in add
    self.db.execute("INSERT INTO complements (:record) VALUES (:field)", {"record": record, "field": field})
sqlite3.OperationalError: near ":record": syntax error

CodePudding user response：

Column names (and other identifiers like table names) can't be parameterized which is why you are getting a syntax error. Only "values" (the data to be inserted) can be parameterized. To fix this, format the query yourself, for example using format strings:

record, field = ...
self.db.execute(f"INSERT INTO complements ({record}) VALUES (:field)", {"field": field})

if record is user-provided you must take care to prevent SQL injection, e.g. by checking whether it is a valid column name: record in ["some", "column", "names"]; it is however rather uncommon to parameterize identifiers in queries, so this feels like an XY-Problem.