数据库查询构建器

2.3

4.0 2.3

介绍

数据库查询构建器提供了一个简便的接口用来创建执行数据库查询。它可以执行大部分的数据库操作，并且可以在所有支持数据库中工作。

由于Orator底层使用DBAPI包，无需对传递参数进行清理。

DBAPI连接自动配置返回为字典类型，而不是元组。

0.9版本新增：支持使用属性访问字段值：

user = db.table('users').first()

name = user['name']
# is equivalent to
name = user.name

0.9版本变更：返回行映射为集合以便与ORM保持一致行为。

查询

从表中获取所有行

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 协议，如果我们的工作有侵犯到您的权益，请及时联系我们。

原文地址：https://learnku.com/docs/masonite/2.3/or...

译文地址：https://learnku.com/docs/masonite/2.3/or...