Python进行mysql数据存储+powerbi可视化

本文章涉及mysql建表，Python读取并向mysql存储数据，powerbi度量值和可视化。

一、准备工作

需要先在mysql中创建所需要的表。

/*1.在dos窗口内 输入：  mysql -u root -p   */
/*2.然后回车，输入mysql登录密码。  */
/*3.进入到mysql内，查看现有数据库,输入：show databases;  */
/*4.进入到指定数据库内，输入： use 数据库名;  */
/*5.进到对应的数据库后，查看现有数据表，输入：show tables;   */
/*6.建表语句，创建基础数据表*/
create table `Basic data`
(`编号` int not null auto_increment,
`批次名称` varchar(50),
`模板名称` varchar(30),
`A` int,`B` int,`C` int,`D` int,`E` int,
`未接数` int,`占线数` int,
`总外呼数` int,`接通数` int,
`人工介入数` int,`通话时长/秒` float,
`人工介入时间/秒` float,`组织` varchar(6),
`场景名称` varchar(20),`日期` date,
primary key (`编号`));

        其他常用的SQL命令有：查看当前使用的数据库是哪个： select database(); ；查看mysql版本号：select version(); ；退出mysql：exit 。

二、使用Python连接数据库并存储数据

        涉及使用Python读取数据，对数据进行转化，连接数据库，构建相应的SQL语句并执行，关闭数据库的连接等5部分，可以分别写5个函数。

#导入相关的模块
import numpy as np 
import pandas as pd 
from pandas import DataFrame,Series
import re
import pymysql #连接mysql的模块


def main(): #定义主函数
    #path = str(input("请输入一个地址："))#获取一个地址
    
    date1 = ['01.04','01.05','01.06']  #传入不同表格的定位参数
    
    #给定数据库相关参数
    host,user, password ,database = "localhost","root", "123456","数据库名称"

    #连接数据库
    conn,cursor = connect_database(host,user, password ,database)

    for i in date1: 
        path = 'D:/数据/日数据/2022年/'+ i + '日外呼.xls' #拼接文件路径
        data = []
        data = getdata(path) #通过getdat函数获取表格中的数据
        input_mysql(conn,cursor,data) #通过 input_mysql函数将数据插入数据库
        
    close_database(conn,cursor) #关闭数据库的连接

#获取表格的数据
def getdata(path):
    if len(path) !=0 : #input函数获取信息后的判断，后修改时未删除
        try: #错误信息收集
            data,data1 = [],[]
            #打开表格
            data1 = pd.read_excel(path,3)

            #对表格内的数据进行处理
            data = data1.iloc[:-1] #获取除最后一行的数据
            data = process_hs(data) #调用数据处理函数对数据进行处理
            #print(data.head(5))
            return data    #返回data数据
        except Exception as result:
            print(result)
    pass
 
#话术处理函数
def process_hs(data):
    ccmc = ['abc话术','abdec']
    ccmc1 = ['abc','abd']
    l = len(ccmc) #获取循环次数
    hs = data['模板名称'] #获得指定列数据
    hs1 = hs
    for i in hs.index:
        n = str(hs1[i])
        for m in range(0,l):
            ret = re.findall(".*" + ccmc[m]+ ".*",n) #正则表达式查找
            # display(n)
            if len(ret) > 0:
                hs1[i] = n.replace(ccmc[m],ccmc1[m])
                break
    hs1 = DataFrame(hs1)
    #display(hs1)
    data['模板名称'] = hs1
    data['组织'] = data['组织'].str.strip("小金部")
    return data

#连接数据库
def connect_database(host,user, password ,database):
    conn = pymysql.connect(host = host,user = user, password = password,database = database)
    cursor = conn.cursor()
    print("数据库连接成功")
    return (conn,cursor)


#数据插入数据库
def input_mysql(conn,cursor,data):
    
    #获取数据库表格行数
    sql1 = "select count(`编号`) from `basic data`"
    cursor.execute(sql1)
    num = cursor.fetchone()[0] + 1
    #display(num)
    if num == 0 :
            num = 1
    b = {} #定义一个空字典
    #对data内的数据进行转化

    num0 = len(data.index)
    b[0] = np.arange(num,num+num0,1)
    b[1] = data.iloc[:,1].values
    b[2] = data.iloc[:,2].values
    for i in range(3,15):
        b[i] = data.iloc[:,i].tolist()
    b[15] = data.iloc[:,17].values
    b[16] = data.iloc[:,18].values
    b[17] = data.iloc[:,0].tolist()

    #循环插入
    for i in range(0,num0):
        a = [ab[i] for ab in b.values()]  #将字典的值进行转化，方便后续可以取到每组的第一个值
        #display(a)
        #构建sql语句
        sql = "insert into `basic data` values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');"%(a[0],str(a[1]),a[2],a[3],a[4],a[5],a[6],a[7],a[8],a[9],a[10],a[11],a[12],a[13],a[14],a[15],a[16],a[17])
        
        #display(sql)
        cursor.execute(sql)
    conn.commit() #执行刚刚提交的事务
    print("数据插入成功") 
    pass
#关闭数据库
def close_database(conn,cursor):
    #conn,cursor = connect_database(self)
    cursor.close()
    conn.close()
    print("数据库连接已断开")

if __name__ == "__main__":   #当程序执行时
#调用函数
    main()
    print("运行完毕")

三、powerbi连接数据库并进行可视化

（1）powerBI进行数据库连接

需要输入数据库地址、数据库名、登录账号和密码，如果系统提示需要安装驱动，可以访问 MySQL :: Download Connector/NEThttps://dev.mysql.com/downloads/connector/net/  这个网址进行下载安装，之后再重新试一下。

（2）创建度量值，以接通率为例： 接通率 = DIVIDE(SUM('工作数据 basic data'[接通数]), SUM('工作数据 basic data'[总外呼数]))

（3）日数据可视化