simonw / sqlite-utils

Python CLI utility and library for manipulating SQLite databases
https://sqlite-utils.datasette.io
Apache License 2.0
1.63k stars 111 forks source link

Add COLUMN_TYPE_MAPPING for timedelta #522

Closed maport closed 10 months ago

maport commented 1 year ago

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.