python连接数据库生成可视化_python3.6 连接数据库并用matplotlib可视化代码

python3连接数据库并用matplotlib可视化代码，自己保存的代码，希望大神可以优化！

###############数据库连接并导入相应的表+表头######################################

######mysql数据库

import pymysql

def data_out_sql(DB, sql):

conn = pymysql.connect(host=DB['host'], port=DB['port'], user=DB['user'], passwd=DB['password'], db=DB['dbname'],

charset='utf8')

# 创建游标

cursor = conn.cursor()

# 执行sql语句

cursor.execute(sql)

# 调出数据

data = cursor.fetchall()

# cols为字段信息 例如(('factory_id', 253, None, 6, 6, 0, False), ('szDeviceId', 253, None, 30, 30, 0, False),('update_time', 7, None, 19, 19, 0, False))

cols = cursor.description

# 执行

mit()

conn.close()

# 将数据truple转换为DataFrame

col = []

for i in cols:

col.append(i[0])

data = list(map(list, data))

data = pd.DataFrame(data,columns=col)

print(data)

return data

####postgresql数据库

#导入包

import psycopg2

import pandas as pd

#连接数据库

dbconn = psycopg2.connect(database="****", user="****", password="*****", host="52.82.58.0", port="5432")

print("Opened database successfully")

#读入表

cur = dbconn.cursor()

cur.execute("SELECT * from zzn_all_school_order_user_account_info_test_m")

data = cur.fetchall()

cols = cur.description

cur.close()

dbconn.close()

#加上表头

col=[]

for i in cols:

col.append(i[0])

data=list(map(list,data))

data=pd.DataFrame(data,columns=col)

#把表读为DataFrame格式

df=pd.DataFrame(data,columns=col)

#每个学校和每个月收益条形图

var = df.groupby(['order_time_m','school_name']).total_trevene.sum()

fig = plt.figure(figsize=(15, 15))

ax1 = fig.add_subplot(1,1,1)

ax1.set_xlabel('order_time_m and school_name',fontsize=20)

ax1.set_ylabel('Sum of total_trevene',fontsize=20)

ax1.set_title("order_time_m and school_name wise Sum of total_trevene",fontsize=20)

var.plot(kind='bar')

#每个月收益条形图

var = df.groupby(['order_time_m']).total_trevene.sum()

fig = plt.figure(figsize=(15, 15))

ax1 = fig.add_subplot(1,1,1)

ax1.set_xlabel('order_time_m',fontsize=20)

ax1.set_ylabel('Sum of total_trevene',fontsize=20)

ax1.set_title(" Sum of total_trevene",fontsize=20)

var.plot(kind='bar')

#每个月所有学校收益累加图

var1 = df.groupby(['order_time_m']).total_trevene.sum()

var2=var1.cumsum()

fig = plt.figure(figsize=(15, 15))

ax1 = fig.add_subplot(1,1,1)

ax1.set_xlabel('order_time_m',fontsize=20)

ax1.set_ylabel('Sum of total_trevene',fontsize=20)

ax1.set_title(" Sum of total_trevene",fontsize=20)

var2.plot(kind='line')

#累加图+柱形图

var = df.groupby(['order_time_m']).total_trevene.sum()

fig = plt.figure(figsize=(15, 15))

ax = fig.add_subplot(1,1,1)

var.plot(kind='bar')

ax1 = ax.twinx()

ax1 = ax.twiny()

var1 = df.groupby(['order_time_m']).total_trevene.sum()

var2=var1.cumsum()

var2.plot(kind='line')

ax.legend(loc=0)

ax.grid()

ax1.set_xlabel('order_time_m',fontsize=20)

ax1.set_ylabel('Sum of total_trevene',fontsize=20)

ax1.set_title(" Sum and cumsum of total_trevene",fontsize=20)

ax1.legend(loc=0)

plt.show()

##### 包月净收入及累积

df1=df[df['product_duration_month']==1]

var_1=df1.groupby(['order_time_m']).xinlu_avgevene.sum()

fig = plt.figure(figsize=(15, 15))

ax = fig.add_subplot(1,1,1)

var_1.plot(kind='bar')

ax1 = ax.twinx()

ax1 = ax.twiny()

df2=df[df['product_duration_month']==1]

var1_m=df2.groupby(['order_time_m']).xinlu_avgevene.sum()

var1_cum=var1_m.cumsum()

var1_cum.plot(kind='line')

ax.legend(loc=0)

ax.grid()

ax1.set_xlabel('order_time_m',fontsize=20)

ax1.set_ylabel('month Sum of xinlu_avgevene_cum',fontsize=20)

ax1.set_title("month Sum and cumsum of xinlu_avgevene_cum",fontsize=20)

ax1.legend(loc=0)

#设置刻度

plt.tick_params(labelsize=15)

labels = ax1.get_xticklabels() + ax1.get_yticklabels()

[label.set_fontname('SimHei') for label in labels]

plt.show()

##### 包学期净收入及累积

df3=df[df['product_duration_month']==5]

var5_m=df3.groupby(['order_time_m']).xinlu_avgevene_cum.max()

fig = plt.figure(figsize=(15, 15))

ax = fig.add_subplot(1,1,1)

var5_m.plot(kind='bar')

ax1 = ax.twinx()

ax1 = ax.twiny()

df4=df[df['product_duration_month']==5]

var5_sm=df4.groupby(['order_time_m']).xinlu_avgevene_cum.max()

var5_cum=var5_sm.cumsum()

var5_cum.plot(kind='line')

ax.legend(loc=0)

ax.grid()

ax1.set_xlabel('order_time_m',fontsize=20)

ax1.set_ylabel('semester Sum of xinlu_avgevene_cum',fontsize=20)

ax1.set_title("semester Sum and cumsum of xinlu_avgevene_cum",fontsize=20)

ax1.legend(loc=0)

#设置刻度

plt.tick_params(labelsize=15)

labels = ax1.get_xticklabels() + ax1.get_yticklabels()

[label.set_fontname('SimHei') for label in labels]

plt.show()

###包月+包学期

var_m=var_1+var5_m

fig = plt.figure(figsize=(15, 15))

ax = fig.add_subplot(1,1,1)

var_m.plot(kind='bar')

ax1 = ax.twinx()

ax1 = ax.twiny()

var_cum=var1_cum+var5_cum

var_cum.plot(kind='line')

ax.legend(loc=0)

ax.grid()

ax1.set_xlabel('order_time_m',fontsize=20)

ax1.set_ylabel('semester and month Sum of xinlu_avgevene_cum',fontsize=20)

ax1.set_title("semester and month Sum and cumsum of xinlu_avgevene_cum",fontsize=20)

ax1.legend(loc=0)

#设置刻度

plt.tick_params(labelsize=15)

labels = ax1.get_xticklabels() + ax1.get_yticklabels()

[label.set_fontname('SimHei') for label in labels]

plt.show()

##分学校分月总人数

sum_people=df.groupby(['order_time_m','school_name','product_name','payed_type_case']).people_co.sum()

len(sum_people)

list3=sum_people.tolist()

##分学校分月总净收入

List2=df.groupby(['order_time_m','school_name','product_name','payed_type_case']).order_co.sum()

list2=List2.tolist()

List1=df.groupby(['order_time_m','school_name','product_name','payed_type_case']).avg_reven_mon.sum()

len(List1)

arpu=[]

for i in range(len(list1)):

arpu.append(list1[i]*list2[i]/list3[i])

y=arpu

from pandas.core.frame import DataFrame

df1=df[['order_time_m','school_name','product_name','payed_type_case','arpu']]

df1

##ARPU画图

var_arpu=df1.groupby(['order_time_m','school_name']).arpu.mean()

fig = plt.figure(figsize=(15, 15))

ax = fig.add_subplot(1,1,1)

var_arpu.plot(kind='bar')

#设置刻度

plt.tick_params(labelsize=15)

labels = ax.get_xticklabels() + ax.get_yticklabels()

[label.set_fontname('SimHei') for label in labels]

plt.show()

##每笔订单的散点图

df.plot.scatter(x='order_co',y='xinlu_trevene')

##热力图

df0=df[['order_time_m','school_name','product_name','payed_type_case','reven']]

target = pd.pivot_table(df0, index='order_time_m',columns='school_name',values= 'reven')

fig = plt.figure(figsize=(15, 15))

ax = fig.add_subplot(1,1,1)

plt.imshow(target, cmap=plt.cm.hot)

plt.colorbar(shrink=0.5)

#设置刻度

plt.tick_params(labelsize=15)

labels = ax.get_xticklabels() + ax.get_yticklabels()

[label.set_fontname('SimHei') for label in labels]

plt.show()

##各学校收入占比 饼状图

schoo_sum=df.groupby('school_name').xinlu_trevene.sum()

xinlu_sum=df['xinlu_trevene'].sum()

y1=schoo_sum/xinlu_sum.tolist()

#labels = '西北大学', '西安交通大学', '西安科技大学', '陕西中医药大学','西安邮电大学','西安欧亚学院','咸阳师范学院' # 设置标签

#sizes = [xibei_sum/xinlu_sum, xj_sum/xinlu_sum, xike_sum/xinlu_sum, shanxizy_sum/xinlu_sum,xy_sum/xinlu_sum,ouya_sum/xinlu_sum,xianyang_sum/xinlu_sum] # 占比，和为100

labels='咸阳师范学院','西北大学','西安交通大学','西安欧亚学院','西安科技大学','西安邮电大学','陕西中医药大学'

sizes=y1

colors = ['yellowgreen', 'gold', 'lightskyblue', 'lightcoral'] # 颜色

explode = (0, 0.1, 0, 0,0,0,0) # 展开第二个扇形，即Hogs，间距为0.1

plt.pie(sizes, explode=explode, labels=labels, colors=colors, autopct='%1.1f%%', shadow=True,

startangle=90) # startangle控制饼状图的旋转方向

plt.axis('equal') # 保证饼状图是正圆，否则会有一点角度偏斜

plt.show()

#### 各学校订单数环形图

vals1=df.groupby('school_name').order_co.sum()

vals2=df.order_co.sum()

y2=vals1/vals2

y3=y2.tolist()

val1 = y3

#val2 = y3

val3=[1]

fig, ax = plt.subplots()

labels = '咸阳师范学院', '西北大学', '西安交通大学', '西安欧亚学院','西安科技大学','西安邮电大学','陕西中医药大学'

ax.pie(val1, radius=1.2,autopct='%1.1f%%',pctdistance=0.9)

#ax.pie(val2, radius=1,autopct='%1.1f%%',pctdistance=0.75)

ax.pie(val3, radius=0.6,colors='w')

ax.set(aspect="equal", title='Pie plot with `ax.pie`') #plt.legend()

plt.legend(labels,bbox_to_anchor=(1, 1), loc='best', borderaxespad=0.)

plt.show()