1.环境安装
1.python3.85
2.模块
pip3 install flask -i https://pypi.douban.com/simple
pip3 install pymysql -i https://pypi.douban.com/simple
pip3 install flask-script -i https://pypi.douban.com/simple
pip3 install flask-sqlalchemy -i https://pypi.douban.com/simple
pip3 install flask-migrate==2.7.0 -i https://pypi.douban.com/simple
pip3 install SQLAlchemy -i https://pypi.douban.com/simple
pymysql:建立联系
flask-sqlalchemy:orm
flask-script:自定义命令
flask-migrate:制作命令
配置 settings.py
class DevConfig:
DEBUG = True
# 数据库连接配置,必须的,格式为(数据库+数据库驱动://数据库用户名:密码@数据库主机地址:端口号/数据库名称)
my_config = {
"MYSQL": "mysql",
"CYMSQL": "pymysql",
"ACCOUNT": "root",
"PASSWORD": 'foobared',
"ADDRESS": "127.0.0.1",
"PORT": 3306,
"DATABASENAME": "ymq"
}
SQLALCHEMY_DATABASE_URI = "{MYSQL}+{CYMSQL}://{ACCOUNT}:{PASSWORD}@{ADDRESS}:{PORT}/{DATABASENAME}".format(
**my_config)
SQLALCHEMY_TRACK_MODIFICATIONS = False # 为True时,flask-sqlalchemy会跟踪对象的修改
SQLALCHEMY_ECHO = True
exts包里面
# sqlalchemy实例化 第二种创建方式
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
初始化
from flask import Flask
from settings import dev
from apps.user.view import user_bp
from exts import db
def create_app():
app = Flask(__name__, template_folder='../templates', static_folder='../static')
app.config.from_object(dev.DevConfig)
# 蓝图注册
app.register_blueprint(user_bp)
# db初始化
db.init_app(app=app)
return app
实例化
from apps import create_app
from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand
from exts import db
app = create_app()
# 构建命令
manager = Manager(app=app)
# 将db交给manager
migrate = Migrate(app=app, db=db)
manager.add_command('db', MigrateCommand)
if __name__ == '__main__':
manager.run()
操作
# 只有第一次初始化
python app.py db init
# 记录数据
python app.py db migrate# 同步数据
python app.py db upgrade
python app.py db init
python app.py db migrate
python app.py db upgrade
基础操作
models
from datetime import datetime
from exts import db
class User(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
username = db.Column(db.String(15), nullable=False)
password = db.Column(db.String(64), nullable=False)
phone = db.Column(db.String(11), unique=True)
isdelete = db.Column(db.Boolean, default=False)
rdatetime = db.Column(db.DateTime, default=datetime.now)
def __str__(self):
return self.username
操作
1.查询:
查询所有: 模型类.query.all() ~ select * from user;
如果有条件的查询:
模型类.query.filter_by(字段名 = 值) ~ select * from user where 字段=值;
模型类.query.filter_by(字段名 = 值).first() ~ select * from user where 字段=值 limit..;
select * from user where age>17 and gender='男';
select * from user where username like 'zhang%';
select * from user where rdatetime> xxx and rdatetime < xxx;
模型类.query.filter() 里面是布尔的条件 模型类.query.filter(模型名.字段名 == 值)
模型类.query.filter_by() 里面是一个等值 模型类.query.filter_by(字段名 = 值)
***** 模型类.query.filter() ******
1. 模型类.query.filter().all() -----> 列表
2. 模型类.query.filter().first() ----->对象
3.User.query.filter(User.username.endswith('z')).all() select * from user where username like '%z';
User.query.filter(User.username.startswith('z')).all() # select * from user where username like 'z%';
User.query.filter(User.username.contains('z')).all() # select * from user where username like '%z%';
User.query.filter(User.username.like('z%')).all()
多条件:
from sqlalchemy import or_, and_,not_
并且: and_ 获取: or_ 非: not_
User.query.filter(or_(User.username.like('z%'), User.username.contains('i'))).all()
类似: select * from user where username like 'z%' or username like '%i%';
User.query.filter(and_(User.username.contains('i'), User.rdatetime.__gt__('2020-05-25 10:30:00'))).all()
# select * from user where username like '%i%' and rdatetime < 'xxxx'
补充:__gt__,__lt__,__ge__(gt equal),__le__ (le equal) ----》通常应用在范围(整型,日期)
也可以直接使用 > < >= <= !=
User.query.filter(not_(User.username.contains('i'))).all()
18 19 20 17 21 22 ....
select * from user where age in [17,18,20,22];
排序:order_by
user_list = User.query.filter(User.username.contains('z')).order_by(-User.rdatetime).all() # 先筛选再排序
user_list = User.query.order_by(-User.id).all() 对所有的进行排序
注意:order_by(参数):
1。 直接是字符串: '字段名' 但是不能倒序
2。 填字段名: 模型.字段 order_by(-模型.字段) 倒序
限制: limit
# limit的使用 + offset
# user_list = User.query.limit(2).all() 默认获取前两条
user_list = User.query.offset(2).limit(2).all() 跳过2条记录再获取两条记录
总结:
1. User.query.all() 所有
2. User.query.get(pk) 一个
3. User.query.filter() * ???????
如果要检索的字段是字符串(varchar,db.String):
User.username.startswith('')
User.username.endswith('')
User.username.contains('')
User.username.like('')
User.username.in_(['','',''])
User.username == 'zzz'
如果要检索的字段是整型或者日期类型:
User.age.__lt__(18)
User.rdatetime.__gt__('.....')
User.age.__le__(18)
User.age.__ge__(18)
User.age.between(15,30)
多个条件一起检索: and_, or_
非的条件: not_
排序:order_by()
获取指定数量: limit() offset()
4. User.query.filter_by()
删除:
两种删除:
1。逻辑删除(定义数据库中的表的时候,添加一个字段isdelete,通过此字段控制是否删除)
id = request.args.get(id)
user = User.query.get(id)
user.isdelete = True
db.session.commit()
2。物理删除(彻底从数据库中删掉)
id = request.args.get(id)
user = User.query.get(id)
db.session.delete(user)
db.session.commit()
更新:
id = request.args.get(id)
user = User.query.get(id)
# 修改对象的属性
user.username= xxxx
user.phone =xxxx
# 提交更改
db.session.commit()
# 添加
user = User()
user.username = username
user.password = password
user.phone = phone
# 添加并提交
db.session.add(user)
db.session.commit()
两张表
表关系
from datetime import datetime
from exts import db
class User(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
username = db.Column(db.String(15), nullable=False)
password = db.Column(db.String(64), nullable=False)
phone = db.Column(db.String(11), unique=True, nullable=False)
email = db.Column(db.String(30))
icon = db.Column(db.String(100))
isdelete = db.Column(db.Boolean, default=False)
rdatetime = db.Column(db.DateTime, default=datetime.now)
# 增加一个字段 relationship 反向查询使用
articles = db.relationship('Article', backref='user')
def __str__(self):
return self.username
from datetime import datetime
from exts import db
class Article_type(db.Model):
__tablename__ = 'type'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
type_name = db.Column(db.String(20), nullable=False)
articles = db.relationship('Article', backref='articletype')
class Article(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
title = db.Column(db.String(50), nullable=False)
content = db.Column(db.Text, nullable=False)
pdatetime = db.Column(db.DateTime, default=datetime.now)
click_num = db.Column(db.Integer, default=0)
save_num = db.Column(db.Integer, default=0)
love_num = db.Column(db.Integer, default=0)
# 外键 同步到数据库的外键关系
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
type_id = db.Column(db.Integer, db.ForeignKey('type.id'), nullable=False)
comments = db.relationship('Comment', backref='article')
class Comment(db.Model):
# 自定义表的名字
__tablename__ = 'comment'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
comment = db.Column(db.String(255), nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
article_id = db.Column(db.Integer, db.ForeignKey('article.id'))
cdatetime = db.Column(db.DateTime, default=datetime.now)
def __str__(self):
return self.comment