方法一:主要使用sql语句[不易查资料]
# -*- coding: utf-8 -*-
from flask import Flask,render_template,flash,request,redirect,url_for
from flask_sqlalchemy import SQLAlchemy
import pymysql
from flask_wtf import FlaskForm
from wtforms import StringField,SubmitField
from wtforms.validators import DataRequired
app=Flask(__name__)
app.secret_key='itheima'
#创建数据库对象,获取游标
db = pymysql.connect('localhost', user="root", passwd="5366892", db='flask_books',use_unicode=True, charset="utf8",cursorclass=pymysql.cursors.DictCursor)
cursor=db.cursor()
#定义书和作者模型
cursor.execute('drop table if exists books')#先删除带有外键的
cursor.execute('drop table if exists authors')
sql="""CREATE TABLE IF NOT EXISTS `authors` (
`id` int(11) AUTO_INCREMENT,
`name` VARCHAR(16),
PRIMARY KEY (`id`)
) ENGINE=InnoDB"""
cursor.execute(sql)
sql="""CREATE TABLE IF NOT EXISTS `books` (
`id` int(11) AUTO_INCREMENT primary key,
`name` VARCHAR(16),
`pid` int(11) ,
FOREIGN KEY (`pid`) REFERENCES authors(`id`)
) ENGINE=InnoDB """
cursor.execute(sql)
cursor.close()
#db.close()
#自定义表单类
class AuthorForm(FlaskForm):
author= StringField('作者',validators=[DataRequired()])
book=StringField('书籍',validators=[DataRequired()])
submit = SubmitField('提交')
#网页中删除->点击需要发送书籍的ID给删除书籍的路由->路由需要接受参数
@app.route('/delete_book/<book_id>')
def delete_book(book_id):
#1查询数据库,是否有该ID的书,如果有就删除,没有就提示错误
cursor = db.cursor()
sql_id = "select id from books where id='%s'"#混乱
cursor.execute(sql_id % book_id)
cunzai_id = cursor.fetchall()
#2如果有就删除
if len(cunzai_id):
try:
sql_id = "delete from books where id='%s'" # 混乱
cursor.execute(sql_id % book_id)
except Exception as e:
flash('删除书籍出错')
else:
flash('书籍找不到')
#3没有就提示错误
#如何返回当前网址->重定向
#redirect:重定向,需要传入网络/路由地址
#url_for('index'):需要传入视图函数名,返回该试图函数对应的路由地址
return redirect(url_for('index'))
@app.route('/',methods=['GET','POST'])
def index():
#创建自定义的表单类
#
author_form=AuthorForm()
#1调用WTF的函数实现验证
if author_form.validate_on_submit():
#2验证通过的数据
author_name=author_form.author.data
book_name=author_form.book.data
#3查询作者是否存在
cursor = db.cursor()
#print(author_name)
##传参!
sql_talk = "select name from authors where name='%s'"
cursor.execute(sql_talk % author_name)
cunzai=cursor.fetchall()
#print(cunzai)
#如果作者存在
if len(cunzai):
#判断书籍是否存在
sql_book = "select name from books where name='%s'"
cursor.execute(sql_book % book_name)
cunzai_book=cursor.fetchall()
if len(cunzai_book) :
flash('已存在同名书籍')
else:
try:
#没有存在同名书籍,获得作者ID,提交作者ID和书名
add_book_name=cunzai[0]['name']
#print(name)
sql_id="select id from authors where name='%s'"
cursor.execute(sql_id % add_book_name)
add_book_name= cursor.fetchall()#获得的作者的ID
add_book_name_id=add_book_name[0]['id']
#print(book_name,add_book_name_id)
#cursor.execute('INSERT INTO books(name,pid) VALUES({0},{1})'.format(book_name,add_book_name_id))
cursor.execute('insert into books(name,pid) values(%s,%s)',(book_name,add_book_name_id))
db.commit()
except Exception as e:
flash('添加书籍失败')
else:#如果作者不存在,添加作者和书籍
cursor.execute('insert into authors(name) values(%s)', author_name) #添加作者的名字,ID应该是自己加的
sql_id = "select id from authors where name='%s'"
cursor.execute(sql_id % author_name)
add_book_name = cursor.fetchall() # 获得的作者的ID
add_book_name_id = add_book_name[0]['id']
cursor.execute('insert into books(name,pid) values(%s,%s)', (book_name, add_book_name_id))
db.commit()
else:
if request.method== 'post':
flash('参数有误')
#global db
#查询所有作者信息,让信息传递给模板
cursor = db.cursor()
sql = 'select * from authors;'
cursor.execute(sql)
authors_cx = cursor.fetchall()
#sql_1='select books.`name` AS booksName from authors,books where authors.id=books.pid '
sql_1 = 'select authors.`name` AS authorsName,books.`name` AS booksName from authors,books where authors.id=books.pid '
cursor.execute(sql_1)
#[{'booksName': '吞噬星空', 'authorsName': '郭敬明'}, {'booksName': '寸芒', 'authorsName': '郭敬明'},
#{'booksName': '缥缈之旅', 'authorsName': '韩寒'}, {'booksName': '冰火魔厨', 'authorsName': '唐家三少'},
#{'booksName': '老王回忆录', 'authorsName': '唐家三少'}]
#cursor.fetchall()
jieguo_dict=cursor.fetchall()
authors_cx={}
for book_dict in jieguo_dict:
if authors_cx.get(book_dict['authorsName'])==None:
authors_cx[book_dict['authorsName']]=[]
authors_cx[book_dict['authorsName']].append(book_dict['booksName'])
# print(authors_cx)
#########################################################
result=[]
for key in authors_cx:
author_dict={}
author_dict['authorsName']=key
author_dict['booksName']=authors_cx[key]
result.append(author_dict)
#print(result)
cursor.execute('select * from books')
books=cursor.fetchall()
print(books)
books_form={}
for book in books:
book_name=book['name']
book_id=book['id']
books_form[book_name]=book_id
print(books_form)
return render_template('books.html', authors=result,form=author_form,book_form=books_form)
if __name__ == '__main__':
cursor = db.cursor()
sql_insert1='INSERT INTO authors(name) VALUES("郭敬明"),("韩寒"),("唐家三少")'
cursor.execute(sql_insert1)
sql_insert2 = 'INSERT INTO books(name,pid) VALUES("吞噬星空",1),("寸芒",1),("缥缈之旅",2),("冰火魔厨",3),("老王回忆录",3)'
cursor.execute(sql_insert2)
db.commit()
'''''
#result=cursor.execute('select books.`id`,books.`name`,books.`pid` from books')
#books=result.fetchall()###沿着错误
cursor.execute('select * from books')
books=cursor.fetchall()
print(books)
'''''
cursor.close()
# db.close()
app.run()
books.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<form method="post">
{{form.csrf_token()}}<br>
{{form.author.label}}{{form.author}}<br>
{{form.book.label}}{{form.book}}<br>
{{form.submit}}
<!--使用遍历获取闪现的消息-->
{%for message in get_flashed_messages() %}
{{message}}
{%endfor%}
</form>
<hr>
{% for author in authors %}
<li>{{author.authorsName}}</li>
<ul>
{% for book in author.booksName %}
<li>{{book}}<a href="{{url_for('delete_book' ,book_id=book_form[book])}}">删除</a></li>
{% endfor %}
</ul>
{% endfor %}
</body>
</html>
方法二:使用flask-sqlchemy[简单]
# -*- coding: utf-8 -*-
from flask import Flask,render_template,flash,request,redirect,url_for
from flask_sqlalchemy import SQLAlchemy
from flask_wtf import FlaskForm
from wtforms import StringField,SubmitField
from wtforms.validators import DataRequired
app = Flask(__name__)
#数据库配置
app.secret_key = 'csdnblog'
#app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:root@127.0.0.1:3306/flask_books'
app.config['SQLALCHEMY_DATABASE_URI']='mysql://root:5366892@localhost/flask_books'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
#作者模型
class Author(db.Model):
__tablename__ = 'authors'
id = db.Column(db.Integer,primary_key=True)
name = db.Column(db.String(16),unique=True)
#关系引用
books = db.relationship('Book',backref='author')
def __repr__(self):
return 'Author :%s' % self.name
#书籍模型
class Book(db.Model):
__tablename__ = 'books'
id = db.Column(db.Integer,primary_key=True)
name = db.Column(db.String(16),unique=True)
author_id = db.Column(db.Integer,db.ForeignKey('authors.id'))
def __repr__(self):
return 'Book : %s %s' % (self.name,self.author_id)
#自定义表单类
class AuthorForm(FlaskForm):
author = StringField('作者',validators=[DataRequired()])
book = StringField('书籍',validators=[DataRequired()])
submit = SubmitField('提交')
#删除作者逻辑同删除书籍
@app.route('/delete_author/<author_id>')
def delete_author(author_id):
author = Author.query.get(author_id)
if author:
try:
#查询完后直接删除
Book.query.filter_by(author_id=author.id).delete()
#删除作者
db.session.delete(author)
db.session.commit()
except Exception as e:
print(e)
flash('删除作者失败')
db.session.rollback()
else:
# 书籍不存在提示错误
flash('作者没有找到')
return redirect(url_for('index'))
#删除书籍 - -->网页中删除 - -->点击需要发送书籍的ID给删除书籍的路由 - -->路由需要接收参数
#传入书籍ID
@app.route('/delete_book/<book_id>')
def delete_book(book_id):
#查询是否存在该ID的书
book= Book.query.get(book_id)
#如果有就删除
if book:
try:
db.session.delete(book)
db.session.commit()
except Exception as e:
print(e)
flash('删除书籍失败')
db.session.rollback()
else:
#书籍不存在提示错误
flash('书籍没有找到')
#url_for传入视图函数名返回该视图函数对应的路由地址
return redirect(url_for('index'))
@app.route('/',methods=['POST','GET'])
def index():
author_form = AuthorForm()
#调用WTF函数实现验证
if author_form.validate_on_submit():
#验证通过获取数据
author_name = author_form.author.data
book_name = author_form.book.data
#判断作者是否存在
author = Author.query.filter_by(name = author_name).first()
#如果作者存在
if author:
#判断书籍是否存在
book = Book.query.filter_by(name = book_name).first()
#有就提示重复
if book:
flash('已存在同名书籍')
#没有重复书籍就添加数据
else:
try:
new_book = Book(name=book_name,author_id=author.id)
db.session.add(new_book)
db.session.commit()
except Exception as e:
print(e)
flash('添加书籍失败')
db.session.rollback()
#如果作者不存在添加作者书籍
else:
try:
new_author = Author(name=author_name)
db.session.add(new_author)
db.session.commit()
new_book = Book(name=book_name,author_id=new_author.id)
db.session.add(new_book)
db.session.commit()
except Exception as e:
print(e)
flash('添加作者和书籍失败')
db.session.rollback()
#验证不通过就提示错误
else:
if request.method == 'POST':
flash('参数不全')
authors = Author.query.all()
return render_template('books1.html',authors=authors,form = author_form)
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='如何征服美丽少男',author_id=au2.id)
bk5 = Book(name='如何使自己变得更骚',author_id=au3.id)
bk6 = Book(name='我变秃了,也变强了',author_id=au3.id)
db.session.add_all([bk1, bk2, bk3,bk4,bk5,bk6])
db.session.commit()
app.run(debug=True)
books1.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<form method="post">
{{ form.csrf_token() }}
{{ form.author.label }}{{ form.author }}<br>
{{ form.book.label }}{{ form.book }}<br>
{{ form.submit }}<br>
{% for message in get_flashed_messages() %}
{{ message }}
{% endfor %}
</form>
<hr>
<ul>
{% for author in authors %}
<li>{{ author.name }}<a href="{{ url_for("delete_author",author_id = author.id) }}">删除</a></li>
<ul>
{% for book in author.books %}
<li>{{ book.name }}<a href="{{ url_for("delete_book",book_id = book.id) }}">删除</a></li>
{% else %}
<li>无</li>
{% endfor %}
</ul>
{% endfor %}
</ul>
</body>
</html>
以前写的,网上不大好查资料,和伙伴交流最终两种方法都实现了!开森~