python mysql 数据库

最新推荐文章于 2023-09-13 16:43:24 发布

默默前行的蜗牛

最新推荐文章于 2023-09-13 16:43:24 发布

阅读量217

点赞数

分类专栏： python 文章标签： python

本文链接：https://blog.csdn.net/ywf008/article/details/73648750

版权

python 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

#mysql数据库demo
# -*-coding:utf-8-*-
import pymysql
import pandas as pd
import numpy as np

def 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 frame

def 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 conn

if __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()