Skip to content

Datetime fields with microsecond shows as None (SF bug #325)Β #24

Closed

Description

@tyzhnenko

Original bug on https://sourceforge.net/p/mysql-python/bugs/325/

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)))

Activity

  1. added a commit that references this issue on Aug 18, 2013
  2. lfyzjck commented on Jun 29, 2016

    @lfyzjck

    Sorry, i still have the issue on version 1.2.5

    MySQL Server: 5.6.24 Homebrew
    MySQL-python: 1.2.5

    Here is my table schema:

    CREATE TABLE `app` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 
    `name` varchar(120) NOT NULL UNIQUE, 
    `is_deleted` bool NOT NULL, 
    `created` datetime(6) NOT NULL, 
    `updated` datetime(6) NOT NULL
    );
    

    Test Code:

    import MySQLdb
    db=MySQLdb.connect(host="localhost",user="test",passwd="test",db="test")
    c=db.cursor()
    c.execute('select * from app')
    c.fetchall()
    

    Output:

    ((1L, 'test1', 0, None, None), (2L, 'test2', 0, None, None))
    

    And the datetime value shows well in mysql shell.

  3. methane commented on Jun 29, 2016

    @methane
    Collaborator
  4. lfyzjck commented on Jun 30, 2016

    @lfyzjck

    @methane It works, thank you very much!

    But why MySQLdb don't fix this issue? it seems very critical.

  5. methane commented on Jun 30, 2016

    @methane
    Collaborator

    The author is inactive for these years.
    Look commit log.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions