Flask(三)
一.wtforms
from flask import Flask, render_template, request, redirect from wtforms import Form from wtforms.fields import simple from wtforms import validators from wtforms import widgets app = Flask(__name__, template_folder='templates') app.debug = True class LoginForm(Form): # 字段(内部包含正则表达式) name = simple.StringField( label='用户名', validators=[ validators.DataRequired(message='用户名不能为空.'), validators.Length(min=6, max=18, message='用户名长度必须大于%(min)d且小于%(max)d') ], widget=widgets.TextInput(), # 页面上显示的插件 render_kw={'class': 'form-control'} ) # 字段(内部包含正则表达式) pwd = simple.PasswordField( label='密码', validators=[ validators.DataRequired(message='密码不能为空.'), validators.Length(min=8, message='用户名长度必须大于%(min)d'), validators.Regexp(regex="^(?=.*[a-z])(?=.*[A-Z])(?=.*\d)(?=.*[$@$!%*?&])[A-Za-z\d$@$!%*?&]{8,}", message='密码至少8个字符,至少1个大写字母,1个小写字母,1个数字和1个特殊字符') ], widget=widgets.PasswordInput(), render_kw={'class': 'form-control'} ) @app.route('/login', methods=['GET', 'POST']) def login(): if request.method == 'GET': form = LoginForm() return render_template('login.html', form=form) else: form = LoginForm(formdata=request.form) if form.validate(): print('用户提交数据通过格式验证,提交的值为:', form.data) else: print(form.errors) return render_template('login.html', form=form) if __name__ == '__main__': app.run()
login.html:
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Title</title> </head> <body> <h1>登录</h1> <form method="post"> <p>{{form.name.label}} {{form.name}} {{form.name.errors[0] }}</p> <p>{{form.pwd.label}} {{form.pwd}} {{form.pwd.errors[0] }}</p> <input type="submit" value="提交"> </form> </body> </html>
用法二:
from flask import Flask, render_template, request, redirect from wtforms import Form from wtforms.fields import core from wtforms.fields import html5 from wtforms.fields import simple from wtforms import validators from wtforms import widgets app = Flask(__name__, template_folder='templates') app.debug = True class RegisterForm(Form): name = simple.StringField( label='用户名', validators=[ validators.DataRequired() ], widget=widgets.TextInput(), render_kw={'class': 'form-control'}, default='cxw' ) pwd = simple.PasswordField( label='密码', validators=[ validators.DataRequired(message='密码不能为空.') ], widget=widgets.PasswordInput(), render_kw={'class': 'form-control'} ) pwd_confirm = simple.PasswordField( label='重复密码', validators=[ validators.DataRequired(message='重复密码不能为空.'), validators.EqualTo('pwd', message="两次密码输入不一致") ], widget=widgets.PasswordInput(), render_kw={'class': 'form-control'} ) email = html5.EmailField( label='邮箱', validators=[ validators.DataRequired(message='邮箱不能为空.'), validators.Email(message='邮箱格式错误') ], widget=widgets.TextInput(input_type='email'), render_kw={'class': 'form-control'} ) gender = core.RadioField( label='性别', choices=( (1, '男'), (2, '女'), ), #这句话的意思是上面的choices元组的第一个值是int类型 #如果上上面为(‘1’, '男'),(‘2’, '女'),则下面的coerce则不用写 coerce=int # “1” “2” ) #这里是单选框 city = core.SelectField( label='城市', choices=( ('bj', '北京'), ('sh', '上海'), ) ) #这里是多选框 hobby = core.SelectMultipleField( label='爱好', choices=( (1, '篮球'), (2, '足球'), ), coerce=int ) #这里是多选的checkbox favor = core.SelectMultipleField( label='喜好', choices=( (1, '篮球'), (2, '足球'), ), widget=widgets.ListWidget(prefix_label=False), option_widget=widgets.CheckboxInput(), coerce=int, default=[1, 2] ) #这里可以改值 def __init__(self, *args, **kwargs): super(RegisterForm, self).__init__(*args, **kwargs) self.favor.choices = ((1, '篮球'), (2, '足球'), (3, '羽毛球')) def validate_pwd_confirm(self, field): """ 自定义pwd_confirm字段规则,例:与pwd字段是否一致 :param field: :return: """ # 最开始初始化时,self.data中已经有所有的值 if field.data != self.data['pwd']: # raise validators.ValidationError("密码不一致") # 继续后续验证 raise validators.StopValidation("密码不一致") # 不再继续后续验证 @app.route('/register', methods=['GET', 'POST']) def register(): if request.method == 'GET': #这里可以传默认值 form = RegisterForm(data={'gender': 2,'hobby':[1,]}) # initial return render_template('register.html', form=form) else: form = RegisterForm(formdata=request.form) if form.validate(): print('用户提交数据通过格式验证,提交的值为:', form.data) else: print(form.errors) return render_template('register.html', form=form) if __name__ == '__main__': app.run()
register.html
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Title</title> </head> <body> <h1>用户注册</h1> <form method="post" novalidate style="padding:0 50px"> {% for field in form %} <p>{{field.label}}: {{field}} {{field.errors[0] }}</p> {% endfor %} <input type="submit" value="提交"> </form> </body> </html>
二.多app应用
from werkzeug.wsgi import DispatcherMiddleware from werkzeug.serving import run_simple from flask import Flask, current_app app1 = Flask('app01') app2 = Flask('app02') @app1.route('/index') def index(): return "app01" @app2.route('/index2') def index2(): return "app2" # http://www.oldboyedu.com/index # http://www.oldboyedu.com/sec/index2 dm = DispatcherMiddleware(app1, { '/sec': app2, }) if __name__ == "__main__": run_simple('localhost', 5000, dm)
三.flask-script (manage.py就是当前要执行的文件)
from flask import Flask,request from flask_script import Manager app = Flask(__name__) manager=Manager(app) @app.route("/") def index(): return "ok" if __name__ == '__main__': manager.run() #以后在执行,直接:python3 manage.py runserver #python3 manage.py runserver --help
自定义命令:
from flask import Flask from flask_script import Manager app = Flask(__name__) manager=Manager(app) @app.route("/") def index(): return "ok" @manager.command def custom1(arg): """ 自定义命令 python manage.py custom 123 :param arg: :return: """ print(arg) @manager.option('-n', '--name', dest='name') @manager.option('-u', '--url', dest='url') def cmd(name, url): """ 自定义命令(-n也可以写成--name) 执行: python manage.py cmd -n lqz -u http://www.oldboyedu.com 执行: python manage.py cmd --name lqz --url http://www.oldboyedu.com :param name: :param url: :return: """ print(name, url) #有什么用? #把excel的数据导入数据库,定制个命令,去执行 if __name__ == '__main__': manager.run()
四.SQLAlchemy
介绍
SQLAlchemy是一个基于Python实现的ORM框架。该框架建立在 DB API之上,使用关系对象映射进行数据库操作,简言之便是:将类和对象转换成SQL,然后使用数据API执行SQL并获取执行结果。
pip3 install sqlalchemy
组成部分:
Engine,框架的引擎
Connection Pooling ,数据库连接池
Dialect,选择连接数据库的DB API种类
Schema/Types,架构和类型
SQL Exprression Language,SQL表达式语言
SQLAlchemy本身无法操作数据库,其必须以来pymsql等第三方插件,Dialect用于和数据API进行交流,根据配置文件的不同调用不同的数据库API,从而实现对数据库的操作,如:
MySQL-Python mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> pymysql mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] MySQL-Connector mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...] 更多:http://docs.sqlalchemy.org/en/latest/dialects/index.html
创建表模型,初始化和删除表,不可修改字段:
import datetime from sqlalchemy import create_engine from sqlalchemy.orm import relationship from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index Base = declarative_base() class Users(Base): __tablename__ = 'users' # 数据库表名称 id = Column(Integer, primary_key=True) # id 主键,autoincrement name = Column(String(32), index=True, nullable=False) # name列,索引,不可为空 age =Column(Integer,default=0) def __repr__(self): return self.name +"_"+str(self.id) #email = Column(String(32), unique=True) # #datetime.datetime.now不能加括号,加了括号,以后永远是当前时间 # ctime = Column(DateTime, default=datetime.datetime.now) # extra = Column(Text, nullable=True) # __table_args__ = ( # UniqueConstraint('id', 'name', name='uix_id_name'), #联合唯一 # Index('ix_id_name', 'name', 'email'), #索引 # ) # class use1(Base): __tablename__ = 'users1' # 数据库表名称 ids = Column(Integer, primary_key=True) # id 主键,autoincrement names = Column(String(32), index=True, nullable=False) # class Hobby(Base): __tablename__ = 'hobby' id = Column(Integer, primary_key=True) caption = Column(String(50), default='篮球') class Person(Base): __tablename__ = 'person' nid = Column(Integer, primary_key=True) name = Column(String(32), index=True, nullable=True) # hobby指的是tablename而不是类名,uselist=False hobby_id = Column(Integer, ForeignKey("hobby.id")) # 跟数据库无关,不会新增字段,只用于快速链表操作 # 类名,backref用于反向查询 hobby = relationship('Hobby', backref='pers') class Boy2Girl(Base): __tablename__ = 'boy2girl' id = Column(Integer, primary_key=True, autoincrement=True) girl_id = Column(Integer, ForeignKey('girl.id')) boy_id = Column(Integer, ForeignKey('boy.id')) class Girl(Base): __tablename__ = 'girl' id = Column(Integer, primary_key=True) name = Column(String(64), unique=True, nullable=False) class Boy(Base): __tablename__ = 'boy' id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(64), unique=True, nullable=False) # 与生成表结构无关,仅用于查询方便,放在哪个单表中都可以 girl = relationship('Girl', secondary='boy2girl', backref='boys') def init_db1(): """ 根据类创建数据库表 :return: """ engine = create_engine( "mysql+pymysql://root:root@127.0.0.1:3306/aaa?charset=utf8", max_overflow=0, # 超过连接池大小外最多创建的连接 pool_size=5, # 连接池大小 pool_timeout=30, # 池中没有线程最多等待的时间,否则报错 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收(重置) ) Base.metadata.create_all(engine) def drop_db(): """ 根据类删除数据库表 :return: """ engine = create_engine( "mysql+pymysql://root:root@127.0.0.1:3306/aaa?charset=utf8", max_overflow=0, # 超过连接池大小外最多创建的连接 pool_size=5, # 连接池大小 pool_timeout=30, # 池中没有线程最多等待的时间,否则报错 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收(重置) ) Base.metadata.drop_all(engine) if __name__ == '__main__': #drop_db() init_db1()
orm操作及线程安全scoped_session::
from sqlalchemy.orm import sessionmaker,scoped_session from sqlalchemy import create_engine from models import Users engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/aaa", max_overflow=0, pool_size=5) Session = sessionmaker(bind=engine) """ # 线程安全,基于本地线程实现每个线程用同一个session # 特殊的:scoped_session中有原来方法的Session中的一下方法: public_methods = ( '__contains__', '__iter__', 'add', 'add_all', 'begin', 'begin_nested', 'close', 'commit', 'connection', 'delete', 'execute', 'expire', 'expire_all', 'expunge', 'expunge_all', 'flush', 'get_bind', 'is_modified', 'bulk_save_objects', 'bulk_insert_mappings', 'bulk_update_mappings', 'merge', 'query', 'refresh', 'rollback', 'scalar' ) """ #scoped_session类并没有继承Session,但是却又它的所有方法 session = scoped_session(Session) # ############# 执行ORM操作 ############# obj1 = Users(name="lqz") session.add(obj1) # 提交事务 session.commit() # 关闭session session.close()
单表增删改查:
import time import threading from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine from sqlalchemy.sql import text from models import Users,use1 engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/aaa", max_overflow=0, pool_size=5) Session = sessionmaker(bind=engine) session = Session() # ################ 添加 ################ # # obj1 = Users(name="wupeiqi",age=19) # session.add(obj1) # # session.add_all([ # Users(name="lqz",age=18), # Users(name="egon",age=20), # use1(names="c1.com"), # ]) # session.commit() # ################ 删除 ################ # session.query(Users).filter(Users.id > 4).delete() # session.commit() # ################ 修改 ################ #传字典 # session.query(Users).filter(Users.id > 0).update({"name" : "lqz"}) #类似于django的F查询,这个后面必须配合synchronize_session, #如果是字符串就用False,如果是数字就用#evaluata # session.query(Users).filter(Users.id > 0).update({Users.name: Users.name + "099"}, synchronize_session=False) # session.query(Users).filter(Users.id > 0).update({"age": Users.age + 1}, synchronize_session="evaluate") # session.commit() # ################ 查询 ################ # r1 = session.query(Users).all() # print(r1[0].name) #只取age列,把name重命名为xx # r2 = session.query(Users.name.label('xx'), Users.age).all() # print(r2[0].xx) #filter传的是表达式,filter_by传的是参数 # r3 = session.query(Users).filter(Users.name == "lqz").all() # print(r3[0].name) # r4 = session.query(Users).filter_by(name='lqz').all() # print(r4[0].name) # r5 = session.query(Users).filter_by(name='lqz').first() # print(r5.name) #:value 和:name 相当于占位符,用params传参数 # r6 = session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by(Users.id).all() #自定义查询sql #r7 = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all() # 条件 # ret = session.query(Users).filter_by(name='lqz').all() #表达式,and条件连接 # ret = session.query(Users).filter(Users.id > 2, Users.name == 'lqz').all() # print(ret[0].name,ret[0].id) # ret = session.query(Users).filter(Users.id.between(1, 3)).all() # print(ret) #注意下划线 # ret = session.query(Users).filter(Users.id.in_([1,3,4])).all() # print(ret) #~非,除。。外 # ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all() # print(ret) #二次筛选 # ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter(Users.name=='lqz'))).all() # print(ret) from sqlalchemy import and_, or_ #or_包裹的都是or条件,and_包裹的都是and条件 # ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'lqz')).all() # ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'lqz')).all() # print(ret) # ret = session.query(Users).filter( # or_( # Users.id < 2, # and_(Users.name == 'eric', Users.id > 3), # Users.extra != "" # )).all() # 通配符,以e开头,不以e开头 (_代表占位) # ret = session.query(Users).filter(Users.name.like('_u%')).all() # print(ret) # ret = session.query(Users).filter(~Users.name.like('e%')).all() # 限制,用于分页,区间 # ret = session.query(Users).all()[1:2] # print(ret) # 排序,根据name降序排列(从大到小) # ret = session.query(Users).order_by(Users.name.desc()).all() #第一个条件重复后,再按第二个条件升序排 # ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all() # print(ret) # 分组 # from sqlalchemy.sql import func # # ret = session.query(Users).group_by(Users.name).all() # print(ret) #分组之后取最大id,id之和,最小id # ret = session.query( # func.max(Users.id), # func.sum(Users.id), # func.min(Users.id)).group_by(Users.name).all() # print(ret) #haviing筛选 # ret = session.query( # func.max(Users.id), # func.sum(Users.id), # func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all() # print(ret) #增,删,改都要commit() session.close()
一对多: 一个爱好对应多个人
class Hobby(Base): __tablename__ = 'hobby' id = Column(Integer, primary_key=True) caption = Column(String(50), default='篮球') class Person(Base): __tablename__ = 'person' nid = Column(Integer, primary_key=True) name = Column(String(32), index=True, nullable=True) # hobby指的是tablename而不是类名,uselist=False hobby_id = Column(Integer, ForeignKey("hobby.id")) # 跟数据库无关,不会新增字段,只用于快速链表操作 # 类名,backref用于反向查询 hobby = relationship('Hobby', backref='pers')
基本操作
import time import threading from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine from sqlalchemy.sql import text from sqlalchemy.engine.result import ResultProxy from models import Users, Hobby, Person engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/aaa?charset=utf8", max_overflow=0, pool_size=5) Session = sessionmaker(bind=engine) session = Session() # 添加一 # session.add_all([ # Hobby(caption='乒乓球'), # Hobby(caption='羽毛球'), # Person(name='张三', hobby_id=1), # Person(name='李四', hobby_id=2), # ]) # person = Person(name='张九', hobby=Hobby(caption='姑娘')) # session.add(person) #添加二 # hb = Hobby(caption='人妖') # hb.pers = [Person(name='文飞'), Person(name='博雅')] # session.add(hb) # # session.commit() # 使用relationship正向查询 # v = session.query(Person).filter(Person.hobby_id==4).first() # print(v.name) # print(v.hobby.caption) # 使用relationship反向查询 # v = session.query(Hobby).filter(Hobby.id==4).first() # print(v.caption) # print(v.pers) #方式一,自己链表 # person_list=session.query(Person).join(Hobby,isouter=True) # ret = session.query(Person).join(Hobby,Person.nid == Hobby.id, isouter=True) # print(ret) # print(person_list) # person_list=session.query(Person,Hobby).join(Hobby,isouter=True).all() # for row in person_list: # # print(row.name,row.caption) # print(row[0].name,row[1].caption) #方式二:通过relationship # person_list=session.query(Person).all() # for row in person_list: # print(row.name,row.hobby.caption) #查询喜欢姑娘的所有人 # obj=session.query(Hobby).filter(Hobby.id==1).first() # persons=obj.pers session.close()
多对多:
class Boy2Girl(Base): __tablename__ = 'boy2girl' id = Column(Integer, primary_key=True, autoincrement=True) girl_id = Column(Integer, ForeignKey('girl.id')) boy_id = Column(Integer, ForeignKey('boy.id')) class Girl(Base): __tablename__ = 'girl' id = Column(Integer, primary_key=True) name = Column(String(64), unique=True, nullable=False) class Boy(Base): __tablename__ = 'boy' id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(64), unique=True, nullable=False) # 与生成表结构无关,仅用于查询方便,放在哪个单表中都可以 girl = relationship('Girl', secondary='boy2girl', backref='boys')
基本操作:
from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine from models import Girl,Boy,Boy2Girl engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/aaa?charset=utf8", max_overflow=0, pool_size=5) Session = sessionmaker(bind=engine) session = Session() # 添加 # session.add_all([ # Girl(name='g_com1'), # Girl(name='g2.com1'), # Boy(name='A组1'), # Boy(name='B组2'), # ]) # session.commit() # ''' # s2g = Boy2Girl(girl_id=2, boy_id =2) # session.add(s2g) # session.commit() # 正向添加 # gp = Boy(name='C组') # gp.girl = [Girl(name='c3.com'),Girl(name='c4.com')] # session.add(gp) # session.commit() # 反向添加 # """ # ser = Girl(name='c6.com') # ser.boys = [Boy(name='F组pp'),Boy(name='G组ll')] # session.add(ser) # session.commit() # # """ # # # # 使用relationship正向查询 # """ # ''' # v = session.query(Boy).filter(Boy.id==2).first() # print(v.name) # print(v.girl) # ''' # # 使用relationship反向查询 # # ''' # v = session.query(Girl).filter(Girl.id==3).first() # print(v.name) # print(v.boys[0].name) # # # session.close()
五.flask_sqlalchemy和sql_migrate
sansa---------views--------------- | | | |-----account.py | | manage.py |----_init_.py |
| |
settings.py | |----models.py
manage.py:
#!/usr/bin/env python # -*- coding:utf-8 -*- """ 生成依赖文件: pipreqs ./ """ from sansa import create_app from flask_script import Manager from flask_migrate import Migrate,MigrateCommand from sansa import db app = create_app() manager=Manager(app) Migrate(app,db) manager.add_command('db1', MigrateCommand) if __name__ == '__main__': # app.run() manager.run()
init.py:
#!/usr/bin/env python # -*- coding:utf-8 -*- from flask import Flask from flask_sqlalchemy import SQLAlchemy db = SQLAlchemy() from .models import * from .views import account def create_app(): app = Flask(__name__) app.config.from_object('settings.DevelopmentConfig') # 将db注册到app中 db.init_app(app) # 注册蓝图 app.register_blueprint(account.account) return app
account.py:
#!/usr/bin/env python # -*- coding:utf-8 -*- from flask import Blueprint from .. import db from .. import models account = Blueprint('account', __name__) @account.route('/login') def login(): db.session.add(models.Users(username='lqz', email='123')) db.session.query(models.Users).all() db.session.commit() # 添加示例 """ db.session.add(models.Users(username='lqz', pwd='123', gender=1)) db.session.commit() obj = db.session.query(models.Users).filter(models.Users.id == 1).first() print(obj) PS: db.session和db.create_session """ # db.session.add(models.Users(username='wupeiqi1', email='wupeiqi1@xx.com')) # db.session.commit() # db.session.close() # # db.session.add(models.Users(username='wupeiqi2', email='wupeiqi2@xx.com')) # db.session.commit() # db.session.close() # db.session.add(models.Users(username='alex1',email='alex1@live.com')) # db.session.commit() # db.session.close() user_list = db.session.query(models.Users).all() db.session.close() for item in user_list: print(item.username) return 'login'
settings.py (引擎不用注册):
#!/usr/bin/env python # -*- coding:utf-8 -*- class BaseConfig(object): # SESSION_TYPE = 'redis' # session类型为redis # SESSION_KEY_PREFIX = 'session:' # 保存到session中的值的前缀 # SESSION_PERMANENT = True # 如果设置为False,则关闭浏览器session就失效。 # SESSION_USE_SIGNER = False # 是否对发送到浏览器上 session:cookie值进行加密 SQLALCHEMY_DATABASE_URI = "mysql+pymysql://root:root@127.0.0.1:3306/bbb?charset=utf8" SQLALCHEMY_POOL_SIZE = 5 SQLALCHEMY_POOL_TIMEOUT = 30 SQLALCHEMY_POOL_RECYCLE = -1 # 追踪对象的修改并且发送信号 SQLALCHEMY_TRACK_MODIFICATIONS = False class ProductionConfig(BaseConfig): pass class DevelopmentConfig(BaseConfig): pass class TestingConfig(BaseConfig): pass
models.py:
#!/usr/bin/env python # -*- coding:utf-8 -*- from . import db class Users(db.Model): """ 用户表 """ __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(80), unique=True, nullable=False) email = db.Column(db.String(120), unique=True, nullable=False) ids = db.Column(db.Integer) def __repr__(self): return '<User %r>' % self.username
执行命令:
flask-migrate
python3 manage.py db1 init 初始化:只执行一次
python3 manage.py db1 migrate 等同于 makemigartions
python3 manage.py db1 upgrade 等同于migrate
执行并登login即可添加数据:
python3 manage.py runserver