数据库查询构建器
介绍
数据库查询构建器提供了一个简便的接口用来创建执行数据库查询。它可以执行大部分的数据库操作,并且可以在所有支持数据库中工作。
由于Orator底层使用DBAPI包,无需对传递参数进行清理。
DBAPI连接自动配置返回为字典类型,而不是元组。
0.9版本新增:支持使用属性访问字段值:
user = db.table('users').first()
name = user['name']
# is equivalent to
name = user.name
查询
从表中获取所有行
users = db.table('users').get()
for user in users:
print(user['name'])
从表中分块结果
for users in db.table('users').chunk(100):
for user in users:
# ...
从表中取得单行
user = db.table('users').where('name', 'John').first()
print(user['name'])
从单行中获取单列值
user = db.table('users').where('name', 'John').pluck('name')
根据字段获取列表
roles = db.table('roles').lists('title')
该方法将返回角色标题列表。如果传递第二个参数的话,它还会返回一个字典类型。
roles = db.table('roles').lists('title', 'name')
指定选择子句
users = db.table('users').select('name', 'email').get()
users = db.table('users').distinct().get()
users = db.table('users').select('name as user_name').get()
往查询语句追加选择子句
query = db.table('users').select('name')
users = query.add_select('age').get()
使用where操作
users = db.table('users').where('age', '>', 25).get()
Or 语句
users = db.table('users').where('age', '>', 25).or_where('name', 'John').get()
使用Between语句
users = db.table('users').where_between('age', [25, 35]).get()
使用 Not Between
users = db.table('users').where_not_between('age', [25, 35]).get()
使用 In
users = db.table('users').where_in('id', [1, 2, 3]).get()
users = db.table('users').where_not_in('id', [1, 2, 3]).get()
查询Null值
users = db.table('users').where_null('updated_at').get()
Order by, group by 和 having 子句
query = db.table('users').order_by('name', 'desc')
query = query.group_by('count')
query = query.having('count', '>', 100)
users = query.get()
Offset 和 limit
users = db.table('users').skip(10).take(5).get()
users = db.table('users').offset(10).limit(5).get()
Joins
查询构造器还可以实现join语句。
基础join语句
db.table('users')
.join('contacts', 'users.id', '=', 'contacts.user_id')
.join('orders', 'users.id', '=', 'orders.user_id')
.select('users.id', 'contacts.phone', 'orders.price')
.get()
Left join语句
db.table('users').left_join('posts', 'users.id', '=', 'posts.user_id').get()
还可以指定更复杂的join子句:
clause = JoinClause('contacts').on('users.id', '=', 'contacts.user_id').or_on(...)
db.table('users').join(clause).get()
如果你更喜欢在join里使用where子句风格,使用where和orWhere方法。不对比两个列,而是让字段对比一个值:
clause = JoinClause('contacts').on('users.id', '=', 'contacts.user_id').where('contacts.user_id', '>', 5)
db.table('users').join(clause).get()
高级 where
有时,你可以希望创建更高级的where子句,例如"where exists"或者嵌套的分组查询。Orator查询构造器里非常简单。
分组参数
db.table('users')
.where('name', '=', 'John')
.or_where(
db.query().where('votes', '>', 100).where('title', '!=', 'admin')
).get()
以上将会生成如下SQL语句:
SELECT * FROM users WHERE name = 'John' OR (votes > 100 AND title != 'Admin')
Exists 语句
db.table('users').where_exists(
db.table('orders').select(db.raw(1)).where_raw('order.user_id = users.id')
)
以上查询将生成如下 SQL:
SELECT * FROM users
WHERE EXISTS (
SELECT 1 FROM orders WHERE orders.user_id = users.id
)
聚合
查询构造器还支持各种聚合函数,例如count
,max
,min
,avg
和sum
。
users = db.table('users').count()
price = db.table('orders').max('price')
price = db.table('orders').min('price')
price = db.table('orders').avg('price')
total = db.table('users').sum('votes')
原始表达式
有时你需要在查询中原始表达式。这些表达式会注入到查询字符串,所以要小心SQL注入!要创建原始表达式,使用raw()
方法:
db.table('users')
.select(db.raw('count(*) as user_count, status'))
.where('status', '!=', 1)
.group_by('status')
.get()
插入
插入数据到表
db.table('users').insert(email='foo@bar.com', votes=0)
db.table('users').insert({
'email': 'foo@bar.com',
'votes': 0
})
这里有两种方式。原因很简单:为处理不适合作为关键字参数时替代方案。
插入记录到包含自动增长ID
如果一个表包含自动增长ID,使用insert_get_id
插入记录,并获取ID:
id = db.table('users').insert_get_id({
'email': 'foo@bar.com',
'votes': 0
})
插入到记录到表中
db.table('users').insert([
{'email': 'foo@bar.com', 'votes': 0},
{'email': 'bar@baz.com', 'votes': 0}
])
更新
更新记录
db.table('users').where('id', 1).update(votes=1)
db.table('users').where('id', 1).update({'votes': 1})
和insert
语句一样,同样有两种方式。
增加或者减少字典值
db.table('users').increment('votes') # Increment the value by 1
db.table('users').increment('votes', 5) # Increment the value by 5
db.table('users').decrement('votes') # Decrement the value by 1
db.table('users').decrement('votes', 5) # Decrement the value by 5
还可以追加更新字段:
db.table('users').increment('votes', 1, name='John')
删除
删除记录
db.table('users').where('age', '<', 25).delete()
删除所有记录
db.table('users').delete()
Truncate
db.table('users').truncate()
联合
查询构造器提供了简单的"union"两个查询:
first = db.table('users').where_null('first_name')
users = db.table('users').where_null('last_name').union(first).get()
同样还有union_all
方法。
悲观锁
查询构造器为SELECT语句提供了一些函数来实现“悲观锁”。
在SELECT语句使用"共享锁",在查询中使用"shared_lock"方法:
db.table('users').where('votes', '>', 100).shared_lock().get()
SELECT语句中使用"更新锁",在查询中使用lock_for_update
方法:
db.table('users').where('votes', '>', 100).lock_for_update().get()
本译文仅用于学习和交流目的,转载请务必注明文章译者、出处、和本文链接
我们的翻译工作遵照 CC 协议,如果我们的工作有侵犯到您的权益,请及时联系我们。