#325 Datetime fields with microsecond shows as None

MySQLdb-1.3
open
Andy Dustman
None
1
2015-02-10
2012-10-03
Dmitry T.
No

When SELECT datatime field which contained microseconds from DB(MariaDB 5.5 and MySQL 5.6). Result can not be converted to datetime.datetime and data in that field shows as None. I use MariaDB 5.5.27.

For example:
Make table in DB and fill its

import MySQLdb
db=MySQLdb.connect(host="localhost",user="test",passwd="test",db="test")
c=db.cursor()
c.execure("""DROP TABLE IF EXISTS `t1`""")
c.execure("""CREATE TABLE `t1` (
  `id` int(11)
  `date` datatime(6) NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8""")
c.execure("""INSERT INTO t1 VALUES(1,now(6)), VALUES(2,NOW(6)), VALUES(3,NOW(6))""")

Now we can get data.

c.execute("""SELECT * FROM t1""")
r = c.fetchall()
print r

OUT:
((1L, None), (2L, None), (3L, None))

After patch:

c.execute("""SELECT * FROM t1""")
r = c.fetchall()
print r

OUT:
((1L, datetime.datetime(2012, 10, 3, 10, 56, 47, 800462)), (2L, datetime.datetime(2012, 10, 3, 10, 56, 47, 800462)), (3L, datetime.datetime(2012, 10, 3, 10, 56, 47, 800462)))
1 Attachments

Related

Bugs: #325

Discussion

  • Andy Dustman
    Andy Dustman
    2012-10-03

    It's not obvious what version of MySQLdb you tested against. Can you test against 1.2.4b2? I believe the issue is fixed there.

    • assigned_to: Andy Dustman
     
    • Dmitry T.
      Dmitry T.
      2013-03-28

      I have tried in 1.2.4b4, result tuple have None if DateTime field contained microsecond.

      Bug in DateTime_or_None() on times.py when func try to return value it can't split microsecond from time

      return datetime(*[ int(x) for x in d.split('-')+t.split(':') ])

      import datetime.datetime
      d = '2013-03-28'
      t = '11:11:11.000011'
      datetime(*[ int(x) for x in d.split('-')+t.split(':') ])
      Traceback (most recent call last):
        File "<stdin>", line 1, in <module>
      ValueError: invalid literal for int() with base 10: '11.000011'
      
       
  • The example works for the new versions of MySQLdb, but DateTime_Or_None() still parses datetimes with microseconds incorrectly, which leads to problems for clients using it directly (see i.e. https://code.djangoproject.com/ticket/19716). If it is possible, please make changes to DateTime_Or_None().

     
    Last edit: Zmicier Zaleznicenka 2013-03-28
  • Also, if talking about these ms issues, while Time_Or_None() parses microseconds, rounding errors may appear. Instead, I suggest to use string methods.

    This happens now:

    >>> s='50.991'
    >>> s = float(s)
    >>> int(s)
    50
    >>>int(math.modf(s)[0]*1000000)
    990999
    

    This is what I think it should be:

    >>> s='50.991'
    >>> s,ms = s.split('.')
    >>> int(s)
    50
    >>> int(ms.ljust(6,'0'))
    991000
    
     
    • Dmitry T.
      Dmitry T.
      2013-05-29

      Yep, and this problem exist.

      May be I can provide more simply way and without math module.

      >>> s = '50.991'
      >>> s = float(s)
      >>> int(s)
      50
      >>> int(round(s - int(s),6) * 1000000)
      991000
      >>>
      
       
  • Math module is used now and causes rounding errors. In my post I suggest using string method ljust instead of math.modf. Ljust is simpler and faster (i believe) than rounding the way you propose.

     
  • Agree, there is no need in float here. Below is one of the ways to fix the problem:

    def DateTime_or_None(s):
        if not ' ' in s and 'T' in s:
            return Date_or_None(s)
    
        try:
            if len(s) == 23: s = s + '000'
            s = s.replace('-', ' ').replace(':', ' ').replace('.', ' ').replace('T', ' ')
            return datetime(*[ int(x) for x in s.split()])
        except (SystemExit, KeyboardInterrupt):
            raise
        except:
            return Date_or_None(s)
    
     
  • Mark Wilby
    Mark Wilby
    2014-02-05

    I just ran into this problem while using twisted.enterprise.adbapi. A simple query of datetime objects returned a None object an all records. The patch here did not fix the problem as it was in the DateTime_or_None method which is not modified.

    I guess its already fixed in the next version, but I have posted this just in case. Again its a problem of handling the milliseconds. I made a slight change to this method, adding an if statement after the split and it seems to work.

    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 = t.replace('.', ':')
            else:
                t = t + ':0'
            return datetime(*[ int(x) for x in d.split('-')+t.split(':') ])
        except (SystemExit, KeyboardInterrupt):
            raise
        except:
            return Date_or_None(s)
    

    I think the string replace is not too costly and everything else is the same.

    I apologise, I have limited expertise with Python and its tools, so due to my ignorance I am just posting the code change, not the patch.

     
    • Dmitry T.
      Dmitry T.
      2014-02-05

      Please can you provide dataset sample for testing.
      Try get sources of MySQLdb from https://github.com/farcepest/MySQLdb1
      I have pushed changes to github repo.

       
      • Mark Wilby
        Mark Wilby
        2014-02-06

        Sorry for not replying yesterday, but your email made me notice
        something strange and I needed to think about it.

        At first glance the basic test outlined below should have picked up the
        problem I saw. Essentially I was doing the same, retrieving results with
        a datetime object in them and printing them out. I saw the same problem
        both before and after the patch. This leaves two possibilities I can see.

        1. I was using your code via twisted, which uses reflection to build a
          wrapper around your library. It might be possible that the two
          approaches, literally build a different stack sequence for
          processing an execute and different functions are used to convert
          the data to a datetime object. (something I find unlikely.)
        2. The patch I applied was not the right one. Although I took it
          directly from the link on the bug report and it modified the methods
          TimeDelta_or_None and Time_or_None, which were never called in my
          test and it left the method DateTime_or_None unchanged. In the
          spirit of simplicity I suspect this is the explanation.

        I have also noticed that due to these changes, the module is a now a bit
        of a patchwork. For example format_TIMEDELTA does not support milliseconds.

        I am bogged down with deadlines at the moment, but in the next few days
        I will build a test example that I can release using the twisted
        library. I have not had time to look at the development branch, but if
        you don't already have it I could add a test case for this module. Its
        not going to track the stack sequence on the call, but it should help to
        keep track of the validity of the methods.

        On 05/02/14 13:04, Dmitry T. wrote:

        Please can you provide dataset sample for testing.
        Try get sources of MySQLdb from https://github.com/farcepest/MySQLdb1
        I have pushed changes to github repo.


        [bugs:#325] http://sourceforge.net/p/mysql-python/bugs/325/
        Datetime fields with microsecond shows as None

        Status: open
        Created: Wed Oct 03, 2012 03:21 PM UTC by Dmitry T.
        Last Updated: Wed Feb 05, 2014 12:50 AM UTC
        Owner: Andy Dustman

        When SELECT datatime field which contained microseconds from
        DB(MariaDB 5.5 and MySQL 5.6). Result can not be converted to
        |datetime.datetime| and data in that field shows as |None|. I use
        MariaDB 5.5.27.

        For example:
        Make table in DB and fill its

        import MySQLdb
        db=MySQLdb.connect(host="localhost",user="test",passwd="test",db="test")
        c=db.cursor()
        c.execure("""DROP TABLE IF EXISTS t1""")
        c.execure("""CREATE TABLE t1 (
        id int(11)
        date datatime(6) NOT NULL,
        PRIMARY KEY (id),
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8""")
        c.execure("""INSERT INTO t1 VALUES(1,now(6)), VALUES(2,NOW(6)), VALUES(3,NOW(6))""")

        Now we can get data.

        c.execute("""SELECT * FROM t1""")
        r = c.fetchall()
        print r

        OUT:
        ((1L, None), (2L, None), (3L, None))

        After patch:

        c.execute("""SELECT * FROM t1""")
        r = c.fetchall()
        print r

        OUT:
        ((1L, datetime.datetime(2012, 10, 3, 10, 56, 47, 800462)), (2L, datetime.datetime(2012, 10, 3, 10, 56, 47, 800462)), (3L, datetime.datetime(2012, 10, 3, 10, 56, 47, 800462)))


        Sent from sourceforge.net because you indicated interest in
        https://sourceforge.net/p/mysql-python/bugs/325/

        To unsubscribe from further messages, please visit
        https://sourceforge.net/auth/subscriptions/

         

        Related

        Bugs: #325

        Attachments
        • Dmitry T.
          Dmitry T.
          2014-02-06

          Hi,

          I'm sorry, this my fail. I was inattentive when read your comment
          Please try replace functions code in times.py. Working code below.

          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
          
           
          • Mark Wilby
            Mark Wilby
            2014-02-06

            This works fine and the ljust protected against badly formatted time
            strings, which my suggestion didn't do. I will use this version form now
            on. Thanks for your time.

            Just one last point, your attached code does not include the
            Time_or_None method, which also needs to be changed. In the unlikely
            event someone else is effected before the next release, they need to be
            aware they need to change all three methods.

            Just for completeness, using the same pattern I think that the
            Time_or_None method would be

            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
            
             
            Attachments