Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add COLUMN_TYPE_MAPPING for timedelta #522

Closed
maport opened this issue Jan 23, 2023 · 0 comments · Fixed by #596
Closed

Add COLUMN_TYPE_MAPPING for timedelta #522

maport opened this issue Jan 23, 2023 · 0 comments · Fixed by #596
Labels
bug Something isn't working

Comments

@maport
Copy link

maport commented Jan 23, 2023

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.

nezhar added a commit to nezhar/sqlite-utils that referenced this issue Sep 29, 2023
@simonw simonw added the bug Something isn't working label Nov 4, 2023
simonw added a commit that referenced this issue Nov 4, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants