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
– Prerak Sola, Commented Jan 28, 2015 at 13:59
No I guess , the id here is referring to inconsistent use of date while creating table and year thereafter. — ZdaR
– ZdaR, Commented Jan 28, 2015 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
– Daniele, Commented Jan 28, 2015 at 16:27

Colonel Thirty Two · Accepted Answer · 2015-01-28 15:04:48Z

0

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

answered Jan 28, 2015 at 15:04

Colonel Thirty Two

27k9 gold badges52 silver badges94 bronze badges

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

Comments

CL. · Accepted Answer · 2015-01-28 15:04:11Z

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, 2015 at 15:04

CL.

182k18 gold badges241 silver badges282 bronze badges

Collectives™ on Stack Overflow

python sqlite named parameter containing dash

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related