6.数据库练习之简陋图书馆练习

这是一个使用Python Flask框架和SQLite数据库实现的图书管理系统。系统包括增删作者和书籍的功能，通过HTML模板展示数据，并使用了Flask-SQLAlchemy和Flask-WTF进行数据操作和表单验证。用户可以添加新作者和书籍，也可删除作者及其关联的书籍。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

练习

hello.py

'''
图书馆项目查询
'''
from flask import Flask,render_template,redirect,flash,request
from flask_sqlalchemy import SQLAlchemy
from flask_wtf.csrf import CSRFProtect


app = Flask(__name__)


#配置数据库
app.config['SQLALCHEMY_DATABASE_URI'] = "mysql+pymysql://root:root@127.0.0.1:3306/library"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SECRET_KEY'] = 'hard to guess the string'

CSRFProtect(app)

# 创建SQLAlchemy对象，关联APP
db = SQLAlchemy(app)



#创建模型类
#作者
class Author(db.Model):
    __tablename__ = "authors"
    id = db.Column(db.Integer,primary_key=True)
    name = db.Column(db.String(32))
    #关系
    books = db.relationship("Book",backref="author")



#书籍
class Book(db.Model):
    __tablename__ = "books"
    id = db.Column(db.Integer,primary_key=True)
    name = db.Column(db.String(32))

    #外键
    author_id = db.Column(db.Integer,db.ForeignKey(Author.id))
    #author_id = db.Column(db.Integer,db.ForeignKey("authors.id"))






@app.route('/')
def show_index():
    authors = Author.query.all()
    return render_template("library.html",authors=authors)

@app.route('/add_data',methods=["POST"])
def add_data():
    author_name = request.form.get('author')
    book_name = request.form.get('book')

    author = Author.query.filter(Author.name == author_name).first()
    
    if not all([author_name,book_name]):
        flash('书籍和作者不能为空')
        return redirect("/")

    if author:
        book = Book.query.filter(Book.name == book_name,Book.author_id == author.id)
        if book:
            flash("该作者已经写过该书了，请重新填写")
        else:
            db.session.add(Book(name=book_name,author_id=author.id))
            db.session.commit()

    else:
        author = Author(name=author_name)
        db.session.add(author)
        db.session.commit()
        db.session.add(Book(name=book_name,author_id=author.id))
        db.session.commit()
    return redirect('/')

@app.route('/delete_book/<string:book_name>')
def delete_book(book_name):
    book = Book.query.filter(Book.name == book_name).first()
    db.session.delete(book)
    db.session.commit()
    return redirect('/')

@app.route('/delete_author/<int:author_id>')
def delete_author(author_id):
    author = Author.query.get(author_id)
    for book in author.books:
        db.session.delete(book)
    db.session.delete(author)
    db.session.commit()
    return redirect('/')



if __name__ == "__main__":
    #演示
    db.drop_all()
    db.create_all()

    #添加测试数据
    au1 = Author(name="老王")
    au2 = Author(name="老尹")
    au3 = Author(name="老刘")

    db.session.add_all([au1,au2,au3])
    db.session.commit()

    bk1 = Book(name="老王回忆录",author_id=au1.id)
    bk2 = Book(name="我读书少，别骗我",author_id=au1.id)
    bk3 = Book(name="让自己更强",author_id=au2.id)
    bk4 = Book(name="征服1",author_id=au3.id)
    bk5 = Book(name="征服2",author_id=au3.id)

    db.session.add_all([bk1,bk2,bk3,bk4,bk5])
    db.session.commit()


    app.run(debug=True)

templates/library.html

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<title>Library</title>
</head>
<body>
	<form action="/add_data" method="post">
		<input type="hidden" name="csrf_token" value="{{ csrf_token() }}">
		作者：<input type="text" name="author"><br/>
		书籍：<input type="text" name="book"><br/>
		<input type="submit" value="添加"><br/>
		{% for message in get_flashed_messages() %}
		<span style="color:red;">{{ message }}</span>
		{% endfor %}
	</form>
<hr>

<!--  数据展示  -->
<ul>
	{% for author in authors %}
	<li>作者： {{ author.name }}  <a href="/delete_author/{{ author.id }}">删除</a> </li>
	    <ul>
	    {% for book in author.books %}
	    <li>书籍： {{ book.name }} <a href="/delete_book/{{ book.name }}">删除</a> </li>
		{% endfor %}
		</ul>
	{% endfor %}

</ul>

</body>
</html>