import pandas as pd
import pymysql
import datetime
from sqlalchemy import create_engine

def con_sql(db,sql):
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        result = cursor.fetchall()
        df = pd.DataFrame(list(result))
    except Exception:
        print("发生异常", Exception)
        df = pd.DataFrame()
    finally:
        db.close()
    return df


def multi():
    db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_prod')
    sql = "select diary_id,level2_ids from diary_feat"
    df = con_sql(db, sql).dropna()
    print(df.shape)
    df = df.rename(columns={0: "cid", 1: "level"})
    df["l1"] = "lost"
    df["l2"] = "lost"
    df["l3"] = "lost"
    for i in list(df["level"].unique()):
        l = [int(j) for j in i.split(";")]
        l = sorted(l)
        if len(l) >= 3:
            df.loc[df["level"] == i, ["l1"]] = l[0]
            df.loc[df["level"] == i, ["l2"]] = l[1]
            df.loc[df["level"] == i, ["l3"]] = l[2]
        elif len(l) == 2:
            df.loc[df["level"] == i, ["l1"]] = l[0]
            df.loc[df["level"] == i, ["l2"]] = l[1]
        elif len(l) == 1:
            df.loc[df["level"] == i, ["l1"]] = l[0]
    df = df.drop("level",axis=1)
    print(df.head())
    # a = list(df["l1"].unique())
    # b = list(df["l2"].unique())
    # c = list(df["l3"].unique())
    # print(len(a))
    # print(a)
    # print(len(b))
    # print(b)
    # print(len(c))
    # print(c)

    yconnect = create_engine('mysql+pymysql://root:3SYz54LS9#^9sBvC@10.66.157.22:4000/jerry_test?charset=utf8')
    n = 200000
    for i in range(0, df.shape[0], n):
        if i == 0:
            temp = df.iloc[0:n]
        elif i + n > df.shape[0]:
            temp = df.iloc[i:]
        else:
            temp = df.iloc[i:i + n]
        pd.io.sql.to_sql(temp, "cid_level2", yconnect, schema='jerry_test', if_exists='append', index=False)
        print("insert done")



if __name__ == "__main__":
    multi()