flask4

数据库

app

from flask import Flask
from flask_script import Manager

from App.ext import db
from App.views import bp

app = Flask(__name__)
app.config.from_pyfile("settings.py")
manager = Manager(app)

db.init_app(app)
app.register_blueprint(bp)

if __name__ == '__main__':
    manager.run()

APP.ext

from flask_sqlalchemy import SQLAlchemy

# 创建数据库对象
db = SQLAlchemy()

settings

DEBUG = True
SQLALCHEMY_DATABASE_URI = "mysql+pymysql://root:123@127.0.0.1:3306/day04"
SQLALCHEMY_TRACK_MODIFICATIONS = False

App.views

• select [字段列表]
• from [表名]
• [where]
• [group by]
• [having]
• [order by]
• [limit]

from flask import Blueprint, render_template, redirect, url_for
from App.models import User,db

bp = Blueprint('bp',__name__,url_prefix='/bp')

# 修改、删除记录
@bp.route("/update/")
def db_update():
    # 查询记录
    user = User.query.get(1)
    print(user)
    # 修改记录
    user.password = "123"
    # 删除记录
    if user:
        db.session.delete(user)
    # 默认是开启事务，必须手动提交
    db.session.commit()

    return "更新记录"

# 自定义基类实现增加、删除、修改记录
@bp.route("/change/")
def db_change():
    # 增加一条记录
    user = User(username='华为',password='123')
    user.save()

    # 更新
    from hashlib import sha1
    user = User.query.get(3)
    if user:
        user.password = sha1("23423".encode('utf8')).hexdigest()
        user.save()

    # 删除
    user = User.query.get(3)
    if user:
        user.delete()

    # 增加多条记录
    user1 = User(username="李泽",password='123')
    user2 = User(username="骆鑫",password='123')
    User.save_all(user1,user2)
    return "change"

# 查询
@bp.route("/find/")
def find():
    # 基础查询
    # get 根据主键值查询，获取一条记录，不成返回None，成功返回模型对象
    users = User.query.get(3)
    if users: # 判断对象是否存在
        print(users,type(users))

    # all 查询表中的所有记录
    users = User.query.all()

    # first从结果集中取一条记录
    users = User.query.first()

    # 获取指定字段
    users = User.query.with_entities(User.username,User.uid).all()
    users = db.session.query(User).with_entities(User.username).all()

    # 隐藏重复记录
    users = db.session.query(User.password).distinct().all()
    
    # 排序
    # 字段前的-表示降序，默认是升序
    users = User.query.order_by(-User.uid).all()
    # 多列排序
    users = User.query.order_by(-User.sex,-User.password).all()
    
    # 聚合函数 ：max min count sum avg
    from sqlalchemy import func
    # [(3,)]
    users = db.session.query(func.count(User.username)).all()
    # (3,)
    users = db.session.query(func.count(User.uid)).first()
    # 3  scalar()直接获取数值，返回的必须是一条记录一个字段
    users = db.session.query(func.count(User.uid)).scalar()
    # 3  统计记录个数可以直接使用count方法
    users = db.session.query(User).count()

    users = db.session.query(func.max(User.uid)).scalar() # 3
    users = User.query.with_entities(func.min(User.uid)).scalar() # 1
    
    # 分组
    # 统计user表中男和女的数目
    # [(False, 2), (True, 1)]
    users = db.session.query(User.sex,func.count(User.uid)).group_by(User.sex).all()
    # having
    # [(False, 2)]
    users = db.session.query(User.sex,func.count(User.sex)).group_by(User.sex).having(func.count(User.sex)>1).all()
    num = func.count(User.sex).label("num")
    users = db.session.query(User.sex,num).group_by(User.sex).having(num>1).all()

    # limit offset
    # limit取开头n条记录
    users = User.query.limit(2).all()
    # offset跳过n条记录
    users = User.query.offset(1).limit(1).all()
    
    # 条件查询
    # 关系运算
    users = User.query.filter(User.uid==2).all()
    users = User.query.filter(User.uid!=2).all()
    # uid != 2
    users = User.query.filter(User.uid.__ne__(2)).all()
    # uid!=2 and uid < 5 默认多个条件之间是逻辑与的关系
    users = User.query.filter(User.uid!=2, User.uid<5).all()

    # 字符串处理  like
    users = User.query.filter(User.username.like("小%")).all()
    users = User.query.filter(User.username.like('%雨%')).all()

    # in_和notin_
    users = User.query.filter(User.uid.in_([1,2,3])).all()
    # ~ 等价于notin_
    users = User.query.filter(~User.uid.in_([1,2,3])).all()
    users = User.query.filter(User.uid.notin_([1,2,3])).all()

    # 判空
    # sex is null
    users = User.query.filter(User.sex==None).all()
    # is not null
    users = User.query.filter(User.sex != None).all()

    from sqlalchemy import or_, not_
    # 逻辑运算
    # 逻辑或 or_
    users = User.query.filter(or_(User.sex==None,User.uid<5)).all()
    # 逻辑非，not_
    users = User.query.filter(not_(User.sex==None)).all()
    
    print(users)
    return render_template('list.html',**locals())
    
@bp.route("/many/")
def db_union():
    # 不加连接条件的两表联合查询 笛卡尔积
    data = db.session.query(User,Reply).all()
    # 内连接 连接条件User.uid == Reply.uid
    select * from User u,Reply r where u.uid=r.uid
    data = db.session.query(User,Reply).filter(User.uid==Reply.uid).all()

    # 外连接
    data = User.query.outerjoin(Reply).all()
    print(data)
    return "多表联合查询"

App.models

from App.ext import db

class BaseModel:
    # 保存新增或修改的一条记录
    def save(self):
        try:
            db.session.add(self)
            db.session.commit()
            return True
        except Exception as e:
            print(e)
            db.session.rollback()
            return False
    @classmethod
    def save_all(cls,*args):
        try:
            db.session.add_all(args)
            db.session.commit()
            return True
        except Exception as e:
            print(e)
            db.session.rollback()
            return False

    # 删除记录
    def delete(self):
        try:
            db.session.delete(self)
            db.session.commit()
            return True
        except Exception as e:
            print(e)
            db.session.rollback()
            return False


# 必须继承自Model
class User(db.Model,BaseModel):
    uid = db.Column(db.Integer,primary_key=True,autoincrement=True)
    # nullable本列不能为空，必须有值
    username = db.Column(db.String(30),nullable=False)
    password = db.Column(db.String(128),nullable=False)
    # name指的是数据库表中的字段名
    sex = db.Column(db.Boolean,default=False,name='gender')
    __tablename__ = 'user'
    
    def __str__(self):
        return "{}   {}".format(self.uid,self.username)
    
class Reply(db.Model):
    __tablename__ = 'reply'

    rid = db.Column(db.Integer, primary_key=True)
    content = db.Column(db.String(3000))
    uid = db.Column(db.Integer)
    create_time = db.Column(db.DateTime)

list.html

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="UTF-8">
        <title>用户列表</title>
    </head>
    <body>
        <table>
            {% if users is iterable %}
                {% for user in users %}
                <tr>
                    <td>{{ user.username }}</td>
                    <td>{{ user.password }}</td>
                    <td>{{ user.sex }}</td>
                </tr>
                {% endfor %}
            {% endif %}
        
            {% if users is not iterable %}
                <tr>
                    <td>{{ users.username }}</td>
                    <td>{{ users.password }}</td>
                    <td>{{ user.sex }}</td>
                </tr>
            {% endif %}
        </table>
    </body>
</html>