系统分析和准备 概述 通过计算机完成高等院校的招生录取工作，是一个复杂而又有代表性的数据库应用。其中涉及到大量考生和院校数据的录入、整理、存储以及由数据库应用程序来实现录取过程的自动化等工作。因为在校学生对高考录取过程都有一定程度的了解，所以我们选用这一项目来达到综合运用Oracle数据库的训练目的。因为这个系统比较复杂，所以我们通过设计和实现一个简化的“招生录取系统”来模拟招生录取的过程。  系统首先要建立合理的数据结构和关系，并输入必要的基本数据，然后根据院校的招生要求和学生填报的志愿，实现对符合要求的考生的录取。通过对这一过程的模拟，我们可以比较全面地应用前面所学的知识和技能，并提高使用Oracle数据库技术解决实际问题的能力。基本需求分析 先来做一个简单的需求分析。高考招生和录取工作，一般是由招生部门和院校合作完成的。招生部门和院校是该应用程序的使用者。该应用程序为考生只提供一些简单的查询功能。 1．院校 院校应提供招生的要求，如招生的人数、最低录取分数线以及对考生的其他条件的限制。 院校需要在录取结束后查询院校的录取名单。 2．招生部门 招生部门要收集和整理考生信息，建立考生信息库和院校信息库。考生信息库用于存取考生的基本信息，包括考生的高考成绩和报考志愿；院校信息库用于存取院校的基本信息和招生信息。 招生部门通过数据库中的院校信息和考生信息，由相应的数据库应用程序来完成一系列的数据加工处理过程，其中最主要的就是投档录取过程。所谓投档，就是把满足院校要求的考生档案信息发送给院校，由院校审查档案后决定考生的录取与否。为了简化录取过程，我们由系统投档程序来完成考生的录取工作，一旦考生满足院校招生条件，即视为被录取。被录取的考生，在数据库中要标识成录取状态，并记录录取院校的信息，在院校信息库中要回填录取人数的有关信息。 在录取过程中或录取结束后，招生部门要进行查询和统计，主要是在录取结束后统计所有院校的招生情况。 3．考生 考生在录取过程中需要查询其高考分数和录取状态。 10.1.3 功能分析设计 系统完成的主要功能有数据录入、投档和查询统计。 1．数据录入 数据录入要完成院校和学生信息表的数据输入及修改工作。 在本系统中，为了完成录取的模拟，可以虚拟10所院校，给出院校的基本信息和招生要求；虚拟至少50名考生，给出考生的基本信息以及考试成绩，并为每个考生填报2个院校志愿(一志愿和二志愿)。为了简化数据录入，可直接由SQL语句或其他工具完成，比如可以使用TOAD软件来完成。 2．投档过程 招生录取的原则是：一志愿要求优先录取，一志愿全部录取完毕后，才能开始二志愿的录取；二志愿录取对没有达到计划招生人数的院校进行补充录取。 根据以上原则，投档可分为一志愿投档和二志愿投档。一志愿投档是根据院校编号完成对一个学校的一志愿投档的；二志愿投档是根据院校编号完成对一个学校的二志愿投档的，二志愿投档应该在一志愿投档完成以后进行。一次完成全部院校的投档称为自动投档。自动投档一次完成对所有院校的一志愿或二志愿投档。如果使用自动投档，只需为一志愿和二志愿分别投档一次，即完成投档过程。作为补充，可以设计一个调剂投档功能，对一、二志愿没有被录取的考生，如果存在没有招满的院校，补充录取同意调剂的考生。 3．查询统计 在投档过程中或投档结束之后，根据院校编号显示院校的录取结果，即录取考生按分数排序的名单，同时应该显示考生的分数、录取的志愿等信息。 在录取结束之后，按院校的录取平均分数排名，显示所有院校的招生统计信息。开发账户的创建和授权 在开发之前，要为新的应用创建模式账户，并授予必要的权限，以便创建表和其他数据库对象。为了能够创建账户和授权，必须使用具有足够权限的管理账户，可使用系统管理员账户来创建新的应用账户。 在users表空间上创建开发账户，增加一些权限。 步骤1：创建账户： Sql代码 见附件   说明：从结果可以看出，深圳职业技术学院计划招生8人，已经录取8人。其中一志愿录取3人，二志愿录取5人。最高分数为589分，最低分数为455分，录取控制分数线为450分。 2．招生情况统计 显示按平均分降序排列的招生情况统计表： EXEC COLLEGE_TOTAL; 执行结果： Sql代码 院校编号 院校名称             招生人数 录取人数 男生人数 女生人数 最高分数 最低分数 平均分数  1001        清华大学     5         5        3        2     689     627     660  1002        北京大学        4        4        1        3        615    600        609  1005        复旦大学        4         2        2        0     610     600        605  1006        中山大学         5        2        0        2        608     587    598  1003        武汉大学        6         6            2         4         617    560            595    1004        华南科技大学         3         2         1         1         595        588            592    1007        华南理工大学         4         4         3            1        585        532            557    1008        暨南大学         3         3            1         582        534        552    1009        深圳大学         6         5            3        2         555        502    522    1010        深圳职业技术学院 8        8         5        3         598        455        502    PL/SQL 过程已成功完成。 说明：从结果可以看出，清华大学平均录取分数最高，为660分。该校计划招收5人，已经招满，其中男生3人，女生2人，最高分为689分，最低分为627分。 结果分析 系统招生录取过程中和结束后，应该对系统数据进行分析，检查是否存在投档错误，避免造成损失。如果发现问题，则要重新投档。 检查没有被录取的考生一志愿报考的院校： Sql代码 SELECT 编号,姓名,总分,院校名称 FROM STUDENT S,COLLEGE C         WHERE S.一志愿=C.院校编号 AND S.录取志愿 IS NULL ORDER BY 总分 DESC; 执行结果： Sql代码 编号 姓名                                    总分 院校名称 --------------- ----------------------------- ----------- ------------------------------           10045 张韦                                     553 复旦大学           10047 高飞云                                 540 复旦大学           10042 邓树林                                 485 北京大学           10030 国丹丹                                 415 深圳大学           10034 洪智力                                 378 武汉大学 后5名考生因为分数不够(低于最低的录取分数线450分)不能被录取，前3名考生要对其进行检查(以第一个考生10045为例)： Sql代码   SELECT * FROM STUDENT WHERE 编号=10045; 执行结果： Sql代码 编号 姓名                        性             总分             一志愿         二志愿         ---------------- --------------------- ------ --------------------- -------------- ---------------                  10045 张韦                        2                 553 1             1005             1007 该生总分为553分，一志愿报1005，二志愿报1007。先检查一志愿的情况： Sql代码 SELECT * FROM COLLEGE WHERE 院校编号=1005; 执行结果： Sql代码 院校编号 院校名称                 录取分数线     招生人数     录取人数      --------------- ---------------------------------------------- ----------------- --------------- ----------------               1005 复旦大学                                                            580                    4                    2 复旦大学虽然没有招满，但因该考生的分数低于复旦大学的录取分数线，所以一志愿落选正常。继续检查二志愿情况： Sql代码 SELECT * FROM COLLEGE WHERE 院校编号=1007; 执行结果： Sql代码 院校编号 院校名称             录取分数线     招生人数     录取人数 --------------- ---------------------------------------------- ---------------- ---------------- ---------------                1007 华南理工大学                    520                    4                    4 该考生达到了华南理工大学的录取分数线。该校招生4人，录取4人。继续检查该考生为什么没有被二志愿录取： Sql代码 SELECT 编号,姓名,总分,录取志愿 FROM STUDENT WHERE 录取院校=1007 ORDER BY 总分 DESC; 执行结果： Sql代码 编号 姓名                                    总分    --------------- ------------------------------- ----------           10017 罗惯通                                 585 1           10046 胡月                                     557 1           10006 杨煌                                     555 2           10041 林晨曦                                 532 1 该校有3名考生被一志愿录取，二志愿录取1人。按照一志愿优先的原则，10045号考生只能参加二志愿投档。该校二志愿缺额1人，按分数从高到低排序，考生杨煌的分数555高于考生张韦的分数553，故杨煌优先录取，达到录取人数4人，录取结束。 通过以上分析可知，张韦没有被录取属于正常情况。 对其他考生的情况分析可以模仿以上步骤进行。系统改进 至此完成了高校招生模拟的全部工作。该系统在以下方面还有待于改进： (1) 可按照模块功能以包的形式对模块进行分类，比如可以分为投档包、查询包和公用包。 (2) 可以设立多个应用账户，通过授予不同的系统权限对象及包的访问权限，来限定对数据的访问和操作。 (3) 进一步完成调剂投档的程序。 以上工作，有兴趣的读者可以进一步完善。练习 设计开发“图书管理系统”，完成图书的入库、查询、出借、归还和信息统计工作。系统主要的表应该包括图书表、出版社表、图书出借表和读者表(参照第4章内容)。 系统的主要功能包括： (1) 插入修改图书表、出版社表和读者表。 (2) 按图书名称、出版社、出版时间和作者检索图书。 (3) 对图书进行模糊检索。 (4) 出借和归还图书。 (5) 查询过期未归还的图书。 (6) 查询在借图书的有关信息。 (7) 热门图书统计(按出借次数统计)。 (8) 按日期查询新进图书。 本文转自 烂泥行天下 51CTO博客，原文链接：http://blog.51cto.com/ilanni/758055

基础整理数据是运营的基础，人员数据是基础数据，首先需要搞定人员信息。训练营里的人员信息来自多个渠道，有通过 APP 报名的，有调查问卷收集的，还有人工录取的。加上同一个可能在不太的地方用不一样的名字，以及不同渠道收集的数据完整性不同，所以整理基础数据工作耗费了将近两天时间。最初用 Excel 的 VLookup 做数据合并，但灵活度小，限制大，放弃了。最后使用 Python 处理各个渠道的数据，再录入了数据库，完成了数据整理工作。这里重点说一下数据库。使用数据库的好处是，方便数据整合、统计和更新。但是数据库一般比较重，维护部署都是问题，于是选用了文本数据库 SQLite[1] 作为数据库。SQLite 很轻，不需要服务器，但功能与 MySQL[2] 类似。使用起来安装 Python 的 SQLite 模块就可以了：pip install sqlite3创建数据库链接：import sqlite3 conn = sqlite3.connect('database.db')其中 database.db 就是一个普通文件，如果没有，会自动创建一个。有了链接，就可以执行数据库操作了，比如创建一个库表,插入数据：# 创建一个游标 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 设计，直接从库表设计开始。首先分析业务。训练营的运营数据包括，打卡数据，开单数据，组长日常工作，以及成员积分（通过积分规则，再自动核算部分展开）。另外，成员有职务之分：普通成员 和 组长。规则是：组长可以作为普通成员，普通成员不能作为组长。那么人员库表中，加入了职务，和组别，以便区分人员角色：人员表mixin_id 是用户注册App的idstd_id 为打卡系统的idteam 为小组名title 为职务然后设置一个活动类型表，并指定活动与职务的关系：活动表type 为活动类型value 为活动积分tilte 为该活动对于的职务接下来就是活动记录表了，由于已经定义了活动与职务的对于关系，所以，活动记录表中，只需记录活动类型即可：mixin_id 为用户id，std_id 其实是没必要的，不过录入打卡记录时顺带记录了date 为活动发生的日期type 为活动内容如果同一个人同一天同一个活动出现多次，就会有重复记录，那么如何区分是否真的重复呢？在 数据收集 中展开。除了基本的数据结构，还有积分统计明细和积分合计表，这里不再赘述，会在核算部分提及。数据收集现在数据框架有了，数据从何而来呢？这个训练营的数据主要来自两个地方，第一是打卡数据，第二是日常记录数据。打卡数据由鲸打卡提供，可以在浏览器中查看，并且提供了导出打卡 Excel 的功能。不过操作比较麻烦：首先登录后台（用微信扫码登录），再先选择导出条件（一般为时间区间），下载Excel，然后打开 Excel，才能复制其中的打卡信息，存入文本文件，最后才能执行脚本处理。好问题：为什么不直接处理 Excel 呢？因为Excel 处理需要安装额外库，也没有文本文件处理方便。另外未来考虑做成 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 dbrowsrecord_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 dbrowsteam_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 datetprj_team_check_rate 是用于按组和日期存放打卡率select 语句中，使用了行转列的技巧，使得结果为 第一列为日期，后面列为各个组，这样是为了绘制成图表方便其实结果可以导入 Excel ，生成报表，更方便一些，但是我没这样做，因为：操作 Excel 比较费劲，调试工作量大我有更大的打算，即最终实现为在线版的，所以花费大量时间不值得因此我直接将数据输出到文本文件里了。例如对打卡率的输出是这样的：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 resultcheck_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()) # 成员积分 ...filename 为要写入的文本文件，这里利用当前时间作为文件名，是为了不重复打开文件，用 writelines 方法将返回的行写入文件中这里还可以调用其他产生输出方法，将结果写入文件最后，文件中数据如下：date 1组 2组 3组 4组 5组 2021-08-01 65% 90% 79% 85% 72% 2021-08-02 75% 90% 79% 85% 67% 2021-08-03 55% 90% 84% 75% 67% 2021-08-04 60% 95% 74% 75% 61%复制到 Excel 的图表数据中就会形成打卡率图表：打卡率图表日常维护运营工作不是一成不变的，比如为了激励成员对提出的问题进行整理，新增了一个积分点叫 解答整理。就得调整积分项，因为之前已经将积分项用库表存储了，现在只需要增加一条记录，并指明该积分适用于成员角色就可以了。另外，在 活动详情 报表中，需要按活动名称记录每个人的数据，也是个行转列的操作，但麻烦的是活动项是会变的。于是先将获取项动态获取到，然后合成为行转列的语句，再和查询语句合并为完整的 Sql 语句，这样活动再有调整时，只管添加数据项就好了，代码如下：score_type_temp = "max(case when type ='{atype}' then num else 0 end) as '{atype}'" types = db.query("select type, value from tbas_score where title='%s'" % title) temps = [] for t in types: temps.append(sql.score_type_temp.format(atype=t['type'])) allsql = sql.member_score.format(",\n".join(temps))最后，将各部分的代码集成起来，放在一个 main 函数中，每天执行一次，将输出的文本文件中的数据复制到 Excel 中，就完成当日报表了，整个操作耗时不到十分钟，还算满意。总结促使我这么做的是，不想在机械的事情上耗费时间，所以会尽可能的将能自动处理的，让程序处理。虽然让一切程序化是一个理想，在实现的道路上会有很多阻碍，所以还需要找到落地的平衡点，需要接受不完美，需要已实用为导向 —— 先实现，再完美。下期，在实现基本功能的基础上，我们聊聊如何将这个平台 Web 化。比心！

最近参加了一个训练营，作为副教练，承担起训练营的运营工作。事不大，活不少，打卡记录、活动积分、奖励制度、评优方案、趋势对比，应有尽有……开始认为 Excel 就足够应付，没想到第一项工作 —— 人员汇总，就把我难倒了，于是果断拎起 Python 这把大刀，披荆斩棘，利用业余时间，不到一周竟然打造出了一套运营管理系统，到底是如何做的呢？一起来看。基础整理数据是运营的基础，人员数据是基础数据，首先需要搞定人员信息。训练营里的人员信息来自多个渠道，有通过 APP 报名的，有调查问卷收集的，还有人工录取的。加上同一个可能在不太的地方用不一样的名字，以及不同渠道收集的数据完整性不同，所以整理基础数据工作耗费了将近两天时间。最初用 Excel 的 VLookup 做数据合并，但灵活度小，限制大，放弃了。最后使用 Python 处理各个渠道的数据，再录入了数据库，完成了数据整理工作。这里重点说一下数据库。使用数据库的好处是，方便数据整合、统计和更新。但是数据库一般比较重，维护部署都是问题，于是选用了文本数据库 SQLite[1] 作为数据库。SQLite 很轻，不需要服务器，但功能与 MySQL[2] 类似。使用起来安装 Python 的 SQLite 模块就可以了：pip install sqlite3创建数据库链接：import sqlite3 conn = sqlite3.connect('database.db')其中 database.db 就是一个普通文件，如果没有，会自动创建一个。有了链接，就可以执行数据库操作了，比如创建一个库表,插入数据：# 创建一个游标 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 设计，直接从库表设计开始。首先分析业务。训练营的运营数据包括，打卡数据，开单数据，组长日常工作，以及成员积分（通过积分规则，再自动核算部分展开）。另外，成员有职务之分：普通成员 和 组长。规则是：组长可以作为普通成员，普通成员不能作为组长。那么人员库表中，加入了职务，和组别，以便区分人员角色：人员表mixin_id 是用户注册App的idstd_id 为打卡系统的idteam 为小组名title 为职务然后设置一个活动类型表，并指定活动与职务的关系：活动表type 为活动类型value 为活动积分tilte 为该活动对于的职务接下来就是活动记录表了，由于已经定义了活动与职务的对于关系，所以，活动记录表中，只需记录活动类型即可：mixin_id 为用户id，std_id 其实是没必要的，不过录入打卡记录时顺带记录了date 为活动发生的日期type 为活动内容如果同一个人同一天同一个活动出现多次，就会有重复记录，那么如何区分是否真的重复呢？在 数据收集 中展开。除了基本的数据结构，还有积分统计明细和积分合计表，这里不再赘述，会在核算部分提及。数据收集现在数据框架有了，数据从何而来呢？这个训练营的数据主要来自两个地方，第一是打卡数据，第二是日常记录数据。打卡数据由鲸打卡提供，可以在浏览器中查看，并且提供了导出打卡 Excel 的功能。不过操作比较麻烦：首先登录后台（用微信扫码登录），再先选择导出条件（一般为时间区间），下载Excel，然后打开 Excel，才能复制其中的打卡信息，存入文本文件，最后才能执行脚本处理。好问题：为什么不直接处理 Excel 呢？因为Excel 处理需要安装额外库，也没有文本文件处理方便。另外未来考虑做成 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 dbrowsrecord_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 dbrowsteam_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 datetprj_team_check_rate 是用于按组和日期存放打卡率select 语句中，使用了行转列的技巧，使得结果为 第一列为日期，后面列为各个组，这样是为了绘制成图表方便其实结果可以导入 Excel ，生成报表，更方便一些，但是我没这样做，因为：操作 Excel 比较费劲，调试工作量大我有更大的打算，即最终实现为在线版的，所以花费大量时间不值得因此我直接将数据输出到文本文件里了。例如对打卡率的输出是这样的：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 resultcheck_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()) # 成员积分 ...filename 为要写入的文本文件，这里利用当前时间作为文件名，是为了不重复打开文件，用 writelines 方法将返回的行写入文件中这里还可以调用其他产生输出方法，将结果写入文件最后，文件中数据如下：date 1组 2组 3组 4组 5组 2021-08-01 65% 90% 79% 85% 72% 2021-08-02 75% 90% 79% 85% 67% 2021-08-03 55% 90% 84% 75% 67% 2021-08-04 60% 95% 74% 75% 61%复制到 Excel 的图表数据中就会形成打卡率图表：打卡率图表日常维护运营工作不是一成不变的，比如为了激励成员对提出的问题进行整理，新增了一个积分点叫 解答整理。就得调整积分项，因为之前已经将积分项用库表存储了，现在只需要增加一条记录，并指明该积分适用于成员角色就可以了。另外，在 活动详情 报表中，需要按活动名称记录每个人的数据，也是个行转列的操作，但麻烦的是活动项是会变的。于是先将获取项动态获取到，然后合成为行转列的语句，再和查询语句合并为完整的 Sql 语句，这样活动再有调整时，只管添加数据项就好了，代码如下：score_type_temp = "max(case when type ='{atype}' then num else 0 end) as '{atype}'" types = db.query("select type, value from tbas_score where title='%s'" % title) temps = [] for t in types: temps.append(sql.score_type_temp.format(atype=t['type'])) allsql = sql.member_score.format(",\n".join(temps))最后，将各部分的代码集成起来，放在一个 main 函数中，每天执行一次，将输出的文本文件中的数据复制到 Excel 中，就完成当日报表了，整个操作耗时不到十分钟，还算满意。总结促使我这么做的是，不想在机械的事情上耗费时间，所以会尽可能的将能自动处理的，让程序处理。虽然让一切程序化是一个理想，在实现的道路上会有很多阻碍，所以还需要找到落地的平衡点，需要接受不完美，需要已实用为导向 —— 先实现，再完美。下期，在实现基本功能的基础上，我们聊聊如何将这个平台 Web 化。比心！

2018年云栖大会，吸引了来自64个国家和地区的12万人参加，吴慧霞、周红运和李帅是其中的三个。他们的身份有些特殊，他们是阿里云邀请的河北师大学生，门票是阿里云免费提供的，阿里云还为他们提供了往返差旅补助。 吴慧霞（左二）和其他四位同学在云栖大会 6月下旬，他们刚刚在石家庄的校园里，参加了阿里云的脱贫公益项目“初级认证工程师培训”。阿里云、云计算原本对他们都是“觉得很遥远”的存在，当阿里云通过公益的形式走进他们世界的时候，就像一束光照了进来。他们的世界因此改变了。 “原来是这么回事” 吴慧霞今年大三，1996年生，软件工程专业。高考时，她报的几乎所有专业都是计算机类的。 报计算机是因为高考之后，她发现自己对智能世界一无所知。电脑不会开关机，新买的智能手机“连最基本的导航都不会”。她感觉未来是信息化的时代，对互联网一窍不通的话，不行。“不懂的地方就要补回来。”她是好强的性格，越是“瘸腿”，越要迎难而上。 好强的性格缘自吴慧霞特殊的家庭。从她记事的时候起，母亲就是一个病人，长年吃药。父亲身体也不好，有时也要吃药。初中的时候，妹妹和弟弟相继出生。两个大人的药费、抚养三个孩子的钱都靠家里几亩地的微薄收入维持着。 爸爸妈妈都相信“知识改变命运”，他们常说的一句话就是，砸锅卖铁也要供三个孩子读大学。每次听到这句话，吴慧霞就在心里告诉自己：“必须要好好学！一定要好好学！” 要更努力，要抓住每一个机会成了吴慧霞的人生信条。所以当她从辅导员那里得知阿里云的云计算培训项目时，第一时间报了名。 汽车服务工程专业的周红运和地图学与地理信息系统专业的李帅也报了名。他们也都看到了互联网和人工智能是未来的方向，所以都在想往这个方向靠。 李帅今年研二，他本科读的是地理科学，考研时特地换了一个跟“信息”沾边的专业。周红运大三，她的专业是调剂的。查到录取结果前，她压根没听过这个专业。经过一番了解，她发现这个专业还不错，可以学到一门实实在在的技术，就业率在河北师大也数一数二。她现在想往新能源汽车和车联网方向发展，这两个方向热门，而且在她看来，比起汽车维修这样的方向，也更适合女生。 班主任吴慧霞和班上的同学 他们可以参加阿里云的免费培训，是因为他们有一个共同的身份：都是建档立卡学生。所谓建档立卡学生，就是来自被录入国家扶贫数据库中的贫困家庭的学生。这个培训是阿里云大学面向贫困大学生的公益项目，两天的时间里，阿里云的讲师给他们系统讲授数据上云、云上迁移，负载均衡、安全处理等云计算相关的内容，通过考试的同学可以获得阿里云初级认证工程师（ACA）证书。 阿里云大学是阿里云计算有限公司旗下的核心教育培训部门，致力于从IT时代向DT时代转型过程中在云计算、大数据、人工智能、云安全等领域，打造“科技+管理”新范式的全产业链人才生态。 这是整个阿里巴巴集团脱贫战略的一部分。2017年12月，阿里巴巴宣布成立脱贫基金，脱贫成为集团三大发展战略之一，教育和电商、女性、医疗、生态是阿里脱贫战略的重点发力领域。发布会现场，马云向几位脱贫基金副主席放出“狠话”：“你们四个副主席，业务做不好，还能给你个机会，脱贫做不好，不会放过你。”除了四个副主席，阿里体系内的每个独立公司都会负责一个脱贫项目，对总裁进行脱贫KPI考核。 河北师大是阿里云教育脱贫赋能计划在河北省的首站，吴慧霞、周红运、李帅和其他72位来自贫困家庭的学生参加了首批培训。培训在临近暑假的一个周末进行，两整天的时间，每天从早上八点到晚上九点。这样的培训对这些身处相对闭塞的象牙塔的学生们来说无疑是一次特殊的体验。我问起他们各自的感受。 周红运：“大一就是简单的C语言编程嘛，现在突然有那么多编码，有点懵，感觉挺好玩的。” 李帅：“有点颠覆（之前的认识）的，培训结束就感觉云计算需要的知识还是特别多的。” 吴慧霞：“大学很少有这样集中学习的时间，尤其是他们（阿里云老师讲的）好多都是实训的。我们学的都是理论和原理，这种实战的比较少的。以前不能想象到它们是怎么样用的，学下来之后知道，做博客是这样写，怎么样去用这个服务器。实训之后就感觉，原来是这么回事。” 吴慧霞 简言之，阿里云的培训让他们平时学的理论“落地”了，也给他们接下来更深入的自学提供了“抓手”。 更大的世界 暑假的时候，又一个机会出现了。所有已经参加阿里云教育脱贫赋能计划并拿到ACA证书的同学都可以报名参加九月在杭州举办的云栖大会。吴慧霞“很激动”，又第一时间报了名。 报名时她正在北京做兼职。这是她第二次去北京。上一次去北京是高考之后。在这之前，她从没出过沧州，她迫不及待地“想出去看看外面的世界”。两年多过去了，她的足迹只是多了北京和石家庄。她还从没有去过南方，杭州对她来说仍然是渴望看看的“外面的世界”。 最终，吴慧霞如愿了。她和周红运、李帅以及另外两位同学入选了。从杭州回来，她写了一篇1872字的感想，她用了很多华丽的语句表达她对杭州的喜欢： “和梦里的一样，没有北方的单调，有的都是令人向往。” “有古风古韵的邸府墙楼，有充满灵气的林荫大道，也有平静怡人的神秘感，令人向往、憧憬，平淡中的神秘无比性感。” …… 有机会到杭州参加云栖大会，周红运也“挺兴奋的”。她也没有出过远门，也同样想看看外面的世界。周红运出生于河北衡水一个省级贫困县（今年九月刚退出贫困县序列），石家庄是她此前到过的最远的地方。对她来说，成长也是一步步见识更大的世界的过程。“之前觉得石家庄挺好的，后来去了杭州就更好了。”再之前，觉得自己的县城挺好的，到了石家庄之后，发现原来“外面的世界挺大的”。 周红运 周红运也来自一个多病人的家庭。家里一半的收入都花在了母亲和爷爷奶奶的药费上。尽管父亲从来不说，但她知道家里是欠钱的，有时她在家里会遇上前来讨债的亲戚。上大学之后，她终于可以不问家里要钱了，现在建档立卡学生可以享受免学费、住宿费和书本费的政策，兼职挣的钱和奖学金、助学金除了给自己花，有时还可以帮助家里一点。 头两天的云栖大会，周红运逛了斑马汽车体验区，听了城市大脑和高德地图的论坛。让她印象深刻的是高德地图的一个功能，导航可以告诉你某个时间某个路口是红灯还是绿灯。还有阿里云的城市大脑，“不用报警，交警就能知道哪儿出了事故，比较惊讶。看完五花八门的机器人、AI，周红运在心里感慨，“现在都已经发展成这样了。”惊讶之外更多的是好奇和信心，她很想搞懂这些技术是“怎么弄出来的”，也更坚定了之前往车联网方向发展的想法，目标更清晰了。 李帅也是这样。他和我讲起在一场论坛上碰到的细节。一个中科院的嘉宾演讲完之后说，我们不是搞计算机专业的，我们是从地理学的角度研究这些东西。主持人问现场听众，哪些人觉得自己也不是搞计算机的，很多人举手。“主持人说了一句话让我印象特别深刻，他说虽然很多人觉得自己不是搞计算机、云计算的，但是大家的成果里却处处离不开云计算。”他很赞同，他现在在实验室做旅游信息挖掘的项目也越来越多地用到人工智能模型。 李帅在实验室 在李帅之前的印象里，阿里巴巴是一家电商公司，但这次近距离的接触让他完全转变了看法：“现在我感觉阿里真的是全能的、各方面发展的高科技公司。” 吴慧霞和周红运也有相同的感觉。阿里巴巴原本在她们的世界里只是一两个APP（支付宝、淘宝）和取快递包裹的地方（菜鸟驿站），但现在它的形象“立体”了。吴慧霞知道了阿里还有致力于探索科技未知的达摩院，周红运知道了阿里“合作伙伴特别多”——这是一家已经成为赋能360行的公司。 吴慧霞高中的时候就有将来要创业的想法。这次参加云栖大会，她有了更多的动力。她说，听讲座时坐在她身旁的一个个CEO让她“感觉很有压力”。以她的性格，压力即动力。她在谈杭州之行感受的文章里写：“近在眼前的距离，却莫名不见五指，一直想达到的彼岸，唯有砥砺前行，逼着自己跑，才可以去拥贴近和拥抱。加油！”“杭州之旅，回味无穷，让震撼激励自己进步，让野心鼓舞自己成长。遇见，爱上，杭州，等我......” 吴慧霞说，这两次经历对她是一种很大的激励，“让我对未来有更多的选择和构想。”看到了更大的世界，也一直想到更大的世界闯荡的她很确定自己毕业后一定不会留在省内。她现在的理想城市就是杭州。她称杭州是她“一见钟情”的城市。 故事刚刚开始 从杭州回来，吴慧霞更加感觉选对了专业。她重新回到忙碌的日常生活。她是班里的团支书，还是18级新生的班主任。这是她典型的一天：早上6点前起床，7点准时到教室。白天的时间都是在教室和图书馆度过的，除了上课之外，就是工作和见缝插针、争分夺秒的学习，“我这个意外事情特别多，有可能我正准备学习，啪就来个事情”。晚上10点30分宿舍关门，她总是在教室或图书馆待到10点15分，小跑着回宿舍。 在做班主任的过程中，吴慧霞很注重通过自己的言行影响班上的同学们，“告诉他们在大学里应该是什么样的，少走弯路，让自己给自己的人生画出不一样的片段。”我问她理想的人生是什么样的，她举了一个“斜杠”CEO的例子。大一的心理课上，老师有一次讲到他的一个女同学，先创办了一家公司，后来因为兴趣做了鼓手，还开了一家蛋糕店。 “我觉得这样的女生其实更好。”吴慧霞说。当年村里一起玩耍的小伙伴们现在多数都在过着与她完全不同的生活。他们很多都在外打工，有不少已经结婚生子。吴慧霞觉得大学是她人生的一个分水岭，读大学的这几年是她人生密度最大的几年：从最初的电脑盲到现在拿到编程竞赛的省级三等奖、获得阿里云ACA证书、参加云栖大会……有种瞬间跨越几个时代的恍惚感。 我问她觉得自己与昔日小伙伴最大的不同是什么，她说：“我觉得是追求。他们选择了一种稳定的生活，然而我选择了更可以闯荡、发展女性（可能性）的那种（生活）。” 阿里云工作人员与参加云栖大会的五位同学 李帅从杭州参加云栖大会回来，身边的同学都很羡慕。他们见到他都问他：“你见到马云了吗？”他没有见到马云，但是他有了一个与马云有关的理想。那就是毕业后到阿里张北数据中心上班。 三年前，他看新闻说，阿里将在张北建大型云计算基地。张北就在他的家乡张家口。他不想到离家太远的地方工作，也喜欢阿里（“我很喜欢阿里，包括阿里的软件每个细节都会给我一种很舒服的感觉，每个细节都渗透一种情感”），当时就有一个模糊的想法，在这儿工作，公司好，离家近——两个都占了。但当时觉得阿里很遥远，没有敢多想。现在他敢想了，“到时肯定会投张北基地的招聘。” 吴慧霞、李帅和周红运这三个年轻人的故事刚刚开始，阿里云的教育脱贫故事也刚刚开始。2018年7月13日，“阿里云教育脱贫赋能计划”进入河北省的第二站河北农业大学，93名贫困学生参加了培训。 除此之外，这一计划也在贵州等地展开。今年5月的贵阳数博会上，阿里云总裁胡晓明宣布同贵州省教育厅、贵州省大数据管理局合作，计划未来三年内为超过8000名贵州贫困大学生、贫困区县技术从业者提供免费的云计算、大数据培训与认证机会。这束光将照进越来越多的贫困大学生的世界。 原文发布时间为：2018-10-29 本文作者：刘磊 本文来自云栖社区合作伙伴“天下网商”，了解相关信息可以关注“天下网商”。