python mysql 数据库

#mysql数据库demo# -*-coding:utf-8-*-import pymysqlimport pandas as pdimport numpy as npdef read_table(cur, sql_order,columns): # sql_order is a string    try:        cur.execute(sql_order) # 多少条记录    except Exception: #, e:        frame = pd.DataFrame()        # print e        # continue    else:        data  = cur.fetchall(  )        frame = pd.DataFrame(list(data),columns=columns)    return framedef get_con(host,port,user,passwd,db):    # 创建连接    conn = pymysql.connect(host='10.161.20.19', port=3306, user='agg_data', passwd='agg_data', db='agg_data',                           charset='utf8')    return connif __name__=='__main__':    host = '10.161.20.19'    port = 3306    user = 'agg_data'    passwd = 'agg_data'    db = 'agg_data'    # 创建连接    conn = get_con(host,port,user,passwd,db)    # 创建游标    cursor = conn.cursor()    #查询sql    query = """SELECT t1.line,        car_id,        FROM_UNIXTIME(curdate / 1000, '%Y%m') ym,        # FUEL_COST,        # DISTANCE,        hkm_fuel_cost    FROM        t_busgeneral_stat_outer t,businfo t1    WHERE        FROM_UNIXTIME(curdate / 1000, '%Y%m') IN (        '201701',        '201702',        '201703',        '201704',        '201705'    )    and t.car_id = t1.busnumber    AND t1.line in ('厦门30路','厦门107路','厦门127路','厦门97路','厦门39路','厦门657路','厦门655路','厦门658路','厦门33路','厦门27路')    order by ym desc,line"""    column = ['line', 'car_id', 'ym', 'FUEL_COST']    #获取查询结果    data =  read_table(cursor,query,column)    data['sort_id']=data['FUEL_COST'].groupby([data['line'],data['ym']]).rank()    pivot_rs = pd.pivot_table(data, index=['line', 'car_id'], columns='ym', values=['sort_id'], aggfunc=[np.mean],                              fill_value=' ', margins=False)    print(pivot_rs)    # 提交，不然无法保存新建或者修改的数据    conn.commit()    # 关闭游标    cursor.close()    # 关闭连接    conn.close()