Avoid 'MySQLConverter' object has no attribute '_timestamp_to_mysql' error with datetime64[ns] and MySQL

Question

I'm reading a CSV file like this

Date,Open,High,Low,Close,Volume,Adj Close
2000-12-29,30.88,31.31,28.69,29.06,31702200,27.57
2000-12-28,30.56,31.62,30.38,31.06,25053600,29.46
2000-12-27,30.38,31.06,29.38,30.69,26437500,29.11
2000-12-26,31.50,32.19,30.00,30.94,20589500,29.34
2000-12-22,30.38,31.98,30.00,31.88,35568200,30.23
2000-12-21,27.81,30.25,27.31,29.50,46719700,27.98
2000-12-20,28.06,29.81,27.50,28.50,54440500,27.03
2000-12-19,31.81,33.12,30.12,30.62,58653700,29.05
...
2000-01-13,108.50,109.88,103.50,105.06,55779200,24.91
2000-01-12,112.25,112.25,103.69,105.62,83443600,25.05
2000-01-11,112.62,114.75,109.50,112.38,86585200,26.65
2000-01-10,108.00,116.00,105.50,115.75,91518000,27.45
2000-01-07,95.00,103.50,93.56,103.38,91755600,24.51
2000-01-06,100.16,105.00,94.69,96.00,109880000,22.76
2000-01-05,101.62,106.38,96.00,102.00,166054000,24.19
2000-01-04,115.50,118.62,105.00,107.69,116824800,25.54
2000-01-03,124.62,125.19,111.62,118.12,98114800,28.01

Full data can be download using

python -c "from pyalgotrade.tools import yahoofinance; yahoofinance.download_daily_bars('orcl', 2000, 'orcl-2000.csv')"

see http://gbeced.github.io/pyalgotrade/docs/v0.15/html/tutorial.html

I try to put this CSV data to a MySQL database using Python, Pandas, SQLAlchemy, read_csv and to_sql:

filename = "orcl-2000.csv"
df = pd.read_csv(filename, sep=',')
db_uri = "mysql+mysqlconnector://{user}:{password}@{host}:{port}/{db}" # or without mysqlconnector (need MySQLdb)
db_uri = db_uri.format(
    user = "root",
    password = "123456",
    host = "127.0.0.1",
    db = "test",
    port = 3306
)
engine = sqlalchemy.create_engine(db_uri)
df["Date"] = pd.to_datetime(df["Date"])
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("test_table", engine, flavor="mysql", if_exists="replace")

(see full code here)

I get the following output:

$ python main.py
          Date    Open    High     Low   Close     Volume  Adj Close
0   2000-12-29   30.88   31.31   28.69   29.06   31702200      27.57
1   2000-12-28   30.56   31.62   30.38   31.06   25053600      29.46
2   2000-12-27   30.38   31.06   29.38   30.69   26437500      29.11
3   2000-12-26   31.50   32.19   30.00   30.94   20589500      29.34
4   2000-12-22   30.38   31.98   30.00   31.88   35568200      30.23
5   2000-12-21   27.81   30.25   27.31   29.50   46719700      27.98
6   2000-12-20   28.06   29.81   27.50   28.50   54440500      27.03
7   2000-12-19   31.81   33.12   30.12   30.62   58653700      29.05
8   2000-12-18   30.00   32.44   29.94   32.00   61640100      30.35
9   2000-12-15   29.44   30.08   28.19   28.56  120004000      27.09
10  2000-12-14   29.25   29.94   27.25   27.50   45894400      26.08
11  2000-12-13   31.94   32.00   28.25   28.38   37933600      26.91
12  2000-12-12   31.88   32.50   30.41   30.75   26481200      29.17
13  2000-12-11   30.50   32.25   30.00   31.94   50279700      30.29
14  2000-12-08   30.06   30.62   29.25   30.06   40052600      28.51
15  2000-12-07   29.62   29.94   28.12   28.31   41088300      26.85
16  2000-12-06   31.19   31.62   29.31   30.19   42125600      28.63
17  2000-12-05   29.44   31.50   28.88   31.50   59754700      29.88
18  2000-12-04   26.25   28.88   26.19   28.19   40710400      26.74
19  2000-12-01   26.38   27.88   25.50   26.44   48663500      25.08
20  2000-11-30   21.75   27.62   21.50   26.50   84386200      25.14
21  2000-11-29   23.19   23.62   21.81   22.88   75409600      21.70
22  2000-11-28   23.50   23.81   22.25   22.66   43075300      21.49
23  2000-11-27   25.44   25.81   22.88   23.12   45665200      21.93
24  2000-11-24   23.31   24.25   23.12   24.12   22443900      22.88
25  2000-11-22   23.62   24.06   22.06   22.31   53315300      21.16
26  2000-11-21   24.81   25.62   23.50   23.88   58647400      22.65
27  2000-11-20   24.31   25.88   24.00   24.75   89778400      23.48
28  2000-11-17   26.94   29.25   25.25   28.81   59636000      27.33
29  2000-11-16   28.75   29.81   27.25   27.38   37986600      25.96
..         ...     ...     ...     ...     ...        ...        ...
222 2000-02-14   60.88   62.25   58.62   62.19   37599800      29.49
223 2000-02-11   62.50   64.75   58.75   59.69   55774000      28.31
224 2000-02-10   60.00   62.62   58.00   62.31   45288600      29.55
225 2000-02-09   60.06   61.31   58.81   59.94   52471600      28.43
226 2000-02-08   60.75   61.44   59.00   59.56   55718000      28.25
227 2000-02-07   59.31   60.00   58.88   59.94   44691200      28.43
228 2000-02-04   57.62   58.25   56.81   57.81   40916000      27.42
229 2000-02-03   55.38   57.00   54.25   56.69   55533200      26.88
230 2000-02-02   54.94   56.00   54.00   54.31   63933000      25.76
231 2000-02-01   51.25   54.31   50.00   54.00   57105600      25.61
232 2000-01-31   47.94   50.12   47.06   49.95   68148000      23.69
233 2000-01-28   51.50   51.94   46.62   47.38   86394000      22.47
234 2000-01-27   55.81   56.69   50.00   51.81   61054000      24.57
235 2000-01-26   56.75   58.94   55.00   55.06   47569200      26.11
236 2000-01-25   55.06   57.50   54.88   56.44   53059200      26.77
237 2000-01-24   60.25   60.38   54.00   54.19   50022400      25.70
238 2000-01-21   61.50   61.50   59.00   59.69   50891000      28.31
239 2000-01-20   59.00   60.25   58.12   59.25   54526800      28.10
240 2000-01-19   56.12   58.25   54.00   57.12   49198400      27.09
241 2000-01-18  107.88  114.50  105.62  111.25   66780000      26.38
242 2000-01-14  109.00  111.38  104.75  106.81   57078000      25.33
243 2000-01-13  108.50  109.88  103.50  105.06   55779200      24.91
244 2000-01-12  112.25  112.25  103.69  105.62   83443600      25.05
245 2000-01-11  112.62  114.75  109.50  112.38   86585200      26.65
246 2000-01-10  108.00  116.00  105.50  115.75   91518000      27.45
247 2000-01-07   95.00  103.50   93.56  103.38   91755600      24.51
248 2000-01-06  100.16  105.00   94.69   96.00  109880000      22.76
249 2000-01-05  101.62  106.38   96.00  102.00  166054000      24.19
250 2000-01-04  115.50  118.62  105.00  107.69  116824800      25.54
251 2000-01-03  124.62  125.19  111.62  118.12   98114800      28.01

[252 rows x 7 columns]
Date         datetime64[ns]
Open                float64
High                float64
Low                 float64
Close               float64
Volume                int64
Adj Close           float64
dtype: object
(<class 'pandas.tseries.index.DatetimeIndex'>, dtype('<M8[ns]'))
<class 'pandas.tslib.Timestamp'>
Traceback (most recent call last):
  File "main.py", line 28, in <module>
    main()
  File "main.py", line 25, in main
    df.to_sql("test_table", engine, flavor="mysql", if_exists="replace")
  File "/usr/local/lib/python2.7/dist-packages/pandas/core/generic.py", line 950, in to_sql
    index_label=index_label)
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 475, in to_sql
    index_label=index_label)
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 842, in to_sql
    table.insert()
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 611, in insert
    self.pd_sql.execute(ins, data_list)
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 810, in execute
    return self.engine.execute(*args, **kwargs)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1614, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 662, in execute
    params)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 761, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 874, in _execute_context
    context)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
    exc_info
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 196, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 856, in _execute_context
    context)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 321, in do_executemany
    cursor.executemany(statement, parameters)
  File "/usr/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 557, in executemany
    values.append(fmt % self._process_params(params))
  File "/usr/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 344, in _process_params
    return self._process_params_dict(params)
  File "/usr/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 335, in _process_params_dict
    "Failed processing pyformat-parameters; %s" % err)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) Failed processing pyformat-parameters; 'MySQLConverter' object has no attribute '_timestamp_to_mysql' u'INSERT INTO test_table (`index`, `Date`, `Open`, `High`, `Low`, `Close`, `Volume`, `Adj Close`) VALUES (%(index)s, %(Date)s, %(Open)s, %(High)s, %(Low)s, %(Close)s, %(Volume)s, %(Adj Close)s)' ({'index': 0, 'High': 31.31, 'Adj Close': 27.57, 'Volume': 31702200, 'Low': 28.69, 'Date': Timestamp('2000-12-29 00:00:00'), 'Close': 29.06, 'Open': 30.88}, {'index': 1, 'High': 31.62, 'Adj Close': 29.46, 'Volume': 25053600, 'Low': 30.38, 'Date': Timestamp('2000-12-28 00:00:00'), 'Close': 31.06, 'Open': 30.56}, {'index': 2, 'High': 31.06, 'Adj Close': 29.11, 'Volume': 26437500, 'Low': 29.38, 'Date': Timestamp('2000-12-27 00:00:00'), 'Close': 30.69, 'Open': 30.38}, {'index': 3, 'High': 32.19, 'Adj Close': 29.34, 'Volume': 20589500, 'Low': 30.0, 'Date': Timestamp('2000-12-26 00:00:00'), 'Close': 30.94, 'Open': 31.5}, {'index': 4, 'High': 31.98, 'Adj Close': 30.23, 'Volume': 35568200, 'Low': 30.0, 'Date': Timestamp('2000-12-22 00:00:00'), 'Close': 31.88, 'Open': 30.38}, {'index': 5, 'High': 30.25, 'Adj Close': 27.98, 'Volume': 46719700, 'Low': 27.31, 'Date': Timestamp('2000-12-21 00:00:00'), 'Close': 29.5, 'Open': 27.81}, {'index': 6, 'High': 29.81, 'Adj Close': 27.03, 'Volume': 54440500, 'Low': 27.5, 'Date': Timestamp('2000-12-20 00:00:00'), 'Close': 28.5, 'Open': 28.06}, {'index': 7, 'High': 33.12, 'Adj Close': 29.05, 'Volume': 58653700, 'Low': 30.12, 'Date': Timestamp('2000-12-19 00:00:00'), 'Close': 30.62, 'Open': 31.81}  ... displaying 10 of 252 total bound parameter sets ...  {'index': 250, 'High': 118.62, 'Adj Close': 25.54, 'Volume': 116824800, 'Low': 105.0, 'Date': Timestamp('2000-01-04 00:00:00'), 'Close': 107.69, 'Open': 115.5}, {'index': 251, 'High': 125.19, 'Adj Close': 28.01, 'Volume': 98114800, 'Low': 111.62, 'Date': Timestamp('2000-01-03 00:00:00'), 'Close': 118.12, 'Open': 124.62})

Column Date type is datetime64[ns]. SQLAlchemy doesn't seems to like this kind of Numpy type so it raises:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) Failed processing pyformat-parameters; 'MySQLConverter' object has no attribute '_timestamp_to_mysql'

How can I cleanly avoid this kind of error ?

Orchardman

Answer 1

I'm not very familiar with MySQL Connector, but according to this, you should be able to add a datetime64 converter using something like

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

    def _timestamp_to_mysql(self, value):
        return value.view('<i8')

config = {
    'user'    : 'user',
    'host'    : 'localhost',
    'password': 'xxx',
    'database': 'db1'}

conn = mysql.connector.connect(**config)
conn.set_converter_class(Datetime64Converter)

Since all datetime64 dtypes are 8-bytes, they can be viewed and stored as 8-byte integers. I'm not sure what facilities MySQL Connector provides for pulling the data back out as datetime64s. But if all else fails, you can convert the 8-byte integers back into datetime64[ns] like this:

In [33]: s.view('<i8')
Out[33]: 
0    978307200000000000
1    978393600000000000
2    978480000000000000
3    978566400000000000
4    978652800000000000
5    978739200000000000
6    978825600000000000
7    978912000000000000
8    978998400000000000
9    979084800000000000
dtype: int64

In [34]: s.view('<i8').view('<M8[ns]')
Out[34]: 
0   2001-01-01
1   2001-01-02
2   2001-01-03
3   2001-01-04
4   2001-01-05
5   2001-01-06
6   2001-01-07
7   2001-01-08
8   2001-01-09
9   2001-01-10
dtype: datetime64[ns]

Rhetoric

thanks for your solution (that was also my idea) but you can have same resolution and timespan with datetime and datetime64 see docs.scipy.org/doc/numpy-dev/reference/arrays.datetime.html that's why I'm not looking for such a solution. I was thinking to add _timestamp_to_mysql attribute to MySQLConverter in order to convert datetime64[ns] to unix timestamp (integer value in ns). So even if I have Date with ns resolution it should works. – Orchardman

typo: (you can't have) – Orchardman

it also raises error if Datecolumn is set as index using df = df.set_index("Date") – Orchardman

Storing the data as 8-byte integers sounds like a good idea then. I've updated the post accordingly. – Rhetoric

thanks @Rhetoric I didn't know .view('<i8') and .view('<M8[ns]'). I was using delta = dt - UNIX_EPOCH return(int(delta.total_seconds()*ts_mult)). Your solution is better on this part but the problem with your solution is that you pass Datetime64Converter to conn which is a mysql.connector.connection.MySQLConnection. Is it possible to pass it to a sqlalchemy.engine.base.Engine instead ? – Orchardman

I don't have experience with sqlalchemy, but it looks like this is the way to create an engine with a custom connection. (More specifically, I think you could use the third method, an call conn.set_converter_class(Datetime64Converter) before returning conn.) – Rhetoric

Thanks @Rhetoric but I did scheme = 'mysql+mysqlconnector' config = { 'host' : 'localhost', 'database': 'test', 'user' : 'root', 'password': '123456', 'port': 3306 } db_uri = "{scheme}://".format(scheme=scheme) conn = mysql.connector.connect(**config) conn.set_converter_class(Datetime64Converter) engine = sqlalchemy.create_engine(db_uri, creator=conn) but I get TypeError: 'MySQLConnection' object is not callable – Orchardman

It's necessary to use SQLAlchemy according pandas-docs.github.io/pandas-docs-travis/generated/… – Orchardman

I also try to define a connect function like 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) and engine = sqlalchemy.create_engine("mysql+mysqlconnector://", creator=connect) but it raises now sqlalchemy.exc.ProgrammingError: (ProgrammingError) Failed processing pyformat-parameters; 'Timestamp' object has no attribute 'view' – Orchardman

Your second method, using creator=connect is correct. However, the error is saying that the value passed to _timestamp_to_mysql does not have a view method. I had guessed that value would be a datetime64 but apparently that is wrong. Since I don't have experience with MySQL Connector, perhaps you would be better off asking a new question, tagged mysql-connector-python about this to get more knowledgeable eyeballs on the problem. – Rhetoric

You might also try print(type(value)) to find out what kind of object is getting passed to _timestamp_to_mysql... – Rhetoric

print(type(df.index), df.index.dtype) print(type(df.index[0])) returns (<class 'pandas.tseries.index.DatetimeIndex'>, dtype('<M8[ns]')) <class 'pandas.tslib.Timestamp'> so value is pandas.tslib.Timestamp – Orchardman

As value is a Pandas timestamp I changed converter to: class Datetime64Converter(mysql.connector.conversion.MySQLConverter): """ A mysql.connector Converter that handles datetime64 types """ def _timestamp_to_mysql(self, v): return v.value but that's really strange... in DB I get mysql> select * from orcl; | 0000-00-00 00:00:00 | 30.88 | 31.31 | 28.69 | 29.06 | and mysql> show columns from orcl; +-----------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | | Date | datetime | YES | MUL | NULL | | – Orchardman

Let us continue this discussion in chat. – Orchardman

Answer 2

I found by simply reformatting the dates as strings solves the problem.

df['Date'] = df['Date'].apply(lambda x: x.strftime('%Y-%m-%d'))

MySQL still recognized it as a date rather than VarChar. I ran into the exact same problem at work, and this solution has been working.

Overplay