Sqlite3.operationalerror: Unrecognized Token: "01t00" Python Datestamp

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

Solution 1:

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.

Solution 2:

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