MySQL Connector/Python

DATETIME Output As 'None'

Asked by Forrest Young

Hi, I just installed version 0.3.2 and have it working with Python 3.2.3 & MySQL 5.5.22-0Ubuntu1. Problem is, it outputs the value of DATETIME fields as 'None' and TIME fields as datetime.timedelta(0). I tried importing python's datetime module, but that isn't helping. Is this a bug? I'm not trying to do anything fancy either, here's the code --

-- start of script --
import mysql.connector, sys, os

from datetime import datetime
import time

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

conn = mysql.connector.Connect(host='localhost',user='forrest',\
                        password='mypass',database='musicchart')

c = conn.cursor()
c.execute (sql)

for row in c.fetchall():
        print (row)

print(sql)
c.close()
-- end of script --

In case it helps, here's the MySQL table description:

    +----------+-----------------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +----------+-----------------------+------+-----+---------+----------------+
    | id | bigint(100) unsigned | NO | PRI | NULL | auto_increment |
    | own | tinyint(1) | NO | | 0 | |
    | played | 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 | |
    +----------+-----------------------+------+-----+---------+----------------+

... it is possible to grab the year, month, & day separately, by using a modified SQL query like this:

SELECT artist,title,year(played),month(played),day(played) FROM ...

But that's not quite ideal &, adding in time(played), still doesn't output the time so it's still not usable.

Oh, and I tried replacing the for loop printing with the printing in the dates.py example that comes in the example folder. It didn't fix it. I really can't see why the code would work in the dates.py but not in my code.

So, am I just missing something or is this a bug that has yet to be worked out?

Question information

Language:
English Edit question
Status:
Solved
For:
MySQL Connector/Python Edit question
Assignee:
No assignee Edit question
Solved by:
Geert JM Vanderkelen
Solved:
Last query:
Last reply:
Revision history for this message
Geert JM Vanderkelen (geertjmvdk) said : 		#1

The example shown does not match the table used. The field 'heard' is for example not a column in the table definition, I presume 'played' was meant.

Below is an example using a smaller table and some data. It gets the data and prints out correctly.

Can you double check the actual data in the table whether it is correct? Also, if you post a table definition, best to use SHOW CREATE TABLE. (And an extra tip, using YEAR(c1) = <value> is not good, since it will not use any indexing).

from datetime import date, datetime

import mysql.connector

cnx = mysql.connector.connect(database='test')

table = """
CREATE TABLE t1 (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
released DATE,
played DATETIME,
PRIMARY KEY (id))
"""

cur = cnx.cursor()
cur.execute("DROP TABLE IF EXISTS t1")
cur.execute(table)

data = [
    (date(1982,2,1), datetime(2012,2,1,12,0,2)),
    (date(1983,2,1), datetime(2012,3,1,16,0,3)),
    ]
cur.executemany("INSERT INTO t1 (released, played) VALUES (%s,%s)", data)
cnx.commit()

cur.execute("SELECT id,released,played FROM t1 WHERE YEAR(released)=%s",
            (1982,))

for row in cur:
    print row

cnx.close()

Revision history for this message
Forrest Young (zamphatta) said : 		#2

I made a simpler script & DB table, added a few dates and discovered the problem isn't what I thought. The problem is that if the date is an illegal date, like 2012-05-00 00:00:01 or 2012-00-03 21:00:00, then either Python or myconnpy is altering the result to 'None'.

As a developer, I'd consider this a bug that needs to be fixed, since the module should return the result set data that MySQL gives it. I can understand if you don't consider this a bug though, 'cause the date is technically illegal. Although, if the database can hold it & use it in queries, then I should be able to use it too.

Anyway, in case it helps, here's the code I'm using to show this:

-- python script --
import mysql.connector, sys, os
from datetime import datetime
import time

conn = mysql.connector.Connect(host='localhost',user='forrest',password='mypass',database='test')
c = conn.cursor()
c.execute("SELECT * FROM test")

for row in c.fetchall():
        print (row)
c.close()
-- end python script --

To create the table:

CREATE TABLE `test` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `released` date DEFAULT NULL,
  `played` datetime NOT NULL DEFAULT '2012-05-00 00:00:00',
  `tiempo` time DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8

The data I used in the table to see this:

INSERT INTO `test` (`id`, `released`, `played`, `tiempo`) VALUES
(1, '2000-01-01', '1999-12-31 01:34:00', '01:38:04'),
(2, '2012-05-00', '2012-05-08 00:01:00', '12:09:00'),
(3, '2005-08-00', '2006-04-02 00:00:00', '00:00:01'),
(4, '2007-00-00', '2008-00-00 17:10:01', '17:10:01');

Thanks for your patience with me. I'm a lil' new to Python & trying to get it workin' with the databases that I've been using with my PHP apps. I'm trying to get my apps to work in Python, so I hope you decide to consider this a bug & fix it.

Revision history for this message
Best Geert JM Vanderkelen (geertjmvdk) said : 		#3

As you mentioned, the DATE[TIME] values are considered illegal in Python, so the returned value is None. Maybe we could throw an exception for this instead of returning None?

If you still want to use wrong data in your database, you can use the 'raw' option for the cursor:
  cursor = cnx.cursor(raw=True)

This will give you the result back as returned by the MySQL server without converting it to Python types. You are then responsible to parse it. (You can also write your own converter class and hook in a special case for illegal dates, but that's already stretching it..)

Please report a bug on bugs.mysql.com if you want to make Connector/Python throw an exception instead of returning None. It can be useful since a DATETIME field might be set to allow NULL values, which are also None. We will never be able to return a Python type with invalid dates from the Standard Library though.

Revision history for this message
Geert JM Vanderkelen (geertjmvdk) said : 		#4

Correction to the answer: ".. to use wrong data.." should be ".. to use illegal dates.."

Revision history for this message
Forrest Young (zamphatta) said : 		#5

I see what you're saying.

Since most of my DB's with datetime don't involve illegal dates, I can just use the raw=True for this. I never would've guessed. I can see the sense of being limited by the language and that's not a bad thing. As long as there's a way around it for special cases, I can work with that. In fact, I have to say that I love Connector/Python, it's very easy to jump into using.

I don't want to create a new account just to file one bug report, could someone else report the bug?

Thanks!

Revision history for this message
Forrest Young (zamphatta) said : 		#6

Thanks Geert JM Vanderkelen, that solved my question.