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) 收藏 举报