Add COLUMN_TYPE_MAPPING for timedelta #522
Description
Currently trying to create a column with Python type datetime.timedelta results in an error:
>>> from sqlite_utils import Database
>>> db = Database("test.db")
>>> test_tbl = db['test']
>>> test_tbl.insert({'col1': datetime.timedelta()})
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/lib/python3.10/dist-packages/sqlite_utils/db.py", line 2979, in insert
return self.insert_all(
File "/usr/local/lib/python3.10/dist-packages/sqlite_utils/db.py", line 3082, in insert_all
self.create(
File "/usr/local/lib/python3.10/dist-packages/sqlite_utils/db.py", line 1574, in create
self.db.create_table(
File "/usr/local/lib/python3.10/dist-packages/sqlite_utils/db.py", line 961, in create_table
sql = self.create_table_sql(
File "/usr/local/lib/python3.10/dist-packages/sqlite_utils/db.py", line 852, in create_table_sql
column_type=COLUMN_TYPE_MAPPING[column_type],
KeyError: <class 'datetime.timedelta'>
The reason this would be useful is that MySQLdb uses timedelta for MySQL TIME columns:
>>> import MySQLdb
>>> conn = MySQLdb.connect(host='database', user='user', passwd='pw')
>>> csr = conn.cursor()
>>> csr.execute("SELECT CAST('11:20' AS TIME)")
>>> tuple(csr)
((datetime.timedelta(seconds=40800),),)
So currently any attempt to convert a MySQL DB with a TIME column using db-to-sqlite will result in the above error.
I was rather surprised that MySQLdb uses timedelta for TIME columns but I see that this column type is intended for time intervals as well as the time of day so it makes sense.