[python+FastAPI+Sqlite]实现简单的学生成绩管理系统案例

目录

案例需求

1.数据库设计

2. API接口 

实现思路

个人写法案例提供

我的思路

成绩的展示界面

代码参考

function.py

 addstudent.html

add_student_score.html

student_show.tml 

socr_show.html

标准案例代码参考

main.py

index.html

个人的反思和总结

---

案例需求

1.数据库设计

创建学生表(students) :包含id. name、 age、class. _name字段

创建成绩表(scores) :包含id、 student _id、 subject. score、 create _time字段

2. API接口 

 POST /students、添加学生信息

GET /students -获取所有学生列表

POST /scores -添加学生成绩

GET /students/{student_ id}/scores -获取指定学生的所有成绩GET /students/average - 获取所有学生的平均成绩

实现思路

【1】先创建方法

【2】再创建页面

【3】最后创建js实现前后端交互

1.创建app对象

2.创建数据库，表内容

3.创建api接口

4.具体对表操作的方法

5.前端页面

6.js交互

个人写法案例提供

我的思路

对于数据不同的操作来创建不同的页面来实现不同的操作

对学生信息的添加页面

对成绩的插入页面

信息的展示界面

成绩的展示界面

代码参考

function.py

from fastapi import FastAPI, HTTPException
from fastapi.middleware.cors import CORSMiddleware
from pydantic import BaseModel, Field, constr
import sqlite3
from typing import Optional
app = FastAPI()



app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],  # 允许所有源
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)


class ResponseModel(BaseModel):
    name: str
    age: int
    class_name: str
    data: Optional[dict] = None

class ResponseModel2(BaseModel):
    subject: str
    student_id: int
    score: float
    data: Optional[dict] = None

def get_db():
    conn = sqlite3.connect('student_infor.db')
    return conn

def create_table():
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE IF NOT EXISTS students (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL,
                    age INTEGER NOT NULL,
                    class_name TEXT NOT NULL
                )''');
    cursor.execute('''CREATE TABLE IF NOT EXISTS scores (
                            id INTEGER PRIMARY KEY AUTOINCREMENT,
                            student_id INTEGER NOT NULL,
                            subject TEXT NOT NULL,
                            score INTEGER NOT NULL,
                            creat_time TEXT DEFAULT (CURRENT_TIMESTAMP),
                             FOREIGN KEY (student_id) REFERENCES students(id)
            )''')
    conn.commit()
    conn.close()

create_table()

@app.post("/students")
async def create_student(student: ResponseModel):
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('''INSERT INTO students (name, age, class_name) VALUES (?, ?, ?)''',
                   (student.name, student.age, student.class_name))
    conn.commit()
    conn.close()
    return {"message":"添加成功"}
@app.post("/scores")
async def create_score(score: ResponseModel2):
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('''INSERT INTO scores (student_id, subject, score) VALUES (?, ?, ?)''',
                   (score.student_id, score.subject, score.score)
    )
    conn.commit()
    conn.close()
    return {"message": "添加成功"}

@app.get("/students")
async def get_students():
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('''SELECT * FROM students''')
    students = cursor.fetchall()
    conn.close()
    results = [ {"id":student[0],"name": student[1], "age": student[2], "class_name": student[3]} for student in students]
    return results

@app.get("/scores/{student_id}/scores")
async def get_scores(student_id: int):
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('''SELECT students.id, students.name, students.class_name, scores.score 
                      FROM students 
                      INNER JOIN scores ON students.id = scores.student_id 
                      WHERE students.id = ?''', (student_id,))
    scores = cursor.fetchall()
    conn.close()
    return scores

@app.get("/scores/average")
async def get_average_scores():
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('''SELECT student_id, AVG(score) AS average_score FROM scores GROUP BY student_id''')
    average_scores = cursor.fetchall()
    conn.close()
    return average_scores

if __name__ == "__main__":
    import uvicorn
    uvicorn.run(app, host="127.0.0.1", port=8000)

 addstudent.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>学生信息添加表单</title>
</head>
<body>
<div style="width: 500px;height: 100px;display: inline-flex" >
    <div style="width:200px;height: 100px" onclick="window.location.href = 'student_put_score.html'">添加学生成绩</div>
    <div  style="width:200px;height: 100px"onclick="window.location.href = 'student_show.html'">查询所有学生信息</div>
    <div style="width:200px;height: 100px" onclick="window.location.href = 'student_score_show.html'">学生成绩查询表单</div>
</div>
<div>
  姓名 :<input type="text" name="username"></input>
  年龄:<input type="number" name="age"></input>
  课程名:<input type="text" name="class_name"></input>
  <input type="button" value="提交" id="btn" onclick="onclick()"></input>
</div>
<script src="js/axios.min.js"></script>
<script>
  document.getElementById('btn').onclick = function (){
    let usernameInput = document.getElementsByName('username')[0];
    let ageInput = document.getElementsByName('age')[0];
    let classNaemInput = document.getElementsByName('class_name')[0];
    axios({
      url: 'http://127.0.0.1:8000/students',
      method: 'post',
      data:{
        name:usernameInput.value,
        age: ageInput.value,
        class_name: classNaemInput.value
      }
    })
    .then(function (response) {
      console.log(response);
    })
  }
</script>
</body>
</html>

add_student_score.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>成绩录入表单</title>
</head>
<body>
<div style="width: 500px;height: 100px;display: inline-flex" >
    <div style="width:200px;height: 100px" onclick="window.location.href = 'student.html'">添加学生信息</div>
    <div  style="width:200px;height: 100px"onclick="window.location.href = 'student_show.html'">查询所有学生信息</div>
    <div style="width:200px;height: 100px" onclick="window.location.href = 'student_score_show.html'">学生成绩查询表单</div>
</div>
<div>
学生id:<input type="text" name="id"></input>
科目:<input type="text" name="subject" ></input>
成绩:<input type="number" name="score" ></input>
<input type="submit" value="提交" onclick="onclick()" id="btn2"></input>
</div>
<script src="js/axios.min.js"></script>
<script>
  document.getElementById('btn2').onclick = function (){
      let id = document.getElementsByName("id")[0]
      let subject = document.getElementsByName("subject")[0]
      let score = document.getElementsByName("score")[0]
      console.log(id.value,subject.value,score.value)
    axios({
            url:'http://127.0.0.1:8000/scores',
            method:'post',
            data:{
                student_id:id.value,
                subject:subject.value,
                score:score.value
            }
    }).then(function (response) {
      console.log(response.data)
    })
  }
</script>
</body>
</html>

student_show.tml 

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>学生信息展示表单</title>
</head>
<body>
<div style="width: 500px;height: 100px;display: inline-flex" >
    <div style="width:200px;height: 100px" onclick="window.location.href = 'student.html'">添加学生信息</div>
    <div style="width:200px;height: 100px" onclick="window.location.href = 'student_put_score.html'">添加学生成绩</div>
    <div style="width:200px;height: 100px" onclick="window.location.href = 'student_score_show.html'">查询学生成绩</div>
</div>
<div>
    <h2>学生列表</h2>
    <button id="btn">查询所有学生</button>
</div>

<!-- 学生表格 -->
<div id="student-list" style="margin-top: 20px;"></div>

<script src="js/axios.min.js"></script>
<script>
document.getElementById("btn").onclick = function () {
    axios.get('http://127.0.0.1:8000/students')
        .then(function (response) {
            const students = response.data;
            const container = document.getElementById("student-list");

            // 清空旧内容
            container.innerHTML = "";

            if (!students.length) {
                container.innerHTML = "<p>暂无学生信息</p>";
                return;
            }

            // 创建表格
            const table = document.createElement("table");
            table.style.border = "1px solid #ccc";
            table.style.width = "100%";
            table.style.textAlign = "center";

            // 表头
            let headerRow = document.createElement("tr");
            ["ID", "姓名", "年龄", "课程名称"].forEach(text => {
                let th = document.createElement("th");
                th.innerText = text;
                headerRow.appendChild(th);
            });
            table.appendChild(headerRow);

            // 数据行
            students.forEach(student => {
                let row = document.createElement("tr");

                [student.id, student.name, student.age, student.class_name].forEach(text => {
                    let td = document.createElement("td");
                    td.innerText = text;
                    row.appendChild(td);
                });

                table.appendChild(row);
            });

            container.appendChild(table);
        })
        .catch(function (error) {
            console.error("请求失败:", error);
        });
};
</script>

</body>
</html>

socr_show.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>学生成绩表单</title>
</head>
<body>
<div>成绩展示表单</div>
<div style="width: 500px;height: 100px;display: inline-flex" >
    <div  style="width:200px;height: 100px"onclick="window.location.href = 'student.html'">添加学生信息</div>
    <div style="width:200px;height: 100px" onclick="window.location.href = 'student_put_score.html'">添加学生成绩</div>
    <div style="width:200px;height: 100px" onclick="window.location.href = 'student_show.html'">查询学生信息</div>
</div>
<div>
    <input type="text" name="id" placeholder="请输入学生id">
    <input type="text" name="name" placeholder="请输入学生姓名">
    <input type="button" value="查询" onclick="onclick()" id="btn4"></input>
    <div id="score-list"></div>
</div>
<script src="js/axios.min.js"></script>
<script>
    document.getElementById('btn4').onclick = function () {
        // 获取输入框元素并提取值
        let idInput = document.getElementsByName('id')[0];
        let studentId = idInput.value.trim(); // 获取实际输入值

        // 校验是否为空
        if (!studentId) {
            alert("请输入学生ID");
            return;
        }

        // 构造 URL 并替换占位符 {studentId}
        let url = "http://127.0.0.1:8000/scores/{studentId}/scores";
        url = url.replace("{studentId}", encodeURIComponent(studentId));

        // 发起请求
        axios.get(url)
            .then(function (response) {
                console.log("成功获取成绩数据:", response.data);

                const scoreListDiv = document.getElementById("score-list");
                scoreListDiv.innerHTML = ""; // 清空旧内容

                if (response.data.length === 0) {
                    scoreListDiv.innerText = "该学生暂无成绩记录";
                    return;
                }

                response.data.forEach(scores => {
                    const p = document.createElement("p");
                    console.log(scores)
                    p.innerText = `学生姓名:${scores[1]},科目: ${scores[2]}, 分数: ${scores[3]}`;
                    scoreListDiv.appendChild(p);
                });
            })
            .catch(function (error) {
                console.error("请求失败:", error);
                alert("查询失败，请查看控制台日志");
            });
    };
</script>
</body>
</html>

标准案例代码参考

main.py

from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
import sqlite3
from datetime import datetime
from typing import List, Optional
app = FastAPI(title="学生成绩管理系统")
# Pydantic模型
class Student(BaseModel):
name: str
age: int
class_name: str
class Score(BaseModel):
student_id: int
subject: str
score: float
class StudentResponse(BaseModel):
id: int
name: str
age: int
class_name: str
# 数据库初始化
def init_db():
conn = sqlite3.connect('students.db')
cursor = conn.cursor()
# 创建学生表
cursor.execute('''
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER NOT NULL,
class_name TEXT NOT NULL
)
''')
# 创建成绩表
cursor.execute('''
CREATE TABLE IF NOT EXISTS scores (
id INTEGER PRIMARY KEY AUTOINCREMENT,
student_id INTEGER NOT NULL,
subject TEXT NOT NULL,
score REAL NOT NULL,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (student_id) REFERENCES students (id)
)
''')
conn.commit()
conn.close()
# 启动时初始化数据库
init_db()
@app.post("/students")
async def add_student(student: Student):
conn = sqlite3.connect('students.db')
cursor = conn.cursor()
try:
cursor.execute(
"INSERT INTO students (name, age, class_name) VALUES (?, ?, ?)",
(student.name, student.age, student.class_name)
)
conn.commit()
student_id = cursor.lastrowid
return {"id": student_id, "message": "学生添加成功"}
except Exception as e:
raise HTTPException(status_code=500, detail=str(e))
finally:
conn.close()
@app.get("/students", response_model=List[StudentResponse])
async def get_students():
conn = sqlite3.connect('students.db')
cursor = conn.cursor()
try:
cursor.execute("SELECT id, name, age, class_name FROM students")
students = cursor.fetchall()
return [
StudentResponse(id=s[0], name=s[1], age=s[2], class_name=s[3])
for s in students
]
finally:
conn.close()
@app.post("/scores")
async def add_score(score: Score):
conn = sqlite3.connect('students.db')
cursor = conn.cursor()
try:
# 检查学生是否存在
cursor.execute("SELECT id FROM students WHERE id = ?", (score.student_id,))
if not cursor.fetchone():
raise HTTPException(status_code=404, detail="学生不存在")
cursor.execute(
"INSERT INTO scores (student_id, subject, score) VALUES (?, ?, ?)",
(score.student_id, score.subject, score.score)
)
conn.commit()
return {"message": "成绩添加成功"}
except HTTPException:
raise
except Exception as e:
raise HTTPException(status_code=500, detail=str(e))
finally:
conn.close()
@app.get("/students/{student_id}/scores")
async def get_student_scores(student_id: int):
conn = sqlite3.connect('students.db')
前端代码示例（index.html）- 使用 Axios 版本
cursor = conn.cursor()
try:
cursor.execute("""
SELECT s.name, sc.subject, sc.score, sc.create_time
FROM students s
JOIN scores sc ON s.id = sc.student_id
WHERE s.id = ?
""", (student_id,))
scores = cursor.fetchall()
if not scores:
raise HTTPException(status_code=404, detail="未找到该学生的成绩")
return {
"student_name": scores[0][0],
"scores": [
{"subject": score[1], "score": score[2], "create_time": score[3]}
for score in scores
]
}
finally:
conn.close()
@app.get("/students/average")
async def get_average_scores():
conn = sqlite3.connect('students.db')
cursor = conn.cursor()
try:
cursor.execute("""
SELECT s.name, AVG(sc.score) as avg_score
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id
GROUP BY s.id, s.name
""")
averages = cursor.fetchall()
return [
{"student_name": avg[0], "average_score": round(avg[1] or 0, 2)}
for avg in averages
]
finally:
conn.close()
if __name__ == "__main__":
import uvicorn
uvicorn.run(app, host="0.0.0.0", port=8000)

index.html

<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>学生成绩管理系统</title>
<style>
body {
font-family: Arial, sans-serif;
max-width: 1200px;
margin: 0 auto;
padding: 20px;
background-color: #f5f5f5;
}
.container {
background: white;
padding: 20px;
border-radius: 8px;
box-shadow: 0 2px 10px rgba(0, 0, 0, 0.1);
margin-bottom: 20px;
}
h1,
h2 {
color: #333;
border-bottom: 2px solid #007bff;
padding-bottom: 10px;
}
.form-group {
margin-bottom: 15px;
}
label {
display: block;
margin-bottom: 5px;
font-weight: bold;
color: #555;
}
input,
select,
button {
width: 100%;
padding: 10px;
border: 1px solid #ddd;
border-radius: 4px;
font-size: 14px;
}
button {
background-color: #007bff;
color: white;
border: none;
cursor: pointer;
transition: background-color 0.3s;
}
button:hover {
background-color: #0056b3;
}
.students-list {
display: grid;
grid-template-columns: repeat(auto-fill, minmax(300px, 1fr));
gap: 15px;
}
.student-card {
background: #f8f9fa;
padding: 15px;
border-radius: 6px;
border-left: 4px solid #007bff;
}
.message {
padding: 10px;
margin: 10px 0;
border-radius: 4px;
}
.success {
background-color: #d4edda;
color: #155724;
border: 1px solid #c3e6cb;
}
.error {
background-color: #f8d7da;
color: #721c24;
border: 1px solid #f5c6cb;
}
</style>
</head>
<body>
<h1>学生成绩管理系统</h1>
<!-- 学生信息录入 -->
<div class="container">
<h2>添加学生</h2>
<form id="studentForm">
<div class="form-group">
<label for="studentName">姓名：</label>
<input type="text" id="studentName" required />
</div>
<div class="form-group">
<label for="studentAge">年龄：</label>
<input type="number" id="studentAge" required />
</div>
<div class="form-group">
<label for="studentClass">班级：</label>
<input type="text" id="studentClass" required />
</div>
<button type="submit">添加学生</button>
</form>
</div>
<!-- 成绩录入 -->
<div class="container">
<h2>添加成绩</h2>
<form id="scoreForm">
<div class="form-group">
<label for="studentSelect">选择学生：</label>
<select id="studentSelect" required>
<option value="">请选择学生</option>
</select>
</div>
<div class="form-group">
<label for="subject">科目：</label>
<input type="text" id="subject" required />
</div>
<div class="form-group">
<label for="score">成绩：</label>
<input
type="number"
id="score"
min="0"
max="100"
step="0.1"
required
/>
</div>
<button type="submit">添加成绩</button>
</form>
</div>
<!-- 学生列表和成绩展示 -->
<div class="container">
<h2>学生列表</h2>
<div id="studentsList" class="students-list"></div>
</div>
<!-- 引入axios库 -->
<script src="https://cdn.jsdelivr.net/npm/axios@1.6.0/dist/axios.min.js"></script>
<script>
// 配置axios基础URL和默认headers
axios.defaults.baseURL = "http://localhost:8000";
axios.defaults.headers.post["Content-Type"] = "application/json";
// 添加请求拦截器
axios.interceptors.request.use(
(config) => {
console.log("发送请求:", config.method.toUpperCase(), config.url);
return config;
},
(error) => {
return Promise.reject(error);
}
);
// 添加响应拦截器
axios.interceptors.response.use(
(response) => {
console.log("响应成功:", response.status, response.config.url);
return response;
},
(error) => {
console.error("响应错误:", error.response?.status, error.config?.url);
return Promise.reject(error);
}
);
// 显示消息
function showMessage(message, type = "success") {
const messageDiv = document.createElement("div");
messageDiv.className = `message ${type}`;
messageDiv.textContent = message;
document.body.insertBefore(messageDiv, document.body.firstChild);
setTimeout(() => {
messageDiv.remove();
}, 3000);
}
// 加载学生列表
async function loadStudents() {
try {
const response = await axios.get("/students");
const students = response.data;
// 更新学生选择下拉框
const select = document.getElementById("studentSelect");
select.innerHTML = '<option value="">请选择学生</option>';
// 更新学生列表显示
const listDiv = document.getElementById("studentsList");
listDiv.innerHTML = "";
for (const student of students) {
// 添加到下拉框
const option = document.createElement("option");
option.value = student.id;
option.textContent = `${student.name} (${student.class_name})`;
select.appendChild(option);
// 获取学生成绩
try {
const scoresResponse = await axios.get(
`/students/${student.id}/scores`
);
let scoresHtml = "<p>暂无成绩</p>";
if (scoresResponse.status === 200) {
const scoresData = scoresResponse.data;
scoresHtml = scoresData.scores
.map(
(score) => `<div>${score.subject}: ${score.score}分</div>`
)
.join("");
}
// 创建学生卡片
const studentCard = document.createElement("div");
studentCard.className = "student-card";
studentCard.innerHTML = `
<h3>${student.name}</h3>
<p>年龄: ${student.age} | 班级: ${student.class_name}</p>
<div><strong>成绩:</strong></div>
${scoresHtml}
`;
listDiv.appendChild(studentCard);
} catch (error) {
// 如果是404错误（没有成绩），显示暂无成绩
if (error.response && error.response.status === 404) {
const studentCard = document.createElement("div");
studentCard.className = "student-card";
studentCard.innerHTML = `
<h3>${student.name}</h3>
<p>年龄: ${student.age} | 班级: ${student.class_name}
</p>
<div><strong>成绩:</strong></div>
<p>暂无成绩</p>
`;
listDiv.appendChild(studentCard);
} else {
console.error("获取成绩失败:", error);
}
}
}
} catch (error) {
showMessage("加载学生列表失败", "error");
console.error("Error:", error);
}
}
// 添加学生
document
.getElementById("studentForm")
.addEventListener("submit", async (e) => {
e.preventDefault();
const studentData = {
name: document.getElementById("studentName").value,
age: parseInt(document.getElementById("studentAge").value),
class_name: document.getElementById("studentClass").value,
};
try {
const response = await axios.post("/students", studentData);
if (response.status === 200) {
showMessage("学生添加成功");
document.getElementById("studentForm").reset();
loadStudents();
}
} catch (error) {
showMessage("添加学生失败", "error");
console.error("Error:", error);
}
});
// 添加成绩
document
.getElementById("scoreForm")
.addEventListener("submit", async (e) => {
e.preventDefault();
const scoreData = {
student_id: parseInt(
document.getElementById("studentSelect").value
),
subject: document.getElementById("subject").value,
score: parseFloat(document.getElementById("score").value),
};
try {
const response = await axios.post("/scores", scoreData);
if (response.status === 200) {
showMessage("成绩添加成功");
document.getElementById("scoreForm").reset();
loadStudents();
}
} catch (error) {
if (error.response && error.response.status === 404) {
showMessage("学生不存在", "error");
} else {
showMessage("添加成绩失败", "error");
}
console.error("Error:", error);
}
});
// 页面加载时获取学生列表
loadStudents();
</script>
</body>
</html>

个人的反思和总结

编程思路决定工作量，好的编程思路能帮忙节省大量的时间，同时也会解决掉一些不需要出现和繁杂的小问题，在代码的检查和测试的时候也会减少一些不必要的麻烦问题。

我的个人代码看似很多，很全。实际上要等运行的时候会发现一些问题，比如页面的设计很难看，这都算是小问题，还有些隐藏的问题可能是我还没有发现。总之加油吧，共进勉。