SELECT¶
Use select()
of QueryFactory
to create a SELECT statement.
The return value is an instance of SelectQuery
.
factory = QueryFactory(CommonEngine())
query = factory \
.select() \
.from_('users') \
.limit(100) \
.compile()
print(query.sql) # SELECT * FROM "users" LIMIT 100
print(query.params) # ()
Specific columns can be selected:
query = factory \
.select('id', 'username') \
.from_('users') \
.compile()
print(query.sql) # SELECT "id", "username" FROM "users"
print(query.params) # ()
additional columns can be added:
query = factory \
.select('id', 'username') \
.add_columns('password') \
.from_('users') \
.compile()
print(query.sql) # SELECT "id", "username", "password" FROM "users"
print(query.params) # ()
As well as additional tables:
query = factory \
.select('users.username', 'groups.name') \
.from_('users') \
.add_from('groups') \
.compile()
print(query.sql) # SELECT "users"."username", "groups"."name" FROM "users", "groups"
print(query.params) # ()
WHERE¶
Apply criteria to a WHERE condition:
query = factory \
.select() \
.from_('countries') \
.where(field('language')->eq('EN')) \
.compile()
print(query.sql) # SELECT * FROM "countries" WHERE "language" = ?
print(query.params) # ('EN', )
Additional criteria can be added using and_where and or_where
query = factory \
.select() \
.from_('users') \
.where(field('id')->gt(1)) \
.or_where(field('login_at')->is_null()) \
.or_where(field('is_inactive')->eq(1)) \
.compile()
print(query.sql) # SELECT * FROM "users" WHERE "id" > ? OR "login_at" IS NULL OR "is_inactive" > ?
print(query.params) # (1, 1)
JOIN¶
Joins are added in a similar way:
query = factory \
.select('u.id', 'c.name') \
.from_(alias('users', 'u')) \
.join(alias('countries', 'c'), on('u.country_id', 'c.id')) \
.compile()
print(query.sql) # SELECT * FROM "users" AS "u" JOIN "countries" AS "c" ON "u"."country_id" = "c"."id"
print(query.params) # ()
The join type can be specified as third parameter of join or one of these helpers can be used for common types:
left_join
right_join
inner_join
full_join
ORDER BY¶
Ordering can be applied:
factory = QueryFactory(CommonEngine())
query = factory \
.select() \
.from_('users') \
.order_by('username', 'asc') \
.compile()
print(query.sql) # SELECT * FROM "users" ORDER BY "username" DESC
print(query.params) # ()
ordering can be reset by omitting the order column:
query.order_by()
LIMIT and OFFSET¶
Limits and offsets can be applied:
factory = QueryFactory(CommonEngine())
query = factory \
.select() \
.from_('posts') \
.offset(10)
.limit(10)
.compile()
print(query.sql) # SELECT * FROM "posts" OFFSET 10 LIMIT 10
print(query.params) # ()
Note
When using the SQL Server engine an offset must be defined for the limit to be applied! Use offset(0) when no offset is required.
GROUP BY¶
Grouping can be applied:
factory = QueryFactory(CommonEngine())
query = factory \
.select(
alias(func('COUNT', 'id'), 'total')
) \
.from_('employees') \
.group_by('department') \
.compile()
print(query.sql) # SELECT COUNT("id") AS "total" FROM "employees" GROUP BY "department"
print(query.param) # ()
And also the having clause can be applied:
factory = QueryFactory(CommonEngine())
salary_sum = func('SUM', 'salary')
query = factory \
.select(
'department',
alias(salary_sum, 'total')
) \
.from_('employees') \
.group_by('department') \
.having(field(salary_sum).gt(5000))
print(query.sql) # SELECT "department", SUM("salary") AS "total" FROM "employees" GROUP BY "department" HAVING SUM("salary") > ?
print(query.params) # (5000, )
CTE¶
From version 1.1.0, CTE can be used. Create the CTE as a normal select query and use with_()
of SelectQuery
to use it with another query.
factory = QueryFactory(CommonEngine())
cte = factory.select("id").from_("users").order_by("users.id").limit(10)
select = factory.select() \
.with_("limit_users", cte) \
.from_("users") \
.left_join("limit_users", on("limit_users.id", "users.id"))