import pymysql # 连接mysql数据库的模块
import pandas as pd
import time

class DataCenter():

    def __init__(self) -> None:
        
        self.client = pymysql.connect(
        host="127.0.0.1",
        port=3306,
        user="root",
        password="111111",
        database="future_sb3_db",
        charset='utf8mb4', # 一定不要写utf-8
        # 针对增删改操作，执行重要程度高，若要执行，必须要有一步确认的操作，autocommit=True默认对数据库进行增删改操作时自动提交至数据库；若此处不写，在下面代码 cursor_obj.execute(sql) 后面手动需要添加 client.commit()操作
        autocommit=True
        )
        '''
        client = pymysql.connect(
        host="rm-8vby6h31662bd2m29.mysql.zhangbei.rds.aliyuncs.com",
        port=3306,
        user="ghuazo",
        password="%@Code1314",
        database="stock_forecast",
        charset='utf8mb4', # 一定不要写utf-8
        # 针对增删改操作，执行重要程度高，若要执行，必须要有一步确认的操作，autocommit=True默认对数据库进行增删改操作时自动提交至数据库；若此处不写，在下面代码 cursor_obj.execute(sql) 后面手动需要添加 client.commit()操作
        autocommit=True
        )
        '''
    def __del__(self):
        self.client.close()
    
    def _exe_sql(self,sql):
        
        
        # print(client)  # 打印结果：<pymysql.connections.Connection object at 0x00000000028A8B88>
        
        
        # 2.获取游标对象。游标：可以用来提交sql命令
        # pymysql.cursors.DictCursor：将查询出来的结果制作成字典的形式返回
        cursor_obj = self.client.cursor(pymysql.cursors.DictCursor)
        result = None
        # 3.通过execute可以提交sql语句(增删改查都可以，可以使用try来捕获异常)
        try:
        
            # 2）提交sql语句
            res = cursor_obj.execute(sql)
            #print(res)  # execute返回的是当前sql语句所影响的行数
            # client.commit()  # 上面连接数据库的代码处已写了autocommit=True，此处注释掉
            
            # 3.1）提交后，通过cursor_obj对象.fetchall()  获取所有查询到的结果
            result = cursor_obj.fetchall()
            
            # 3.2）.fetchone()  只获取查询结果中的一条
            # print(cursor_obj.fetchone())
            
            # 3.3）.fetchmany() 可以指定获取几条数据
            # print(cursor_obj.fetchmany(4))  # 获取四条数据
            
        except Exception as e:
            print(e)
        
        # 关闭游标
        cursor_obj.close()
        

        return result

    def save_tick_info(self,tick_info):

        sql = "INSERT INTO `future_sb3_db`.`rb_tick_info`(`time`,`tick`,`price`,`buy_price`,`buy_volume`,`volume`,`sell_price`,`sell_volume`,`delta_hold`,`standard`,`trade_day`) VALUES"
        date_time = str(tick_info["业务日期"])+" "+str(tick_info["最后修改时间"])
        current_date = time.strptime(date_time,'%Y%m%d %H:%M:%S')
        sql += "('"+time.strftime('%Y-%m-%d %H:%M:%S',current_date)+"',"
        sql += str(tick_info["最后修改毫秒"])+","
        sql += str(tick_info["最新价"])+","
        sql += str(tick_info["申卖价一"])+","
        sql += str(tick_info["申卖量一"])+","
        sql += str(tick_info["数量"])+","
        sql += str(tick_info["申买价一"])+","
        sql += str(tick_info["申买量一"])+","
        sql += str(tick_info["持仓量"]-tick_info["昨持仓量"])+","
        sql += str(tick_info["上次结算价"])+","
        sql += str(tick_info["交易日"])+")"
        
        self._exe_sql(sql)

    def save_csv_info(self,csv_data):

        begin_sql = "INSERT INTO `future_sb3_db`.`rb_tick_info`(`time`,`tick`,`price`,`buy_price`,`buy_volume`,`volume`,`sell_price`,`sell_volume`,`delta_hold`,`standard`,`trade_day`) VALUES"
        sql = begin_sql
        for index, row in csv_data.iterrows():
            date_time = str(row["业务日期"])+" "+str(row["最后修改时间"])
            current_date = time.strptime(date_time,'%Y%m%d %H:%M:%S')
            sql += "('"+time.strftime('%Y-%m-%d %H:%M:%S',current_date)+"',"
            sql += str(row["最后修改毫秒"])+","
            sql += str(row["最新价"])+","
            sql += str(row["申卖价一"])+","
            sql += str(row["申卖量一"])+","
            sql += str(row["数量"])+","
            sql += str(row["申买价一"])+","
            sql += str(row["申买量一"])+","
            sql += str(row["持仓量"]-row["昨持仓量"])+","
            sql += str(row["上次结算价"])+","
            sql += str(row["交易日"])+")"
            if index % 1000 == 0:
                self._exe_sql(sql)
                sql = begin_sql
            else:
                if index < len(csv_data.values)-1:
                    sql+=","
        self._exe_sql(sql)


csv_file = [
    "rb主力连续_20220901.csv",
    "rb主力连续_20220902.csv",
    "rb主力连续_20220905.csv",
    "rb主力连续_20220906.csv",
    "rb主力连续_20220907.csv",
    "rb主力连续_20220908.csv",
    "rb主力连续_20220909.csv",
    "rb主力连续_20220913.csv",
    "rb主力连续_20220914.csv",
    "rb主力连续_20220915.csv",
    "rb主力连续_20220916.csv",
    "rb主力连续_20220919.csv",
    "rb主力连续_20220920.csv",
    "rb主力连续_20220921.csv",
    "rb主力连续_20220922.csv",
    "rb主力连续_20220923.csv",
    "rb主力连续_20220926.csv",
    "rb主力连续_20220927.csv",
    "rb主力连续_20220928.csv",
    "rb主力连续_20220929.csv",
    "rb主力连续_20220930.csv",
    ]

if __name__ == '__main__':
    data_center = DataCenter()
    for it in csv_file:
        csv_data = pd.read_csv("../data/rb_202209/"+it,encoding="utf-8")
        data_center.save_csv_info(csv_data)