[Python知识库]系统学习数据库day18

排序/修改/删除

    def exercise_order1(self):
        """ 将三年级所有学生的语文成绩按照从高到低进行排序 """
        stmt = select(StudentGrade).where(StudentGrade.student_id == Student.id).where(StudentGrade.course_id == Course.id)\
            .where(Course.course_name == '语文').order_by(StudentGrade.score.desc())
        querySet = self.session.execute(stmt).mappings().all()
        for item in querySet:
            print(item)
    def exercise_order2(self):
        """ 将三年级所有学生的期末考试总成绩按照从高到低进行排序 """
        stmt = select(Student, func.sum(StudentGrade.score).label('score_sum'))\
            .where(Student.id == StudentGrade.student_id)\
            .group_by(Student.id).order_by(desc("score_sum"))
        querySet = self.session.execute(stmt).mappings().all()
        for item in querySet:
            print(item)

    # 修改
    def update_one_data(self):
        # 第二个是主键
        stu_obj = self.session.get(Student, 2)
        stu_obj.phone_no = "13877778888"
        stu_obj.age = stu_obj.age + 1
        self.session.add(stu_obj)
        self.session.commit()

    def update_more_data(self):
        stmt = update(Student).where(Student.id <=5).values(age=Student.age+1)
        res = self.session.execute(stmt)
        self.session.commit()
        print("行数", res.rowcount)
    # 删除
    def delete_one_data(self):
        # stu_obj = self.session.get(Student, 3)
        # print(stu_obj.id)
        # self.session.delete(stu_obj)
        # self.session.commit()
        stmt = delete(Student).where(Student.id==3)
        res = self.session.execute(stmt)
        self.session.commit()
        print("行数", res.rowcount)
    def delete_more_data(self):
        stmt = delete(StudentGrade).where(StudentGrade.score < 60)
        res = self.session.execute(stmt)
        self.session.commit()
        print("行数", res.rowcount)

Flask

http://127.0.0.1:5000/
127.0.0.1 本机
5000 端口号
@app.route("/") 这个就是首页

from flask import Flask

app = Flask(__name__)

@app.route("/index")
def hello():
    return "Hello Flask"
if __name__ == '__main__':
    app.run()

常见路由

from flask import Flask, request, make_response, redirect, abort
app = Flask(__name__)

@app.route('/')
# http://127.0.0.1:5000/?name=eric&age=19
def hello_world():
    # get 请求 一般在地址里面的都是get
    uname = request.args.get("name")
    print("uname", uname)
    age = request.args.get("age")
    print("age", age)
    return "hello"
# http://127.0.0.1:5000/user/list/77
@app.route('/user/list/<int:page>', methods=["GET"])
def user_list(page=1):

    return "这是用户列表的第{}页数据".format(page)

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

网易云新闻

from datetime import datetime

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# 数据库连接的配置
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:root@127.0.0.1/studydatabase1?charset=utf8'
app.config['SQLALCHEMY_ECHO'] = True

db = SQLAlchemy(app)

class News(db.Model):
    """ 新闻模型 """
    __tablename__ = 'news'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False, comment='标题')
    img_url = db.Column(db.String(200), nullable=False, comment='主图地址')
    content = db.Column(db.String(2000), nullable=False, comment='新闻内容')
    is_valid = db.Column(db.Boolean, default=True, comment='逻辑删除')
    is_top = db.Column(db.Boolean, default=False, comment='是否置顶')
    created_at = db.Column(db.DateTime, default=datetime.now(), comment='创建时间')
    updated_at = db.Column(db.DateTime, default=datetime.now(), comment='最后修改时间')
    news_type = db.Column(db.Enum('本地', '百家', '娱乐', '军事'), comment='新闻类别')
@app.route("/")
def hello():
    db.create_all()
    return "Hello Flask"
if __name__ == '__main__':
    app.run()

app.py

from datetime import datetime

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# 数据库连接的配置
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:root@127.0.0.1/studydatabase1?charset=utf8'
app.config['SQLALCHEMY_ECHO'] = True

db = SQLAlchemy(app)

class News(db.Model):
    """ 新闻模型 """
    __tablename__ = 'news'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False, comment='标题')
    img_url = db.Column(db.String(200), nullable=False, comment='主图地址')
    content = db.Column(db.String(2000), nullable=False, comment='新闻内容')
    is_valid = db.Column(db.Boolean, default=True, comment='逻辑删除')
    is_top = db.Column(db.Boolean, default=False, comment='是否置顶')
    created_at = db.Column(db.DateTime, default=datetime.now(), comment='创建时间')
    updated_at = db.Column(db.DateTime, default=datetime.now(), comment='最后修改时间')
    news_type = db.Column(db.Enum('本地', '百家', '娱乐', '军事'), comment='新闻类别')
@app.route("/")
def hello():
    username = "张三"
    age = 80
    list_user = [
        {'username': '张三', 'age': 32, 'address': '北京'},
        {'username': '李四', 'age': 22},
        {'username': '王五', 'age': 32, 'address': '北京'},
        {'username': '王文', 'age': 22}
    ]
    return render_template('index.html',
                           age=age,
                           username =username, 
                           list_user = list_user)
if __name__ == '__main__':
    app.run()

index.html

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>模板变量和循环标签</title>
  <style type="text/css">
    .red {
      color: #ff0000;
    }
    .blue {
      color: #0000ff;
    }
  </style>
</head>
<body>
  <h1 style="color: #f00">新闻标题</h1>
  <p>{{username}}</p>
  <p>{{age}}</p>
  {% for item in list_user %}
{#      <p>{{item}}</p>#}
      <p>{{item['username']}}</p>
  {% endfor %}


</body>
</html>

首页展示

from datetime import datetime

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# 数据库连接的配置
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:root@127.0.0.1/studydatabase1?charset=utf8'
app.config['SQLALCHEMY_ECHO'] = True

db = SQLAlchemy(app)

class News(db.Model):
    """ 新闻模型 """
    __tablename__ = 'news'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False, comment='标题')
    img_url = db.Column(db.String(200), nullable=False, comment='主图地址')
    content = db.Column(db.String(2000), nullable=False, comment='新闻内容')
    is_valid = db.Column(db.Boolean, default=True, comment='逻辑删除')
    is_top = db.Column(db.Boolean, default=False, comment='是否置顶')
    created_at = db.Column(db.DateTime, default=datetime.now(), comment='创建时间')
    updated_at = db.Column(db.DateTime, default=datetime.now(), comment='最后修改时间')
    news_type = db.Column(db.Enum('本地', '百家', '娱乐', '军事'), comment='新闻类别')
@app.route("/")
def hello():
    new_list = News.query.filter(News.is_valid==True, News.is_valid==True).all()
    for new in new_list:
        print(new)
    return render_template('index.html',new_list=new_list)
if __name__ == '__main__':
    app.run()

模板

<!DOCTYPE html>
<html>

<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta content="width=device-width, initial-scale=1.0" name="viewport" />
    <link rel="stylesheet" href="/static/css/index.css">
    <title>首页</title>
</head>

<body>
    <!-- 页面头部 -->
    <header>
        <nav>
            <ul>
                <li><a href="/">首页</a></li>
                <li><a href="/cat/本地/">本地</a></li>
                <li><a href="/cat/百家/">百家</a></li>
                <li><a href="/cat/娱乐/">娱乐</a></li>
                <li><a href="/cat/军事/">军事</a></li>
            </ul>
        </nav>
    </header>
    <!-- /页面头部 -->
    <!-- 新闻内容部分 -->
    <article>
       {% for item in new_list %}
        <div class="list-news">
            <div class="img-content">
                <img src="{{ item.img_url }}" alt="">
            </div>
            <div class="right-content">
                <p><a href="#">{{item.title  }}</a></p>
                <small>{{ item.created_at }}</small>
            </div>
        </div>
       {% endfor %}

    </article>
    <!-- /新闻内容部分 -->
</body>

</html>