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

import click
import datetime
from datetime_toolbox import *

import traceback
import sqlalchemy
import pandas as pd
import numpy as np

import mysql.connector

import logging

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

    def _timestamp_to_mysql(self, v):
        return v.value
        #return value.view('<i8')
#http://stackoverflow.com/questions/25093950/avoid-mysqlconverter-object-has-no-attribute-timestamp-to-mysql-error-with
#def connect(config):
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():
    #df = pd.DataFrame({"a": [1,2,3,4]})
    filename = "orcl-2000.csv"
    df = pd.read_csv(filename, sep=',')
    #db_uri = "mysql+mysqlconnector://{user}:{password}@{host}:{port}/{database}" # or without mysqlconnector (need MySQLdb)
    #db_uri = "mysql+mysqldb://{user}:{password}@{host}:{port}/{database}" # or without mysqlconnector (need MySQLdb)
    #db_uri = db_uri.format(
    #    user = "root",
    #    password = "123456",
    #    host = "127.0.0.1",
    #    database = "test",
    #    port = 3306
    #)
    #engine = sqlalchemy.create_engine(db_uri)

    #scheme = 'mysql+mysqldb'
    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)
    """

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

    #engine = sqlalchemy.create_engine(db_uri, creator=lambda: connect(config))

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

    df["Date"] = pd.to_datetime(df["Date"])
    df["Date"] = df["Date"] + np.timedelta64(1, 'us') # to test resolution issue
    #df["Date"] = df["Date"].astype(datetime.datetime)
    #df["Date"] = df["Date"].map(dt2int)
    #df["Date"] = df["Date"].view("<i8")
    #df["Date"] = pd.DatetimeIndex(df["Date"]).to_pydatetime()

    df = df.set_index("Date")
    ##df.index = df.index.to_pydatetime()
    #df.index = df.index.map(datetime.datetime)
    #df.index = df.index.astype(datetime.datetime)

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