History_value 2018-05-14

# -*- coding: UTF-8 -*-

import os.path
import os
import time
import datetime
import pandas as pd
from sqlalchemy import create_engine
import pymysql
import datetime
import numpy as np


def history_value_all():

    # 打开数据库连接
    db = pymysql.connect(host="192.168.0.22", port=3307, user="public",
                         password="123456", db="product_value", charset="utf8")
    db_ = create_engine(
        'mysql+pymysql://public:123456@192.168.0.22:3307/product_value?charset=utf8')
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    # 使用execute方法执行SQL语句
    cursor.execute(
        "select table_name from information_schema.tables where table_schema='product_value' and table_type='base table';")

    # 使用 fetchone() 方法获取一条数据
    datas = cursor.fetchall()
    db.close()
    nowTime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    rename_columns = {
        '日期': 'datetime',
        '当日盈亏': 'profit',
        '净值': 'net_value',
        '回撤':  'drawdown',
        '股票资产': 'stock_assert',
        '期货资产':   'future_assert',
        '总资产':  'total_assert',
        '出入金':  'cash_in_out',
        '备注': 'notes',

    }

    column = ['datetime', 'account_name', 'profit', 'net_value', 'drawdown', 'stock_assert',
              'future_assert', 'total_assert', 'cash_in_out', 'notes', 'insert_time']
    df_all = pd.DataFrame(columns=column)

    for data in datas:

        sql_ = r"select * from {0}".format(data[0])
        df = pd.read_sql(sql_, db_,)
        del df['index']
        df['account_name'] = data[0]
        df['insert_time'] = str(nowTime)

        df.rename(columns=rename_columns, inplace=True)

        df_all = df_all.append(df, ignore_index=True)

    df_all = df_all[column]
    df_all['datetime'] = df_all['datetime'].astype(str)

    df_all.to_sql(name='product_value', con=db_, if_exists='replace')

    try:
        sql_string = r"ALTER TABLE product_value  ADD PRIMARY KEY ( datetime(10) ,account_name(20))"
        cursor.execute(sql_string)
    except:
        pass


if __name__ == '__main__':

    history_value_all()