我用的是 Python3:
import os
import sqlite3
__ROOT__ = "F:/PythonFiels/"
if not os.path.exists(__ROOT__):
os.mkdir(__ROOT__)
__LINK__ = sqlite3.connect(os.path.join(__ROOT__, 'datas.db'))
__CURSOR__ = __LINK__.cursor()
def students():
students = []
for i in range(1, 31):
students.append(
{
'学号' : i,
'姓名' : '李荣浩',
'性别' : '男',
'年龄' : 34,
}
)
return students
def init():
try:
createTable()
except:
pass
for student in students():
studentPath = os.path.join(__ROOT__, str(student['学号']))
os.mkdir(studentPath)
studentAvatar = os.path.join(studentPath, '{}.jpg'.format(student['学号']))
with open(studentAvatar, 'wb') as f:
pass
studentExcel = os.path.join(studentPath, "{}.csv".format(student['姓名']))
with open(studentExcel, 'w') as f:
f.write("姓名,性别,年龄,照片\n")
f.write("{},{},{},{}".format(
student['姓名'], student['性别'], student['年龄'], '{}.jpg'.format(student['学号'])
))
sql = "INSERT INTO students (id,name,gender,age,avatar) VALUES ('{}','{}','{}','{}','{}')".format(
student['学号'], student['姓名'], student['性别'], student['年龄'], '{}.jpg'.format(student['学号'])
)
if __CURSOR__.execute(sql):
__LINK__.commit()
print('一条数据插入成功\n')
else:
print('一条数据插入失败\n')
return menu()
def createTable(tablename='students'):
sql = """CREATE TABLE {}
(id INT(6) PRIMARY KEY NOT NULL,
name VARCHAR(10) NOT NULL,
age VARCAHR(10) NOT NULL,
gender VARCHAR(10) NOT NULL,
avatar VARCHAR(10) NOT NULL)""".format(tablename)
return __CURSOR__.execute(sql)
def read():
print('所有数据列表:')
for value in all():
print(value)
return menu()
def all():
sql = "SELECT * FROM students"
r = __CURSOR__.execute(sql)
return r
def info():
sql = "SELECT count(*) FROM students"
r = __CURSOR__.execute(sql)
number = 0
age = 0
for value in r:
number = value[0]
sql = "SELECT avg(age) FROM students"
r = __CURSOR__.execute(sql)
for value in r:
age = value[0]
print('学生共有 {} 人, 年龄平均数为 : {}'.format(number, age))
menu()
def update():
for value in all():
age = value[2] + 1
id = value[0]
sql = "UPDATE students SET age = '{}' WHERE id = {}".format(age, id)
__CURSOR__.execute(sql)
__LINK__.commit()
print('更新完毕!')
return menu()
def action(id):
id = str(id)
if id == '1':
return init()
elif id == '2':
return read()
elif id == '3':
return info()
elif id == '4':
return update()
else:
print('请对照选项进行选择!')
return menu()
def menu():
id = input("请选择 : 1.初始化数据 2.读取所有数据(请先初始化数据) 3.显示学生数量和平均年龄(请先初始化数据) 4.每人年龄+1(请先初始化数据)");
action(id)
menu()