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


def get_mysql_data(host,port,user,passwd,db,sql):
    db = pymysql.connect(host=host, port=port, user=user, passwd=passwd,db=db)
    cursor = db.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    db.close()
    return result


def get_user():
    sql = "select user_id,phone,name,city_id,channel,auth_type from api_userextra"
    df = pd.DataFrame(list(get_mysql_data(host,port,user,passwd,db,sql)))
    df = df.rename(columns={0: "user_id", 1: "phone",2:"name",3:"city_id",4:"channel",5:"auth_type"})
    print(df.shape)
    l = 0
    r = 2000
    length = len(df)
    while l < length:
        pd.io.sql.to_sql(df[l:r], "user", yconnect, schema='jerry_test', if_exists='append', index=False)
        l += 2000
        r += 2000
        print("insert done")


def get_device():
    sql = "select device_id,platform,version,model,screen,channel,idfv from statistic_device"


if __name__ == "__main__":
    host = "172.16.30.141"
    port = 3306
    user = "work"
    passwd = "BJQaT9VzDcuPBqkd"
    db = "zhengxing"
    yconnect = create_engine('mysql+pymysql://root:3SYz54LS9#^9sBvC@172.16.40.170:4000/jerry_test?charset=utf8')
    get_user()