2
token=uuid.uuid4().bytes.encode("base64")
expires=datetime.now()+timedelta(days=1)
print token
print expires
con = sqlite3.connect(dbpath,detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute(
    "INSERT INTO token VALUES ('%s', ?)" % 
      (token,expires))
a=cur.fetchone()
con.commit()
con.close()

Table CREATE TABLE token (token varchar(255),expires DATE);

Error TypeError: not all arguments converted during string formatting

Abdul Kader
  • 5,781
  • 4
  • 22
  • 40
  • 1
    This has been answered already http://stackoverflow.com/questions/1829872/read-datetime-back-from-sqlite-as-a-datetime-in-python – XORcist Apr 17 '11 at 12:06

2 Answers2

9

Never use % operator with SQL - it can lead to SQL injection. Fix your execute statement like this:

cur.execute("INSERT INTO token VALUES (?, ?)", (token,expires))

Actually there is another one problem: you can't use cur.fetchone() after INSERT.

Full example:

$ sqlite3 test.db
sqlite> create table token (token text primary key, expires text);

$ python
>>> import sqlite3
>>> from datetime import datetime, timedelta
>>> from uuid import uuid4
>>> token = uuid4().bytes.encode("base64")
>>> expires = datetime.now() + timedelta(days=1)
>>> conn = sqlite3.connect("test.db")
>>> cur = conn.cursor()
>>> cur.execute("INSERT INTO token VALUES (?, ?)", (token, expires))
<sqlite3.Cursor object at 0x7fdb18c70660>
>>> cur.execute("SELECT * FROM token")
<sqlite3.Cursor object at 0x7fdb18c70660>
>>> cur.fetchone()
(u'9SVqLgL8ShWcCzCvzw+2nA==\n', u'2011-04-18 15:36:45.079025')
hdima
  • 3,627
  • 1
  • 19
  • 19
  • ... which is documented in more detail on this page: http://docs.python.org/release/2.6/library/sqlite3.html – arie Apr 17 '11 at 11:27
  • i can persist into the database if i use %s placeholder in sqlite3. But will it be datetime.datetime when i retrieve – Abdul Kader Apr 17 '11 at 11:34
  • @Abdul Kader I added example session for sqlite3 and python. And you can't retrieve datetime object back from SQLite database without parsing because there is no datetime type in SQLite and datetime object will be stored as text. – hdima Apr 17 '11 at 11:45
  • I actully it stores as a unicode object. I want it to store as a datetime.datetime object. You can have a look here http://stackoverflow.com/questions/4272908/sqlite-date-storage-and-conversion – Abdul Kader Apr 17 '11 at 11:51
  • Check out this section of the documentation: http://docs.python.org/release/2.6/library/sqlite3.html#default-adapters-and-converters But this example doesn't work for me. And you can also create converters yourself: http://docs.python.org/release/2.6/library/sqlite3.html#converting-sqlite-values-to-custom-python-types – hdima Apr 17 '11 at 12:07
  • To get the datetime object. Did something like this expires=datetime.strptime(row['expires'],"%Y-%m-%d %H:%M:%S.%f") – Abdul Kader Apr 19 '11 at 16:01
1

The error is self-speaking.

The string interpolation fails because you are passing two parameters to the INSERT string but there is only one %s placeholder. What do you want with '?' here.

Perhaps you ant

cur.execute('INSERT....', token, expires)

??

  • I want to persist datetime object.. Which placeholder i should use so that i comes as datetime object not as string – Abdul Kader Apr 17 '11 at 11:19