SQL SERVER date月份带0 sql年月

主题概要

1. 数据采集：使用Python3采集b站经验值数据变化
2. 数据储存：将数据储存到mysql数据库中
3. 数据分析：使用Power BI连接数据库分析数据

前期准备

1. Python3环境，以及代码调试工具，推荐vscode
2. 可以长时间运行的硬件环境：群晖/电脑/树莓派
3. 安装运行mysql数据库，可以参考文章:在树莓派上配置MariaDB

数据采集

直接放Python代码，只需要将代码中注释的部分进行修改即可

代码主要实现的功能

1. 需要提前导入的包

a. import sys:不需要安装，树莓派环境下，没有这个第三方的包不能调用成功（我也不知道原因。。。）

b. requests/json/time:这三个都是python下很寻常的包

c. pip3 install pymysql：操作mysql数据库的包

1. userInfo()函数：获取自己的b站信息，需要拿到自己的cookie和mid，便于登录
2. push_bark(title, text)函数：给自己手机发送通知（可以不使用）
3. userInfo_for_sql(userInfo_all)：执行sql语句的函数，将采集的数据储存至mysql数据库
4. connetSql()函数：数据库相关配置

import sys
sys.path.append('/usr/lib/python3.7/lib-dynload')
sys.path.append('/home/pi/.local/lib/python3.7/site-packages')
sys.path.append('/usr/local/lib/python3.7/dist-packages')
sys.path.append('/usr/lib/python3/dist-packages')
#import sys，到这行，均为在树莓派执行时需要的语句，解决调用时无法找到对应的第三方包的问题，其他环境可以不用
import requests
import json
import pymysql
import time

cookie = ""#替换为自己b站的cookie
header = {'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/53.0.2785.143 Safari/537.36',
          'Connection': 'keep-alive',
          'accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8',
          'Cookie': cookie}


def userInfo(mid):
    userInfo_all = {}
    userInfo_url = 'https://account.bilibili.com/home/userInfo'
    userInfo_data = requests.get(userInfo_url, headers=header)
    userInfo = json.loads(userInfo_data.text)
    current_level = userInfo['data']['level_info']['current_level']  
    current_exp = userInfo['data']['level_info']['current_exp']  
    coins = userInfo['data']['coins']  
    stat_url = 'https://api.bilibili.com/x/relation/stat?vmid={}'.format(
        mid)
    stat_data = requests.get(stat_url, headers=header)
    stat = json.loads(stat_data.text)
    following = stat['data']['following']
    follower = stat['data']['follower']
    userInfo_all['mid'] = mid
    userInfo_all['current_level'] = current_level
    userInfo_all['current_exp'] = current_exp
    userInfo_all['coins'] = coins
    userInfo_all['following'] = following
    userInfo_all['follower'] = follower
    userInfo_all['create_datetime'] = str(
        time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
    print(userInfo_all)
    text = "执行成功！b站我的数据，时间：{}".format(userInfo_all['create_datetime'])
    title = "爬虫执行成功"
    push_bark(title, text)#调用push_bark函数，爬虫爬取成功后，会发push给手机；若不需要刻意注释掉此行
    return userInfo_all

  #爬虫成功后push手机函数，需要下载bark手机app，若不需要，上面不要调用push_bark(title, text)函数即可
def push_bark(title, text):
    #push_url：打开bark app，复制里面的连接，进行替换
    push_url = 'https://api.day.app/xxxxx/{}/{}'.format(
        title, text)
    headers = {
        'content-type': "application/x-www-form-urlencoded",
    }
    response = requests.post(push_url, headers=headers)
    print(push_url)
    text = json.loads(response.text)
    print(text)

def userInfo_for_sql(userInfo_all):
    db = connetSql()  
    conn = db[0]
    database = db[1]
    mid = userInfo_all['mid']
    current_level = str(userInfo_all['current_level'])
    current_exp = str(userInfo_all['current_exp'])
    coins = str(userInfo_all['coins'])
    following = str(userInfo_all['following'])
    follower = str(userInfo_all['follower'])
    create_datetime = userInfo_all['create_datetime']
    #sql：执行的数据库语句，案例中的数据库表名为“bili2_user_info”，根据需要替换
    sql = str("insert into bili2_user_info(mid,current_level,current_exp,coins,following,follower,create_datetime) values('{}','{}','{}','{}','{}','{}','{}')".format(mid,current_level,current_exp,coins,following,follower,create_datetime))
    conn.execute(sql)
    database.commit()
    database.close()
    conn.close()


def connetSql():
  #数据库连接配置：user需要设置成自己的数据库用户名，password需要设置成自己的数据库密码，host需要设置成自己的数据库地址，db需要设置成自己的数据库名称，port需要设置为自己的数据库端口号（默认3306）
    sqlConnect = pymysql.connect(user="", password="",
                                 host="192.168.50.226", db="", charset="utf8", port=3306)
    conn = sqlConnect.cursor()
    return conn, sqlConnect


if __name__ == '__main__':
  #执行，需要将userInfo('2417142')中的2417142替换为自己的user id，在b站可以找到自己的id
    userInfo_for_sql(userInfo('2417142'))

作为市场转型产品狗自学sql/python，大佬轻拍代码写的烂

数据库结构介绍

如果不想使用数据库储存自己的数据，也可以使用csv/txt储存，当然sqlite也是可以的

mysql数据库配置

数据库名：bili2_user
数据表名：bili2_user_info

数据表字段
mid:用户id
current_level:等级
current_exp:经验值
coins:硬币数量
create_datetime:记录添加时间

数据库的字段设置除了create_datetime是datetime类型外，其他均使用int/255即可，粗暴简单

执行python

1. 配置好python3环境
2. 代码修改为自己的配置
3. 数据库搭建ok后

即可执行啦～幸运的话很快就可以在sql数据库中看到自己的数据啦

如下图：⬇️

配置到环境中定时执行

树莓派环境（其他linux同理）

推荐vscode直接远程连接到树莓派，本地代码，远程调试

方法：添加插件“remote-ssh”

然后你就可以远程连接树莓派，进行调试了

代码部署后，可以使用crontab定时调用即可

crontab使用方法

编辑配置文件

sudo crontab -e

在文件末尾加上调用规则

0 * * * * /usr/bin/python3.7 /home/pi/mypisoft/bili2/bili2_my_userinfo.py >>  /home/pi/mypisoft/bili2/log/bili2_my_userinfo.log 2>&1 &

注释：
0 * * * * ：表示每个整点调用一次
/usr/bin/python3.7：python安装路径
/home/pi/mypisoft/bili2/bili2_my_userinfo.py：python脚本路径，需要替换成自己的储存路径
/home/pi/mypisoft/bili2/log/bili2_my_userinfo.log 2>&1 &：调用的日志储存路径，以及后台运行

大家可以根据自己的需求选择数据爬取的时间间隔

添加完毕后保存文件，并进行重启crontab

sudo /etc/init.d/cron restart

查看自己的任务列表

sudo crontab -l

更多关于crontab使用可以百度下，答主并不擅长

windows环境下可以尝试使用“定时任务”

数据分析阶段

没有用python的工具，觉得不适合大多数python小白（主要是我太菜）

就推荐大家下载安装微软旗下的power bi，操作友好，效果拔群

下载地址：Power Bi

没有Mac/linux版本的，直接虚拟机走起

连接数据库-获取数据

按照⬇️图的步骤操作即可，成功后右侧“字段”下会显示表名

建立数据看板

点击可视化-折线图，会在左侧建立一个折线图

数据添加到看板中

第一步：将create_datetime拖入到轴中，并点击 年/季度/月份后面的叉

第二步：将current_exp拖入到值中，并选择值的展示为最大值

此时，你的经验值变化，按照天的维度已经做好了！每次点击刷新，就可以重新连接数据库刷新数据

数据预测

点击“分析”-“预测”-“预测长度（灵活设置，多次调整，200意味着是预测200天）”-“应用”即可

判断自己的经验值何时可以到达28888（6级）

鼠标放到折线图中的线上，可以看到，党横坐标为245时，我的经验值会到28888以上，于是推断，大概在224天后，我会成为6级大佬（245-21，因为我的数据到21日）

结语

python只是我用来获取数据的工具

分析层面我更多应用的是图形化工具power bi

预测不一定准确，但是随着时间推移，数据的积累，会越来越准确