项目-pymysql迁移数据库+用powerbi定时展示仪表盘

作者: 照希 | 来源:发表于2021-02-28 16:33 被阅读0次

项目-pymysql迁移数据库+用powerbi定时展示仪表盘
Django学习笔记Day1
django.core.exceptions.Improperl
Python3连接数据库
用Python操作MySQL数据库（pymysql库）
pymysql操作mysql数据库
python manage.py makemigrations
mysql与python3交互
Day28销售仪表盘（2019-11-29）
Django教程

1.应用场景

之前数据清洗常做的操作是，从MySQL中读取数据集导出CSV数据集，然后用pandas读取数据，然后做数据报告。用pymysql模块可方便很多。对于一般数据分析来说，一般生产库只给查询权限，因此需要用pymysql模块迁移一个新数据仓库，供PowerBI的仪表盘使用。

在此分享一个小案例。

2.先写好SQL查询语句，并且在数据仓库建表
在生产库中，写好查询语句；
在数据仓库，即自己有权限的数据库中，建一张表，等会用于查询语句的迁入。

CREATE TABLE `counts_order` (
  `houseid` int(11) DEFAULT NULL COMMENT '房源id',
  `order_code` varchar(50) DEFAULT NULL COMMENT '订单号',
  `merchant_name` varchar(64) DEFAULT NULL,
  `check_in_time` datetime DEFAULT NULL COMMENT '入住时间',
  `order_data` date DEFAULT NULL COMMENT '订单日期',
  `group_name` varchar(50) DEFAULT NULL COMMENT '分组名称',
  `order_count` int(11) DEFAULT NULL COMMENT '订单数',
  `order_gmv` decimal(10,2) DEFAULT NULL COMMENT '订单GMV',
  `order_nights` int(11) DEFAULT NULL COMMENT '间夜数',
  `order_count_qx` int(11) DEFAULT NULL COMMENT '取消订单数',
  `order_gmv_qx` decimal(10,2) DEFAULT NULL COMMENT '取消订单GMV',
  `order_nights_qx` int(11) DEFAULT NULL COMMENT '取消间夜数',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3.写Python脚本，将查询数据迁入到新数据仓库。

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import traceback
import pymysql

lis = []

def main():
    #修改SQL
    insert_sql = '''
select 
    a.house_id,a.order_code,merchant.merchant_name,a.check_in_time, DATE(a.order_time) order_data,hg.group_name,
    mark_zc as order_count,
    a.GMV*a.mark_zc as order_gmv,
    a.jianye*a.mark_zc as order_nights,
    mark_qx as order_count_qx,
    a.GMV*a.mark_qx as order_gmv_qx,
    a.jianye*a.mark_qx as order_nights_qx
    from (
        select house_id,order_code,merchant_id,order_status,type,inventory,order_time,check_in_time,
        ifnull(total_money,0)+ifnull(clean_money,0) as GMV,
        TIMESTAMPDIFF(day,check_in_time,check_out_time)*inventory as jianye,
        if(order_status IN ('WAITING_CHECKIN','CHECKIN','CHECKOUT'),1,0) as mark_zc,
        if(order_status ='CANCELED',1,0) as mark_qx
        from tbl_biz_order 
        where type = 'normal' 
        ) a 
left join tbl_info_house house on a.house_id = house.houseid
left join house_group_rel hgr on hgr.house_id = house.houseid
left join house_group hg on hg.id = hgr.house_group_id
left join merchant on merchant.id = a.merchant_id;
    '''
    # print("sql:" + inquire_sql)
#从生产库查数据
    try:
        conn = pymysql.connect(
            host='******',
            port=3306,
            user='******',
            passwd='******',
            db='******',
            charset='UTF8'
        )
        cmd = conn.cursor()
        cmd.execute(query=insert_sql)
        #fetchall遍历sql读取的数据集
        for i in cmd.fetchall():
            lis.append(i)

    except Exception:
        print("处理异常：" + traceback.format_exc())

    finally:
        conn.close()

"""
adm写入层
"""
def MySQL_Insert(lis):
    #需要插入的表格
    insert_sql = 'insert into counts_order (houseid,order_code,merchant_name,check_in_time,order_data,group_name,order_count,order_gmv,order_nights, order_count_qx,order_gmv_qx,order_nights_qx) value (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
#将查询数据插入到新数据仓库，刚才建的表里面
    try:
        conn = pymysql.connect(
            host='********',
            port=3306,
            user='********',
            passwd='*******',
            db='******',
            charset='UTF8'
        )
        cmd = conn.cursor()
        #对应修改的sql表
        cmd.execute(query="truncate counts_order")
        cmd.executemany(insert_sql, lis)

        conn.commit()

    except Exception:
        print("处理异常：" + traceback.format_exc())

    finally:
        conn.close()


if __name__ == '__main__':
    print("start")
    main()
    print("result:" )
    print(lis[0])
    MySQL_Insert(lis)
    print("end")

4.将上面的脚本放在脚本服务器上，让这个脚本的定时刷新，那么数据仓库中的数据就会根据现有数据库而更新，保证数据的实时性。

5.PowerBI展示数据
接下来，可以用PowerBI连接刚才的数据。