Issue 19065: sqlite3 timestamp adapter chokes on timezones (original) (raw)

If you use detect_types=sqlite3.PARSE_DECLTYPES with sqlite3 and insert a timezone-aware datetime instance, you will get a ValueError if you attempt to read it back out:

File "/usr/local/Cellar/python3/3.3.2/Frameworks/Python.framework/Versions/3.3/lib/python3.3/sqlite3/dbapi2.py", line 68, in convert_timestamp hours, minutes, seconds = map(int, timepart_full[0].split(b":")) ValueError: invalid literal for int() with base 10: '03+00'

Although this immediately gets into the thorny stdlib timezone support situation, it's extremely annoying to have the out-of-the-box module break round-tripping data and it looks like support for simple UTC offsets isn't too horrible – something like https://gist.github.com/acdha/6655391 works in very limited testing:

def tz_aware_timestamp_adapter(val): datepart, timepart = val.split(b" ") year, month, day = map(int, datepart.split(b"-"))

if b"+" in timepart:
    timepart, tz_offset = timepart.rsplit(b"+", 1)
    if tz_offset == b'00:00':
        tzinfo = datetime.timezone.utc
    else:
        hours, minutes = map(int, tz_offset.split(b':', 1))
        tzinfo = datetime.timezone(datetime.timedelta(hours=hours, minutes=minutes))
else:
    tzinfo = None

timepart_full = timepart.split(b".")
hours, minutes, seconds = map(int, timepart_full[0].split(b":"))

if len(timepart_full) == 2:
    microseconds = int('{:0<6.6}'.format(timepart_full[1].decode()))
else:
    microseconds = 0

val = datetime.datetime(year, month, day, hours, minutes, seconds, microseconds, tzinfo)

return val

sqlite3.register_converter('timestamp', tz_aware_timestamp_adapter)

Just open an issue to propose the deprecation, if they aren't part of the DBAPI. We wouldn't actually remove them, though, until after 2.7 goes out of maintenance (if then...we still haven't quite decided what we're going to do about removals post 2.7).