目录
连接mysql数据库测试(专业版)
[注意1:要导入text库,否则可能出现找不到select 1错误]
[注意2:若出现下列问题,可按照模板代码的顺序db = SQLAlchemy(app) 的位置]
RuntimeError: Either 'SQLALCHEMY_DATABASE_URI' or 'SQLALCHEMY_BINDS' must be set.
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text
app = Flask(__name__)
# MySQL所在主机名,默认127.0.0.1
HOSTNAME = "127.0.0.1"
PORT = 3306
USERNAME = "root"
PASSWORD = "****"
DATABASE = "***"
app.config['SQLALCHEMY_DATABASE_URI'] = f"mysql+pymysql://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8"
db = SQLAlchemy(app)
with app.app_context():
with db.engine.connect() as conn:
result = conn.execute(text("select 1"))
print(result.fetchone()) # (1,)
ORM的实现
class User(db.Model):
__tablename = 'admin'
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
sname = db.Column(db.String(100),nullable=False)
snumber = db.Column(db.String(100),nullable=False)
address = db.Column(db.String(100),nullable=False)
with app.app_context():
db.create_all()
命令行的实现及结果
mysql> show tables;
+---------------+
| Tables_in_flk |
+---------------+
| admin |
| user |
+---------------+
2 rows in set (0.00 sec)mysql> desc admin;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| sname | varchar(100) | NO | | NULL | |
| snumber | varchar(100) | NO | | NULL | |
| address | varchar(100) | NO | | NULL | |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
事务
try:
db.session.add(xxx)
db.seesion.commit()
except:
db.session.rollback()
db.session.flush()
ORM插入实现
# 插入单条数据
user = User(sname='pass', snumber=10, address='163@qq.com')
db.session.add(user)
db.session.commit()
# 插入多条数据
persons = []
persons.append(user)
db.session.add_all(persons)
db.session.commit()
mysql> select * from admin;
+----+-------+---------+------------+
| id | sname | snumber | address |
+----+-------+---------+------------+
| 1 | pass | 10 | 163@qq.com |
+----+-------+---------+------------+
1 row in set (0.00 sec)
ORM查询实现
常用查询:
- all()
- first()
- get()
- count()
- paginate()
查询属性:
- contains
- startwith
- endswith
- in_
- __gt__ 大于
user = User.query.get(1)
print(user.sname)
# 输出 pass
ORM筛选实现
过滤器:
- filter(xxx==xxx)
- filter_by(xxx=xxx) 等值
- limit()
- offset()
- order_by()
- goup_by()
user = User.query.filter_by(sname='pass')
print(user[0].address)
# 输出 163@qq.com
分页实现
# 手动分页 page,per_page=x,x persons = Person.query.offset((page-1)*per_page).limit(per_page) # 自动分页 persons = Person.query.paginate(page=page,per_page=per_page,error_out=False).items
paginate 属性:
- items 当页的内容列表
- has_next 是否还有下一页
- has_perv
- page 当前页码
- pages 总页码
- per_page 每页显示数量
- prev_num 上一页页码数
- total 查询返回的记录总数
ORM更新实现
user = User.query.filter_by(sname='pass').first()
user.address = '666@qq.com'
db.session.commit()
ORM删除实现
user = User.query.get(1)
db.session.delete(user)
db.session.commit()
ORM的 单向/双向 数据绑定
relationship 外键关联
backref 反向引用的名称
back_populates 实现外键的信息双向绑定
lazy 懒加载,未调用,不访问DB
class User(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
sname = db.Column(db.String(100),nullable=False)
snumber = db.Column(db.String(100),nullable=False)
address = db.Column(db.String(100),nullable=False)
# author = db.relationship('Article', back_populates='author')
# relationship 引用外键所指向的Model
class Article(db.Model):
__tablename__= 'article'
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
title = db.Column(db.String(200),nullable=False)
content = db.Column(db.Text,nullable=False)
author_id = db.Column(db.Integer,db.ForeignKey('user.id'))
# author = db.relationship('User',back_populates='articles')
# relationship 引用外键所指向的Model
author = db.relationship('User',backref='articles') # relationship 引用外键所指向的Model
# back_populates 实现外键的信息双向绑定 ,backref 实现单向绑定
# 访问技巧 Article.query.get(x).articles.sname
@app.route('/article/add')
def add():
article1 = Article(title='Flask',content='xxx')
article1.author=User.query.get(1)
article2 = Article(title='Django',content='yyy')
article2.author=User.query.get(1)
db.session.add_all([article1,article2])
db.session.commit()
return 'ok'
@app.route('/article/find')
def find():
user = User.query.get(1)
# print(user)
for article in user.articles:
print(article)
print(article.title)
return 'ok'
注意: 多对多关联时需要增加中间表来实现数据关联secondary=collects
collects = db.Table('collects',db.Column('user.id'),db.Integer,db.ForeignKey('user.id')) class xxx(db.Model): xxx = db.relationship('xxx',backref='xxx',secondary=collects)
运行结果:
mysql> select * from article;
+----+--------+---------+-----------+
| id | title | content | author_id |
+----+--------+---------+-----------+
| 1 | Flask | xxx | NULL |
| 2 | Django | yyy | NULL |
+----+--------+---------+-----------+
2 rows in set (0.00 sec)127.0.0.1 - - [13/Feb/2024 18:00:20] "GET /article/find HTTP/1.1" 200 -
Flask
Django
生成ORM模型的迁移
flask db init
flask db migrate
flask db upgrade
蓝图使用
from flask import Blueprint
bp=Blueprint('auth',__name__,url_prefix='/auth')
@bp.route('/login')
def login():
pass
app.register_blueprint(bp) # 注册绑定
Flask 插件
缓存使用
pip install flask-caching # 安装依赖
# 初始化
from flask_cache import Cache
cache = Cache(config={'CACHE_TYPE':'simple',})
cache.init_app(app=app)
# view
@cache.cached(timeout=30)
def xxx():
print('xxx')
钩子函数
定义:指在执行函数和目标函数之间挂载的函数,提供一个point-挂载点,是 AOP编程思想
常用的钩子函数:
- before_first_request 第一次请求之前执行
- before_request 每次请求之前执行
- after_requtest 每次请求之后执行
- teardown_appcontext 当app上下文被移除之后的函数可以进行提交或回滚
常见场景:反爬、黑名单
# 反爬
value = cache.get(xxx)
if value:
return "你在执行爬虫程序!"
esle:
cache.set(key,'xx',timeout=1)
# 黑名单
ip =request.remote_addr
if ip === 'xxx':
return '黑名单用户'
前后端分离:
前后端分离:
jsonify() 序列化字符串返回给前端页面
前后端不分离:
render_template('xxx.html',data)
flask-restful
pip install flask-restful 安装依赖
Resource 实现类
# apis.py
from .urls.py import *
from flask_restful import Resource
class xxx(Resource):
def get(self):
return {'data':'get test'}
...
# exts.py
from flask_restful import Resource,Api
api = Api()
# 配置 urls.py 文件
from .exts import api
from .apis import *
# 路由
api.add_resource(xxx,'/test/')