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.

• Marichyasana: I think the default value for null is "" in sqlite3. Try just adding values for just columns col1 and col2.

Answer 1

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.

Answer 2

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)"

Answer 3

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)