| Bug #108733 | python connector will return a date object when time is 00:00:00 | ||
|---|---|---|---|
| Submitted: | 11 Oct 2022 7:39 | Modified: | 5 Dec 2022 23:01 |
| Reporter: | Zhao Rong (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / Python | Severity: | S3 (Non-critical) |
| Version: | 8.0.29/8.0.30 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution | ||
[11 Oct 2022 8:43]
Zhao Rong
created a PR for suggested fix: https://github.com/mysql/mysql-connector-python/pull/83
[11 Oct 2022 12:23]
MySQL Verification Team
Thank you for the report and contribution. regards, Umesh
[11 Oct 2022 23:49]
OCA Admin
Contribution submitted via Github - Fix: Bug #108733 python connector will return a date object when time… (*) Contribution by Zhao Rong (Github rozhao2, mysql-connector-python/pull/83#issuecomment-1275396086): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: git_patch_1082966548.txt (text/plain), 2.09 KiB.
[13 Oct 2022 14:53]
Nuno Mariz
Posted by developer: Thank you for the contribution.
[5 Dec 2022 23:01]
Philip Olson
Posted by developer: Fixed as of the upcoming MySQL Connector/Python 8.0.32 release, and here's the proposed changelog entry from the documentation team: When using a prepared cursor, if a datetime column contained 00:00:00 as the time, a Python date object was returned instead of datetime. Thanks to Rong Zhao for the contribution. Thank you for the bug report.
Description: python connector will return a date object when time is 00:00:00 when set binary=True How to repeat: Sample data: +--------------+---------------------+ | record_id | updated_at | +--------------+---------------------+ | 1111111 | 2016-02-01 00:00:00 | +--------------+---------------------+ table schema +----------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+------------+------+-----+---------+-------+ | record_id | bigint(20) | NO | PRI | NULL | | | updated_at | datetime | YES | | NULL | | +----------------+------------+------+-----+---------+-------+ please notice, here column type is datetime, not date Then, run python code: conn = connector.connect(host="127.0.0.1", database="xxxx", user="xxxx", password="xxxx", port=3306, ssl_disabled=True) cursor = conn.cursor(prepared=True) cursor.execute("select updated_at from table111 where record_id='1111111'") result = cursor.fetchall() print(result) result is: [(datetime.date(2016, 2, 1),)] If we change cursor = conn.cursor(prepared=True) to cursor = conn.cursor(prepared=False) then result will be: [(datetime.datetime(2016, 2, 1, 0, 0),)] Same problem: https://stackoverflow.com/questions/36703855/how-can-i-stop-python-from-converting-a-mysql... Suggested fix: The column type is datetime, it should return python datetime object whatever the value is. The root cause is some cursor implementation set binary=True, then socket receive packet will be short when time is 00:00:00 then function _parse_binary_timestamp will judge value type by packet length: if length == 4: value = datetime.date( year=struct.unpack('<H', packet[1:3])[0], month=packet[3], day=packet[4]) elif length >= 7: mcs = 0 if length == 11: mcs = struct.unpack('<I', packet[8:length + 1])[0] value = datetime.datetime( year=struct.unpack('<H', packet[1:3])[0], month=packet[3], day=packet[4], hour=packet[5], minute=packet[6], second=packet[7], microsecond=mcs)