import pandas as pd
import pymysql
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 cut_map(x):
    if 0 < x <= 5:
        return 2
    elif 5 < x <= 10:
        return 3
    elif 10 < x <= 15:
        return 4
    elif 15 < x <= 20:
        return 5
    elif 20 < x <= 40:
        return 6
    else:
        return 7


def cut():
    db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_test')
    sql = "select cid_id,time from cid_time"
    df = con_sql(db, sql)
    df = df.rename(columns={0: "cid", 1: "time"})
    print(df.shape)
    part_1 = df.loc[df["time"] == 0]
    part_2 = df.loc[df["time"] != 0]
    part_1["time"] = 1
    part_2["time"] = part_2["time"].map(cut_map)
    merge = part_1.append(part_2)
    print(merge.shape)
    yconnect = create_engine('mysql+pymysql://root:3SYz54LS9#^9sBvC@10.66.157.22:4000/jerry_test?charset=utf8')
    pd.io.sql.to_sql(merge, "cid_time_cut", yconnect, schema='jerry_test', if_exists='replace', index=False)

if __name__ == "__main__":
    cut()