mysqldb converts timestamp data to None

Question

I am using MySQLdb to talk to mysql database and I am able to retrieve dynamically all the result sets.

My problem is that once I get the result set, there are a couple columns which are declared as timestamps in mysql but when it is retrieved, it turns to None.

I have two columns, both are declared timestamps but one returns correct data while other returns None. Both utime and enddate are declared timestamps but utime does not return correctly while enddate does.

['utime', 'userstr', 'vstr_client', 'enddate']

((None, '000102030ff43260gg0809000000000004', '7.7.0', '1970-01-01 12:00:00.000000'))

def parse_data_and_description(cursor, data):

    res = []
    cols = [d[0] for d in cursor.description]
    print cols
    print data

    for i in data:
        res.append(OrderedDict(zip(cols, i)))
    return res

def call_multi_rs(sp, args):

    rs_id=0;
    conn = connect()
    cursor = conn.cursor()
    try:
        conn.autocommit(True)
        cursor.execute ("CALL %s%s" % (sp, args))
        while True:
            rs_id+=1
            data = cursor.fetchone( )
            listout = parse_data_and_description(cursor, data)
            print listout
            if cursor.nextset( )==None:
            # This means no more recordsets available
            break

Answer 1

Finally after nobody answered or tried finding more information, I went ahead and looked for more solutions and found that the MySQLdb library converts the datatypes from sql to python and there is a bug which does not convert the timestamp.

I still do not know why one of them is converted and the other is not. If somebody can figure that out, please update this.

But here is the modification that needs to be done when connecting to the mysql database. MySQLdb can't serialize a python datetime object

try:
    import MySQLdb.converters
except ImportError:
    _connarg('conv')

def connect(host='abc.dev.local', user='abc', passwd='def', db='myabc', port=3306):

    try:
        orig_conv = MySQLdb.converters.conversions
        conv_iter = iter(orig_conv)
        convert = dict(zip(conv_iter, [str,] * len(orig_conv.keys())))
        print "Connecting host=%s user=%s db=%s port=%d" % (host, user, db, port)
        conn = MySQLdb.connect(host, user, passwd, db, port, conv=convert)
    except MySQLdb.Error, e:
        print "Error connecting %d: %s" % (e.args[0], e.args[1])
    return conn

Answer 2

I stumbled upon the same problem: Retrieving data of the DATETIME(1)-type returns None.

Some research brought up MySQLdb-Bug #325. According to that bug tracker, the issue is still open (has been for over 2 years now), but the comments provide a working solution:

In times.py of the MySQLdb package, you need to insert some lines to handle microseconds like this:

def DateTime_or_None(s):
    if ' ' in s:
       sep = ' '
    elif 'T' in s:
        sep = 'T'
    else:
        return Date_or_None(s)

    try:
        d, t = s.split(sep, 1)
        if '.' in t:
            t, ms = t.split('.',1)
            ms = ms.ljust(6, '0')
        else:
            ms = 0
        return datetime(*[ int(x) for x in d.split('-')+t.split(':')+[ms] ])
    except (SystemExit, KeyboardInterrupt):
        raise
    except:
        return Date_or_None(s)

def TimeDelta_or_None(s):
    try:
        h, m, s = s.split(':')
        if '.' in s:
            s, ms = s.split('.')
            ms = ms.ljust(6, '0')
        else:
            ms = 0
        h, m, s, ms = int(h), int(m), int(s), int(ms)
        td = timedelta(hours=abs(h), minutes=m, seconds=s,
                       microseconds=ms)
        if h < 0:
            return -td
        else:
            return td
    except ValueError:
        # unpacking or int/float conversion failed
        return None

def Time_or_None(s):
    try:
        h, m, s = s.split(':')
        if '.' in s:
            s, ms = s.split('.')
            ms = ms.ljust(6, '0')
        else:
            ms = 0
        h, m, s, ms = int(h), int(m), int(s), int(ms)
        return time(hour=h, minute=m, second=s, microsecond=ms)
    except ValueError:
        return None

What I cannot explain, though, is your original query working on one column and not on the other.. Maybe, the second does not have any microsecond-information in it?