[New-bugs-announce] [issue43831] sqlite: convert_timestamp raises ValueError for empty columns

[Lumír Balhar]

New submission from Lumír Balhar <frenzy.madness at gmail.com>:

Hello.

I've discovered this issue when I was debugging a test failure in IPython. See this issue for more details (not needed): https://github.com/ipython/ipython/issues/12906

I'm testing this on Fedora Linux with Python 3.10.0a7 from our RPM package with SQLite 3.35.4.

I have a very simple SQLite database:

# sqlite3 test.sqlite 
SQLite version 3.35.4 2021-04-02 15:20:15
Enter ".help" for usage hints.
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE sessions (session integer
                        primary key autoincrement, start timestamp,
                        end timestamp, num_cmds integer, remark text);
INSERT INTO sessions VALUES(1,'2021-04-13 09:44:58.903345',NULL,NULL,'');
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('sessions',1);
COMMIT;

When I query it without special converters, it works well:

# python3
>>> import sqlite3
>>> conn = sqlite3.connect('test.sqlite')
>>> c = conn.cursor()
>>> c.execute("SELECT * from sessions where session == 1").fetchone()
(1, '2021-04-13 09:44:58.903345', None, None, '')
>>>

but with detect_types=sqlite3.PARSE_DECLTYPES, it fails to parse the date and time:

# python3
>>> import sqlite3
>>> conn = sqlite3.connect('test.sqlite', detect_types=sqlite3.PARSE_DECLTYPES)
>>> c = conn.cursor()
>>> c.execute("SELECT * from sessions where session == 1").fetchone()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib64/python3.10/sqlite3/dbapi2.py", line 67, in convert_timestamp
    datepart, timepart = val.split(b" ")
ValueError: not enough values to unpack (expected 2, got 1)
>>>

With help of pdb in ipython, I've discovered that the value convert_timestamp gets to parse is an empty bytestring:

In [5]: c.execute("SELECT * from sessions where session == 1").fetchone()                     
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-5-c234e57285e4> in <module>
----> 1 c.execute("SELECT * from sessions where session == 1").fetchone()

/usr/lib64/python3.10/sqlite3/dbapi2.py in convert_timestamp(val)
     65 
     66     def convert_timestamp(val):
---> 67         datepart, timepart = val.split(b" ")
     68         year, month, day = map(int, datepart.split(b"-"))
     69         timepart_full = timepart.split(b".")

ValueError: not enough values to unpack (expected 2, got 1)
> /usr/lib64/python3.10/sqlite3/dbapi2.py(67)convert_timestamp()
     65 
     66     def convert_timestamp(val):
---> 67         datepart, timepart = val.split(b" ")
     68         year, month, day = map(int, datepart.split(b"-"))
     69         timepart_full = timepart.split(b".")

ipdb> val                                                                                     
b''
ipdb>

Is anything in my database wrong? It seems that the content of the start column is correct and covert_timestamp should be able to parse it. Is it possible that the troublemaker here is the empty column `end` of type timestamp?

Answer to my own question: yes, the issue here is that the column `end` is of type timestamp and it's empty. If I update it with a date and time, everything works:

# sqlite3 test.sqlite
sqlite> update sessions set end='2021-04-14 09:44:58.903345' where session = 1;
sqlite> select * from sessions;
1|2021-04-13 09:44:58.903345|2021-04-14 09:44:58.903345||
sqlite>

# python3
>>> import sqlite3
>>> conn = sqlite3.connect('test.sqlite', detect_types=sqlite3.PARSE_DECLTYPES)
>>> c = conn.cursor()
>>> c.execute("SELECT * from sessions where session == 1").fetchone()
(1, datetime.datetime(2021, 4, 13, 9, 44, 58, 903345), datetime.datetime(2021, 4, 14, 9, 44, 58, 903345), None, '')

So, the final question is whether this is correct behavior. I believe that columns without content should not be passed to converters.

----------
components: Library (Lib)
messages: 390953
nosy: frenzy
priority: normal
severity: normal
status: open
title: sqlite: convert_timestamp raises ValueError for empty columns
versions: Python 3.10

_______________________________________
Python tracker <report at bugs.python.org>
<https://bugs.python.org/issue43831>
_______________________________________