'MySQLConverter' object has no attribute '_timestamp_to_mysql' error with datetime64[ns], MySQL and MySQL-connector

[femtotrader]

Hello,

This code

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import pandas as pd
import numpy as np
import sqlalchemy

def main():
    N = 5
    df = pd.DataFrame({'Date':pd.date_range('2000-12-29', freq='D', periods=N), 'Open': range(N)})
    scheme = 'mysql+mysqlconnector'

    config = {
        'host'    : 'localhost',
        'database': 'test',
        'user'    : 'root',
        'password': '123456',
        'port': 3306
    }

    db_uri = "{scheme}://{user}:{password}@{host}:{port}/{database}"
    db_uri = db_uri.format(
        scheme = scheme,
        user = config['user'],
        password = config['password'],
        port = config['port'],
        host = config['host'],
        database = config['database']
    )
    engine = sqlalchemy.create_engine(db_uri)

    df["Date"] = df["Date"] + np.timedelta64(1, 'us') # to test resolution issue

    df = df.set_index("Date")

    print(df)
    print(df.dtypes)
    print(type(df.index), df.index.dtype)
    print(type(df.index[0]))

    df.to_sql("orcl", engine, flavor="mysql", if_exists="replace")

if __name__ == '__main__':
    main()

raises following error:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) Failed processing pyformat-parameters; 'MySQLConverter' object has no attribute '_timestamp_to_mysql' u'INSERT INTO orcl (`Date`, `Open`) VALUES (%(Date)s, %(Open)s)' ({'Date': Timestamp('2000-12-29 00:00:00.000001'), 'Open': 0}, {'Date': Timestamp('2000-12-30 00:00:00.000001'), 'Open': 1}, {'Date': Timestamp('2000-12-31 00:00:00.000001'), 'Open': 2}, {'Date': Timestamp('2001-01-01 00:00:00.000001'), 'Open': 3}, {'Date': Timestamp('2001-01-02 00:00:00.000001'), 'Open': 4})

I try to pass my own converter see

• http://stackoverflow.com/questions/25093950/avoid-mysqlconverter-object-has-no-attribute-timestamp-to-mysql-error-with/25094000#25094000
• http://chat.stackoverflow.com/rooms/58566/discussion-between-femto-trader-and-unutbu

But I didn't have success

I think it should be nice if datetime64[ns] where automatically stored as BIGINT in database.

I'm aware that I can convert df column (or index) with datetime64[ns] (or Pandas Timestamp) data to integer data before calling to_sql but I don't consider this as a clean way of doing.

In [1]: import pandas as pd

In [2]: pd.__version__
Out[2]: '0.14.1'

In [3]: import sqlalchemy

In [4]: sqlalchemy.__version__
Out[4]: '0.8.4'

In [5]: import numpy as np

In [6]: np.__version__
Out[6]: '1.8.1'

In [7]: import mysql.connector

In [8]: mysql.connector.__version__
Out[8]: '1.1.6'

[jorisvandenbossche]

Thanks for reporting! But some questions to make it more clear / easier to work with:

• can you make a smaller, self-contained example (without the need to have special packages installed to download the data). Try to generate some artificial data that reproduces the problem. Something simple like df = pd.DataFrame({'Date':pd.date_range('2000-12-29', freq='D', periods=5), 'Open': range(5)}) can maybe already be enough.
• Further try to clean up your example script (again, to the essential code that reproduces the problem), remove some of the comments
• give the resulting error first without passing your custom converter, and then with.
• Can you list the versions of the relevant packages? (pandas, numpy, sqlalchemy, mysql.connector)

You can do that by just adapting your initial post.

[femtotrader]

I also notice an other issue (if I change scheme = 'mysql+mysqlconnector' to scheme = 'mysql+mysqldb' this code execute fine but if I query database I notice that I have only second resolution timestamp (no microsecond, in fact no fractional seconds).

$ mysql -u root mysql -p
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.5.38-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from orcl;
+---------------------+------+
| Date                | Open |
+---------------------+------+
| 2000-12-29 00:00:00 |    0 |
| 2000-12-30 00:00:00 |    1 |
| 2000-12-31 00:00:00 |    2 |
| 2001-01-01 00:00:00 |    3 |
| 2001-01-02 00:00:00 |    4 |
+---------------------+------+
5 rows in set (0.00 sec)

mysql> show columns from orcl;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| Date  | datetime   | YES  | MUL | NULL    |       |
| Open  | bigint(20) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

[femtotrader]

Now code with mysql.connector and custom converter

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import pandas as pd
import numpy as np
import sqlalchemy
import mysql.connector

class Datetime64Converter(mysql.connector.conversion.MySQLConverter):
    """ A mysql.connector Converter that handles datetime64 types """

    def _timestamp_to_mysql(self, value):
        return value.value

def connect():
    config = {
        'host'    : 'localhost',
        'database': 'test',
        'user'    : 'root',
        'password': '123456',
        'port': 3306
    }
    conn = mysql.connector.connect(**config)
    conn.set_converter_class(Datetime64Converter)
    return(conn)

def main():
    N = 5
    df = pd.DataFrame({'Date':pd.date_range('2000-12-29', freq='D', periods=N), 'Open': range(N)})

    scheme = 'mysql+mysqlconnector'
    #scheme = 'mysql+mysqldb'

    db_uri = "{scheme}://".format(scheme=scheme)

    engine = sqlalchemy.create_engine(db_uri, creator=connect)

    df["Date"] = df["Date"] + np.timedelta64(1, 'us') # to test resolution issue

    df = df.set_index("Date")

    print(df)
    print(df.dtypes)
    print(type(df.index), df.index.dtype)
    print(type(df.index[0]))

    df.to_sql("orcl", engine, flavor="mysql", if_exists="replace")

if __name__ == '__main__':
    main()

doesn't raise any error but table index is wrong in database:

mysql> select * from orcl;
+---------------------+------+
| Date                | Open |
+---------------------+------+
| 0000-00-00 00:00:00 |    0 |
| 0000-00-00 00:00:00 |    1 |
| 0000-00-00 00:00:00 |    2 |
| 0000-00-00 00:00:00 |    3 |
| 0000-00-00 00:00:00 |    4 |
+---------------------+------+
5 rows in set (0.00 sec)

mysql> show columns from orcl;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| Date  | datetime   | YES  | MUL | NULL    |       |
| Open  | bigint(20) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

[jorisvandenbossche]

OK, I can reproduce this (although with a slightly different error: Failed processing pyformat-parameters; Python 'timestamp' cannot be converted to a MySQL type).
So mysql.connector can be added to the list of drivers with which datetime does not work (this is also the case with pymysql, while it does work with mysqldb (as you also experienced), see also issue #7103).

For the sub-second resolution, can you open a seperate issue?

[femtotrader]

Sub-second resolution issue with mysqldb driver opened here #7938

[jorisvandenbossche]

With the custom converter, I get it working when using:

class Datetime64Converter(mysql.connector.conversion.MySQLConverter):
    """ A mysql.connector Converter that handles datetime64 types """
    def _timestamp_to_mysql(self, value):
        return value.strftime('%Y-%m-%d %H:%M:%S')

So returning a string instead of an int (for some reason sqlalchemy specifies it as text, although I thought mysql supported datetime types, have to check that). I only get occasional an error like InternalError: Unread result found. (seems to have something to do with replacing an existing table, if I change the name, it always works)

[femtotrader]

Anyway this solution will lead to second rounding (no sub-second resolution), isn't it ?
I think that if rounding occurs, we should at least see a warning message... but I would prefer to have sub-second resolution.

[jorisvandenbossche]

Correction: mysql just expects a string format for the datetime, but it is stored as a datetime type in the database with the solution above.

Indeed, this will round to second resolution, but as I say in the other issue, this is a limitation of mysql, not pandas. So if you want to use the datetime type in mysql, you have to live with that, or otherwise you can yourself first convert it to a string or int (as is most convenient for you) and then write to mysql.

[jorisvandenbossche]

For future reference, the problem is the following: mysql-connector has a mysql.connector.conversion.MySQLConverter._datetime_to_mysql function that deals with converting a datetime.datetime object to mysql, but this does not work for the Timestamp class (although it is a subclass of datetime.datetime), which is the same problem as with pymysql.

So doing:

mysql.connector.conversion.MySQLConverter._timestamp_to_mysql = mysql.connector.conversion.MySQLConverter._datetime_to_mysql

should also work as a workaround.