最近参加了一个训练营。作为助理教练，她承担了训练营的运营工作。没什么大不了的，一大堆工作，签到记录，活动积分，奖励制度，测评方案，趋势对比，应有尽有……一开始以为Excel就可以搞定，没想到第一个任务-人事总结，难倒我于是果断拿起Python这把大刀，排除万难，利用业余时间用了不到一周的时间搭建了一个运营管理系统。我是怎么做到的？一起来看看吧。分拣基础数据是作业的基础，人员数据是基础数据。首先，需要对人员信息进行结算。集训营的人员信息来自多个渠道，有的是通过APP注册的，有的是通过问卷收集的，还有的是人工招聘的。再加上同一个可能在不同地方使用不同的名字，不同渠道收集到的数据完整性也不一样，所以整理基础数据的工作花了将近两天的时间。一开始用Excel的VLookup做数据合并，但是灵活性小，限制大，所以放弃了。最后使用Python对各个渠道的数据进行处理，然后录入数据库，完成数据整理工作。这里我们主要关注数据库。使用数据库的好处是便于数据的整合、统计和更新。但是数据库一般比较重，维护和部署都是问题，所以选择了文本数据库SQLite作为数据库。SQLite是轻量级的，不需要服务器，但功能类似于MySQL。要使用它，只需安装PythonSQLite模块：pipinstallsqlite3创建数据库连接：importsqlite3conn=sqlite3.connect('database.db')其中database.db是一个普通文件，如果没有，会自动创建一个.有了链接，就可以进行数据库操作，比如创建库表和插入数据：#创建游标cur=con.cursor()#执行创建库表的SQl语句cur.execute('''CREATETABLEstocks(datetext,transtext,symboltext,qutyreal,pricereal)''')#将数据插入库表cur.execute("INSERTINTOstocksVALUES('2006-01-05','BUY','RHAT',100,35.14)")#提交变更结果con.commit()#关闭链接con.close()因为需要经常操作数据库，所以把这些操作写到一个类中：classDBSqlite:def__init__(self,db):super().__init__()self.db=dbself._conn=nonedef__del__(self):ifself._conn:self._conn.close()def_get_conn:ifnotself._conn:Self._connnnnnn=Sqlite3.connect(self.db)self._conn.row_factory=sqlite3.Row返回Self._connnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn在cur.execute(sql)中：rows.append(row)cur.close()返回行def德（自我，SQL):conn=self._get_conn()cur=conn.cursor()forsinsql.split(";"):cur.execute(s)conn.commit()cur.close()rowreturnTruesdelf表,insert(conn=self._get_conn()cur=conn.cursor()cur.executemany("insertinto%svalues(%s)"%(table,("?,"*len(rows[0]))[:-1]),rows)conn.commit()cur.close()返回真defquery(self,sql):conn=self._get_conn()cur=urconn.cursor()sutecurrow(sql.exe)fetchall()cur.close()返回行封装了基本操作，de是执行数据库操作，insert是插入数据，query是执行查询，需要注意的是self._conn.row_factory=sqlite3._get_conn中的Row语句有功能随时执行查询后，将返回的结果转换成sqlite.Row对象，这样可以通过字段名读取值，参与处理基本数据在row_factory中有详细介绍。有了数据库工具，就可以开始搭建系统了。不管多小的结构de标志系统，首先要从结构设计入手。本系统只是单机版（其实可以扩展成Web，下一章会展开），所以网络和API的设计就省略了，直接从库表的设计开始.先分析业务。训练营运营数据包括签到数据、开票数据、领队日常工作、会员积分（通过积分规则，再扩展自动记账部分）。此外，成员还有职位：普通成员和组长。规则是：团长可以是普通会员，普通会员不能当团长。然后在personal数据库表中加入jobtitles和groups来区分人员角色：personnel表mixin_id是用户注册App的idstd_id，签到系统的idteam是团队名，title是职位标题。然后设置一个活动类型表，指定活动与Job的关系：activitytabletype为活动类型value为活动积分tilte为活动对应的job接下来就是活动记录表。由于已经定义了activity和job的关系，在activity记录表中，只需要记录activity的类型就可以了：mixin_id是用户id，std_id其实可以不用，但是在进入签到记录的时候，记录date为事件发生的日期，type为事件的内容。如果同一个人同一事件在同一天发生多次，就会出现Duplicaterecord，那么如何区分是否真的重复呢？展开数据收集。除了基本的数据结构外，还有积分统计明细和积分总表，这里不再赘述，记账部分会提到。数据收集既然数据框架已经就位，那么数据从哪里来呢？本次训练营的数据主要来自两个地方，第一个是签到数据，第二个是日记录数据。签到数据由鲸签提供，可在浏览器中查看，并提供导出签到到Excel的功能。不过操作比较麻烦：先登录后台（微信扫码登录），然后选择导出条件（一般是时间间隔），下载Excel，然后打开Excel复制里面的签到信息，保存在一个文本文件中，最后执行脚本处理。好问题：\为什么不只处理Excel？因为Excel处理需要安装额外的库，所以不如文本文件处理方便。另外考虑到以后是Web系统，所以没有对Excel做进一步的扩展。如果不选择导出，就得用程序让鲸鱼签到抢。于是研究了一下打开管理后台的request，分析了一下，发现request中的一个cookie值是key，于是复制request，转成Python代码。采集到的数据为JSON格式，转成List，插入数据库：defrecord_check(rows):dbrows=[]forrowinrows:u=get_user(std_id=int(row[0]))ifu:ifrow[2]!="×":dbrows.append((u['mixin_id'],u['std_id'],row[1],"login",1,row[2],无))else:print("Nouserfound:",row)iflen(dbrows)>0:db.insert("tprj_activity",dbrows)returndbrowsrecord_check方法用于记录打开的记录，参数rows取自打开后台数据get_user可以根据登录用户的id从user表中找到用户记录，然后结合登录记录完成登录记录。根据训练营的实际情况用Excel记录更方便，如会员开票、领队值班等。每天统计一次，所以我直接把数据复制处理，存到一个文本文件中，用程序解析成记录行，插入到库表中，显示解析方法：defmerge_activity(数据文件名):rows=[]withopen(datafilename,'r',encoding='utf-8')ascheck_f:data={}forlineincheck_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('-')linedata[2]=rows.append((mixin_id,date,atype))...可以看出，通过读入文本行，然后拆分成字段，就合成了activerecord.这样两个数据采集任务就完成了，这里还有一个问题需要解决——避免数据重复。比较容易想到的方法是给数据设置一个联合主键，然后增量更新数据。但这样做需要做更多的工作，并且经过充分测试。从业务分析可以看出，活动数据不多，学生人数只有一百人。那你还不如每次都重新计算呢！？即每次执行时，先删除数据库表数据，然后再重新插入。虽然效率不高，但也算是和框架交换了时间。交流的不是机器时间，而是我的工作时间哈哈。自动记账数据统计完成后，需要根据活动积分计算每个人积分的总明细。既然选择了数据库，那我们就直接用Sql语句来做吧。与程序处理相比，Sql更容易做统计的事情。统计普通会员积分明细的语句如下：mixin_id=u.mixin_idLEFTJOINtbas_scoresONa.type=s.typeWHEREs.title='Member'GROUPBYa.mixin_id,u.team,u.title,a.date查询标题所属的所有活动点members，并插入会员积分详情表tprj_activity是一个活动记录表，链接到tprj_user用户表，再链接到活动表tbas_score，用于约束活动类where条件，活动类型必须是memberactivitysum(s.value)是会员当天的总积分和日期，按条件反映在group中。和这个类似，需要写很多统计报表，比如组长的，组的，各自的总分，就不一一展示了。由于sql语句较多，为了便于管理，将sql语句整理到sql.py文件中，导入主要程序代码，最后调用DBSqlite工具方法执行，例如：importsql...db。de（sql.user_score_detail）。..它不那么优雅吗？通过统计活动记录计算签到率：defcal_check_rate():##Calculatecheck-inrateteam_member={}forrindb.query(sql.team_member_count):team_member[r['team']]=r['mcount']dbrows=[]forrindb.query(sql.team_check_count):dbrows.append((r['team'],r['date'],round((r['checkcount']/team_member[r['team']])*100)))iflen(dbrows)>0:db.insert("tprj_team_check_rate",dbrows)returndbrowsteam_member_count语句得到每个组的人数，因为有些人可能还没有注册打卡，群里的人数只是通过签到记录得到的，不够严谨。team_check_count语句是按组和日期计算的组签到次数。签到率公式为：（签到人数/团内人数）*100%。计算出的入住率会按日期存储在dbrows中，最后插入到数据库中。这里需要注意重复数据的问题，处理方法简单粗暴：清除所有重新计算，其他数据处理类似。报表导出数据已经处理好了。要让数据发挥它的作用，就需要把它做成报表，让其他人可以使用。本着简明扼要的原则（主要是需要尽快提供结果），选择将统计结果也以Excel形式呈现。应该输出什么？需要入住率、会员积分、团体排名等。对于签到率，需要按组分类，这样才能读出组的成员。如何提取数据？随便写一个Sql，语句check_rate_show获取签到率如下：SELECTdate,max(casewhenteam='1group'thenrateelse0end)as'1group',max(casewhenteam='2组'然后将else0结束）作为'2组'，最大（当团队='3组'然后对else0结束的情况）作为'3组'，最大（当团队='4组'时的情况然后评分else0end)as'group4',max(casewhenteam='group5'thenrateelse0end)as'group5'FROMtprj_team_check_rateGROUPBYdatetprj_team_check_rate用于按组和日期存储签到率在select语句，使用rowtocolumn技术使结果在第一列是日期，在后一列是每个组。这是为了方便绘制图表。其实结果可以导入Excel生成报表，比较方便，但我没有这样做，因为：操作Excel比较费力，调试工作量大，我有一个更大的打算，就是最终实现成网络版，不值得花很多时间，所以直接把数据输出到文本文件。例如入住率的输出是这样的：defshow_check_rate():data=db.qj(sql.check_rate_show)result=[]#processingheaderline='\t'.join(data[0].keys())+"\n"result.append(line)#为dindata生成header:row=[]forkind.keys():ifk!='date':row.append(str(d[k])+"%")else:row.append(d[k])line='\t'.join(row)+"\n"result.append(line)result.append('\n')returnresultcheck_rate_show执行Sql获取数据从数据中获取表头信息，做一行记录。请注意，字段由制表符分隔。这是为了方便直接粘贴到Excel中取出每一行数据，做成一个表体数据行最后加一个回车，就是把方法执行的结果和其他输出分开写到文本文件：filename="result_%s.txt"%today.strftime("%Y-%m-%d%H_%M_%S")withopen(filename,'w',encoding='utf-8')asr:r.writelines(show_check_rate())#签到率r.writelines(show_member_score())#会员积分...filename是要写入的文本文件。这里，当前时间用作文件名。为了不重复打开文件，使用writelines方法将返回的行写入文件。这里也可以调用其他输出方法写入结果。在输入文件的最后，文件中的数据如下：date1group2组3组4组5组2021-08-0165%90%79%85%72%2021-08-0275%90%79%85%67%2021-08-0355%90%84%75%67%2021-08-0460%95%74%75%61%在Excel中复制到图表数据将形成入住率图表：入住率图表的日常维护和操作不是静态的，比如为了激励会员提出来整理题目，新增一个点叫解法排序，你要调整点项，因为点项之前已经存在库表中了，现在你只需添加一条记录，并注明该积分适用于会员角色即可。另外，在活动详情报表中，需要根据活动名称记录每个人的数据，这也是一种行列转换的操作，但麻烦的是活动项会发生变化。所以先动态获取获取的item，然后合成成行到列的语句，再和query语句合并成一个完整的Sql语句，这样在activity再次调整的时候，只要添加数据项，代码如下：score_type_temp="max(casewhentype='{atype}'thennumelse0end)as'{atype}'"types=db.query("selecttype,valuefromtbas_scorewheretitle='%s'"%title)temps=[]fortintypes:temps.append(sql.score_type_temp.format(atype=t['type']))allsql=sql.member_score.format(",\n".join(temps))最后将各部分代码整合，放在一个main函数中，每天执行一次，将输出文本文件中的数据复制到Excel中，完成日报表。整个操作不到十分钟，还是比较满意的。总结促使我这样做的原因是我不想把时间花在机械的东西上，所以我会尽量自动处理它们，让程序来处理。虽然让一切程序化是一种理想，但在实现的道路上会遇到很多障碍，所以需要找到落地的平衡点，接受不完美，以实际为导向——先实现，再完善。以上就是本次分享的全部内容。觉得文章还不错的话，请关注公众号：Python编程学习圈，每日干货分享，发送“J”还能收到海量学习资料，涵盖Python电子书和教程，数据库编程、Django、爬虫、云计算等。或者去编程学习网了解更多编程技术知识。
