当多人同时对一个表格进行操作时,同步协作很重要。以往的做法是每人一张表格进行处理,然后再手工汇总得到最后结果。这种做法效率极低,不但有很多重复性工作,而且容易出错。另一种比较好的的方法是,每个人都可以从服务器下载表格,然后各自处理,再上传到服务器,让服务器进行自动处理,简称为数据处理的同步协作。下面以某学校班级学生成绩的数据处理为例,详细介绍这种思路的实现方法:
假设某班级需要多人同时处理成绩表,成绩表有“姓名”、“姓别”、“班级”、“语文”、“数学”、“英语”、“总分”等栏目。服务器中已安装MYSQL server,服务器的地址是“192.168.0.2”,服务器数据库的帐号为“root”,登录密码为“123456”,数据库名为“mysql”,表名为“学生成绩册”,里面设“姓名”、“姓别”、“班级”、“语文”、“数学”、“英语”、“总分”等字段。本地已建有空的名称为《学生成绩册》的excel表,有“状态”、“姓名”、“姓别”、“班级”、“语文”、“数学”、“英语”、“总分”等栏,该excel表比数据库中的表多出了“状态”一栏很关键,共可设“留”、“删”、“增”、“改”等四种状态,服务器可以根据不同的内容对服务器的数据进行不同的操作,下文还将详细介绍。
程序的使用简单,运行后将出现两个选择,按数字“1”表示将服务器数据下载到本地,覆盖本地的excel表数据,并将excel表中所有数据的“状态”设置为“存”,这样操作者如果不修改数据的“状态”为其他,之前的数据再上传到服务器时,不会影响服务器原有的数据;如果操作者发现数据有修改或增减,可直接在本地的excel中进行,然后在相应数据行设置为“增”、“删”、“改”,再重新运行该程序,选择数字“2”,就可以将本地excel表《学生成绩册》的数据同步到服务器数据库,下次再从服务器数据库下载的内容就是最新的了。这个过程可以多人同时在多台可以访问服务器的电脑上操作,尤其是在同一局域网内。
本程序用python语言编写,只涉及MYSQL数据库与excel文件两个基本操作,简单易学。为方便理解,该程序省略了许多可能发生情况的处理,并对重要代码作了详细的注解。在具体编程或操作中有需要沟通共同学习的,
import pymysql
import pandas as pd
#打开数据库连接,192.168.0.2是服务器地址,root是进入mysql的帐号
#123456是进入mysql密码,mysql是数据库名
#表名是学生成绩册,内有:姓名、性别、班级、语文、数学、英语、总分等列
#如果不写localhost而用实际ip地址,必须修改mysql中的user表,将root对应的host改为%
#本程序的思路:通过本地的excel文件(学生成绩册.xls)与服务器的mysql通信
#excel表中第一列的“状态”(增、删、改,空代表不变)决定服务器的mysql操作
#本程序暂只适用xls版本,新的xlsx是否可用还有待验证
print('温馨提示:本程序实现远程mysql服务与本地excel操作数据同步')
print('1(下载): 表示将服务器数据下载到本地')
print('2(上传): 表示将本地数据上传到服务器')
ch=input('请选择:')
print()
#df1=pd.dataframe()
df0 = pd.DataFrame(columns=['状态','姓名','性别','班级','语文','数学','英语','总分'])
df = pd.read_excel("学生成绩册.xls",sheet_name='Sheet1')
nrows=df.shape[0] #获取最大行数
ncols=df.columns.size #获取最大列数
db = pymysql.connect("192.168.0.2","root","123456","mysql" )
cursor = db.cursor() # 使用cursor()方法获取操作游标
if ch=='1': #数据下载
sql ='select * from 学生成绩册'
try:
cursor.execute(sql) # 执行SQL语句
results = cursor.fetchall() # 获取所有记录列表,fetchall接收全部的返回结果行.
except:
input ("Error: unable to fetch data,按任一键继续......")
print('开始下载sql服务器数据到本地......')
print()
nn=1
for row in results:
fname = row[0]
fage = row[1]
fgroup = row[2]
fyuwen = row[3]
fshuxue = row[4]
fyingyu =row[5]
fzongfen=row[6]
df0.loc[nn]=['留',fname,fage,fgroup,fyuwen,fshuxue,fyingyu,fzongfen]
nn+=1
df0=df0.sort_values(by="班级",ascending=True)
print('以下是sql服务器中表的内容:')
print('---------------------------')
print(df0)
df0.to_excel("学生成绩册.xls", sheet_name='Sheet1', index=False) #写入原始数据
if ch=='2': #数据上传
print('现在将本地excel表数据上传到sql服务器......')
print('------------------------------------------')
for ii in range(nrows):
if df.iloc[ii,0]=='留':
print('行数:',ii,'保持不变')
if df.iloc[ii,0]=='增':
print('行数:',ii,'添加记录')
df.iloc[ii,7]=df.iloc[ii,4]+df.iloc[ii,5]+df.iloc[ii,6]
sql="insert into 学生成绩册 values(\'"+df.iloc[ii,1]+"\',\'"+df.iloc[ii,2]+\
"\',\'"+df.iloc[ii,3]+"\',"+str(df.iloc[ii,4])+","+str(df.iloc[ii,5])+\
","+str(df.iloc[ii,6])+","+str(df.iloc[ii,7])+")"
try:
cursor.execute(sql) # 执行SQL语句
db.commit()
except:
input ("Error: 不能插入记录,请检查后按任一键继续......")
if df.iloc[ii,0]=='删':
print('行数:',ii,'删除记录')
sql="delete from 学生成绩册 where 姓名=\'"+df.iloc[ii,1]+"\'"
try:
cursor.execute(sql) # 执行SQL语句
db.commit()
except:
input ("Error: 不能删除记录,请检查后按任一键继续......")
if df.iloc[ii,0]=='改':
print('行数:',ii,'修改记录')
df.iloc[ii,7]=df.iloc[ii,4]+df.iloc[ii,5]+df.iloc[ii,6]
sql="update 学生成绩册 set 性别=\'"+df.iloc[ii,2]+"\',"+\
"班级=\'"+df.iloc[ii,3]+"\',"+"语文="+str(df.iloc[ii,4])+\
",数学="+str(df.iloc[ii,5])+",英语="+str(df.iloc[ii,6])+",总分="+str(df.iloc[ii,7])+\
" where 姓名=\'"+df.iloc[ii,1]+"\'"
try:
cursor.execute(sql) # 执行SQL语句
db.commit()
except:
input ("Error: 不能修改记录,请检查后按任一键继续......")
sql ='select * from 学生成绩册 order by 班级'
try:
cursor.execute(sql) # 执行SQL语句
results = cursor.fetchall() # 获取所有记录列表,fetchall接收全部的返回结果行.
except:
input ("Error: unable to fetch data,按任一键继续......")
print()
print('以下为sql数据器的最新数据:')
print('--------------------------')
for row in results:
fname = row[0]
fage = row[1]
fgroup = row[2]
fyuwen = row[3]
fshuxue = row[4]
fyingyu =row[5]
fzongfen=row[6]
print(fname,fage,fgroup,fyuwen,fshuxue,fyingyu,fzongfen)
print()
input('按任一键结束程序......')
db.close() # 关闭数据库连接