最全python办公自动化，看完这一篇就够了！_python的自动化办公(2)，面试资料不拿回来安全吗

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化学习资料的朋友，可以戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

创建一个游标cur = con.cursor()# 执行SQl 语句创建库表cur.execute(‘’‘CREATE TABLE stocks (date text, trans text, symbol text, qty real, price real)’‘’)# 向库表中插入数据cur.execute(“INSERT INTO stocks VALUES (‘2006-01-05’,‘BUY’,‘RHAT’,100,35.14)”)# 提交更改结果con.commit()# 关闭链接con.close()

因为需要频繁地操作数据库，所以将这些操作写成一个类：

class DBSqlite: def __init__(self, db): super().init() self.db = db self._conn = None def __del__(self): if self._conn: self._conn.close() def _get_conn(self): if not self._conn: self._conn = sqlite3.connect(self.db) self._conn.row_factory = sqlite3.Row return self._conn def _query(self, sql): conn = self._get_conn() cur = conn.cursor() rows = [] for row in cur.execute(sql): rows.append(row) cur.close() return rows def de(self, sql): conn = self._get_conn() cur = conn.cursor() for s in sql.split(“;”): cur.execute(s) conn.commit() cur.close() return True def insert(self, table, rows): conn = self._get_conn() cur = conn.cursor() cur.executemany(“insert into %s values (%s)” % (table, (“?,”*len(rows[0]))[:-1]), rows) conn.commit() cur.close() return True def query(self, sql): conn = self._get_conn() cur = conn.cursor() cur.execute(sql) rows = cur.fetchall() cur.close() return rows

* 封装了基本操作，`de` 为执行一个数据库操作，`insert` 为插入数据，`query` 执行一个查询
* 需要注意的是 `_get_conn` 中的 `self._conn.row_factory = sqlite3.Row` 语句，作用时执行查询后，返回的结果会被转化为 `sqlite.Row` 对象，以便通过字段名来读取值，详细参加 row\_factory[3]。


处理好基础数据，也有了数据库工具，就可以开始构建系统了。


### 结构设计


系统再小，也需要先从结构设计入手。


这个系统只是单机版（实际上可以做 Web 扩展，下篇展开），所以省去了网络和 API 设计，直接从库表设计开始。


首先分析业务。


训练营的运营数据包括，打卡数据，开单数据，组长日常工作，以及成员积分（通过积分规则，再自动核算部分展开）。


另外，成员有职务之分：普通成员 和 组长。规则是：组长可以作为普通成员，普通成员不能作为组长。


那么人员库表中，加入了职务，和组别，以便区分人员角色：


[图片上传失败…(image-d0fe1e-1649679796363)]



 人员表 

* mixin\_id 是用户注册App的id
* std\_id 为打卡系统的id
* team 为小组名
* title 为职务


然后设置一个活动类型表，并指定活动与职务的关系：


[图片上传失败…(image-43e240-1649679796363)]



 活动表 

* type 为活动类型
* value 为活动积分
* tilte 为该活动对于的职务


接下来就是活动记录表了，由于已经定义了活动与职务的对于关系，所以，活动记录表中，只需记录活动类型即可：[图片上传失败…(image-9db191-1649679796363)]


* mixin\_id 为用户id，std\_id 其实是没必要的，不过录入打卡记录时顺带记录了
* date 为活动发生的日期
* type 为活动内容


如果同一个人同一天同一个活动出现多次，就会有重复记录，那么如何区分是否真的重复呢？在 数据收集 中展开。


除了基本的数据结构，还有积分统计明细和积分合计表，这里不再赘述，会在核算部分提及。


### 数据收集


现在数据框架有了，数据从何而来呢？


这个训练营的数据主要来自两个地方，第一是打卡数据，第二是日常记录数据。


打卡数据由鲸打卡提供，可以在浏览器中查看，并且提供了导出打卡 Excel 的功能。


不过操作比较麻烦：首先登录后台（用微信扫码登录），再先选择导出条件（一般为时间区间），下载Excel，然后打开 Excel，才能复制其中的打卡信息，存入文本文件，最后才能执行脚本处理。



> 
> 好问题：  
>  为什么不直接处理 Excel 呢？
> 
> 
> 1. 因为Excel 处理需要安装额外库，也没有文本文件处理方便。
> 2. 另外未来考虑做成 Web 系统，所以没有做 Excel 的进一步扩展。
> 
> 
> 


不选择导出，就得用程序请鲸鱼打卡上抓取了。


所以就研究了下打开管理后台的请求，分析了一下，请求中有个 `cookie` 值是关键，于是，复制请求，转化为 Python 代码，详细描述见 [自动预约程序]( )


收集到的数据是 JSON 格式的，将其转化为 List，插入数据库：

def record_check(rows): dbrows = [] for row in rows: u = get_user(std_id=int(row[0])) if u: if row[2] != “×”: dbrows.append((u[‘mixin_id’], u[‘std_id’], row[1], “打卡”, 1, row[2], None)) else: print(“没有找到用户：”, row) if len(dbrows) > 0: db.insert(“tprj_activity”, dbrows) return dbrows

* `record_check` 方法是用来记录打开记录的，参数 `rows` 是从打开后台抓取的数据
* `get_user` 是可以根据打卡用户的 id，从用户表中找到用户记录，然后结合打卡记录，补全打卡记录
* `db` 是 上面提到的 DBSqlite 的一个实例，调用其 `insert` 方法将数据插入数据库


日常记录，需要根据训练营中的实际情况做记录，比如成员开单，组长轮值等，记录在 Excel 中比较方便。每日统计一次，所以我直接将数据复制处理，也存放到文本文件中，用程序解析成记录行，插入库表，展示一下解析方法：

def merge_activity(datafilename): rows = [] with open(datafilename, ‘r’, encoding=‘utf-8’) as check_f: data = {} for line in check_f: linedata = line[:-1].split(‘\t’) date = linedata[0].replace(“/”,“-”) userinfo = linedata[1].split(“/”) team = userinfo[0] name, mixin_id, std_id = userinfo[1].split(‘-’) atype = linedata[2] rows.append((mixin_id, date, atype)) …

可以看到，通过读入文本行，再拆分成对于字段，合成活动记录。


这样两个数据收集工作就做好了，这里还需要解决一个问题 —— 避免数据重复。


容易想到的方法是，为数据设置联合主键，然后对数据做增量式更新。


但是这样做需要做更多的工作，而且还要很好的测试。


从业务上分析可知：活动数据并不多，学员个数不过一百。


那么不妨每次重算！？


即每次执行时，先库表数据删除，然后重新插入一遍。


虽然效率了不高，也算是用框架换时间吧，换的不出机器时间，而是我的工作时间哈哈。


### 自动核算


数据统计收集完毕，就需要根据活动积分，计算每个人的积分明细合计。


既然我们选用了数据库，就直接用 Sql 语句搞定吧。


相对程序处理来说，Sql 更容易做统计类的事情。


统计普通成员积分明细的语句如下：

INSERT INTO tprj_user_score_detail SELECT a.mixin_id, sum(s.value), u.team, ‘成员’, a.date FROM tprj_activity a LEFT JOIN tprj_user u ON a.mixin_id = u.mixin_id LEFT JOIN tbas_score s ON a.type = s.type WHERE s.title = ‘成员’ GROUP BY a.mixin_id, u.team, u.title, a.date

* 查询所有职务属于 `成员` 的活动积分，插入成员积分明细表
* `tprj_activity` 为活动记录表，与 `tprj_user` 用户表链接，然后再链接上活动表 `tbas_score`，作用是对活动类做约束
* `where` 条件中，限制活动类型必须为 `成员` 活动
* `sum(s.value)` 为一个成员的当日积分合计，`日期` 体现在 `group by` 的条件中了


类似的需要写很多统计语句，比如组长的，小组的，以及各自的积分合计，不再逐个展示了。


由于 sql 语句较多，为了便于管理，将 sql 语句整理到 `sql.py` 文件中，在导入主程序代码，最后调用 `DBSqlite` 工具方法执行，例如：

import sql…db.de(sql.user_score_detail)…

是不优雅多了？


打卡率是通过统计活动记录计算的：

def cal_check_rate(): ## 计算打卡率 team_member = {} for r in db.query(sql.team_member_count): team_member[r[‘team’]] = r[‘mcount’] dbrows = [] for r in db.query(sql.team_check_count): dbrows.append((r[‘team’], r[‘date’], round((r[‘checkcount’]/team_member[r[‘team’]])*100))) if len(dbrows) > 0: db.insert(“tprj_team_check_rate”, dbrows) return dbrows

* `team_member_count` 语句语句获取各组的人数，因为可能有人没有注册打卡。只通过打卡记录获取组内人数，不严谨。
* `team_check_count` 语句是按组和日期分类核算出的组打卡数
* 打卡率公式为：`（打卡个数/组内人数） * 100%`
* 将计算好的打卡率，按日期存入 `dbrows`，最后插入数据库


这里还需要注意的是重复数据问题，处理方法简单粗暴：



> 
> 全部清除重算
> 
> 
> 


其他数据处理也类似。


### 报表导出


数据处理做好了，要让发挥数据的作用，就需要制作成报表，才能让其他人利用。


本着一切从简的原则（主要是需要尽快提供结果），选择也 Excel 呈现统计结果。


要输出哪些内容呢？


打卡率、成员积分、组排名等，是需要的。


对于打卡率，需要按组分类，这样就有读出小组成员的作用，如何抽取数据呢？


写个 Sql 就好了, 获取打卡率的语句 `check_rate_show` 如下：

SELECT
date,
max(case when team =‘1组’ then rate else 0 end) as ‘1组’,
max(case when team =‘2组’ then rate else 0 end) as ‘2组’,
max(case when team =‘3组’ then rate else 0 end) as ‘3组’,
max(case when team =‘4组’ then rate else 0 end) as ‘4组’,
max(case when team =‘5组’ then rate else 0 end) as ‘5组’
FROM tprj_team_check_rate
GROUP BY date

* `tprj_team_check_rate` 是用于按组和日期存放打卡率
* `select` 语句中，使用了行转列的技巧，使得结果为 第一列为日期，后面列为各个组，这样是为了绘制成图表方便


其实结果可以导入 Excel ，生成报表，更方便一些，但是我没这样做，因为：


1. 操作 Excel 比较费劲，调试工作量大
2. 我有更大的打算，即最终实现为在线版的，所以花费大量时间不值得


因此我直接将数据输出到文本文件里了。


例如对打卡率的输出是这样的：

def show_check_rate():
data = db.qj(sql.check_rate_show)
result = []
# 处理表头
line = ‘\t’.join(data[0].keys()) + “\n”
result.append(line)

# 生成表头
for d in data:
    row = []
    for k in d.keys():
        if k != 'date':
            row.append(str(d[k]) + "%")
        else:
            row.append(d[k])
    line = '\t'.join(row) + "\n"
    result.append(line)
result.append('\n')
return result

* `check_rate_show` 执行 Sql 获得数据
* 从数据中获取表头信息，做成一行记录，请注意字段的分隔为 tab 符，这样是为了方便直接粘贴到 Excel 中
* 取出数据中的每一行，做成表体数据行
* 最后再加入一个回车，这是为了和其他的输出分隔开


方法执行的结果，写入文本文件：

filename = “result_%s.txt” % today.strftime(“%Y-%m-%d %H_%M_%S”)
with open(filename, ‘w’, encoding=‘utf-8’) as r:
r.writelines(show_check_rate()) # 打卡率
r.writelines(show_member_score()) # 成员积分
…

### 最后

不知道你们用的什么环境，我一般都是用的Python3.6环境和pycharm解释器，没有软件，或者没有资料，没人解答问题，都可以免费领取（包括今天的代码），过几天我还会做个视频教程出来，有需要也可以领取~  

给大家准备的学习资料包括但不限于：  

Python 环境、pycharm编辑器/永久激活/翻译插件  

python 零基础视频教程  

Python 界面开发实战教程  

Python 爬虫实战教程  

Python 数据分析实战教程  

python 游戏开发实战教程  

Python 电子书100本  

Python 学习路线规划

![](https://img-blog.csdnimg.cn/d29631674929476f9c3b30f7ff58dff0.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaTM0Nzc5NTc5MA==,size_16,color_FFFFFF,t_70)




**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**[需要这份系统化学习资料的朋友，可以戳这里获取](https://bbs.csdn.net/forums/4304bb5a486d4c3ab8389e65ecb71ac0)**

**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**