python sqlite3 line not evaluating correctly

Question

I must have a mistake in here somewhere but I cannot find it, not terribly experienced with writing SQL...

>>> db.c.execute('INSERT INTO {tn} ({c1n}, {c2n}) VALUES ({wn}, {f})'.\
...    format(tn=db.table1, c1n=db.column1, c2n=db.column2, wn='someword', f=1))

it is giving me this traceback...

Traceback (most recent call last):
    File "<stdin>", line 2, in <module>
    sqlite3.OperationalError: no such column: someword

'someword' should be the value it is inserting, not the column it is looking for, right?

score 4 · Accepted Answer · edited May 23 '17 at 11:59

You are missing the quotes around the values:

INSERT INTO {tn} ({c1n}, {c2n}) VALUES ('{wn}', '{f}')
                                    HERE^

As a side note, you should not be making queries via string formatting - it is not safe, you are making your code vulnerable to SQL injection attacks and getting problems with type conversions and quotes (as you can already see). Use parameterized queries. Unfortunately, you cannot parameterize table and column names and should validate them separately; but you can do parameter substition for column values:

db.c.execute('INSERT INTO {tn} ({c1n}, {c2n}) VALUES (:wn, :f)'.\
    format(tn=db.table1, c1n=db.column1, c2n=db.column2), {'wn': 'someword', 'f': 1})

python sqlite3 line not evaluating correctly

1 Answers1