#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()
python mysql 数据库
最新推荐文章于 2023-09-13 16:43:24 发布