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
– Marichyasana, Commented Jul 8, 2016 at 17:23

Community · Accepted Answer · 2017-05-23 11:44:13Z

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

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.

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered Jul 8, 2016 at 17:43

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alden · Accepted Answer · 2016-07-08 17:23:40Z

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 8, 2016 at 17:23

Alden

2,2691 gold badge15 silver badges22 bronze badges

Comments

starball · Accepted Answer · 2023-05-05 09:29:55Z

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

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

edited May 5, 2023 at 9:29

starball♦

59.1k52 gold badges310 silver badges1k bronze badges

answered May 5, 2023 at 8:28

Håvard

1531 silver badge8 bronze badges

Collectives™ on Stack Overflow

Python SQLite3 Insert

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related