Documentation (master)
Query Builder
Query Builder¶
Introduction¶
The database query builder provides a fluent interface to create and run database queries. It can be used to perform most database operations in your application, and works on all supported database systems.
Note
Since Orator uses DBAPI packages under the hood, there is no need to clean parameters passed as bindings.
Note
The underlying DBAPI connections are automatically configured to return dictionaries rather than the default tuple representation.
New in version 0.9: The returned rows also supports accessing the column values by attribute:
user = db.table('users').first()
name = user['name']
# is equivalent to
name = user.name
Changed in version 0.9: The returned rows will now be Collections to be more consistent with the behavior of the ORM.
Selects¶
Retrieving all row from a table¶
users = db.table('users').get()
for user in users:
print(user['name'])
Chunking results from a table¶
for users in db.table('users').chunk(100):
for user in users:
# ...
Retrieving a single row from a table¶
user = db.table('users').where('name', 'John').first()
print(user['name'])
Retrieving a single column from a row¶
user = db.table('users').where('name', 'John').pluck('name')
Retrieving a list of column values¶
roles = db.table('roles').lists('title')
This method will return a list of role titles. It can return a dictionary if you pass an extra key parameter.
roles = db.table('roles').lists('title', 'name')
Specifying a select clause¶
users = db.table('users').select('name', 'email').get()
users = db.table('users').distinct().get()
users = db.table('users').select('name as user_name').get()
Adding a select clause to an existing query¶
query = db.table('users').select('name')
users = query.add_select('age').get()
Using where operators¶
users = db.table('users').where('age', '>', 25).get()
Or statements¶
users = db.table('users').where('age', '>', 25).or_where('name', 'John').get()
Using Where Between¶
users = db.table('users').where_between('age', [25, 35]).get()
Using Where Not Between¶
users = db.table('users').where_not_between('age', [25, 35]).get()
Using Where In¶
users = db.table('users').where_in('id', [1, 2, 3]).get()
users = db.table('users').where_not_in('id', [1, 2, 3]).get()
Using Where Null to find records with null values¶
users = db.table('users').where_null('updated_at').get()
Order by, group by and having¶
query = db.table('users').order_by('name', 'desc')
query = query.group_by('count')
query = query.having('count', '>', 100)
users = query.get()
Offset and limit¶
users = db.table('users').skip(10).take(5).get()
users = db.table('users').offset(10).limit(5).get()
Joins¶
The query builder can also be used to write join statements.
Basic join statement¶
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 statement¶
db.table('users').left_join('posts', 'users.id', '=', 'posts.user_id').get()
You can also specify more advance join clauses:
clause = JoinClause('contacts').on('users.id', '=', 'contacts.user_id').or_on(...)
db.table('users').join(clause).get()
If you would like to use a “where” style clause on your joins, you may use the where and orWhere methods on a join. Instead of comparing two columns, these methods will compare the column against a value:
clause = JoinClause('contacts').on('users.id', '=', 'contacts.user_id').where('contacts.user_id', '>', 5)
db.table('users').join(clause).get()
Advanced where¶
Sometimes you may need to create more advanced where clauses such as “where exists” or nested parameter groupings. It is pretty easy to do with the Orator query builder
Parameter grouping¶
db.table('users') \
.where('name', '=', 'John') \
.or_where(
db.query().where('votes', '>', 100).where('title', '!=', 'admin')
).get()
The query above will produce the following SQL:
SELECT * FROM users WHERE name = 'John' OR (votes > 100 AND title != 'Admin')
Exists statement¶
db.table('users').where_exists(
db.table('orders').select(db.raw(1)).where_raw('order.user_id = users.id')
)
The query above will produce the following SQL:
SELECT * FROM users
WHERE EXISTS (
SELECT 1 FROM orders WHERE orders.user_id = users.id
)
Aggregates¶
The query builder also provides a variety of aggregate methods, `
such as count, max, min, avg, and 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')
Raw expressions¶
Sometimes you may need to use a raw expression in a query.
These expressions will be injected into the query as strings, so be careful not to create any SQL injection points!
To create a raw expression, you may use the raw() method:
db.table('users') \
.select(db.raw('count(*) as user_count, status')) \
.where('status', '!=', 1) \
.group_by('status') \
.get()
Inserts¶
Insert records into a table¶
db.table('users').insert(email='foo@bar.com', votes=0)
db.table('users').insert({
'email': 'foo@bar.com',
'votes': 0
})
Note
It is important to note that there is two notations available. The reason is quite simple: the dictionary notation, though a little less practical, is here to handle columns names which cannot be passed as keywords arguments.
Inserting records into a table with an auto-incrementing ID¶
If the table has an auto-incrementing id, use insert_get_id to insert a record and retrieve the id:
id = db.table('users').insert_get_id({
'email': 'foo@bar.com',
'votes': 0
})
Inserting multiple record into a table¶
db.table('users').insert([
{'email': 'foo@bar.com', 'votes': 0},
{'email': 'bar@baz.com', 'votes': 0}
])
Updates¶
Updating records¶
db.table('users').where('id', 1).update(votes=1)
db.table('users').where('id', 1).update({'votes': 1})
Note
Like the insert statement, there is two notations available.
The reason is quite simple: the dictionary notation, though a little less practical, is here to handle
columns names which cannot be passed as keywords arguments.
Incrementing or decrementing the value of a column¶
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
You can also specify additional columns to update:
db.table('users').increment('votes', 1, name='John')
Deletes¶
Deleting records¶
db.table('users').where('age', '<', 25).delete()
Delete all records¶
db.table('users').delete()
Truncate¶
db.table('users').truncate()
Unions¶
The query builder provides a quick and easy way to “union” two queries:
first = db.table('users').where_null('first_name')
users = db.table('users').where_null('last_name').union(first).get()
The union_all method is also available.
Pessimistic locking¶
The query builder includes a few functions to help you do “pessimistic locking” on your SELECT statements.
To run the SELECT statement with a “shared lock”, you may use the shared_lock method on a query:
db.table('users').where('votes', '>', 100).shared_lock().get()
To “lock for update” on a SELECT statement, you may use the lock_for_update method on a query:
db.table('users').where('votes', '>', 100).lock_for_update().get()
