PyMySQL can't Output MySQL's time field's

[April29th]

Hi.

Using:

• Python 3.2.3
• PyMySQL 0.5
• MySQL 5.5.22-0Ubuntu1

Python 3 script:

    import pymysql

    yr = int(input("What year? (choose 1980-2012) "))
    sql = "SELECT artist,title,heard FROM favsongs WHERE year(heard)=" + str(yr)

    conn = pymysql.connect(host='localhost', port=3306, user='myself', passwd='blahblahblah', db='musicfavs')
    cur = conn.cursor()
    cur.execute(sql)

    for r in cur.fetchall():
       print(r)

    cur.close()
    conn.close()
    # end of Python script

MySQL table description:

    +----------+-----------------------+------+-----+---------+----------------+
    | Field    | Type                  | Null | Key | Default | Extra          |
    +----------+-----------------------+------+-----+---------+----------------+
    | id       | bigint(100) unsigned  | NO   | PRI | NULL    | auto_increment |
    | own      | tinyint(1)            | NO   |     | 0       |                |
    | heard    | datetime              | NO   |     | NULL    |                |
    | station  | varchar(12)           | NO   |     | NULL    |                |
    | released | date                  | NO   |     | NULL    |                |
    | artist   | varchar(255)          | NO   |     | NULL    |                |
    | title    | varchar(255)          | NO   |     | NULL    |                |
    | notes    | tinytext              | NO   |     | NULL    |                |
    +----------+-----------------------+------+-----+---------+----------------+


The script returns everything fine except for the datetime field, which PyMySQL always returns as 'None' even though as date & time exist. I've narrowed down the issue to MySQL's time field, even tho I'm using datetime, 'cause I found that the following works for extracting the date in pieces from the datetime field:

    SELECT artist,title,year(heard),month(heard),day(heard) FROM favsongs WHERE year(heard)=

But if I add `time(heard)`, then PyMySQL will return `datetime.timedelta(0)` instead of the actual time.

I consider this a very serious bug, since it completely stops me from being able to use PyMySQL, and that likely means it's blocking other people from using it too. None of the other python-to-MySQL modules want to install on my system, so I'm stuck 'til this bug is fixed.

[April29th]

Actually, I made a goof. The bug isn't where I thought it was. The bug is that PyMySQL 0.5 or Python itself, is altering the date to 'None' if the date format is an illegal date. For example, a date such as 2012-05-00 or 2012-04-00, would be returned by MySQL and then gets turned into 'None' before I can do anything with the data. As a web developer, this is a show stopper 'cause I need to use the data that MySQL holds & does queries on, even if it is technically illegal.

I've found the same issue in the MySQL Connector/Python database interface module too, so I'm guessing Python might be doin' this to illegal dates without you realizing it?

[ColtonProvias]

Python actually throws a ValueError exception when an illegal date or time is used. pymysql returns None because it catches the ValueError and exchanges it with just returning None. You can see this on lines 199-200 and 220-221 in latest converters.py file here on GitHub.

If you want illegal datetimes, you must handle them as strings since datetime.datetime will throw a ValueError otherwise. Go into your copy of converters.py, locate the return None line in the convert_date function, and change it to return obj

[April29th]

Ahhh I see. Thanks.