Flask 中的数据库操作

参考

from .import db
import datetime


class Users(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(32), index=True, nullable=False)
    email = db.Column(db.String(32), unique=True)
    ctime = db.Column(db.DateTime, default=datetime.datetime.now)
    extra = db.Column(db.Text, nullable=True)

    __table_args__ = (
        db.UniqueConstraint('id', 'name', name='uix_id_name'),
        db.Index('ix_id_name', 'name', 'email'),
    )

#!/usr/bin/env python
from flask import Blueprint
login = Blueprint('login', __name__, url_prefix='/login')
from ..models import *
from .middlewares import *


@login.route("/")
def login():
    # ############# 执行ORM操作 #############
    obj1 = Users(name="tom")
    db.session.add(obj1)
    # 批量增加
        # db.session.add_all([
        #     Users(name="wupeiqi1"),
        #     Users(name="alex"),
        #     Hosts(name="c1.com"),
        # ])
        # 删除
        # db.session.query(Users).filter(Users.id > 2).delete()
        # 修改
        # db.session.query(Users).filter(Users.id > 0).update({"name": "099"})
        # db.session.query(Users).filter(Users.id > 0).update({Users.name: Users.name + "xxx"}, synchronize_session=False)
        # db.session.query(Users).filter(Users.id > 0).update({"age": Users.age + 1}, synchronize_session="evaluate")
        # ################ 查询 ################
        '''
        r1 = db.session.query(Users).all()
        r2 = db.session.query(Users.name.label('xx'), Users.age).all()
        r3 = db.session.query(Users).filter(Users.name == "alex").all()
        r4 = db.session.query(Users).filter_by(name='alex').all()
        r5 = db.session.query(Users).filter_by(name='alex').first()
        r6 = db.session.query(Users).filter(db.text("id<:value and name=:name")).params(value=224, name='fred').order_by(Users.id).all()
        r7 = db.session.query(Users).from_statement(db.text("SELECT * FROM users where name=:name")).params(name='ed').all()
        '''

        # 　条件
        # ret = db.session.query(Users).filter_by(name='alex').all()
        # ret = db.session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()
        # ret = db.session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()  # 1-3之间
        # # in查询
        # ret = db.session.query(Users).filter(Users.id.in_([1, 3, 4])).all()
        # ret = db.session.query(Users).filter(~Users.id.in_([1, 3, 4])).all()  # 取非
        # ret = db.session.query(Users).filter(Users.id.in_(db.session.query(Users.id).filter_by(name='eric'))).all()
        # # and_  or_
        # ret = db.session.query(Users).filter(db.and_(Users.id > 3, Users.name == 'eric')).all()
        # ret = db.session.query(Users).filter(db.or_(Users.id < 2, Users.name == 'eric')).all()
        # ret = db.session.query(Users).filter(
        #     db.or_(
        #         Users.id < 2,
        #         db.and_(Users.name == 'eric', Users.id > 3),
        #         Users.extra != ""
        #     )).all()
        #
        # # 通配符
        # ret = db.session.query(Users).filter(Users.name.like('e%')).all()
        # ret = db.session.query(Users).filter(~Users.name.like('e%')).all()
        #
        # # 限制
        # ret = db.session.query(Users)[1:2]
        #
        # # 排序
        # ret = db.session.query(Users).order_by(Users.name.desc()).all()
        # ret = db.session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()
        #
        # # 分组
        # ret = db.session.query(Users).group_by(Users.extra).all()
        # ret = db.session.query(
        #     db.func.max(Users.id),
        #     db.func.sum(Users.id),
        #     db.func.min(Users.id)).group_by(Users.name).all()
        #
        # ret = session.query(
        #     db.func.max(Users.id),
        #     db.func.sum(Users.id),
        #     db.func.min(Users.id)).group_by(Users.name).having(db.func.min(Users.id) > 2).all()
        #
        # # 连表
        # ret = db.session.query(Hobby, Person).filter(Person.hobby_id == Hobby.id).all()
        # # 内链接
        # ret = db.session.query(Person).join(Hobby).all()
        # # 外连接
        # ret = db.session.query(Person).join(Hobby, isouter=True).all()
        #
        # # 组合
        # q1 = db.session.query(Person.name).filter(Person.id > 2)
        # q2 = db.session.query(Hobby.caption).filter(Hobby.id < 2)
        # ret = q1.union(q2).all()
        #
        # q1 = db.session.query(Person.name).filter(Person.id > 2)
        # q2 = db.session.query(Hobby.caption).filter(Hobby.nid < 2)
        # ret = q1.union_all(q2).all()  # 这一句的区别
        #
        # # 原生sql
        # cursor = db.session.execute('insert into users(name) values(:value)', params={"value": 'wupeiqi'})
        # print(cursor.lastrowid)
        #
        # # relationship
        #
        # db.session.add_all([
        #     Hobby(caption='乒乓球'),
        #     Hobby(caption='羽毛球'),
        #     Person(name='张三', hobby_id=3),
        #     Person(name='李四', hobby_id=4),
        # ])
        #
        # # 使用relationship正向查询
        # v = db.session.query(Person).first()
        # print(v.name)
        # print(v.hobby.caption)
        #
        # # 使用relationship反向查询
        # v = db.session.query(Hobby).first()
        # print(v.caption)
        # print(v.pers)
        # 使用relationship增加
        db.session.add(Hobby(**{'caption': '球'}))
        v = db.session.query(Hobby).first()
        v.pers = [Person(name="xxx"), Person(name='xxx')]
        db.session.add(v)
        # 其他方法增加
        db.session.add(Person(name="xxx2", hobby=Hobby(**{'caption': '球'})))
        # relationship各种用法，多对多查询出来的是一个对象列表
       print(user_obj.role.person[0].name)
        # 多对多修改
        user_obj.role.person = db.session.query(Person).filter(Person.id.in_([1, 2])).all()
        db.session.add(user_obj)

        # 多对多增加
        # db.session.add(Roles(name="普通用户"))
        # obj = db.session.query(Roles).filter_by(name="普通用户").first()
        # obj.person = [Person(name='删除用户'), Person(name='修改用户')]
        # db.session.add(obj)

        # 一对多增加用户
        # db.session.add(Users(name="李晓青", email="lixq@cloudcc.com", password="123456", role_id=1))
        # 一对多修改用户
        db.session.query(Users).filter_by(name="李晓青").update({'role_id': 2})
        """
        user_obj.role_id = 2
        db.session.add(user_obj)
        """
        # 一对多连表增加
        # role为relationship名
        obj = Users(name="李晓", email="lix@cloudcc.com", password="123456", role=Roles(name="来宾"))
        db.session.add(obj)
        # 实在无解，多表联查，然后用点赋值
        """
        obj = db.session.query(Users, Roles).filter(Users.role_id == Roles.id).first()
        obj.Users.name = "honghong"
        obj.Roles.name = "honghong"
        db.session.add(obj)
        """

        # 关联子查询
        # subqry = db.session.query(db.func.count(Server.id).label("sid")).filter(Server.id == Group.id).correlate(
        #     Group).as_scalar()
        # result = db.session.query(Group.name, subqry)



    # 提交事务
    db.session.commit()
    # 关闭session
    db.session.close()