Python SQLite3 Insert

Question

I'm getting an error when I insert values. My db has 3 columns. One autoincrement integer initialized here:

connection.execute("CREATE TABLE IF NOT EXISTS {tn} ({nf1} {ft1} PRIMARY KEY AUTOINCREMENT)"\
    .format(tn = tableName, nf1 = "IDPK", ft1 = "INTEGER"))

and two text fields initialized like this:

connection.execute("ALTER TABLE {tn} ADD COLUMN '{cn}' {ct}".format(tn = tableName, cn = "foo", ct = "TEXT"))
connection.execute("ALTER TABLE {tn} ADD COLUMN '{cn}' {ct}".format(tn = tableName, cn = "bar", ct = "TEXT"))

the execution is here:

connection.execute("INSERT INTO {tn} VALUES (NULL, {col1}, {col2})".format(tn = tableName, col1 = text1, col2 = text2))

And the error thrown is:

sqlite3.OperationalError: no such column: "obfuscatedTextStringInText1"

I don't understand why it thinks the name of the column is in text1. I'm inserting a value into columns 1 and 2 I thought with this syntax, as the autoincrement functions with the NULL keyword.

I think the default value for null is "" in sqlite3. Try just adding values for just columns col1 and col2. — Marichyasana, Jul 08 '16 at 17:23

score 3 · Answer 1 · edited May 23 '17 at 11:44

Don't use string formatting to insert variables into the query. It is dangerous (you are vulnerable to SQL injection attacks) and error-prompt (as you can already see).

Instead, parameterize your query:

connection.execute("""
    INSERT INTO 
        {tn} 
    VALUES 
        (NULL, :col1, :col2)""".format(tn=tableName), 
    {"col1": text1, "col2": text2})

Note that we cannot parameterize table or column names - make sure you validate and properly escape the tableName, or trust your source.

score 1 · Accepted Answer · answered Jul 08 '16 at 17:23

1

There should be quotes arround {col1} and {col2} since they are being inserted as text values. For example, it currently is being evaluated like:

"INSERT INTO table_name VALUES (NULL, my text 1, my text 2)"

answered Jul 08 '16 at 17:23

Alden

2,229
1
15
21

score 1 · Answer 3 · edited May 05 '23 at 09:29

Don't use string formatting to insert variables into the query. It is dangerous (you are vulnerable to SQL injection attacks) and error-prompt (as you can already see).

(from @alecxe's answer) I removed all string formatting for a safer example:

new_element={'col1': 'foo', 'col2': 'TEXT with special characters like " and *'}
connection.execute("INSERT INTO tableName VALUES (NULL, :col1, :col2)", new_element)

Python SQLite3 Insert

3 Answers3