How to set the edited attribute to a variable in SQL-CodePudding

Basically, to be able to write fewer functions, I made the edited attribute a variable. So my editing code looks like this :

cur.execute("""
                    UPDATE acteurs SET ?=? WHERE IDACTEUR=?;
                    """, (attribute, newvalue, actorid))
        conn.commit()

But when running it, I get a syntax error. How should I edit this to make it work?

CodePudding user response：

Python is escaping values before putting in query - for security reasons - so values can't be used in some places because it creates wrong query.

Using ?=? you expect column=value but it creates two strings "column"="value" and it can generate error.

It may also check if ? is in place for table name or column name and raise error - for security reasons.

It may need to put column using string formatting or f-string. But it is not preffered. Queries shouldn't use ? for table names, column names.

Minimal working example.

It uses con.set_trace_callback(print) to display last query.

import sqlite3

con = sqlite3.connect(":memory:")#
con.set_trace_callback(print)

cur = con.cursor()

cur.execute("CREATE TABLE test(key TEXT, value TEXT)")
print('---')
cur.execute("INSERT INTO test(key, value) VALUES (?, ?)", ("a", "1"))
con.commit()
print('---')

cur.execute("SELECT key, value FROM test WHERE key=?", ('a',))
row = cur.fetchone()
print(row)
print('---')

cur.execute("SELECT key, value FROM test WHERE ?=?", ('key', 'a'))
row = cur.fetchone()
print(row)
print('---')

try:
    cur.execute("UPDATE test SET ?=? WHERE key=?", ('value', '3', 'a',))
    con.commit()
except Exception as ex:
    print('Exception:', ex)
print('---')

try:
    column = 'value'
    cur.execute(f"UPDATE test SET {column}=? WHERE key=?", ('3', 'a',))
    con.commit()
except Exception as ex:
    print('Exception:', ex)
print('---')

cur.execute("SELECT key, value FROM test WHERE key=?", ('a',))
row = cur.fetchone()
print(row)
print('---')

con.close()

Example WHERE key=?", ('a',) creates WHERE key='a' and it gets one row.

Example WHERE ?=?", ('key', 'a',) creates WHERE 'key'='a' and it gets zero rows.

Example SET ?=? raises error.

CREATE TABLE test(key TEXT, value TEXT)
---
BEGIN 
INSERT INTO test(key, value) VALUES ('a', '1')
COMMIT
---
SELECT key, value FROM test WHERE key='a'
('a', '1')
---
SELECT key, value FROM test WHERE 'key'='a'
None
---
Exception: near "?": syntax error
---
BEGIN 
UPDATE test SET value='3' WHERE key='a'
COMMIT
---
SELECT key, value FROM test WHERE key='a'
('a', '3')