python sqlite named parameter containing dash

Question

I'm trying to insert a record into an sqlite database using named parameters in python (with the sqlite3 module). The values I want to insert are in a dictionary, but the dictionary keys might contain dashes, for example {'request-id': 100, 'year': '2015'}. I'm trying to execute the following:

import sqlite3
conn = sqlite3.connect('database.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS requests (request_id text, year text)''')
query = '''INSERT INTO requests (request_id, year) VALUES (:request-id, :year)'''
cursor.execute(query, {'request-id': 100, 'year': '2015'})
conn.commit()
conn.close()

I get this error during the insert statement:

sqlite3.OperationalError: no such column: id

It seems like dashes are not well accepted as named parameters.

There are many workarounds for this, like creating a new dictionary where dashes in the keys are replaced by underscores, but I'd like to know if I could use some escaping technique or something else to avoid that.

Thanks for your help

Your `column names` don't match too. It is `date` in `create` and `year` in `insert`. — Prerak Sola, Jan 28 '15 at 13:59
No I guess , the `id` here is referring to inconsistent use of `date` while creating table and `year` thereafter. — ZdaR, Jan 28 '15 at 14:03
Sorry, it was a cut and paste error, the actual table contains the "year" column. I've edited the question accordingly. Thanks — Daniele, Jan 28 '15 at 16:27

score 0 · Answer 1 · answered Jan 28 '15 at 15:04

0

SQL parameter names have no quoting or escaping mechanism; you have to use the same rules as for an unquoted identifier.

answered Jan 28 '15 at 15:04

CL.

173,858
17
217
259

score 0 · Accepted Answer · answered Jan 28 '15 at 15:04

The documentation for sqlite3_bind_* states that parameter names must be composed of alphanumeric characters, and doesn't mention a way of escaping them.

Your query is probably being parsed as :request - id, i.e. :request minus id, and since there's no such column id, SQLite throws an error.

(Also, as Prerak Sola points out, you create the table with a date column but try to insert to a year column which doesn't exist.)

python sqlite named parameter containing dash

2 Answers2