2

I'd like to enter the current time into my sql table using sqlite3 and python. This code results in an error.

cur.execute('''
CREATE TABLE IF NOT EXISTS Finance (date DATE, time TEXT, cost FLOAT, item TEXT, cat TEXT)''')

time = datetime.datetime.now().time()

cur.execute('''INSERT INTO Finance (date, time, cost, item, cat) VALUES ( ?, ?, ?, ?, ? )''', ( date, time, cost, item, cat ) )

This is the error:

ur.execute('''INSERT INTO Finance (date, time, cost, item, cat) VALUES ( ?, ?, ?, ?, ? )''', ( date, time, cost, item, cat ) )
sqlite3.InterfaceError: Error binding parameter 1 - probably unsupported type.

However if I use this format for time,

time = datetime.datetime.now.ctime()

I can input the data with "Wed Mar 9 15:18:37 2016" format.

I just want the time, not the date and time. What datatype should I use? Thanks

jason
  • 113
  • 3
  • 10

2 Answers2

3

SQLite doesn't have DATE or TIME data types. It has some functions that can manipulate dates and times as TEXT (ISO8601 strings), REAL (Julian days) or INTEGER (Unix timestamps) values. So you have to use str, float, int or compatible python values.

https://www.sqlite.org/datatype3.html

To store time values as strings use HH:MM or HH:MM:SS or HH:MM:SS.SSS

Stop harming Monica
  • 12,141
  • 1
  • 36
  • 56
2

Convert your time to string:

str(time)

SQLite does not have a datatype for storing dates and/or times, so using TEXT like you did is the best option in your case. You want to make sure what you pass is a string, however.

pp_
  • 3,435
  • 4
  • 19
  • 27