Python3 + SQL + Power BI进行数据预测；要在b站成为6级老板，还需要花费更多时间

　　主题摘要数据采集: 使用Python3从站点b采集经验值. 数据变化. 数据存储: 将数据存储在mysql数据库中. 数据分析: 使用Power BI连接到数据库以分析数据. 建议初步准备Python3环境和代码调试工具. 建议使用vscode. 长期运行的硬件环境: Synology / Computer / Raspberry Pi以安装和运行mysql数据库，您可以参考以下文章: 在Raspberry Pi上配置MariaDB数据采集并直接放置Python代码，只需修改代码的注释部分. <//p

p代码的主要功能/p

p需要提前导入的包裹/p

pa. import sys: 不需要安装. 在Raspberry Pi环境中，没有此第三方程序包就无法成功调用它（我不知道原因...）/p

pb. requests / json / time: 这三个是python下非常常见的软件包/p

pc. pip3 install pymysql: 用于运行mysql数据库的软件包/p

puserInfo（）函数: 要获取您自己的站点b信息，您需要获取自己的cookie和中间内容以方便登录/p

ppush_bark（标题，文本）功能: 向手机发送通知（可能不使用）/p

puserInfo_for_sql（userInfo_all）: 执行sql语句并将采集的数据存储在mysql数据库中的功能/p

pconnetSql（）函数: 与数据库相关的配置/p

pprecode class="language-python"span class="kn"import/span span class="nn"sys/span

span class="n"sys/spanspan class="o"./spanspan class="n"path/spanspan class="o"./spanspan class="n"append/spanspan class="p"(/spanspan class="s1"&#39;/usr/lib/python3.7/lib-dynload&#39;/spanspan class="p")/span

span class="n"sys/spanspan class="o"./spanspan class="n"path/spanspan class="o"./spanspan class="n"append/spanspan class="p"(/spanspan class="s1"&#39;/home/pi/.local/lib/python3.7/site-packages&#39;/spanspan class="p")/span

span class="n"sys/spanspan class="o"./spanspan class="n"path/spanspan class="o"./spanspan class="n"append/spanspan class="p"(/spanspan class="s1"&#39;/usr/local/lib/python3.7/dist-packages&#39;/spanspan class="p")/span

span class="n"sys/spanspan class="o"./spanspan class="n"path/spanspan class="o"./spanspan class="n"append/spanspan class="p"(/spanspan class="s1"&#39;/usr/lib/python3/dist-packages&#39;/spanspan class="p")/span

span class="c1"#import sys，到这行，均为在树莓派执行时需要的语句，解决调用时无法找到对应的第三方包的问题，其他环境可以不用/span

span class="kn"import/span span class="nn"requests/span

span class="kn"import/span span class="nn"json/span

span class="kn"import/span span class="nn"pymysql/span

span class="kn"import/span span class="nn"time/span

span class="n"cookie/span span class="o"=/span span class="s2"""/spanspan class="c1"#替换为自己b站的cookie/span

span class="n"header/span span class="o"=/span span class="p"{/spanspan class="s1"&#39;User-Agent&#39;/spanspan class="p":/span span class="s1"&#39;Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/53.0.2785.143 Safari/537.36&#39;/spanspan class="p",/span

span class="s1"&#39;Connection&#39;/spanspan class="p":/span span class="s1"&#39;keep-alive&#39;/spanspan class="p",/span

span class="s1"&#39;accept&#39;/spanspan class="p":/span span class="s1"&#39;text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8&#39;/spanspan class="p",/span

span class="s1"&#39;Cookie&#39;/spanspan class="p":/span span class="n"cookie/spanspan class="p"}/span

span class="k"def/span span class="nf"userInfo/spanspan class="p"(/spanspan class="n"mid/spanspan class="p"):/span

span class="n"userInfo_all/span span class="o"=/span span class="p"{}/span

span class="n"userInfo_url/span span class="o"=/span span class="s1"&#39;https://account.bilibili.com/home/userInfo&#39;/span

span class="n"userInfo_data/span span class="o"=/span span class="n"requests/spanspan class="o"./spanspan class="n"get/spanspan class="p"(/spanspan class="n"userInfo_url/spanspan class="p",/span span class="n"headers/spanspan class="o"=/spanspan class="n"header/spanspan class="p")/span

span class="n"userInfo/span span class="o"=/span span class="n"json/spanspan class="o"./spanspan class="n"loads/spanspan class="p"(/spanspan class="n"userInfo_data/spanspan class="o"./spanspan class="n"text/spanspan class="p")/span

span class="n"current_level/span span class="o"=/span span class="n"userInfo/spanspan class="p"[/spanspan class="s1"&#39;data&#39;/spanspan class="p"][/spanspan class="s1"&#39;level_info&#39;/spanspan class="p"][/spanspan class="s1"&#39;current_level&#39;/spanspan class="p"]/span

span class="n"current_exp/span span class="o"=/span span class="n"userInfo/spanspan class="p"[/spanspan class="s1"&#39;data&#39;/spanspan class="p"][/spanspan class="s1"&#39;level_info&#39;/spanspan class="p"][/spanspan class="s1"&#39;current_exp&#39;/spanspan class="p"]/span

span class="n"coins/span span class="o"=/span span class="n"userInfo/spanspan class="p"[/spanspan class="s1"&#39;data&#39;/spanspan class="p"][/spanspan class="s1"&#39;coins&#39;/spanspan class="p"]/span

span class="n"stat_url/span span class="o"=/span span class="s1"&#39;https://api.bilibili.com/x/relation/stat?vmid={}&#39;/spanspan class="o"./spanspan class="n"format/spanspan class="p"(/span

span class="n"mid/spanspan class="p")/span

span class="n"stat_data/span span class="o"=/span span class="n"requests/spanspan class="o"./spanspan class="n"get/spanspan class="p"(/spanspan class="n"stat_url/spanspan class="p",/span span class="n"headers/spanspan class="o"=/spanspan class="n"header/spanspan class="p")/span

span class="n"stat/span span class="o"=/span span class="n"json/spanspan class="o"./spanspan class="n"loads/spanspan class="p"(/spanspan class="n"stat_data/spanspan class="o"./spanspan class="n"text/spanspan class="p")/span

span class="n"following/span span class="o"=/span span class="n"stat/spanspan class="p"[/spanspan class="s1"&#39;data&#39;/spanspan class="p"][/spanspan class="s1"&#39;following&#39;/spanspan class="p"]/span

span class="n"follower/span span class="o"=/span span class="n"stat/spanspan class="p"[/spanspan class="s1"&#39;data&#39;/spanspan class="p"][/spanspan class="s1"&#39;follower&#39;/spanspan class="p"]/span

span class="n"userInfo_all/spanspan class="p"[/spanspan class="s1"&#39;mid&#39;/spanspan class="p"]/span span class="o"=/span span class="n"mid/span

span class="n"userInfo_all/spanspan class="p"[/spanspan class="s1"&#39;current_level&#39;/spanspan class="p"]/span span class="o"=/span span class="n"current_level/span

span class="n"userInfo_all/spanspan class="p"[/spanspan class="s1"&#39;current_exp&#39;/spanspan class="p"]/span span class="o"=/span span class="n"current_exp/span

span class="n"userInfo_all/spanspan class="p"[/spanspan class="s1"&#39;coins&#39;/spanspan class="p"]/span span class="o"=/span span class="n"coins/span

span class="n"userInfo_all/spanspan class="p"[/spanspan class="s1"&#39;following&#39;/spanspan class="p"]/span span class="o"=/span span class="n"following/span

span class="n"userInfo_all/spanspan class="p"[/spanspan class="s1"&#39;follower&#39;/spanspan class="p"]/span span class="o"=/span span class="n"follower/span

span class="n"userInfo_all/spanspan class="p"[/spanspan class="s1"&#39;create_datetime&#39;/spanspan class="p"]/span span class="o"=/span span class="nb"str/spanspan class="p"(/span

span class="n"time/spanspan class="o"./spanspan class="n"strftime/spanspan class="p"(/spanspan class="s2""%Y-%m-/spanspan class="si"%d/spanspan class="s2" %H:%M:%S"/spanspan class="p",/span span class="n"time/spanspan class="o"./spanspan class="n"localtime/spanspan class="p"()))/span

span class="k"print/spanspan class="p"(/spanspan class="n"userInfo_all/spanspan class="p")/span

span class="n"text/span span class="o"=/span span class="s2""执行成功！b站我的数据，时间：{}"/spanspan class="o"./spanspan class="n"format/spanspan class="p"(/spanspan class="n"userInfo_all/spanspan class="p"[/spanspan class="s1"&#39;create_datetime&#39;/spanspan class="p"])/span

span class="n"title/span span class="o"=/span span class="s2""爬虫执行成功"/span

span class="n"push_bark/spanspan class="p"(/spanspan class="n"title/spanspan class="p",/span span class="n"text/spanspan class="p")/spanspan class="c1"#调用push_bark函数，爬虫爬取成功后，会发push给手机；若不需要刻意注释掉此行/span

span class="k"return/span span class="n"userInfo_all/span

span class="c1"#爬虫成功后push手机函数，需要下载bark手机app，若不需要，上面不要调用push_bark(title, text)函数即可/span

span class="k"def/span span class="nf"push_bark/spanspan class="p"(/spanspan class="n"title/spanspan class="p",/span span class="n"text/spanspan class="p"):/span

span class="c1"#push_url：打开bark app，复制里面的连接，进行替换/span

span class="n"push_url/span span class="o"=/span span class="s1"&#39;https://api.day.app/xxxxx/{}/{}&#39;/spanspan class="o"./spanspan class="n"format/spanspan class="p"(/span

span class="n"title/spanspan class="p",/span span class="n"text/spanspan class="p")/span

span class="n"headers/span span class="o"=/span span class="p"{/span

span class="s1"&#39;content-type&#39;/spanspan class="p":/span span class="s2""application/x-www-form-urlencoded"/spanspan class="p",/span

span class="p"}/span

span class="n"response/span span class="o"=/span span class="n"requests/spanspan class="o"./spanspan class="n"post/spanspan class="p"(/spanspan class="n"push_url/spanspan class="p",/span span class="n"headers/spanspan class="o"=/spanspan class="n"headers/spanspan class="p")/span

span class="k"print/spanspan class="p"(/spanspan class="n"push_url/spanspan class="p")/span

span class="n"text/span span class="o"=/span span class="n"json/spanspan class="o"./spanspan class="n"loads/spanspan class="p"(/spanspan class="n"response/spanspan class="o"./spanspan class="n"text/spanspan class="p")/span

span class="k"print/spanspan class="p"(/spanspan class="n"text/spanspan class="p")/span

span class="k"def/span span class="nf"userInfo_for_sql/spanspan class="p"(/spanspan class="n"userInfo_all/spanspan class="p"):/span

span class="n"db/span span class="o"=/span span class="n"connetSql/spanspan class="p"()/span

span class="n"conn/span span class="o"=/span span class="n"db/spanspan class="p"[/spanspan class="mi"0/spanspan class="p"]/span

span class="n"database/span span class="o"=/span span class="n"db/spanspan class="p"[/spanspan class="mi"1/spanspan class="p"]/span

span class="n"mid/span span class="o"=/span span class="n"userInfo_all/spanspan class="p"[/spanspan class="s1"&#39;mid&#39;/spanspan class="p"]/span

span class="n"current_level/span span class="o"=/span span class="nb"str/spanspan class="p"(/spanspan class="n"userInfo_all/spanspan class="p"[/spanspan class="s1"&#39;current_level&#39;/spanspan class="p"])/span

span class="n"current_exp/span span class="o"=/span span class="nb"str/spanspan class="p"(/spanspan class="n"userInfo_all/spanspan class="p"[/spanspan class="s1"&#39;current_exp&#39;/spanspan class="p"])/span

span class="n"coins/span span class="o"=/span span class="nb"str/spanspan class="p"(/spanspan class="n"userInfo_all/spanspan class="p"[/spanspan class="s1"&#39;coins&#39;/spanspan class="p"])/span

span class="n"following/span span class="o"=/span span class="nb"str/spanspan class="p"(/spanspan class="n"userInfo_all/spanspan class="p"[/spanspan class="s1"&#39;following&#39;/spanspan class="p"])/span

span class="n"follower/span span class="o"=/span span class="nb"str/spanspan class="p"(/spanspan class="n"userInfo_all/spanspan class="p"[/spanspan class="s1"&#39;follower&#39;/spanspan class="p"])/span

span class="n"create_datetime/span span class="o"=/span span class="n"userInfo_all/spanspan class="p"[/spanspan class="s1"&#39;create_datetime&#39;/spanspan class="p"]/span

span class="c1"#sql：执行的数据库语句，案例中的数据库表名为“bili2_user_info”，根据需要替换/span

span class="n"sql/span span class="o"=/span span class="nb"str/spanspan class="p"(/spanspan class="s2""insert into bili2_user_info(mid,current_level,current_exp,coins,following,follower,create_datetime) values(&#39;{}&#39;,&#39;{}&#39;,&#39;{}&#39;,&#39;{}&#39;,&#39;{}&#39;,&#39;{}&#39;,&#39;{}&#39;)"/spanspan class="o"./spanspan class="n"format/spanspan class="p"(/spanspan class="n"mid/spanspan class="p",/spanspan class="n"current_level/spanspan class="p",/spanspan class="n"current_exp/spanspan class="p",/spanspan class="n"coins/spanspan class="p",/spanspan class="n"following/spanspan class="p",/spanspan class="n"follower/spanspan class="p",/spanspan class="n"create_datetime/spanspan class="p"))/span

span class="n"conn/spanspan class="o"./spanspan class="n"execute/spanspan class="p"(/spanspan class="n"sql/spanspan class="p")/span

span class="n"database/spanspan class="o"./spanspan class="n"commit/spanspan class="p"()/span

span class="n"database/spanspan class="o"./spanspan class="n"close/spanspan class="p"()/span

span class="n"conn/spanspan class="o"./spanspan class="n"close/spanspan class="p"()/span

span class="k"def/span span class="nf"connetSql/spanspan class="p"():/span

span class="c1"#数据库连接配置：user需要设置成自己的数据库用户名，password需要设置成自己的数据库密码，host需要设置成自己的数据库地址，db需要设置成自己的数据库名称，port需要设置为自己的数据库端口号（默认3306）/span

span class="n"sqlConnect/span span class="o"=/span span class="n"pymysql/spanspan class="o"./spanspan class="n"connect/spanspan class="p"(/spanspan class="n"user/spanspan class="o"=/spanspan class="s2"""/spanspan class="p",/span span class="n"password/spanspan class="o"=/spanspan class="s2"""/spanspan class="p",/span

span class="n"host/spanspan class="o"=/spanspan class="s2""192.168.50.226"/spanspan class="p",/span span class="n"db/spanspan class="o"=/spanspan class="s2"""/spanspan class="p",/span span class="n"charset/spanspan class="o"=/spanspan class="s2""utf8"/spanspan class="p",/span span class="n"port/spanspan class="o"=/spanspan class="mi"3306/spanspan class="p")/span

span class="n"conn/span span class="o"=/span span class="n"sqlConnect/spanspan class="o"./spanspan class="n"cursor/spanspan class="p"()/span

span class="k"return/span span class="n"conn/spanspan class="p",/span span class="n"sqlConnect/span

span class="k"if/span span class="vm"__name__/span span class="o"==/span span class="s1"&#39;__main__&#39;/spanspan class="p":/span

span class="c1"#执行，需要将userInfo(&#39;2417142&#39;)中的2417142替换为自己的user id，在b站可以找到自己的id/span

span class="n"userInfo_for_sql/spanspan class="p"(/spanspan class="n"userInfo/spanspan class="p"(/spanspan class="s1"&#39;2417142&#39;/spanspan class="p"))/span/code/pre/p

p作为一种市场转型产品，狗自学成才的sql / python，大个子*敏*感*词*了代码并写错了/p

p数据库结构简介/p

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

pmysql数据库配置/p

pprecode class="language-sql"span class="err"数据库名：/spanspan class="n"bili2_user/span

span class="err"数据表名：/spanspan class="n"bili2_user_info/span

span class="err"数据表字段/span

span class="n"mid/spanspan class="p":/spanspan class="err"用户/spanspan class="n"id/span

span class="n"current_level/spanspan class="p":/spanspan class="err"等级/span

span class="n"current_exp/spanspan class="p":/spanspan class="err"经验值/span

span class="n"coins/spanspan class="p":/spanspan class="err"*敏*感*词*数量/span

span class="n"create_datetime/spanspan class="p":/spanspan class="err"记录添加时间/span

span class="err"数据库的字段设置除了/spanspan class="n"create_datetime是datetime类型外/spanspan class="err"，其他均使用/spanspan class="nb"int/spanspan class="o"//spanspan class="mi"255/spanspan class="err"即可，粗暴简单/span/code/pre/p

p执行python来配置python3环境代码，并对其进行修改以为自己的配置数据库建立ok/p

p您可以执行它〜如果幸运的话，您很快就能在sql数据库中看到您的数据/p

p如下所示: ⬇️/p

pimg src='https://pic3.zhimg.com/v2-df1c88296b0b613105a36a9785402131_b.png' alt=''//p

p配置环境以定期执行Raspberry Pi环境（与其他linux相同）/p

p建议vscode远程直接连接到Raspberry Pi，本地代码，远程调试/p

p方法: 添加插件“ remote-ssh”/p

pimg src='https://pic2.zhimg.com/v2-59f6729ac584634fbd0fcbe1b3a43e37_b.png' alt=''//p

p然后，您可以远程连接到Raspberry Pi进行调试./p

pimg src='https://pi*敏*感*词*.zhimg.com/v2-c02ddda4d6b08624022be7a7fa27fc81_b.png' alt=''//p

p部署代码后，可以使用crontab定期调用它./p

p如何使用crontab/p

p编辑配置文件/p

pprecode class="language-text"sudo crontab -e/code/pre/p

p在文件末尾添加调用规则/p

pprecode class="language-text"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新手使用（主要是因为我太烹饪了）

　　我建议您下载并安装友好而有效的Microsoft Power bi

　　下载链接: Power Bi

　　如果没有Mac / linux版本，则直接进入虚拟机

　　连接到数据库获取数据

　　按照⬇️图中的步骤操作，成功后，表名称将显示在右侧的“字段”下.

　　

　　创建数据板

　　点击“可视化折线图”，折线图将在左侧创建

　　

　　向看板添加数据

　　第一步: 将create_datetime拖到该轴上，然后单击年/季度/月后面的叉号

　　

　　第2步: 将current_exp拖动到该值中，然后选择该值的显示值作为最大值

　　

　　这时，您的经验值会根据天空的大小而变化！每次单击刷新时，您都可以重新连接到数据库以刷新数据

　　数据预测

　　点击“分析”-“预测”-“预测时长（灵活设置，多次调整，200表示可以预测200天）”-“应用”.

　　

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

　　在折线图中的线条上移动鼠标，您可以看到当聚会横坐标为245时，我的经验值将高于28888，因此可以推断，在大约224天后，我将成为6级老板（245 -21，因为我的数据截止到21日）

　　

　　结论

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

　　在分析级别，我使用了更多的图形化工具，即功能强大的

　　预测可能并不准确，但是随着时间的流逝，数据的积累将越来越准确

　　代码很糟糕，分析方法非常初级，但是它仅提供了一种思维方式. 在数据时代，使用好的工具快速产生结果是最重要的