flask十二: model基本操作、级联查询

一.Insert

class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20))

 

python manage.py db init # 首次
python manage.py db migrate
python manage.py db upgrade

 

1.单条记录插入

@second_blue.route("/addstudent/")
def add_student():
    import random
    student = mod.Student()
    student.username = "名字%d" % random.randint(1, 100)

    db.session.add(student)
    db.session.commit()

    return student.username

 

2.批量插入

@second_blue.route('/addstudents/')
def add_students():
    students = []
    import random
    for i in range(5):
        student = mod.Student()
        student.username = "名字%d" % random.randint(1, 100)
        students.append(student)

    db.session.add_all(students)
    db.session.commit()

    return 'success'

 

二.查询

1.单条查询

@second_blue.route("/getstudent/<int:id>/")
def get_student(id):
    # student = mod.Student.query.get_or_404(ident=id)  # ident数据库的主键
    # return student.username

    # student = mod.Student.query.get(ident=id)
    # return student and student.username or "not found"

    # student = mod.Student.query.filter_by(id=id).first()
    student = mod.Student.query.filter_by(username='名字51').first()
    return student and student.username or "not found"

 

2.批量查询

@second_blue.route("/getstudents/")
def get_students():
    # students = mod.Student.query.all()
    students = mod.Student.query.filter_by(username='名字51')

    return '****'.join([s.username for s in students])

 

查询语法

语法:类名.query.****

1.单个查询写法,返回BaseQuery对象

  • first(),last()
  • get(),无数据返回None
  • get_or_404()

2.获取查询集

  • all()  ---  特殊查询,返回列表,列表内元素为BaseQuery对象
  • filter
    • 返回BaseQuery对象, __str__输出的是sql语句

    • 条件:
      • 类名.属性名.魔术方法(查询条件)  
      • 类名.属性名 操作运算符 查询条件   

3.查询集筛选

  • offset和limit不区分顺序,都是先执行offset
  • order_by,必须在offset和limit之前
  • paginate分页:django中可以对查询集切片,flask不可以。
  • filter_by:用在级联数据查询,条件只能是-- 字段=值

4.逻辑运算 

and_()

@second_blue.route('/getaddresswithcon/')
def get_address_with_con():
    a_id = request.args.get('a_id', type=int)

    # 使用filter,连续过滤
    address_list = mod.Address.query.filter(mod.Address.a_customer_id.__eq__(1)).filter(mod.Address.a_position.endswith('2'))

    # 使用and,连续过滤
    address_list = mod.Address.query.filter(and_(mod.Address.a_customer_id.__eq__(1)), mod.Address.a_position.endswith('2'))

    return '   '.join([a.a_position for a in address_list])

 

or_(),not_()

@second_blue.route('/getaddresswithcon/')
def get_address_with_con():
    # 使用or_
    address_list = mod.Address.query.filter(or_(mod.Address.a_customer_id.__eq__(1)), mod.Address.a_position.endswith('2'))

    # 使用not_
    address_list = mod.Address.query.filter(not_(mod.Address.a_customer_id.__eq__(1)))

    return '   '.join([a.a_position for a in address_list])

 

 

二.级联数据----外键

(一). 1:M

1.手动获取级联数据--使用many表的外键

models.py,一个客户多个地址

class Customer(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    c_name = db.Column(db.String(16))


class Address(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    a_position = db.Column(db.String(128))
    a_customer_id = db.Column(db.Integer, db.ForeignKey(Customer.id))

 

外键insert:
@second_blue.route('/addaddress/')
def add_address():
    address = mod.Address()
    address.a_position = "地址%d" % random.randrange(1000)
    address.a_customer_id = mod.Customer.query.order_by("id").first().id
    db.session.add(address)
    db.session.commit()
    return address.a_position

 外键查询1: 根据客户地址,查询客户信息,1对1

@second_blue.route('/getcustomer/')
def get_customer():
    a_id = request.args.get('a_id', type=int)
    address = mod.Address.query.get_or_404(a_id)

    customer = mod.Customer.query.get(address.a_customer_id)
    return customer.c_name

 

  外键查询2: 根据客户信息,查询客户地址,1对多
@second_blue.route('/getaddress/')
def get_address():
    c_id = request.args.get('c_id', type=int)
    customer = mod.Customer.query.get_or_404(c_id)

    address_list = mod.Address.query.filter_by(a_customer_id=customer.id)
    return '   '.join([a.a_position for a in address_list])

 

2.使用关系relationship获取级联数据---反向引用

在上面的示例中, 一个客户(one表)对应多个地址(many表):customer表(one表),在address表(many表),外键定义在many表(address表),one表(customer表)不需要定义任何字段。

为了在one表查询方便,也可以在one表,定义一个relationship字段。relationship字段,不会生成数据库表,只是为了方便模型使用。

首先,在one表,添加relationship字段

 外键查询2: 根据客户信息,查询客户地址,1对多----就可以直接使用relationship字段,查询出客户地址列表

@second_blue.route('/getaddress/')
def get_address():
    c_id = request.args.get('c_id', type=int)
    customer = mod.Customer.query.get_or_404(c_id)

    # 根据many表的外键查询
    # address_list = mod.Address.query.filter_by(a_customer_id=customer.id)

    #根据one表的relationship字段查询
    address_list = customer.addresses
    return '   '.join([a.a_position for a in address_list])

 

(二). M:N

 

 

posted on 2020-06-20 00:12  myworldworld  阅读(772)  评论(0)    收藏  举报

导航