Flask

最新推荐文章于 2024-09-03 17:22:28 发布

黄佳耀

最新推荐文章于 2024-09-03 17:22:28 发布

阅读量134

点赞数

分类专栏： python 文章标签： python flask

本文链接：https://blog.csdn.net/weixin_46884853/article/details/113888324

版权

python 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

视图

#   @Software:          PyCharm
#   @FileName:          interface.py
#   @Time :             2021/2/19  17:34
#   @Author:            HuangHaiPing
from flask import Flask, request, jsonify, session, views, url_for, redirect, render_template
import json



app = Flask(__name__)

# 设置session秘钥
app.secret_key = "asdmkaldklghkladhglkhal"

get_dict = {
    "name": "黄海平",
    "age": 18
}


@app.route("/")
def index():
    return 'index'

@app.route("/next_url")
def next_url():
    route_url = url_for("index", page=1, code=200)
    return redirect(route_url)


@app.route('/first', methods=['POST'])
def first():
    # my_json = request.get_data()
    #
    # new_my_json = json.loads(my_json.decode('utf-8'))
    # get_name = get_dict.get('name')
    # print(my_json, get_name)
    # print(new_my_json)

    my_json = request.get_json()
    print(my_json)
    return "good"


@app.route("/login", methods=['POST'])
def login():
    # 获取json数据
    login_info = request.get_json()
    print(login_info)
    username = login_info.get('username')
    password = login_info.get('password')

    if not all([username, password]):
        return jsonify(meg="缺少参数")

    if username == "admin123" and password == "123456":
        session['username'] = username
        return jsonify(msg="登录成功", status_code="200")
    else:
        return jsonify(msg="账号或者密码错误")

@app.route("/check_login")
def check_login():
    username = session.get('username')
    if username is not  None:
        return jsonify(msg="已登入")
    else:
        return jsonify(msg="出错了")


# 退出
@app.route("/logout")
def logout():
    # 清除session
    session.clear()
    return jsonify(msg="退出登入")

# 先登录才能访问的装饰器   闭包
def login_required(func):
    def wrapper(*args, **kwargs):
        username = request.args.get("username")
        if username:
            return func(*args, **kwargs)
        else:
            return "请先登录"
    return wrapper


@login_required
def profile():
    return "t123"

# 在类中使用装饰器
class ProfileView(views.View):
    # 在类中使用装饰器   login_required为装饰器名字
    decorators = [login_required]
    def dispatch_request(self):
        return "个人中心"

app.add_url_rule("/profileview", view_func=ProfileView.as_view("profileview"))

# 类视图
class TestView(views.View):
    # dispatch_request必须重写
    def dispatch_request(self):
        return "类视图"

    def demo(self):
        return "hello"


# 返回json数据
class JsonView(views.View):
    def get_response(self):
        # raise XXXX 父类中定义了类似的，子类中必须重写，否则会报错
        raise NotImplementedError()

    def dispatch_request(self):
        response = self.get_response()
        return jsonify(response)


class SonJsonView(JsonView):
    def get_response(self):
        info = {
            "username": "黄海平",
            "age": 18,
            "sex": "男"
        }
        return info



# 返回公共变量
class BasicView(views.View):
    def __init__(self):
        # python2写法
        # super(BasicView, self).__init__()
        # python3写法
        super().__init__()
        self.context = {
         "name": "黄海平",
         "age": 18,
         "height": 175
        }


class LoginView(BasicView):
    def dispatch_request(self):
        return render_template("login.html", **self.context)


class RegisterView(BasicView):
    def dispatch_request(self):
        return render_template("register.html", **self.context)


# 基于调度方法的视图
class BasicMethodView(views.MethodView):
    def get(self, error=None):
        return render_template("login1.html", error=error)

    def post(self):
        name = request.form.get("name")
        password = request.form.get("password")
        print(type(name), password)

        if name == "admin" and password == "123456":
            return "登录成功"
        else:
            return self.get("账号密码错误")



# 添加路由规则
app.add_url_rule('/profile', view_func=profile)
# 类视图路由规则
app.add_url_rule("/list", view_func=TestView.as_view("demo"))
app.add_url_rule("/json", view_func=SonJsonView.as_view("son"))
app.add_url_rule("/Login", view_func=LoginView.as_view("Login"))
app.add_url_rule("/register", view_func=RegisterView.as_view("register"))
app.add_url_rule("/login1", view_func=BasicMethodView.as_view("login1"))


if __name__ == '__main__':
    app.run(debug=True)

蓝图

books.py

#   @Software:          PyCharm
#   @FileName:          books.py
#   @Time :             2021/2/20  18:05
#   @Author:            HuangHaiPing
from flask import Blueprint,url_for


books = Blueprint("books", __name__, url_prefix="/books")

@books.route("/")
def index():
    return "books 首页"


@books.route("/detail/<bid>")
def book_detail(bid):
    print(url_for('index'))
    return "book detail {}".format(bid)

news.py（蓝图设置子域名）

#   @Software:          PyCharm
#   @FileName:          news.py
#   @Time :             2021/2/20  18:05
#   @Author:            HuangHaiPing
from flask import Blueprint

# 设置子域名news.subdomain.com:8000
news = Blueprint("news", __name__, subdomain="news")


@news.route("/")
def index():

    return "news"

BluePrintDemo.py


#   @Software:          PyCharm
#   @FileName:          BluePrintDemo.py
#   @Time :             2021/2/20  17:54
#   @Author:            HuangHaiPing
from flask import Flask
from blueprints.news import news
from blueprints.books import books


app = Flask(__name__)
# 设置配置文件
app.config['SERVER_NAME'] = 'subdomain.com:8000'


# 注册蓝图
app.register_blueprint(news)
app.register_blueprint(books)


@app.route("/")
def index():
    return "index"


if __name__ == '__main__':
    app.run(debug=True)

Flask蓝图寻找静态文件&url_for

<link src="{{ url_for('static', filename='xxx.css') }}">

数据库 sqlalchemy 使用

#   @Software:          PyCharm
#   @FileName:          database.py
#   @Time :             2021/2/21  15:47
#   @Author:            HuangHaiPing

from sqlalchemy import create_engine

HOSTNAME = "127.0.0.1"

DATABASE = "demosql"

USERNAME = "root"

PASSWORD = "root"

PORT = "3306"

# 创建数据库引擎
# dialect+driver://username:password@host:port/database?charset=utf8
# 安装mysqlconnector   可以写成 "mysql+mysqlconnector://{}:{}@{}:{}/{}".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
DB_URL = "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)

engine = create_engine(DB_URL)

# 创建连接
with engine.connect() as conn:
    # 执行Sql语句   原生sql
    res = conn.execute("select * from demo")
    # 查询一条
    # print(res.fetchone())

    print(res.fetchall())

ORM: Object Relationship Mapping,对象关系映射,通过ORM我们可以通过类的⽅式去操作数据库,⽽不⽤写原⽣的SQL语句。通过把表映射成类,把⾏作为实例,把字段作为属性,ORM在执⾏对象操作时候最终还是会把对应的操作转换为数据库原⽣语句。

要使⽤ORM来操作数据库，⾸先需要创建⼀个类来与对应的表进⾏映射。

#   @Software:          PyCharm
#   @FileName:          FLASK-ORM.py
#   @Time :             2021/2/21  16:27
#   @Author:            HuangHaiPing
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

HOSTNAME = "127.0.0.1"

DATABASE = "demosql"

USERNAME = "root"

PASSWORD = "root"

PORT = "3306"

# 创建数据库引擎
# dialect+driver://username:password@host:port/database?charset=utf8
# 安装mysqlconnector   可以写成 "mysql+mysqlconnector://{}:{}@{}:{}/{}".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
DB_URL = "mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)

engine = create_engine(DB_URL)

# 都要继承这个函数生成的基类
Base = declarative_base(engine)


# ORM操作数据库
class User(Base):
    # 定义表名
    __tablename__ = "students"

    # 定义字段 Column('id', ForeignKey('other.id'), primary_key=True, autoincrement='ignore_fk')
    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    name = Column(String(50), nullable=False)
    gender = Column(Integer, default=1, comment="1为男，2为女")


    # 定义这个方法下面查询就可以直接显示信息
    def __str__(self):
        return "id: {}, name: {}, gender: {}".format(self.id, self.name, self.gender)


# 将模型映射导数据库中
# Base.metadata.create_all()

# user = User(name="黄海平", gender=1)
# user1 = User(name="周小雨", gender=2)
# print(user.name)

# 保存到数据库中
Session = sessionmaker(bind=engine)
# sessionmaker中 有__call__方法 可以把类变成方法使用
session = Session()

# 添加
# session.add(user)
# session.add_all([user, user1])


info = {
    'name': "黄海平",
    'gender': 1
}


def add_data(name, gender):
    user = User(name=name, gender=gender)
    print(user.name)

    # # 保存到数据库中
    # Session = sessionmaker(bind=engine)
    # # sessionmaker中 有__call__方法 可以把类变成方法使用
    # session = Session()

    # 添加
    session.add(user)
    session.commit()


# for i in range(0, 10):
#     add_data(name=info.get("name") + str(i), gender=info.get("gender"))


def search_data():
    # 查询
    # data = session.query(User).all()

    # filter ,  filter_by筛选
    # data = session.query(User).filter(User.id == 1).all()
    # data = session.query(User).filter_by(id=1).all()


    # for item in data:
    #
    #     # User类中没写__str__方法前
    #     # print(item.id, end="   ")
    #     # print(item.name)
    #
    #     # 写了__str__后
    #     print(item)

    # 查询name为黄海平的第一条记录
    data = session.query(User).filter_by(name="黄海平0").first()
    print(data)


    # 查询id为4的记录
    data_id = session.query(User).get(4)
    print(data_id)



def delete_data():
    # 先查询再删除
    data = session.query(User).filter(User.name=="黄海平0").first()
    session.delete(data)
    session.commit()
    print(data)


def update_data():
    # 先查询再修改
    data = session.query(User).filter_by(name="黄海平0").first()
    data.name = "修改后的名字"
    session.commit()
    print(data)



# search_data()
update_data()
# delete_data()

sqlalchemy 常用数据类型

#   @Software:          PyCharm
#   @FileName:          data_types.py
#   @Time :             2021/2/22  16:11
#   @Author:            HuangHaiPing
from sqlalchemy import create_engine, Column, Integer, String, Float, DECIMAL, Boolean, Enum, Date, DateTime, Time
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime
from sqlalchemy.dialects.mysql import LONGTEXT



USERNAME = 'root'

PASSWORD = 'root'

HOST = '127.0.0.1'

PORT = 3306

DATABASE = 'demosql'

DB_URL = "mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME, PASSWORD, HOST, PORT, DATABASE)

engine = create_engine(DB_URL)

Base = declarative_base(engine)


class Users(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    name = Column(String(50), nullable=False)
    # price = Column(Float)
    # 定点类型  精度比float高   DECIMAL(总共多少位数，保留几位小数)
    price = Column(DECIMAL(20, 5))
    is_delete = Column(Boolean, comment="true为已删除，false为未删除", default=False)     # 数据库中显示0为false,1为true
    # 枚举类型
    gender = Column(Enum("男", "女"), default="男")
    # 日期 Date   datetime(1998, 05, 01, 12, 12, 12)
    birthday = Column(Date, default=datetime.date(datetime.today()))
    # 加入时间 Datetime
    join_time = Column(DateTime, default=datetime(1998, 5, 1, 12, 12, 12))
    times = Column(Time, default=datetime.time(datetime.today()))
    # LONGTEXT长文本类型
    article = Column(LONGTEXT)


# 删除数据库中的表
Base.metadata.drop_all()
Base.metadata.create_all()

Session = sessionmaker(bind=engine)
session = Session()

user = Users(name="黄海平", price=12.446518785315456789, is_delete=True)

session.add(user)
session.commit()

常用的聚合函数

#   @Software:          PyCharm
#   @FileName:          func.py
#   @Time :             2021/2/22  20:37
#   @Author:            HuangHaiPing
from sqlalchemy import create_engine, Column, Integer, String, DECIMAL
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import func

HOSTNAME = "127.0.0.1"

DATABASE = "demosql"

USERNAME = "root"

PASSWORD = "root"

PORT = "3306"

# 创建数据库引擎
# dialect+driver://username:password@host:port/database?charset=utf8
# 安装mysqlconnector   可以写成 "mysql+mysqlconnector://{}:{}@{}:{}/{}".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
DB_URL = "mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)

engine = create_engine(DB_URL)

# 都要继承这个函数生成的基类
Base = declarative_base(engine)


class FuncSQL(Base):
    __tablename__ = "funcsql"

    id = Column(Integer, autoincrement=True, primary_key=True, nullable=False)
    title = Column(String(20))
    price = Column(DECIMAL(10, 5))

# Base.metadata.drop_all()
# Base.metadata.create_all()

Session = sessionmaker(bind=engine)
session = Session()


# import random
#
# for i in range(0, 50):
#     func_sql = FuncSQL(title="title{}".format(i), price=random.randint(0, 50))
#     session.add(func_sql)
#
# session.commit()


# 聚合函数
# func.count：统计⾏的数量。
# func.avg：求平均值。
# func.max：求最⼤值。
# func.min：求最⼩值。
# func.sum：求和。
result = session.query(func.count(FuncSQL.id)).first()
print(result[0])

result = session.query(func.avg(FuncSQL.price)).first()
print(result[0])

result = session.query(func.max(FuncSQL.price)).first()
print(result[0])

result = session.query(func.min(FuncSQL.price)).first()
print(result[0])

result = session.query(func.sum(FuncSQL.price)).first()
print(result[0])

过滤条件

过滤是数据提取的⼀个很重要的功能，以下对⼀些常⽤的过滤条件进⾏解释，并且这些过滤条件都是只能通过filter⽅法实现的

equals

session.query(User).filter(User.name == 'ed')

not equals

session.query(User).filter(User.name != 'ed')

like

session.query(User).filter(User.name.like("%ed%"))

in

session.query(User).filter(User.name.in_(['ed','wendy','jack']))
 # 同时，in也可以作⽤于⼀个Query
session.query(User).filter(User.name.in_(session.query(User.name).filter(User.name.
like('%ed%'))))

not in

session.query(User).filter(~User.name.in_(['ed','wendy','jack']))

is null (指数据库中为null的没有赋值开辟空间的记录，并不是为空的记录)

session.query(User).filter(User.name==None)
# 或者是
session.query(User).filter(User.name.is_(None))

is not null

session.query(User).filter(User.name != None)
# 或者是
session.query(User).filter(User.name.isnot(None))

and

from sqlalchemy import and_
session.query(User).filter(and_(User.name=='ed',User.fullname=='Ed Jones'))
# 或者是传递多个参数
session.query(User).filter(User.name=='ed',User.fullname=='Ed Jones')
# 或者是通过多次filter操作
session.query(User).filter(User.name=='ed').filter(User.fullname=='Ed Jones')

or

 from sqlalchemy import or_
session.query(User).filter(or_(User.name=='ed',User.name=='wendy'))

外键 (ForeignKey(“表名.字段名”)) 数据库引擎innoDB

外键约束有以下⼏项：

RESTRICT：⽗表数据被删除，会阻⽌删除。默认就是这⼀项。
NO ACTION：在MySQL中，同RESTRICT。
CASCADE：级联删除。
外键
64
SET NULL：⽗表数据被删除，⼦表数据会设置为NULL。

#   @Software:          PyCharm
#   @FileName:          flask-foreign.py
#   @Time :             2021/2/23  9:26
#   @Author:            HuangHaiPing
from sqlalchemy import Column, Integer, String, create_engine, Text, ForeignKey, not_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker



HOSTNAME = "127.0.0.1"

DATABASE = "demosql"

USERNAME = "root"

PASSWORD = "root"

PORT = "3306"

DB_URL = "mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)

engine = create_engine(DB_URL)

Base = declarative_base(engine)


'''
外键约束有以下⼏项：
1. RESTRICT：⽗表数据被删除，会阻⽌删除。默认就是这⼀项。
2. NO ACTION：在MySQL中，同RESTRICT。
3. CASCADE：级联删除。
4. SET NULL：⽗表数据被删除，⼦表数据会设置为NULL。
'''


class ClassName(Base):
    __tablename__ = "classname"

    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    class_name = Column(String(50), nullable=False)

    def __repr__(self):
        return "id: {}, class_name: {}".format(self.id, self.class_name)


class StudentName(Base):
    __tablename__ = "studentname"
    id = Column(Integer, primary_key=True, nullable=False, autoincrement=True)
    student_name = Column(String(50), nullable=False)
    student_info = Column(Text)

    # 外键字段    foreignkey("表名.字段")   4. SET NULL：⽗表数据被删除，⼦表数据会设置为NULL。
    class_id = Column(Integer, ForeignKey('classname.id', ondelete='SET NULL'))

    def __str__(self):
        return "id: {}, student_name: {}, student_info: {}, class_id: {}".format(self.id, self.student_name, self.student_info, self.class_id)

#
# Base.metadata.drop_all()
# Base.metadata.create_all()


Session = sessionmaker(bind=engine)
session = Session()

# datas = session.query(ClassName).all()
# datas = session.query(ClassName).filter(not_(ClassName.id == 2))
#
#
# for data in datas:
#     print(data)

# 删除父表中的元素， 子表会变成null
# data = session.query(ClassName).filter(ClassName.class_name == "进阶班").all()
#
# def delelte_data(data):
#
#     session.delete(data)
#     session.commit()
#
# for i in data:
#     delelte_data(i)


# 外键查询
data = session.query(ClassName).first()
class_id = data.id

data = session.query(StudentName).get(class_id)
print(data)

data = session.query(StudentName).filter(ClassName.id == StudentName.class_id).first()
print(data)

表关系

一对多

#   @Software:          PyCharm
#   @FileName:          one_to_more.py
#   @Time :             2021/2/23  12:01
#   @Author:            HuangHaiPing
from sqlalchemy import Column, Integer, String, create_engine, Text, ForeignKey, not_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship



HOSTNAME = "127.0.0.1"

DATABASE = "demosql"

USERNAME = "root"

PASSWORD = "root"

PORT = "3306"

DB_URL = "mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)

engine = create_engine(DB_URL)

Base = declarative_base(engine)

# 一对多关系
class Article(Base):
    __tablename__ = "articles"
    id = Column(Integer, autoincrement=True, primary_key=True, nullable=False)
    title = Column(String(50))
    content = Column(Text)

    uid = Column(Integer, ForeignKey("author.id", ondelete='SET NULL'))

    # author = relationship('Author')

    def __repr__(self):
        return "id: {}, title: {}, content: {}, uid: {}".format(self.id, self.title, self.content, self.uid)


# 一个用户对应多篇文章
class Author(Base):
    __tablename__ = "author"
    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    name = Column(String(50), nullable=False)

    # 对应的模型  添加反向访问属性，article中的relationship()可以不写
    article = relationship('Article', backref="author")

    def __str__(self):
        return "id: {}, name: {}".format(self.id, self.name)


# Base.metadata.drop_all()
# Base.metadata.create_all()

session = sessionmaker(bind=engine)()

# 根据文章查询作者
# data = session.query(Article).all()
# for i in data:
#     #id: 1, name: 黄海平
#     #id: 1, name: 黄海平
#     print(i.author)

# 根据作者查询文章
data = session.query(Author).all()
for i in data:
    print(i.article)


# 添加单条数据
# author = Author(name="周小雨")
#
# article = Article(title="Python", content="sjdhfjkshfkjsfh")
#
# article.author = author
#
# session.add(article)
# session.commit()


# 添加多条   author表中只加一个， article中加多条（同一个作者）
author = Author(name="刘德华")
article1 = Article(title="天王", content="确实是天王")
article2 = Article(title="歌手", content="确实是歌手")
article3 = Article(title="演员", content="打王者巨坑")

article1.author = author
article2.author = author
article3.author = author

session.add(article1)
session.add(article2)
session.add(article3)
session.commit()

多对多中间表用Table创建， relationship中多加secondary=中间表

#   @Software:          PyCharm
#   @FileName:          more_by_more.py
#   @Time :             2021/2/24  16:48
#   @Author:            HuangHaiPing
from sqlalchemy import Column, Integer, String, create_engine, Text, ForeignKey, not_, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship



HOSTNAME = "127.0.0.1"

DATABASE = "demosql"

USERNAME = "root"

PASSWORD = "root"

PORT = "3306"

DB_URL = "mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)

engine = create_engine(DB_URL)

Base = declarative_base(engine)

# 多对多
# 中间表
teacher_classes = Table(
    # 表名
    "teacher_classes",
    Base.metadata,
    # 相当于定义两张表的外键
    Column("teacher_id",Integer, ForeignKey("teacher.id")),
    Column("class_id", Integer, ForeignKey("classes.id"))
)


# 班级表
class Classes(Base):
    __tablename__ = "classes"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))

    # secondary 与中间表建立关系
    teacher = relationship("Teachers", backref="classes", secondary=teacher_classes)

    def __repr__(self):
        return "班级： {}".format(self.name)


# 老师表
class Teachers(Base):
    __tablename__ = "teacher"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))

    def __repr__(self):
        return "老师： {}".format(self.name)


# Base.metadata.drop_all()
Base.metadata.create_all()

session = sessionmaker(bind=engine)()

teacher1 = Teachers(name="黄海平")
teacher2 = Teachers(name="周小雨")

class1 = Classes(name="基础班")
class2 = Classes(name="进阶班")

# classes是Teachers模型中的relationship中的classes
# teacher1.classes.append(class1)
# teacher2.classes.append(class2)
#
# teacher1.classes.append(class2)
# teacher2.classes.append(class1)



# session.add_all([teacher1, teacher2])
# session.commit()


# 查询老师对应的班级
teachers = session.query(Teachers).all()

for teacher in teachers:
    #老师： 黄海平:[班级： 基础班, 班级： 进阶班]
    #老师： 周小雨:[班级： 基础班, 班级： 进阶班]
    print(str(teacher) + ':' + str(teacher.classes))


# 查询班级对应的老师
classes = session.query(Classes).all()

for class_one in classes:
    # 班级： 基础班: [老师： 黄海平, 老师： 周小雨]
    # 班级： 进阶班: [老师： 黄海平, 老师： 周小雨]
    print(str(class_one) + ":" + str(class_one.teacher))

数据库排序

#   @Software:          PyCharm
#   @FileName:          flask-orm-order.py
#   @Time :             2021/2/24  17:48
#   @Author:            HuangHaiPing
from sqlalchemy import Column, Integer, String, create_engine, Text, ForeignKey, not_, Table, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.dialects.mysql import LONGTEXT, TEXT


HOSTNAME = "127.0.0.1"

DATABASE = "demosql"

USERNAME = "root"

PASSWORD = "root"

PORT = "3306"

DB_URL = "mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)

engine = create_engine(DB_URL)

Base = 

List item

declarative_base(engine)

from datetime import datetime

class Article(Base):
    __tablename__ = "article"
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50))
    content = Column(LONGTEXT)
    time = Column(DateTime, default=datetime.now())

    def __repr__(self):
        return "id： {}， 名字： {}, {}, {}".format(self.id, self.title, self.content, self.time)


    __mapper_args__ = {
        # 设置查询默认以id倒叙查询
        "order_by": id.desc()
    }



# Base.metadata.drop_all()
Base.metadata.create_all()

session = sessionmaker(bind=engine)()

# article = Article(title="Python", content="shfgsjhfshjg")
# session.add(article)
# session.commit()

# order_by是默认升序
# articles = session.query(Article).order_by(Article.time).all()
# for article in articles:
#     print(article)
#
# # 倒叙 desc() 或者前面加负号
# # articles = session.query(Article).order_by(Article.time.desc()).all()
# articles = session.query(Article).order_by(-Article.time).all()
# for article in articles:
#     print(article)


# 模型中添加__mapper_args__ 不必使用order_by查询
articles = session.query(Article).all()
for article in articles:
    print(article)

limit、offset和切⽚(代码承接数据库排序)

####### limit：可以限制每次查询的时候只查询⼏条数据。
####### offset：可以限制查找数据的时候过滤掉前⾯多少条。
####### 切⽚：可以对Query对象使⽤切⽚操作，来获取想要的数据。

# limit
articles = session.query(Article).limit(3).all()
for article in articles:
    print(article)


# 切片   左闭右开
articles = session.query(Article).all()[2: 4]
for article in articles:
    print(article)

高级查询&子查询

group_by:根据某个字段进⾏分组。⽐如想要根据性别进⾏分组，来统计每个分组分别有多少⼈。

having是对查找结果进⼀步过滤。⽐如只想要看未成年⼈的数量，那么可以⾸先对年龄进⾏分组统计⼈数，然后再对分组进⾏having过滤。

#   @Software:          PyCharm
#   @FileName:          flask-query.py
#   @Time :             2021/2/25  18:54
#   @Author:            HuangHaiPing
from sqlalchemy import Column, Integer, String, create_engine, Text, ForeignKey, not_, and_, Enum
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship



HOSTNAME = "127.0.0.1"

DATABASE = "demosql"

USERNAME = "root"

PASSWORD = "root"

PORT = "3306"

DB_URL = "mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)

engine = create_engine(DB_URL)

Base = declarative_base(engine)

class People(Base):
    __tablename__ = "people"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))
    sex = Column(Enum("男", "女"), default="男")
    age = Column(Integer)


    def __repr__(self):
        return "id: {}, name: {}, sex: {}, age: {}".format(self.id, self.name, self.sex, self.age)

class SonQeury(Base):
    __tablename__ = "son_query"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))
    city = Column(String(50))
    sex = Column(Enum("男", "女"), default="男")
    age = Column(Integer)


    def __repr__(self):
        return "id: {}, name: {}, city: {}, sex: {}, age: {}".format(self.id, self.name, self.city, self.sex, self.age)



# Base.metadata.drop_all()
Base.metadata.create_all()

session = sessionmaker(bind=engine)()

import random
# for i in range(0, 10):
#     people = People(name="黄海平"+str(random.randint(0, 10)), age=random.randint(0,100))
#     session.add(people)
#
#
# session.commit()

# 按照性别来分组， 求人数
from sqlalchemy import func
sex = session.query(People.sex, func.count(People.id)).group_by(People.sex).all()
# [('男', 7), ('女', 3)]
print(sex)

# having是对查找结果进⼀步过滤。⽐如只想要看未成年⼈的数量，那么可以⾸先对年龄进⾏分组统计⼈数，然后再对分组进⾏having过滤。
age = session.query(People.age, func.count(People.id)).group_by(People.age).having(People.age<50).all()
# [(30, 1), (31, 1)]
print(age)


# 子查询   两个查询的嵌套
# 查询和黄海平相同年龄和城市的人
info = session.query(SonQeury).filter(and_(SonQeury.name == "黄海平")).first()
infos = session.query(SonQeury).filter(and_(SonQeury.city == info.city, SonQeury.age == info.age)).all()
for i in infos:
    print(i)

join查询分为两种，⼀种是inner join，另⼀种是outer join。默认的是inner。 join，如果指定left join或者是right join则为outer join。如果想要查询User及其对应的Address，则可以通过以下⽅式来实现。

for u,a in session.query(User,Address).filter(User.id==Address.user_id).all():
	print(u)
	print(a)

# 这是通过普通⽅式的实现，也可以通过join的⽅式实现，更加简单
for u,a in session.query(User,Address).join(Address).all():
	print(u)
	print(a)

# 当然，如果采⽤outerjoin，可以获取所有user，⽽不⽤在乎这个user是否有address对象，并且outerjoin默认为左外查询：
for instance in session.query(User,Address).outerjoin(Address).all():
	print(instance)

别名：当多表查询的时候，有时候同⼀个表要⽤到多次，这时候⽤别名就可以⽅便的解决命名冲突的问题了：

from sqlalchemy.orm import aliased
adalias1 = aliased(Address)
adalias2 = aliased(Address)
for username,email1,email2 in session.query(User.name,adalias1.email_address,adalias2.email_address).join(adalias1).join(adalias2).all():
	print(username,email1,email2)

⼦查询:sqlalchemy也⽀持⼦查询，⽐如现在要查找⼀个⽤户的⽤户名以及该⽤户的邮箱地址数量。要满⾜这个需求，可以在⼦查询中找到所有⽤户的邮箱数（通过group by合并同⼀⽤户），然后再将结果放在⽗查询中进⾏使⽤：

from sqlalchemy.sql import func
# 构造⼦查询
stmt = session.query(Address.user_id.label('user_id'),func.count(*).label('address_count')).group_by(Address.user_id).subquery()
# 将⼦查询放到⽗查询中

for u,count in session.query(User,stmt.c.address_count).outerjoin(stmt,User.id==stmt.c.user_id).order_by(User.id):
	print u,count

# 从上⾯我们可以看到，⼀个查询如果想要变为⼦查询，则是通过subquery()⽅法实现，变成⼦查询后，通过⼦查询.c属性来访问查询出来的列。以上⽅式只能查询某个对象的具体字段，如果要查找整个实体，则需要通过aliased⽅法
stmt = session.query(Address)
adalias = aliased(Address,stmt)
for user,address in session.query(User,stmt).join(stmt,User.addresses):
	print user,address

flask-sqlalchemy插件

#   @Software:          PyCharm
#   @FileName:          flask-sqlalchemy-test.py
#   @Time :             2021/2/25  20:16
#   @Author:            HuangHaiPing
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

HOSTNAME = "127.0.0.1"

DATABASE = "flask-sqlalchemy"

USERNAME = "root"

PASSWORD = "root"

PORT = "3306"

DB_URL = "mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)

app = Flask(__name__)



app.config['SQLALCHEMY_DATABASE_URI'] = DB_URL
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# 写在配置下面
db = SQLAlchemy(app)


class User(db.Model):
    __tablename__ = "user"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(50))
    age = db.Column(db.Integer)

    def __repr__(self):
        return "id: {}, name: {}, age: {}".format(self.id, self.name, self.age)


class Article(db.Model):
    __tablename__ = "article"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(50))
    content = db.Column(db.Text)

    uid = db.Column(db.Integer, db.ForeignKey("user.id"))

    user = db.relationship('User', backref="article")

    def __repr__(self):
        return "id: {}, title: {}, content:{}, uid: {}".format(self.id, self.title, self.content, self.uid)


# db.drop_all()
# 映射到数据库中
db.create_all()

# 添加数据
user = User(name="黄海平")
article = Article(title="PYTHON", content="xsafgfdshsh")

article.user = user

# db.session.add(article)
# db.session.commit()

# 查询数据
# user_info = User.query.all()
user_info = db.session.query(User).all()
print(user_info)

# 排序
order_user = User.query.order_by(User.id.desc()).all()
print(order_user)


# 删除
# user_info = User.query.filter(User.name=="黄海平").first()
# db.session.delete(user_info)
# db.session.commit()

@app.route("/")
def index():
    return "首页"


if __name__ == '__main__':
    app.run(debug=True)

flask-script

#   @Software:          PyCharm
#   @FileName:          flask-script-test.py
#   @Time :             2021/2/25  21:11
#   @Author:            HuangHaiPing
from flask_script import Manager
from flask_sqlalchemy_test import app

manage = Manager(app)


@manage.command
def index():
    return "hello"

# 命令行接收参数
@manage.option('-n', '--name', dest='name')
@manage.option('-u', '--url', dest='url')
def test(name, url):
    return "hello", name, url


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

在cmd中运行index方法 python xxx.py 方法名

E:\Flask项目\flask框架学习\代码\复习
(flask框架学习-K29yexAV) $ python flask_script_test.py index
hello

在cmd中传参

python flask_script_test.py test -n(或者写–name) 黄海平 -u www.baidu.com
(‘hello’, ‘黄海平’, ‘www.baidu.com’)

E:\Flask项目\flask框架学习\代码\复习
(flask框架学习-K29yexAV) $ python flask_script_test.py test -n 黄海平 -u www.baidu.com
('hello', '黄海平', 'www.baidu.com')

flask_script小案例

配置文件config.py

#   @Software:          PyCharm
#   @FileName:          config.py
#   @Time :             2021/3/1  14:55
#   @Author:            HuangHaiPing
HOSTNAME = "127.0.0.1"

DATABASE = "flask-sqlalchemy"

USERNAME = "root"

PASSWORD = "root"

PORT = "3306"

DB_URL = "mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)

SQLALCHEMY_DATABASE_URI = DB_URL

SQLALCHEMY_TRACK_MODIFICATIONS = False

主入口文件flask_script_test_project.py

#   @Software:          PyCharm
#   @FileName:          flask_script_test_project.py
#   @Time :             2021/3/1  14:59
#   @Author:            HuangHaiPing
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import config

app = Flask(__name__)

# 加载配置文件config
app.config.from_object(config)

db = SQLAlchemy(app)

class AdminUser(db.Model):
    __tablename__ = "admin_user"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(50))
    email = db.Column(db.String(50))

    def __repr__(self):
        return "id : {}, name : {}, email : {}".format(self.id, self.name, self.email)


db.create_all()


if __name__ == '__main__':
    app.run(debug=True)

manage_test.py

#   @Software:          PyCharm
#   @FileName:          manage_test.py
#   @Time :             2021/3/1  15:08
#   @Author:            HuangHaiPing
from flask_script import Manager
from flask_script_test_project import app, AdminUser, db

manage = Manager(app)

@manage.option("-n", "--name", dest="name")
@manage.option("-e", "--email", dest="email")
def create_user(name, email):
    user = AdminUser(name=name, email=email)
    db.session.add(user)
    db.session.commit()


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

在cmd中运行

E:\Flask项目\flask框架学习\代码\复习
(flask框架学习-K29yexAV) $ python manage_test.py create_user --name 黄海平 --email 123@qq.com

解决相互导入的问题

flask_script_test_project.py(导入db，model)

#   @Software:          PyCharm
#   @FileName:          flask_script_test_project.py
#   @Time :             2021/3/1  14:59
#   @Author:            HuangHaiPing
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import config
from models_test import AdminUser
from exts import db

app = Flask(__name__)

# 加载配置文件config
app.config.from_object(config)

# 解决model互相导入的办法
db.init_app(app)
# db = SQLAlchemy(app)

if __name__ == '__main__':
    app.run(debug=True)

exts.py

#   @Software:          PyCharm
#   @FileName:          exts.py
#   @Time :             2021/3/1  16:23
#   @Author:            HuangHaiPing
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

models_test.py(导入db)

#   @Software:          PyCharm
#   @FileName:          models.py
#   @Time :             2021/3/1  16:20
#   @Author:            HuangHaiPing
from exts import db


class AdminUser(db.Model):
    __tablename__ = "admin_user"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(50))
    email = db.Column(db.String(50))

    def __repr__(self):
        return "id : {}, name : {}, email : {}".format(self.id, self.name, self.email)


# db.create_all()

migrate对数据库的修改

#   @Software:          PyCharm
#   @FileName:          migrate_test.py
#   @Time :             2021/3/1  16:39
#   @Author:            HuangHaiPing
from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand
from flask_script_test_project import app
from exts import db

# 想映射那个模型导入那个模型
from models_test import AdminUser

manage = Manager(app)

Migrate(app, db)

manage.add_command("db", MigrateCommand)

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

之后再命令行中依次输入以下3条命令：(加入需要修改字段等，只需执行后面两条命令)

$ python migrate_test.py db init
$ python migrate_test.py db migrate
$ python migrate_test.py db upgrade

表单验证

黄佳耀

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Flask

视图# encoding: utf8# @Software: PyCharm# @FileName: interface.py# @Time : 2021/2/19 17:34# @Author: HuangHaiPingfrom flask import Flask, request, jsonify, session, views, url_for, redirect, render_temp
复制链接

扫一扫