Python/SQLite: Error inserting datetime.time variable into column of type Time-CodePudding

I am having trouble passing a datetime.time variable into a SQLite database, I have some very basic code here to show what exactly the variable is.

import datetime as dt

time = dt.datetime.now().time() 
time = time.strftime('%H:%M') 
time = dt.datetime.strptime(time, '%H:%M').time()

print(time)
print(type(time))

time = dt.datetime.now().time() gets the current time in type datetime.time.

Output:

17:34:48.286215
<class 'datetime.time'>

time = time.strftime('%H:%M') is then retrieving just the hour and minute but is of type str

Output:

17:35
<class 'str'>

I then convert it back to a datetime.time with time = dt.datetime.strptime(time, '%H:%M').time() which gives the the output:

17:32:00
<class 'datetime.time'>

The column of type Time accepts the format of HH:SS as shown in the documentation (SQLite3 DateTime Documentation), so I am not sure why I am getting this error:

sqlite3.InterfaceError: Error binding parameter 11 - probably unsupported type.

From this INSERT statement:

cursor.execute("INSERT INTO booked_tickets VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", (booking_ref, ticket_date, film, showing, ticket_type, num_tickets, cus_name, cus_phone, cus_email, ticket_price, booking_date, booking_time, ))

EDIT: As requested, here is a snippet of code to recreate the table with the broken columns:

import datetime as dt
import sqlite3

connection = sqlite3.connect("your_database.db")
cursor = connection.cursor()

# Get the current time
time = dt.datetime.now().time()

# Format the time as a string using the '%H:%M' format
time_str = time.strftime('%H:%M')

# Parse the string back to a time object using the '%H:%M' format
time = dt.datetime.strptime(time_str, '%H:%M').time()

# Create the table
cursor.execute("CREATE TABLE test (example_time Time)")

# Insert the time into the example_time column
cursor.execute("INSERT INTO test VALUES (?)", (time, ))

connection.commit()

connection.close()

CodePudding user response：

Checkout the SQLite datatypes documentation

2.2. Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:
TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic
Gregorian calendar. INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can choose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

Store the dates as TEXT datatypes.

CodePudding user response：

There is no Date or Time data type in SQLite.

The documentation from the link that you have in your question clearly states that in SQLite you can store datetime in 3 ways: text in ISO-8601 format, integer unix epochs and float julian days.

If you chose the first way then you should pass strings:

booking_date = dt.datetime.now().date().strftime('%Y-%m-%d')
booking_time = dt.datetime.now().time().strftime('%H:%M:00') 
sql = "INSERT INTO booked_tickets VALUES (?,?,?,?,?,?,?,?,?,?)"
cursor.execute(sql, (booking_ref, ticket_date, film, showing, ticket_type, num_tickets, cus_name, cus_phone, cus_email, ticket_price, booking_date, booking_time))

But, you could also let SQLite get the current date and/or time.

Assuming that in the columns booking_date and booking_time you want the current date and time, you can define these columns as:

booking_date TEXT NOT NULL DEFAULT CURRENT_DATE, 
booking_time TEXT NOT NULL DEFAULT CURRENT_TIME

and then you don't need to pass anything for them in the INSERT statement:

sql = "INSERT INTO booked_tickets VALUES (?,?,?,?,?,?,?,?,?,?)"
cursor.execute(sql, (booking_ref, ticket_date, film, showing, ticket_type, num_tickets, cus_name, cus_phone, cus_email, ticket_price,))

CodePudding user response：

The documentation you refer to mostly discusses how to format column values that representing dates and times. That is, it discusses what you can do with dates and times that already exist in your database.

It does, however, give just enough information to help you here I think. It says:

Date and time values can be stored as

text in a subset of the ISO-8601 format,

numbers representing the Julian day, or

numbers representing the number of seconds since (or before) 1970-01-01 00:00:00 UTC (the unix timestamp).

So you want to define and supply your dates and times as either full ISO-8601 date strings or as numbers. When defining a table, you indicate which of these formats you wish to use by defining a column type as a STRING, REAL or INTEGER respectively.

Here's some documentation that discusses how to store dates and times in one of these formats: https://www.sqlitetutorial.net/sqlite-date/