orator
安装
pip install orator
文档
Orator文档不是很完整，不过可以结合Laravel和ThinkPHP文档，思想和操作基本一致
（英文）https://laravel.com/docs/5.8/database
（中文）https://www.kancloud.cn/manual/thinkphp5/135176
和Laravel一样，支持三种操作方式：
1、Query
2、Query Builder
3、ORM
一、配置
测试使用的表
CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名', `age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '年龄', `sex` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '性别', `created_at` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间', `updated_at` int(11) NOT NULL DEFAULT '0' COMMENT '修改时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表'
1、设置查询日志，打印sql
import logging logger = logging.getLogger('orator.connection.queries') logger.setLevel(logging.DEBUG) formatter = logging.Formatter('%(elapsed_time)s ms %(query)s') handler = logging.StreamHandler() handler.setFormatter(formatter) logger.addHandler(handler)
2、配置数据库连接参数
from orator import DatabaseManager # 配置支持多个数据库连接，支持读写分离 config = { 'default': 'mysql', 'mysql': { 'read': [ { 'host': 'localhost' } ], 'write': [ { 'host': 'localhost' } ], 'driver': 'mysql', 'database': 'data', 'user': 'root', 'password': '123456', 'prefix': '', 'log_queries': True, # 开启日志 'use_qmark': True, # 使用 ? 替代变量 } } db = DatabaseManager(config)
二、Query
1、读操作
# select return list results = db.select('select * from student where id = ?', [2]) print(results) # ({'id': 2, 'name': '李白', 'age': 24, 'sex': 1, 'created_at': 1575127010, 'updated_at': 1575187008},) 、
2、写操作
# insert return int result = db.insert('insert into student (age, name) values (?, ?)', [23, 'John']) print(result) # 1 # update return int result = db.update('update student set age = 10 where name = ?', ['John']) print(result) # 1 # delete return int result = db.delete('delete from student where id = ?', [132]) print(result) # 1
3、表操作和事务
# statement result = db.statement('drop table student') print(result) # 0 # 执行事务 with db.transaction(): db.table('student').update({'age': 1}) # db.table('posts').delete()
三、Query Builder
1、select
# select all row from table users = db.table('student').get() # 'SELECT * FROM `student`' for user in users: print(user['name']) # 或者 user.name # chunk 每次获取5个 for users in db.table('student').chunk(5): print('==') for user in users: print(user['name']) # 获取single row(None) from a table user = db.table('student').where('name', 'John').first() "SELECT * FROM `student` WHERE `name` = 'John' LIMIT 1" print(user['name']) # 返回single column from a row user = db.table('student').where('name', 'John').pluck('name') "SELECT `name` FROM `student` WHERE `name` = 'John' LIMIT 1" print(user) # 返回 某列值的列表 sql = db.table('student').lists('name') 'SELECT `name` FROM `student`' print(list(sql)) # 返回一个字典列表 roles = db.table('student').lists('age', 'name') print(roles) 'SELECT `age`, `name` FROM `student`' # {'John': 1} # select users = db.table('student').select('name', 'age').get() # 'SELECT `name`, `age` FROM `student`' users = db.table('student').distinct().get() # 'SELECT DISTINCT * FROM `student`' users = db.table('student').select('name as user_name').get() # 'SELECT `name` AS `user_name` FROM `student`' # where users = db.table('student').where('age', '>', 25).get() # 'SELECT * FROM `student` WHERE `age` > 25' users = db.table('student').where('age', '>', 25).or_where('name', '--"John').get() # 'SELECT * FROM `student` WHERE `age` > 25 OR `name` = \'--\\"John\'' users = db.table('student').where_between('age', [25, 35]).get() # 'SELECT * FROM `student` WHERE `age` BETWEEN 25 AND 35' users = db.table('student').where_not_between('age', [25, 35]).get() # 'SELECT * FROM `student` WHERE `age` NOT BETWEEN 25 AND 35' users = db.table('student').where_in('id', [1, 2, 3]).get() # 'SELECT * FROM `student` WHERE `id` IN (1, 2, 3)' users = db.table('student').where_not_in('id', [1, 2, 3]).get() # 'SELECT * FROM `student` WHERE `id` NOT IN (1, 2, 3)' users = db.table('student').where_null('updated_at').get() # 'SELECT * FROM `student` WHERE `updated_at` IS NULL' query = db.table('student').select('age').order_by('age') query.group_by('age') query.having('age', '>', 100) users = query.get() # SELECT `age` FROM `student` GROUP BY `age` HAVING `age` > 100 ORDER BY `age` ASC'
2、 insert
db.table('student').insert(name='Tom', age=12) # "INSERT INTO `student` (`name`, `age`) VALUES ('Tom', 12)" db.table('student').insert({ 'name': 'Tom', 'age': 13 }) # "INSERT INTO `student` (`name`, `age`) VALUES ('Tom', 13)" uid = db.table('student').insert_get_id({ 'name': 'Tom', 'age': 13 }) # "INSERT INTO `student` (`age`, `name`) VALUES (13, 'Tom')" db.table('student').insert([ {'name': 'Tom', 'age': 13}, {'name': 'Tom', 'age': 14} ]) # "INSERT INTO `student` (`age`, `name`) VALUES (13, 'Tom'), (14, 'Tom')"
3、update
db.table('student').where('id', 1).update(age=1) # 'UPDATE `student` SET `age` = 1 WHERE `id` = 1' db.table('student').where('id', 1).update({'age': 1}) # 'UPDATE `student` SET `age` = 1 WHERE `id` = 1'
4、increment /decrement
db.table('student').increment('age') # Increment the value by 1 'UPDATE `student` SET `age` = `age` + 1' db.table('student').increment('age', 5) # Increment the value by 5 'UPDATE `student` SET `age` = `age` + 5' db.table('student').decrement('age') # Decrement the value by 1 'UPDATE `student` SET `age` = `age` - 1' db.table('student').decrement('age', 5) # Decrement the value by 5 'UPDATE `student` SET `age` = `age` - 5' db.table('student').increment('votes', 1, name='John') # TypeError: increment() got an unexpected keyword argument 'name' db.table('student').increment('age', 1, {'name': 'John'}) "UPDATE `student` SET `age` = `age` + 1, `name` = 'John'"
5、delete
db.table('student').where('age', '<', 25).delete() # 'DELETE FROM `student` WHERE `age` < 25' db.table('student').delete() 'DELETE FROM `student`' db.table('student').truncate() 'TRUNCATE `student`'
四、Model
定义model
from orator import Model, SoftDeletes Model.set_connection_resolver(db) class User(SoftDeletes, Model): # 设置真正的表名 __table__ = 'student' # 设置可以批量赋值的字段 __fillable__ = ['name', 'age'] # 设置不允许赋值字段 __guarded__ = ['id'] # 禁用时间自动更新 created_at updated_at __timestamps__ = False # 需要继承 SoftDeletes 使用软删除, 好像不起作用 __dates__ = ['deleted_at']
1、select
users = User.all() 'SELECT * FROM `student`' user = User.find(1) 'SELECT * FROM `student` WHERE `student`.`id` = 1 LIMIT 1' # 如果没有抛出异常 model = User.find_or_fail(1) model = User.where('age', '>', 100).first_or_fail() 'SELECT * FROM `student` WHERE `age` > 100 LIMIT 1' users = User.where('age', '>', 100).take(10).get() 'SELECT * FROM `student` WHERE `age` > 100 LIMIT 10' count = User.where('age', '>', 100).count() 'SELECT COUNT(*) AS aggregate FROM `student` WHERE `age` > 100' # 使用原样查询 users = User.where_raw('id > ? and age = 100', [25]).get() 'SELECT * FROM `student` WHERE id > 25 AND age = 100' # 分次取回数据 for users in User.chunk(100): for user in users: pass
2 insert
# Save新增数据 user = User() user.name = 'John' user.save() "INSERT INTO `student` (`name`) VALUES ('John')" # Create user = User.create(name='John') "INSERT INTO `student` (`name`) VALUES ('John')" # 查询数据，如果不存在则创建一条数据 user = User.first_or_create(name='Jini') "SELECT * FROM `student` WHERE (`name` = 'Jini') LIMIT 1" "INSERT INTO `student` (`name`) VALUES ('Jini')" # 查询数据，如果不存在则创建一个实例 user = User.first_or_new(name='java') "SELECT * FROM `student` WHERE (`name` = 'java') LIMIT 1"
3、 Update
# find ->save user = User.find(14) 'SELECT * FROM `student` WHERE `student`.`id` = 14 LIMIT 1' user.name = 'Foo' user.save() "UPDATE `student` SET `name` = 'Foo' WHERE `id` = 14" # update User.where('age', '>', 100).update(name='Jack') "UPDATE `student` SET `name` = 'Jack' WHERE `age` > 100"
4、 delete
# find -> delete user = User.find(14) # 'SELECT * FROM `student` WHERE `student`.`id` = 14 LIMIT 1' user.delete() # 'DELETE FROM `student` WHERE `id` = 14' User.destroy(15) 'SELECT * FROM `student` WHERE `id` IN (15)' 'DELETE FROM `student` WHERE `id` = 15' User.destroy(16, 17, 18) 'SELECT * FROM `student` WHERE `id` IN (16, 17, 18)' 'DELETE FROM `student` WHERE `id` = 16' 'DELETE FROM `student` WHERE `id` = 17' 'DELETE FROM `student` WHERE `id` = 18' User.where('age', '>', 100).delete() 'DELETE FROM `student` WHERE `age` > 100' # User.where('id', '=', 19).delete() 'DELETE FROM `student` WHERE `id` = 19' # 使用软删除后，查询结果没有删除后的数据 phone = User.find(20) 'SELECT * FROM `student` WHERE (`student`.`id` = 20) AND (`student`.`deleted_at` IS NULL) LIMIT 1' # 包含软删除后的数据 User.with_trashed().where('id', 1).get() 'SELECT * FROM `student` WHERE `id` = 1'
五、Model关联查询
引入模块
from orator import Model Model.set_connection_resolver(db) from orator.orm import ( has_one, belongs_to, has_many, belongs_to_many, has_many_through, morph_to, morph_one, morph_many, morph_to_many, morphed_by_many )
1、一对一
""" student <- -> phone student id - integer name - string phone id - integer number - string student_id - integer """ class Student(Model): # 设置真正的表名 __table__ = 'student' # 指定外键 @has_one('student_id') def phone(self): return Phone class Phone(Model): # 指定本表中的外键字段 @belongs_to('student_id') def student(self): return Student phone = Student.find(20).phone 'SELECT * FROM `student` WHERE `student`.`id` = 20 LIMIT 1' print(phone) 'SELECT * FROM `phones` WHERE `phones`.`student_id` = 20 LIMIT 1' ret = Phone.find(1).student 'SELECT * FROM `phones` WHERE `phones`.`id` = 1 LIMIT 1' print(ret) 'SELECT * FROM `student` WHERE `student`.`id` = 20 LIMIT 1'
2、一对多
""" student <- => comment 表结构和一对一样，查询的时候没有limit 1限制 student id - integer name - string comment id - integer name - string student_id - integer """ class Student(Model): # 设置真正的表名 __table__ = 'student' @has_many('student_id', 'id') def comments(self): return Comment class Comment(Model): # 定义反向关系 @belongs_to def student(self): return Student comments = Student.find(20).comments print(comments) 'SELECT * FROM `student` WHERE `student`.`id` = 20 LIMIT 1' 'SELECT * FROM `comments` WHERE `comments`.`student_id` = 20' print(Comment.find(1).student) 'SELECT * FROM `comments` WHERE `comments`.`id` = 1 LIMIT 1' 'SELECT * FROM `student` WHERE `student`.`id` = 20 LIMIT 1'
3、多对多
""" student <- => roles_student <= -> roles student id - integer name - string roles id - integer name - string roles_student id - integer role_id - integer student_id - integer """ class Student(Model): # 设置真正的表名 __table__ = 'student' @belongs_to_many def roles(self): return Role class Role(Model): @belongs_to_many def students(self): return Student roles = Student.find(20).roles print(roles) 'SELECT * FROM `student` WHERE `student`.`id` = 1 LIMIT 1' """ SELECT `roles`. *, `roles_student`. `student_id` AS `pivot_student_id`, `roles_student`. `role_id` AS `pivot_role_id` FROM `roles` INNER JOIN `roles_student` ON `roles`. `id` = `roles_student`. `role_id` WHERE `roles_student`. `student_id` = 20 """ print(Role.find(1).students) 'SELECT * FROM `roles` WHERE `roles`.`id` = 1 LIMIT 1' """ SELECT `student`. *, `roles_student`. `role_id` AS `pivot_role_id`, `roles_student`. `student_id` AS `pivot_student_id` FROM `student` INNER JOIN `roles_student` ON `student`. `id` = `roles_student`. `student_id` WHERE `roles_student`. `role_id` = 1 """
4、跨中间表一对多
""" countries <- => students <- => roles countries id - integer name - string students id - integer name - string country_id - integer roles id - integer title - string student_id - integer """ class Student(Model): # 设置真正的表名 __table__ = 'student' @has_many def roles(self): return Role class Role(Model): @belongs_to def students(self): return Student class Country(Model): __table__ = 'countrys' # 定义反向关系 @has_many_through(Student, 'country_id', 'student_id') def roles(self): return Role print(Country.find(1).roles) 'SELECT * FROM `countrys` WHERE `countrys`.`id` = 1 LIMIT 1' """ SELECT `roles`.*, `student`.`country_id` FROM `roles` INNER JOIN `student` ON `student`.`id` = `roles`.`student_id` WHERE `student`.`country_id` = 1 """
六、Model多态关联
1、多态一对一关联
和一对一关系相比，多了一个type类型字段，标记多个表
# 一个模型属于一个模型 """ Staff <- -> Photo Order <- -> Photo staff id - integer name - string orders id - integer price - integer photos id - integer path - string imageable_id - integer imageable_type - string """ from orator.orm import morph_one, morph_to class Photo(Model): @morph_to def imageable(self): return class Staff(Model): @morph_one('imageable') def photo(self): return Photo class Order(Model): # 重写多态关联字段 __morph_name__ = 'order' @morph_one('imageable') def photo(self): return Photo staff = Staff.find(1) 'SELECT * FROM `staffs` WHERE `staffs`.`id` = 1 LIMIT 1' print(staff.photo) """ SELECT * FROM `photos` WHERE `photos`.`imageable_id` = 1 AND `photos`.`imageable_type` = 'staffs' LIMIT 1 """ photo = Photo.find(1) 'SELECT * FROM `photos` WHERE `photos`.`id` = 1 LIMIT 1'
2、多态一对多关联
没有和多态一对多相比，没有limit
from orator.orm import morph_to, morph_many """ # 一个模型属于多个模型 Staff <- => Photo Order <- => Photo staff id - integer name - string orders id - integer price - integer photos id - integer path - string imageable_id - integer imageable_type - string """ class Photo(Model): @morph_to def imageable(self): return class Staff(Model): @morph_many('imageable') def photos(self): return Photo class Order(Model): # 重写多态关联字段 __morph_name__ = 'order' @morph_many('imageable') def photos(self): return Photo staff = Staff.find(1) 'SELECT * FROM `staffs` WHERE `staffs`.`id` = 1 LIMIT 1' for photo in staff.photos: pass """ SELECT * FROM `photos` WHERE `photos`.`imageable_id` = 1 AND `photos`.`imageable_type` = 'staffs' photo = Photo.find(1) 'SELECT * FROM `photos` WHERE `photos`.`id` = 1 LIMIT 1' imageable = photo.imageable print(imageable) # 'SELECT * FROM `staffs` WHERE `staffs`.`id` = 1 LIMIT 1' """
3、多态多对多
""" posts id - integer name - string videos id - integer name - string tags id - integer name - string taggables tag_id - integer taggable_id - integer taggable_type - string """ from orator.orm import morph_to_many, morphed_by_many class Tag(Model): @morphed_by_many('taggable') def posts(self): return Post @morphed_by_many('taggable') def videos(self): return Video class Post(Model): @morph_to_many('taggable') def tags(self): return Tag class Video(Model): @morph_to_many('taggable') def tags(self): return Tag print(Video.find(1).tags) 'SELECT * FROM `videos` WHERE `videos`.`id` = 1 LIMIT 1' """ SELECT `tags`.*, `taggables`.`taggable_id` AS `pivot_taggable_id`, `taggables`.`tag_id` AS `pivot_tag_id` FROM `tags` INNER JOIN `taggables` ON `tags`.`id` = `taggables`.`tag_id` WHERE `taggables`.`taggable_id` = 1 AND `taggables`.`taggable_type` = 'videos' """
七、其他操作
1、动态属性
from orator import Model, SoftDeletes Model.set_connection_resolver(db) class Student(Model): # 设置真正的表名 __table__ = 'student' @has_many('student_id', 'id') def comments(self): return Comment class Comment(Model): # 定义反向关系 @belongs_to def student(self): return Student print(Student.find(1).comments.count()) 'SELECT * FROM `student` WHERE `student`.`id` = 1 LIMIT 1' 'SELECT * FROM `comments` WHERE `comments`.`student_id` = 1' # 2 # 增加条件查询 print(Student.find(1).comments().where('id', '>', 1).first()) 'SELECT * FROM `student` WHERE `student`.`id` = 1 LIMIT 1' 'SELECT * FROM `comments` WHERE `comments`.`student_id` = 1 AND `id` > 1 LIMIT 1'
2、预加载数据
from orator import Model, SoftDeletes Model.set_connection_resolver(db) class Student(Model): # 设置真正的表名 __table__ = 'student' @has_many('student_id', 'id') def comments(self): return Comment class Comment(Model): # 定义反向关系 @belongs_to def student(self): return Student for comment in Comment.all(): print(comment.student.name) """ 'SELECT * FROM `comments`' 'SELECT * FROM `student` WHERE `student`.`id` = 1 LIMIT 1' 'SELECT * FROM `student` WHERE `student`.`id` = 1 LIMIT 1' 'SELECT * FROM `student` WHERE `student`.`id` = 2 LIMIT 1' """ # 预加载 解决 N + 1 次查询问题 for comment in Comment.with_('student').get(): print(comment.student.name) """ 'SELECT * FROM `comments`' 'SELECT * FROM `student` WHERE `student`.`id` IN (1, 2)' """ # 增加查询条件 Comment.with_({ 'student': Student.query().where('id', '>', 1) }).get() """ 'SELECT * FROM `comments`' 'SELECT * FROM `student` WHERE `student`.`id` IN (1, 2) AND `id` > 1' """ # load加载 comments = Comment.all() comments.load('student') 'SELECT * FROM `comments`' 'SELECT * FROM `student` WHERE `student`.`id` IN (1, 2)' # 添加条件 comments.load({ 'student': Student.query().where('name', 'like', '%foo%') }) "SELECT * FROM `student` WHERE `student`.`id` IN (1, 2) AND `name` like '%foo%'"
3、插入关联数据
Model.set_connection_resolver(db) class Student(Model): # 设置真正的表名 __table__ = 'student' @has_many('student_id', 'id') def comments(self): return Comment class Comment(Model): __fillable__ = ['number'] __timestamps__ = False # 定义反向关系 @belongs_to def student(self): return Student # 插入一条关联数据 comment = Comment(number='A new comment') student = Student.find(1) 'SELECT * FROM `student` WHERE `student`.`id` = 1 LIMIT 1' comment = student.comments().save(comment) "INSERT INTO `comments` (`number`, `student_id`) VALUES ('A new comment', 1)" # 插入多条关联数据 comments = [ Comment(number='Comment 1'), Comment(number='Comment 2'), Comment(number='Comment 3') ] student = student.find(1) student.comments().save_many(comments) """ "INSERT INTO `comments` (`number`, `student_id`) VALUES ('Comment 1', 1)" "INSERT INTO `comments` (`number`, `student_id`) VALUES ('Comment 2', 1)" "INSERT INTO `comments` (`number`, `student_id`) VALUES ('Comment 3', 1)" """ # create方法 student = Student.find(1) student.comments().create(number="123") 'SELECT * FROM `student` WHERE `student`.`id` = 1 LIMIT 1' "INSERT INTO `comments` (`number`, `student_id`) VALUES ('123', 1)"
4、一对多关系维护
from orator import Model, SoftDeletes Model.set_connection_resolver(db) class Student(Model): # 设置真正的表名 __table__ = 'student' @has_many('student_id', 'id') def comments(self): return Comment class Comment(Model): __fillable__ = ['number'] __timestamps__ = False # 定义反向关系 @belongs_to def student(self): return Student # belongs_to 关系更新 comment = Comment.find(1) student = Student.find(22) # # 添加关系 comment.student().associate(student) comment.save() """ 'SELECT * FROM `comments` WHERE `comments`.`id` = 1 LIMIT 1' 'SELECT * FROM `student` WHERE `student`.`id` = 22 LIMIT 1' 'UPDATE `comments` SET `student_id` = 22 WHERE `id` = 1' """ # 解除关系 comment.student().dissociate() comment.save() 'UPDATE `comments` SET `student_id` = NULL WHERE `id` = 1'
5、多对多关系维护
from orator import Model, SoftDeletes Model.set_connection_resolver(db) class Student(Model): # 设置真正的表名 __table__ = 'student' @belongs_to_many def roles(self): return Role class Role(Model): @belongs_to_many def students(self): return Student user = Student.find(1) role = Role.find(1) # 添加关联 user.roles().attach(role) """ 'SELECT * FROM `student` WHERE `student`.`id` = 1 LIMIT 1' 'SELECT * FROM `roles` WHERE `roles`.`id` = 1 LIMIT 1' 'INSERT INTO `roles_student` (`role_id`, `student_id`) VALUES (1, 1)' """ # 解除关联 # user.roles().detach(1) 'DELETE FROM `roles_student` WHERE `student_id` = 1 AND `role_id` IN (1)' # 同步关系 user.roles().sync([1, 2, 3]) """ 'SELECT `role_id` FROM `roles_student` WHERE `student_id` = 1' 'DELETE FROM `roles_student` WHERE `student_id` = 1 AND `role_id` IN (NULL)' 'INSERT INTO `roles_student` (`role_id`, `student_id`) VALUES (1, 1)' 'INSERT INTO `roles_student` (`role_id`, `student_id`) VALUES (2, 1)' 'INSERT INTO `roles_student` (`role_id`, `student_id`) VALUES (3, 1)' """
6、修改时间字段格式
class User(Model): def get_date_format(self): return 'DD-MM-YY'
7、scope重用查询
class Student(Model): # 设置真正的表名 __table__ = 'student' @scope def popular(self, query): return query.where('age', '>', 100) @scope def women(self, query, sex='womem'): return query.where('sex', sex) users = Student.popular().women().order_by('created_at').get() "SELECT * FROM `student` WHERE (`age` > 100) AND (`sex` = 'womem') ORDER BY `created_at` ASC"
8、accessor、mutator
from orator import Model, SoftDeletes from orator.orm import scope, accessor, mutator Model.set_connection_resolver(db) # scope重用查询 class Student(Model): # 设置真正的表名 __table__ = 'student' __timestamps__ = False # 设置序列化字段 __hidden__ = ['created'] __visible__ = ['name', 'age', 'is_man'] # 添加额外字段 __appends__ = ['is_man'] # 类型自动转换 # 支持 int, float, str, bool, dict, list. __casts__ = { 'age': 'str' } sex_map = { 0: '女', 1: '男' } # 访问器 @accessor def get_sex(self): sex = self.get_raw_attribute('sex') sex_map = { 0: '女', 1: '男' } return sex_map.get(sex) # 设置器 @mutator def set_sex(self, value): sex_map_reverse = {v: k for k, v in self.sex_map.items()} self.set_raw_attribute('sex', sex_map_reverse.get(value, 1)) @accessor def is_man(self): return self.get_raw_attribute('sex') == 1 # users = Student.popular().women().order_by('created_at').get() "SELECT * FROM `student` WHERE (`age` > 100) AND (`sex` = 'womem') ORDER BY `created_at` ASC" # print(Student.find(1).first_name) 'SELECT * FROM `student` WHERE `student`.`id` = 1 LIMIT 1' # John student = Student.find(1) # student.set_first_name = 'JAcK' # student.save() print(student.get_sex) student.set_sex = '男' print(type(student.age)) # <class 'str'> student.save() print(student.to_json()) {"name": "1", "age": "0"} print(student.serialize()) {'name': '1', 'age': '0'} # 添加额外字段后输出 {'name': '1', 'age': '0', 'is_man': True}
9、Pagination分页
users = db.table('student').paginate(15, 2) 'SELECT COUNT(*) AS aggregate FROM `student`' 'SELECT * FROM `student` LIMIT 15 OFFSET 15' print(dir(users)) """ [ 'count', 'current_page', 'current_page_resolver', 'first_item', 'get_collection', 'has_more_pages', 'has_pages', 'is_empty', 'items', 'last_item', 'last_page', 'next_page', 'per_page', 'previous_page', 'resolve_current_page', 'serialize', 'to_dict', 'to_json', 'total' ] """ # 转换为json数据 print(users.to_json()) """ [ {"id": 35, "name": "John", "age": 0, "sex": 0, "created_at": 0, "updated_at": 0, "deleted_at": null, "country_id": null}, {"id": 36, "name": "John", "age": 0, "sex": 0, "created_at": 0, "updated_at": 0, "deleted_at": null, "country_id": null} ] """ some_users = Student.where('age', '>', 100).paginate(15, 2) 'SELECT COUNT(*) AS aggregate FROM `student` WHERE `age` > 100' 'SELECT * FROM `student` WHERE `age` > 100 LIMIT 15 OFFSET 15' # simple-pagination ret = Student.simple_paginate(15, 2) 'SELECT * FROM `student` LIMIT 16 OFFSET 15' print(dir(ret)) """ ['count', 'current_page', 'current_page_resolver', 'first_item', 'get_collection', 'has_more_pages', 'has_pages', 'is_empty', 'items', 'last_item', 'next_page', 'per_page', 'previous_page', 'resolve_current_page', 'serialize', 'to_dict', 'to_json'] """ users = db.table('student').paginate(15, 2) 'SELECT COUNT(*) AS aggregate FROM `student`' 'SELECT * FROM `student` LIMIT 15 OFFSET 15' print(dir(users)) """ [ 'count', 'current_page', 'current_page_resolver', 'first_item', 'get_collection', 'has_more_pages', 'has_pages', 'is_empty', 'items', 'last_item', 'last_page', 'next_page', 'per_page', 'previous_page', 'resolve_current_page', 'serialize', 'to_dict', 'to_json', 'total' ] """ # 转换为json数据 print(users.to_json()) """ [ {"id": 35, "name": "John", "age": 0, "sex": 0, "created_at": 0, "updated_at": 0, "deleted_at": null, "country_id": null}, {"id": 36, "name": "John", "age": 0, "sex": 0, "created_at": 0, "updated_at": 0, "deleted_at": null, "country_id": null} ] """ some_users = Student.where('age', '>', 100).paginate(15, 2) 'SELECT COUNT(*) AS aggregate FROM `student` WHERE `age` > 100' 'SELECT * FROM `student` WHERE `age` > 100 LIMIT 15 OFFSET 15' # simple-pagination ret = Student.simple_paginate(15, 2) 'SELECT * FROM `student` LIMIT 16 OFFSET 15' print(dir(ret)) """ ['count', 'current_page', 'current_page_resolver', 'first_item', 'get_collection', 'has_more_pages', 'has_pages', 'is_empty', 'items', 'last_item', 'next_page', 'per_page', 'previous_page', 'resolve_current_page', 'serialize', 'to_dict', 'to_json'] """
