今日实现：学生名单导入和登录判定
名单导入功能的基本实现：
upload.html
<html lang="en"><head> <meta charset="UTF-8"> <title>文件上传title>head><body><h1>导入EXCEL文件h1><form action="" method="post" enctype="multipart/form-data"> <input type="file" name="file"> <input type="submit" value="上传">form>body>html>server.py对应的路由:
@app.route('/upload',methods=['GET','POST'])def upload(): if request.method=='POST': f=request.files['file'] base_path = os.path.abspath(os.path.dirname(__file__)) upload_path = os.path.join(base_path,r'static\uploads',secure_filename(f.filename)) f.save(upload_path) xls_file = r".\static\uploads\students.xls" book = xlrd.open_workbook(xls_file) for sheet in book.sheets(): excel2db.read_excel(sheet) return "文件上传导入成功！" return render_template("upload.html")excel2db.py导入数据模块：
import sqlite3import typesdef read_excel(sheet): # 判断有效sheet if sheet.nrows > 0 and sheet.ncols > 0: for row in range(1, sheet.nrows): row_data = [] for col in range(sheet.ncols): data = sheet.cell(row, col).value row_data.append(data) insert_sqlite(row_data)def insert_sqlite(row_data): # 打开数据库(不存在时会创建数据库) con = sqlite3.connect("./DB/stuScores.db") cur = con.cursor() try: cur.execute("create table if not exists 学生(_id integer primary key autoincrement,学号 varchar(50),密码 varchar(50),姓名 text,班级 integer,分组 integer)") # 插入数据不要使用拼接字符串的方式，容易收到sql注入攻击 cur.execute("select * from 学生 where 学号='%s'" % row_data[0]) if len(cur.fetchall())<=0: cur.execute("insert into 学生(学号,密码,姓名,班级,分组) values(?,?,?,?,?)", row_data) con.commit() except sqlite3.Error as e: print("导入失败，请检查格式: %s", e.args[0]) finally: cur.close con.close这里用了xlwt操作excel，更精简写法，可以换用openpyxl或pandas。
login.html学生登录：
<html lang="en"><head> <meta charset="UTF-8"> <title>学生登录页面title>head><body> <h2>请用学号登录网站h2> <form action='login' method="POST"> <table> <tr> <td>学号：td> <td><input type="text" name='username' >td> tr> <tr> <td>密码：td> <td><input type="password" name='password' >td> tr> <tr> <td><input type="submit" value="登陆">td> tr> table> form>body>html>Server.py对应的路由:
@app.route('/login',methods=['POST','GET'])def login(): if request.method == 'POST': user = request.form.get('username') pwd = request.form.get('password') con = sqlite3.connect("./DB/stuScores.db") cur = con.cursor() cur.execute("select * from 学生 where 学号='%s' and 密码='%s'" % (user,pwd)) r = cur.fetchall() if len(r)>0: return "登录成功：你好， "+str(r[0][3]) else: return "登录失败,学号不存在或密码错误！" return render_template("login.html")效果如下：
当然，这真的只是基本实现，至于登录后的跳转和登录提示的Ajax提示我们将来再修复。
