基于Python+Flask+MySQL的图书馆管理系统

基于Python+Flask+MySQL的图书馆管理系统

分管理端和用户端，带用户登录、注册，基本的图书信息的增加、修改、删除、查询等功能，还有可视化功能和用户申请采购模块

以下是一个基于 Python Flask 和 MySQL 的图书馆管理系统的完整代码示例。该系统包括以下功能模块：

1. 用户管理：分为管理员和普通用户两种角色。
2. 书籍管理：管理员可以添加、编辑和删除书籍。
3. 借阅管理：用户可以借阅和归还书籍，管理员可以查看所有借阅记录。
4. 搜索功能：用户可以通过书名或作者搜索书籍。

---

1. 环境准备

确保安装了以下工具：

• Python 3.x
• Flask (pip install flask)
• Flask-MySQLdb (pip install flask-mysqldb)
• MySQL 数据库

---

2. 创建 MySQL 数据库

在 MySQL 中创建一个名为 library 的数据库，并运行以下 SQL 脚本初始化表结构：

CREATE DATABASE library;

USE library;

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    quantity INT DEFAULT 0
);

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    is_admin BOOLEAN DEFAULT FALSE
);

CREATE TABLE borrow_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    book_id INT NOT NULL,
    borrow_date DATE NOT NULL,
    return_date DATE DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);

—

3. 项目结构

library_management/
├── app.py
├── templates/
│   ├── base.html
│   ├── login.html
│   ├── home.html
│   ├── add_book.html
│   ├── borrow_book.html
│   └── search_books.html
└── static/
    └── style.css

---

4. 主程序 app.py

from flask import Flask, render_template, request, redirect, url_for, flash, session
from flask_mysqldb import MySQL
import MySQLdb.cursors

app = Flask(__name__)
app.secret_key = 'your_secret_key'

# MySQL 配置
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = 'your_password'
app.config['MYSQL_DB'] = 'library'
mysql = MySQL(app)

# 登录页面
@app.route('/', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        cursor.execute('SELECT * FROM users WHERE username = %s AND password = %s', (username, password))
        user = cursor.fetchone()
        if user:
            session['loggedin'] = True
            session['id'] = user['id']
            session['username'] = user['username']
            session['is_admin'] = user['is_admin']
            return redirect(url_for('home'))
        else:
            flash('Invalid credentials!')
    return render_template('login.html')

# 主页
@app.route('/home')
def home():
    if 'loggedin' in session:
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        cursor.execute('SELECT * FROM books')
        books = cursor.fetchall()
        return render_template('home.html', books=books)
    return redirect(url_for('login'))

# 添加书籍（管理员）
@app.route('/add_book', methods=['GET', 'POST'])
def add_book():
    if 'loggedin' in session and session['is_admin']:
        if request.method == 'POST':
            title = request.form['title']
            author = request.form['author']
            quantity = request.form['quantity']
            cursor = mysql.connection.cursor()
            cursor.execute('INSERT INTO books (title, author, quantity) VALUES (%s, %s, %s)', (title, author, quantity))
            mysql.connection.commit()
            flash('Book added successfully!')
            return redirect(url_for('home'))
        return render_template('add_book.html')
    return redirect(url_for('login'))

# 借阅书籍
@app.route('/borrow/<int:book_id>', methods=['POST'])
def borrow_book(book_id):
    if 'loggedin' in session:
        cursor = mysql.connection.cursor()
        cursor.execute('SELECT quantity FROM books WHERE id = %s', (book_id,))
        book = cursor.fetchone()
        if book and book[0] > 0:
            cursor.execute('INSERT INTO borrow_records (user_id, book_id, borrow_date) VALUES (%s, %s, CURDATE())',
                           (session['id'], book_id))
            cursor.execute('UPDATE books SET quantity = quantity - 1 WHERE id = %s', (book_id,))
            mysql.connection.commit()
            flash('Book borrowed successfully!')
        else:
            flash('Book not available!')
        return redirect(url_for('home'))
    return redirect(url_for('login'))

# 归还书籍
@app.route('/return/<int:record_id>', methods=['POST'])
def return_book(record_id):
    if 'loggedin' in session:
        cursor = mysql.connection.cursor()
        cursor.execute('UPDATE borrow_records SET return_date = CURDATE() WHERE id = %s', (record_id,))
        cursor.execute('UPDATE books SET quantity = quantity + 1 WHERE id = (SELECT book_id FROM borrow_records WHERE id = %s)',
                       (record_id,))
        mysql.connection.commit()
        flash('Book returned successfully!')
        return redirect(url_for('home'))
    return redirect(url_for('login'))

# 搜索书籍
@app.route('/search', methods=['GET', 'POST'])
def search_books():
    if 'loggedin' in session:
        if request.method == 'POST':
            query = request.form['query']
            cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
            cursor.execute('SELECT * FROM books WHERE title LIKE %s OR author LIKE %s', (f'%{query}%', f'%{query}%'))
            books = cursor.fetchall()
            return render_template('search_books.html', books=books)
        return render_template('search_books.html', books=[])
    return redirect(url_for('login'))

# 注销
@app.route('/logout')
def logout():
    session.pop('loggedin', None)
    session.pop('id', None)
    session.pop('username', None)
    session.pop('is_admin', None)
    return redirect(url_for('login'))

if __name__ == '__main__':
    app.run(debug=True)

---

5. HTML 模板

templates/base.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Library Management System</title>
    <link rel="stylesheet" href="{{ url_for('static', filename='style.css') }}">
</head>
<body>
    <nav>
        <a href="{{ url_for('home') }}">Home</a>
        {% if session.get('is_admin') %}
            <a href="{{ url_for('add_book') }}">Add Book</a>
        {% endif %}
        <a href="{{ url_for('search_books') }}">Search Books</a>
        <a href="{{ url_for('logout') }}">Logout</a>
    </nav>
    <div class="container">
        {% with messages = get_flashed_messages() %}
            {% if messages %}
                <ul class="messages">
                    {% for message in messages %}
                        <li>{{ message }}</li>
                    {% endfor %}
                </ul>
            {% endif %}
        {% endwith %}
        {% block content %}{% endblock %}
    </div>
</body>
</html>

templates/login.html

{% extends "base.html" %}
{% block content %}
<h2>Login</h2>
<form method="post">
    <input type="text" name="username" placeholder="Username" required>
    <input type="password" name="password" placeholder="Password" required>
    <button type="submit">Login</button>
</form>
{% endblock %}

templates/home.html

{% extends "base.html" %}
{% block content %}
<h2>Books</h2>
<ul>
    {% for book in books %}
        <li>{{ book.title }} by {{ book.author }} ({{ book.quantity }} available)
            {% if not session.get('is_admin') %}
                <form action="{{ url_for('borrow_book', book_id=book.id) }}" method="post" style="display:inline;">
                    <button type="submit">Borrow</button>
                </form>
            {% endif %}
        </li>
    {% endfor %}
</ul>
{% endblock %}

---

6. 运行项目

启动应用：

python app.py