8

I'm experiencing a problem inserting values into a SQLite database. The data I download from the Norwegian Parliament site data.stortinget.no. The error I get is: sqlite3.OperationalError: unrecognized token: "01T00"

Here is the method in which the error occur: (I know about the indentation error in this excerpt)

def get_perioder(cur):
DOK = "stortingsperioder"
try:
     page = urllib2.urlopen(SITE+DOK)
except:
    print "Failed to fetch item "+DOK
if page:
    tree = ElementTree.parse(page)
    root = tree.getroot()
    top = list(root)[2]
    elements = list(top)
    for el in elements:
        fra = el.find('{http://data.stortinget.no}fra').text
        per_id = el.find('{http://data.stortinget.no}id').text
        til = el.find('{http://data.stortinget.no}til').text
        print "id: %s fra: %s til: %s" % (per_id, fra, til)
        cur.execute("INSERT INTO perioder(fra, id, til) VALUES(%s,%s,%s)" % (fra, per_id, til))
else:
    print "Could not load page: "+DOK

The message printed by the print just above cur.execute is: id: 2009-2013 fra: 2009-10-01T00:00:00 til: 2013-09-30T23:59:59 The whole error trace is: 

BigMac:Stortingsdata ola$ python getBasicData.py 
id: 2009-2013 fra: 2009-10-01T00:00:00 til: 2013-09-30T23:59:59
Traceback (most recent call last):
  File "getBasicData.py", line 169, in <module>
    get_perioder(cur)
   File "getBasicData.py", line 26, in get_perioder
     cur.execute("INSERT INTO perioder(fra, id, til) VALUES(%s,%s,%s)" % (fra, per_id, til))
 sqlite3.OperationalError: unrecognized token: "01T00"

I referred with the SQLite manual and it seems that the format is supported, so I'm wondering where the problem come from.

olovholm
  • 1,362
  • 5
  • 16
  • 23
  • 2
    You not pass date with quotation marks `INSERT INTO perioder(fra, id, til) VALUES('%s',%s,'%s')` ? – Jones Jun 22 '12 at 16:51
  • 1
    What you are inserting is `VALUES(2009-2013, 2009-10-01T00:00:00, 2013-09-30T23:59:59)`. You see why that's wrong, right? You just inserted the equivalent of `VALUES(-4, -1-01T00:00:00, 2004-30T23:59:59)`, which is clearly a syntax error since `01T00` can't be subtracted from `-1`. Just use `execute` properly instead of doing string interpolation. – Francis Avila Jun 22 '12 at 17:04
  • I see that now. I thought %s added the needed quotes, but I was wrong. Thank you both for your help, it's now working. Another problem arose when I ran the code. It performs the first method, but doesn't add data from the additional. Can I pass a cursor to methods and add data, or do I need more cursors? (The program doesn't have any concurrency) – olovholm Jun 22 '12 at 17:39

2 Answers2

20

The proper way is to use a parametrized query.
Example:

cur.execute("""INSERT INTO perioder(fra, id, til) 
               VALUES (?,?,?);""", (fra, per_id, til))

There is a specific parameter "style" for each database driver.
In the case of SQLite that parameter style is ?.

Also note that the parameter values are passed as a second argument to execute().
Using string-interpolation leaves you vulnerable to all kinds of quoting issues (like the one that brought you here) and the possibility of SQL-injection attack.

For more information please read the DB-API and the database programming wiki.

mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
  • Is there a better way to do this if the table I'm inserting into has many fields (much more than 3)? – Mr. Shickadance Aug 31 '12 at 15:19
  • Sure. You don't have specify the column names like that. This syntax is also valid: `INSERT INTO your_table VALUES (......);`. And you can dynamically generate the parameter placeholders. Please see this answer http://stackoverflow.com/a/2253879/42346 for information about that second step. – mechanical_meat Aug 31 '12 at 16:27
1

If you want to store the date stamps as strings (TEXT) in SQLite, I recommend you format the text you would like to execute as follows:

cur.execute("""INSERT INTO perioder(fra, id, til) 
           VALUES (\"%s\",\"%s\",\"%s\")""" % (fra, per_id, til))

SQLite returns errors if the values you insert does not have inverted commas. Formatting your text with \"%s\" instead of %s will insert the string value with inverted commas in your formatted string:

"INSERT INTO perioder(fra, id, til) 
           VALUES ("2009-2013", "2009-10-01T00:00:00","2013-09-30T23:59:59")"
Neil
  • 49
  • 5
  • There is really no benefit in manually quoting the values when parameter substitution, as demonstrated in the accepted answer, does it automatically and correctly handles different data types and tricky cases like embedded quotes. – snakecharmerb Sep 18 '21 at 09:55
  • Thanks @Neil. `cursor.execute(f"create table \'{tbl_name}\' (\'{col_str}\');")` worked for my use (when the `tbl_name` strings start with an integer) while `cursor.execute(f"create table (?, ?);" (tbl_name, col_str))` returned `TypeError: 'str' object is not callable` . @snakecharmerb any idea on how to get the 2nd one working too? Thanks. – Lod Nov 11 '22 at 13:43
  • @Lod Parameter substitution only works for _values_. For _identifiers_ like table or column names you need to quote them yourself. – snakecharmerb Nov 11 '22 at 13:49
  • @snakecharmerb but why does _identifiers_ substitutions work then with `cursor.execute("create table %s (%s);" % (tbl_name, col_str)) ` ? Thanks. – Lod Nov 11 '22 at 14:01