homepage

sqlite3/dbapi2.py contains the following - 
 def convert_timestamp(val): 
 datepart, timepart = val.split(b" ")
 timepart_full = timepart.split(b".")
 [...] 
 if len(timepart_full) == 2: 
 microseconds = int(timepart_full[1]) 
 else: 
 microseconds = 0 
It assumes that 'timepart_full[1]' is a string containing 6 digits. 
I have a situation where the string containing 3 digits, so it gives the wrong result. For example, if the string is '456', this represents 456000 microseconds, but sqlite3 returns 456 microseconds.
I think that it should right-zero-fill the string to 6 digits before converting to an int, like this - 
 microseconds = int('{:0<6}'.format(timepart_full[1]))

Can be reproduced with:
>>> con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
>>> cur = con.cursor()
>>> cur.execute("CREATE TABLE t (x TIMESTAMP)")
<sqlite3.Cursor object at 0x7f90a4f69ea0>
>>> cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.456')")
<sqlite3.Cursor object at 0x7f90a4f69ea0>
>>> cur.execute("SELECT * FROM t")
<sqlite3.Cursor object at 0x7f90a4f69ea0>
>>> cur.fetchall()
[(datetime.datetime(2012, 4, 4, 15, 6, 0, 456),)]

Can convert_timestamp(val) be implemented as datetime.datetime.strptime(val.decode(), '%Y-%m-%d %H:%M:%S.%f')?

> Can convert_timestamp(val) be implemented as datetime.datetime.strptime(val.decode(), '%Y-%m-%d %H:%M:%S.%f')?
No. The microseconds are not always included, as can be seen by the current implementation. Nice idea though!

Add patch for 2.7 branch with regression test.

New changeset 6911df35b7b6 by Petri Lehtinen in branch '2.7':
Issue #14720: sqlite3: Convert datetime microseconds correctly
http://hg.python.org/cpython/rev/6911df35b7b6
New changeset 46d5317a51fb by Petri Lehtinen in branch '3.2':
Issue #14720: sqlite3: Convert datetime microseconds correctly
http://hg.python.org/cpython/rev/46d5317a51fb
New changeset 46c96693296f by Petri Lehtinen in branch '3.3':
Issue #14720: sqlite3: Convert datetime microseconds correctly
http://hg.python.org/cpython/rev/46c96693296f
New changeset 6342055ac220 by Petri Lehtinen in branch 'default':
Issue #14720: sqlite3: Convert datetime microseconds correctly
http://hg.python.org/cpython/rev/6342055ac220 

Applied, thanks!

convert_timestamp() can silently return wrong result if seconds saved with more than millisecond precision (i.e. '2012-04-04 15:06:00.000123456'). I propose or truncate fractional part to 6 digits ('{:0<6.6}') or explicitly raise an exception if len(timepart_full[1]) > 6.

Serhiy Storchaka wrote:
> convert_timestamp() can silently return wrong result if seconds
> saved with more than millisecond precision (i.e. '2012-04-04
> 15:06:00.000123456'). I propose or truncate fractional part to 6
> digits ('{:0<6.6}') or explicitly raise an exception if
> len(timepart_full[1]) > 6.
That's a good point. Also, '2012-04-04 15:06:00.1234567' fails with a
ValueError when executing the SELECT statement, because the
microsecond part is not in range 0-999999.
Truncating the fractional part to 6 characters sounds good to me,
because that way we get the best possible precision without failing
abruptly.

New changeset eb45fd74db34 by Petri Lehtinen in branch '2.7':
Issue #14720: Enhance sqlite3 microsecond conversion, document its behavior
http://hg.python.org/cpython/rev/eb45fd74db34
New changeset ae25a38e6c17 by Petri Lehtinen in branch '3.2':
Issue #14720: Enhance sqlite3 microsecond conversion, document its behavior
http://hg.python.org/cpython/rev/ae25a38e6c17
New changeset 17673a8c7083 by Petri Lehtinen in branch '3.3':
Issue #14720: Enhance sqlite3 microsecond conversion, document its behavior
http://hg.python.org/cpython/rev/17673a8c7083
New changeset 0db66afbd746 by Petri Lehtinen in branch 'default':
Issue #14720: Enhance sqlite3 microsecond conversion, document its behavior
http://hg.python.org/cpython/rev/0db66afbd746 

Fixed.