flask_十一、数据库引入数据+视图函数操作数据库表
数据库引入数据+视图函数操作数据库表
一、回顾库表的select数据（单个条件查询）
1.1查看所有数据
>>> from app import Note >>> Note.query.all() [<Note ID: 1 NAME:'name001'>, <Note ID: 2 NAME:'name002'>, <Note ID: 3 NAME:'name003'>]
1.2查看第一条数据
>>> Note.query.first() <Note ID: 1 NAME:'name001'>
1.3查看id==1的数据
>>> Note.query.get(1) <Note ID: 1 NAME:'name001'> >>>
1.4查看表记录数
#查看表记录数 >>> Note.query.count() 3
1.5筛选NAME字段值查找整条记录
#查看NAME==name002的整条记录 >>> Note.query.filter(Note.NAME == 'name002').all() [<Note ID: 2 NAME:'name002'>] #查看NAME=name003的整条记录 >>> Note.query.filter_by(NAME = 'name003').all() [<Note ID: 3 NAME:'name003'>]
1.6组合条件查询
#组合条件查询 >>> Note.query.filter(Note.NAME == 'name003').filter(Note.ID==3).all() [<Note ID: 3 NAME:'name003'>] >>>
1.7模糊查询
# 模糊查询 >>> Note.query.filter(Note.NAME.like ('%002%')).all() [<Note ID: 2 NAME:'name002'>]
1.8范围查询（in）
# 范围查询（in） >>> Note.query.filter(Note.NAME.in_(['name003'])).all() [<Note ID: 3 NAME:'name003'>] >>> >>> Note.query.filter(Note.ID.in_([1,3])).all() [<Note ID: 1 NAME:'name001'>, <Note ID: 3 NAME:'name003'>] >>>
1.9范围查询（not in）
# 范围查询（not in） >>> Note.query.filter(~Note.ID.in_([1,3])).all() [<Note ID: 2 NAME:'name002'>] >>>
二、与条件and查询数据
三、或条件or查询数据
四、更新数据update
五、删除数据delete
六、视图函数里操作数据库
6.1在template下新建base.html(不
<!DOCTYPE html> <html lang="en"> <head> {% block head %} {% block metas %} <meta charset="utf-8"> {% endblock metas %} <title>{% block title %}Database - HelloFlask{% endblock title %}</title> <link rel="stylesheet" type="text/css" href="{{ url_for('static', filename='favicon.ico') }}"> {% block styles %} <link rel="stylesheet" type="text/css" href="{{ url_for('static', filename='style.css' ) }}"> {% endblock styles %} {% endblock head %} </head> <body> <nav> {% block nav %} <u1> <li><a href="{{ url_for('index') }}">Home</a></li> </u1> {% endblock %} </nav> <main> {% for message in get_flashed_messages() %} <div class="alter"> {{message}} </div> {% endfor %} </main> </body>
6.2新建app.py内容（只含有新增数据的逻辑）
# encoding=utf-8 from flask import Flask,render_template,flash,url_for,redirect,request from flask_sqlalchemy import SQLAlchemy from flask_wtf import FlaskForm from wtforms import TextAreaField,SubmitField from wtforms.validators import DataRequired app = Flask(__name__) import os app.secret_key = os.getenv('SECRET_KEY','secret string') app.config['SQLALCHEMY_DATABASE_URI'] = os.getenv('DATABASE_URL','sqlite:///' + os.path.join(app.root_path,'data.db')) app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False db = SQLAlchemy(app) #定义Note模型类，映射到表note class Note(db.Model): ID = db.Column(db.Integer,primary_key = True) NAME = db.Column(db.Text) def __repr__(self): # %r 是用repr()方法处理对象，返回类型本身，而不进行类型转化 return '<Note ID: %r NAME:%r>' % (self.ID,self.NAME) import click @app.cli.command() def initdb(): db.create_all() click.echo('Initialized database') class NewNoteForm(FlaskForm): body = TextAreaField('Body',validators=[DataRequired()]) submit = SubmitField('Save') def initDB(): db.create_all() # class EditNoteForm(FlaskForm): # NAME = IntegerField('NAME',validators=[DataRequired()]) # submit = SubmitField('Update') # class DeleteNoteForm(FlaskForm): # submit = SubmitField('Delete') @app.route('/new/',methods=['GET','POST']) def new_note(): print("request.form in new_note: %s" % request.form) form = NewNoteForm() print("form" , form) print("form.validate_on_submit(): %s" % form.validate_on_submit()) # if request.method == 'POST' and form validate(): if form.validate_on_submit(): print("pass") try: print(Note.query.all()) except: print("initdb...") initDB() body = form.body.data note = Note(body = body) db.session.add(note) db.session.commit() flash("your note is saved") return redirect(url_for('index')) return render_template('new_note.html',form=form) # @app.route('/edit/<int:note_id>',methods=['GET','POST']) # def edit_note(note_id): # print("request.form iedit: %s" % request.form) # form = EditNoteForm() # print("form.body: %s" % form.body) # print("form.body.data: %s" % form.body.data) # note = Note.query.get(note_id) # print("note.body: %s" % note.body) # if form.validate_on_submit(): # print("pass") # try: # print(Note.query.all()) # except: # print("initdb...") # initDB() # body = form.body.body # note = Note(body = body) # db.session.add(note) # db.session.commit() # flash("your note is saved") # return redirect(url_for('index')) # return render_template('new_note.html',form=form) @app.route('/index/') def index(): form = NewNoteForm notes = Note.query.all() return render_template('index.html',notes=notes,form=form) # @app.route('/delete/,int:note_id',method=['POST']) # def delete_note(note_id): # form = DeleteNoteForm() # if form.validate_on_submit(): if __name__ == "__main__": print(app.config) app.run(debug=True)
6.3在template文件下新建new_note.html
{% extends 'base.html'%} {% from 'macros.html' import form_field %} {% block content %} <h2>New Note</h2>> <form method="post"> {{ form.csrf_token}} {{ form_field(form.body,rows=5,cols=50)}} {{form.submit}} </form> {% endblock %}
6.4编辑note数据函数
{% extends 'base.html'%} {% from 'macros.html' import form_field %} {% block title %}Edit Note{% endblock %} {% block content %} <h2>New Note</h2>> <form method="post"> {{ form.csrf_token}} {{ form_field(form.body,rows=5,cols=50)}} {{form.submit}}<br> </form> {% endblock %}
6.5base.html修改太
<!DOCTYPE html> <html lang="e"> <head> <meta charset="utf-8"> {% block head %} <title>{% block title %}Template - HelloFlask{% endblock %}</title> <link rel="icon" type="image/x-icon" href="{{ url_for('static', filename='favicon.ico') }}"> {% block styles %} <link rel="stylesheet" type="text/css" href="{{ url_for('static', filename='style.css' ) }}">
6.6更换template下的index.html
{% extends 'base.html'%} {% block content %} <h1>Notebook</h1> <a href="{{ url_for ('new_note')}}">New Note</a> <h4>{{ notes|length }} notes:</h4> {% for note in notes %} <div class="note"> <p>{{ note.body}}</p> </div> {% endfor %} {% endblock %}
6.7Template下新建的宏文件macros.html
{% macro form_field(field) %} {{ field.label}}<br> {{ field(**kwargs) }}<br> {% if field.errors %} {% for error in field.errors() %} <small class="error">{{ error }}</small><br> {% endfor %} {% endif %} {% endmacro %}
