Lightweight Python SQLBuilder¶
SmartSQL - lightweight Python sql builder, follows the KISS principle. Supports Python2 and Python3.
You can use SmartSQL separatelly, or with Django, or with super-lightweight Ascetic ORM, or with super-lightweight datamapper Openorm (miror) etc.
- Home Page: https://bitbucket.org/emacsway/sqlbuilder
- Docs: https://sqlbuilder.readthedocs.io/
- Browse source code (canonical repo): https://bitbucket.org/emacsway/sqlbuilder/src
- GitHub mirror: https://github.com/emacsway/sqlbuilder
- Get source code (canonical repo):
hg clone https://bitbucket.org/emacsway/sqlbuilder
- Get source code (mirror):
git clone https://github.com/emacsway/sqlbuilder.git
- PyPI: https://pypi.python.org/pypi/sqlbuilder
LICENSE:
- License is BSD
Quick start¶
>>> from sqlbuilder.smartsql import Q, T, compile
>>> compile(Q().tables(
... (T.book & T.author).on(T.book.author_id == T.author.id)
... ).fields(
... T.book.title, T.author.first_name, T.author.last_name
... ).where(
... (T.author.first_name != 'Tom') & (T.author.last_name != 'Smith')
... )[20:30])
('SELECT "book"."title", "author"."first_name", "author"."last_name" FROM "book" INNER JOIN "author" ON ("book"."author_id" = "author"."id") WHERE "author"."first_name" <> %s AND "author"."last_name" <> %s LIMIT %s OFFSET %s', ['Tom', 'Smith', 10, 20])
Django integration¶
Simple add “django_sqlbuilder” to your INSTALLED_APPS.
>>> object_list = Book.s.q.tables(
... (Book.s & Author.s).on(Book.s.author == Author.s.pk)
... ).where(
... (Author.s.first_name != 'James') & (Author.s.last_name != 'Joyce')
... )[:10]
Contents:
Short manual for sqlbuilder.smartsql¶
Table¶
>>> from sqlbuilder.smartsql import T, Q
>>> T.book
<Table: "book", []>
>>> T.book.as_('a')
<TableAlias: "a", []>
>>> T.book__a # Same as T.book.as_('a')
<TableAlias: "a", []>
Compiling instance of TableAlias depends on context of usage:
>>> ta = T.book.as_('a')
>>> ta
<TableAlias: "a", []>
>>> Q().tables(ta).fields(ta.id, ta.status).where(ta.status.in_(('new', 'approved')))
<Query: SELECT "a"."id", "a"."status" FROM "book" AS "a" WHERE "a"."status" IN (%s, %s), ['new', 'approved']>
Field¶
Get field as table attribute:
>>> from sqlbuilder.smartsql import T, F, Q
>>> T.book.title
<Field: "book"."title", []>
>>> T.book.title.as_('a')
<Alias: "a", []>
>>> T.book.title__a # Same as T.book.title.as_('a')
<Alias: "a", []>
What if field name is reserved word in table namespace?
>>> T.book.natural # Will returns a bound method
<bound method Table.<lambda> of <Table: "book", []>>
>>> T.book['natural'] # Will returns Field()
<Field: "book"."natural", []>
>>> T.book.f.natural # Also
<Field: "book"."natural", []>
>>> T.book.f['natural'] # Also
<Field: "book"."natural", []>
>>> T.book.f('natural') # Also
<Field: "book"."natural", []>
>>> F('natural', T.book) # Also
<Field: "book"."natural", []>
Get field as attribute of F class (Legacy way):
>>> F.book__title # Same as T.book.title
<Field: "book"."title", []>
>>> F.book__title.as_('a') # Same as T.book.title.as_('a')
<Alias: "a", []>
>>> F.book__title__a # Same as T.book.title.as_('a')
<Alias: "a", []>
Compiling instance of Alias depends on context of usage:
>>> al = T.book.status.as_('a')
>>> al
<Alias: "a", []>
>>> Q().tables(T.book).fields(T.book.id, al).where(al.in_(('new', 'approved')))
<Query: SELECT "book"."id", "book"."status" AS "a" FROM "book" WHERE "a" IN (%s, %s), ['new', 'approved']>
Table operators¶
>>> (T.book & T.author).on(T.book.author_id == T.author.id)
<TableJoin: "book" INNER JOIN "author" ON ("book"."author_id" = "author"."id"), []>
>>> (T.book + T.author).on(T.book.author_id == T.author.id)
<TableJoin: "book" LEFT OUTER JOIN "author" ON ("book"."author_id" = "author"."id"), []>
>>> (T.book - T.author).on(T.book.author_id == T.author.id)
<TableJoin: "book" RIGHT OUTER JOIN "author" ON ("book"."author_id" = "author"."id"), []>
>>> (T.book | T.author).on(T.book.author_id == T.author.id)
<TableJoin: "book" FULL OUTER JOIN "author" ON ("book"."author_id" = "author"."id"), []>
>>> (T.book * T.author).on(T.book.author_id == T.author.id)
<TableJoin: "book" CROSS JOIN "author" ON ("book"."author_id" = "author"."id"), []>
Nested join is also supported:
>>> t1, t2, t3, t4 = T.t1, T.t2, T.t3, T.t4
>>> t1 + (t2 * t3 * t4)().on((t2.a == t1.a) & (t3.b == t1.b) & (t4.c == t1.c))
<TableJoin: "t1" LEFT OUTER JOIN ("t2" CROSS JOIN "t3" CROSS JOIN "t4") ON ("t2"."a" = "t1"."a" AND "t3"."b" = "t1"."b" AND "t4"."c" = "t1"."c"), []>
>>> t1 + (t2 + t3).on((t2.b == t3.b) | t2.b.is_(None))()
<TableJoin: "t1" LEFT OUTER JOIN ("t2" LEFT OUTER JOIN "t3" ON ("t2"."b" = "t3"."b" OR "t2"."b" IS NULL)), []>
>>> (t1 + t2.on(t1.a == t2.a))() + t3.on((t2.b == t3.b) | t2.b.is_(None))
<TableJoin: ("t1" LEFT OUTER JOIN "t2" ON ("t1"."a" = "t2"."a")) LEFT OUTER JOIN "t3" ON ("t2"."b" = "t3"."b" OR "t2"."b" IS NULL), []>
Condition operators¶
>>> from sqlbuilder.smartsql import T, P
>>> tb = T.author
>>> tb.name == 'Tom'
<Eq: "author"."name" = %s, ['Tom']>
>>> tb.name != 'Tom'
<Ne: "author"."name" <> %s, ['Tom']>
>>> tb.counter + 1
<Add: "author"."counter" + %s, [1]>
>>> 1 + tb.counter
<Add: %s + "author"."counter", [1]>
>>> tb.counter - 1
<Sub: "author"."counter" - %s, [1]>
>>> 10 - tb.counter
<Sub: %s - "author"."counter", [10]>
>>> tb.counter * 2
<Mul: "author"."counter" * %s, [2]>
>>> 2 * tb.counter
<Mul: %s * "author"."counter", [2]>
>>> tb.counter / 2
<Div: "author"."counter" / %s, [2]>
>>> 10 / tb.counter
<Div: %s / "author"."counter", [10]>
>>> tb.is_staff & tb.is_admin
<And: "author"."is_staff" AND "author"."is_admin", []>
>>> tb.is_staff | tb.is_admin
<Or: "author"."is_staff" OR "author"."is_admin", []>
>>> tb.counter > 10
<Gt: "author"."counter" > %s, [10]>
>>> 10 > tb.counter
<Lt: "author"."counter" < %s, [10]>
>>> tb.counter >= 10
<Ge: "author"."counter" >= %s, [10]>
>>> 10 >= tb.counter
<Le: "author"."counter" <= %s, [10]>
>>> tb.counter < 10
<Lt: "author"."counter" < %s, [10]>
>>> 10 < tb.counter
<Gt: "author"."counter" > %s, [10]>
>>> tb.counter <= 10
<Le: "author"."counter" <= %s, [10]>
>>> 10 <= tb.counter
<Ge: "author"."counter" >= %s, [10]>
>>> tb.mask << 1
<LShift: "author"."mask" << %s, [1]>
>>> tb.mask >> 1
<RShift: "author"."mask" >> %s, [1]>
>>> tb.is_staff.is_(True)
<Is: "author"."is_staff" IS %s, [True]>
>>> tb.is_staff.is_not(True)
<IsNot: "author"."is_staff" IS NOT %s, [True]>
>>> tb.status.in_(('new', 'approved'))
<In: "author"."status" IN (%s, %s), ['new', 'approved']>
>>> tb.status.not_in(('new', 'approved'))
<NotIn: "author"."status" NOT IN (%s, %s), ['new', 'approved']>
>>> tb.last_name.like('mi')
<Like: "author"."last_name" LIKE %s, ['mi']>
>>> tb.last_name.ilike('mi')
<Ilike: "author"."last_name" ILIKE %s, ['mi']>
>>> P('mi').like(tb.last_name)
<Like: %s LIKE "author"."last_name", ['mi']>
>>> tb.last_name.rlike('mi')
<Like: %s LIKE "author"."last_name", ['mi']>
>>> tb.last_name.rilike('mi')
<Ilike: %s ILIKE "author"."last_name", ['mi']>
>>> tb.last_name.startswith('Sm')
<Like: "author"."last_name" LIKE REPLACE(REPLACE(REPLACE(%s, '!', '!!'), '_', '!_'), '%%', '!%%') || '%%' ESCAPE '!', ['Sm']>
>>> tb.last_name.istartswith('Sm')
<Ilike: "author"."last_name" ILIKE REPLACE(REPLACE(REPLACE(%s, '!', '!!'), '_', '!_'), '%%', '!%%') || '%%' ESCAPE '!', ['Sm']>
>>> tb.last_name.contains('mi')
<Like: "author"."last_name" LIKE '%%' || REPLACE(REPLACE(REPLACE(%s, '!', '!!'), '_', '!_'), '%%', '!%%') || '%%' ESCAPE '!', ['mi']>
>>> tb.last_name.icontains('mi')
<Ilike: "author"."last_name" ILIKE '%%' || REPLACE(REPLACE(REPLACE(%s, '!', '!!'), '_', '!_'), '%%', '!%%') || '%%' ESCAPE '!', ['mi']>
>>> tb.last_name.endswith('th')
<Like: "author"."last_name" LIKE '%%' || REPLACE(REPLACE(REPLACE(%s, '!', '!!'), '_', '!_'), '%%', '!%%') ESCAPE '!'', ['th']>
>>> tb.last_name.iendswith('th')
<Ilike: "author"."last_name" ILIKE '%%' || REPLACE(REPLACE(REPLACE(%s, '!', '!!'), '_', '!_'), '%%', '!%%') ESCAPE '!'', ['th']>
>>> tb.last_name.rstartswith('Sm')
<Like: %s LIKE REPLACE(REPLACE(REPLACE("author"."last_name", '!', '!!'), '_', '!_'), '%%', '!%%') || '%%' ESCAPE '!', ['Sm']>
>>> tb.last_name.ristartswith('Sm')
<Ilike: %s ILIKE REPLACE(REPLACE(REPLACE("author"."last_name", '!', '!!'), '_', '!_'), '%%', '!%%') || '%%' ESCAPE '!', ['Sm']>
>>> tb.last_name.rcontains('mi')
<Like: %s LIKE '%%' || REPLACE(REPLACE(REPLACE("author"."last_name", '!', '!!'), '_', '!_'), '%%', '!%%') || '%%' ESCAPE '!', ['mi']>
>>> tb.last_name.ricontains('mi')
<Ilike: %s ILIKE '%%' || REPLACE(REPLACE(REPLACE("author"."last_name", '!', '!!'), '_', '!_'), '%%', '!%%') || '%%' ESCAPE '!', ['mi']>
>>> tb.last_name.rendswith('th')
<Like: %s LIKE '%%' || REPLACE(REPLACE(REPLACE("author"."last_name", '!', '!!'), '_', '!_'), '%%', '!%%') ESCAPE '!', ['th']>
>>> tb.last_name.riendswith('th')
<Ilike: %s ILIKE '%%' || REPLACE(REPLACE(REPLACE("author"."last_name", '!', '!!'), '_', '!_'), '%%', '!%%') ESCAPE '!', ['th']>
>>> +tb.counter
<Pos: +"author"."counter", []>
>>> -tb.counter
<Neg: -"author"."counter", []>
>>> ~tb.counter
<Not: NOT "author"."counter", []>
>>> tb.name.distinct()
<Distinct: DISTINCT "author"."name", []>
>>> tb.counter ** 2
<Callable: POW("author"."counter", %s), [2]>
>>> 2 ** tb.counter
<Callable: POW(%s, "author"."counter"), [2]>
>>> tb.counter % 2
<Callable: MOD("author"."counter", %s), [2]>
>>> 2 % tb.counter
<Callable: MOD(%s, "author"."counter"), [2]>
>>> abs(tb.counter)
<Callable: ABS("author"."counter"), []>
>>> tb.counter.count()
<Callable: COUNT("author"."counter"), []>
>>> tb.age.between(20, 30)
<Between: "author"."age" BETWEEN %s AND %s, [20, 30]>
>>> tb.age[20:30]
<Between: "author"."age" BETWEEN %s AND %s, [20, 30]>
>>> tb.age[20]
<Eq: "author"."age" = %s, [20]>
>>> tb.name.concat(' staff', ' admin')
<Concat: "author"."name" || %s || %s, [' staff', ' admin']>
>>> tb.name.concat_ws(' ', 'staff', 'admin')
<Concat: concat_ws(%s, "author"."name", %s, %s), [' ', 'staff', 'admin']>
>>> tb.name.op('MY_EXTRA_OPERATOR')(10)
<Binary: "author"."name" MY_EXTRA_OPERATOR %s, [10]>
>>> tb.name.rop('MY_EXTRA_OPERATOR')(10)
<Binary: %s MY_EXTRA_OPERATOR "author"."name", [10]>
>>> tb.name.asc()
<Asc: "author"."name" ASC, []>
>>> tb.name.desc()
<Desc: "author"."name" DESC, []>
>>> ((tb.age > 25) | (tb.answers > 10)) & (tb.is_staff | tb.is_admin)
<And: ("author"."age" > %s OR "author"."answers" > %s) AND ("author"."is_staff" OR "author"."is_admin"), [25, 10]>
>>> (T.author.first_name != 'Tom') & (T.author.last_name.in_(('Smith', 'Johnson')))
<Binary: "author"."first_name" <> %s AND "author"."last_name" IN (%s, %s), ['Tom', 'Smith', 'Johnson']>
>>> (T.author.first_name != 'Tom') | (T.author.last_name.in_(('Smith', 'Johnson')))
<Binary: "author"."first_name" <> %s OR "author"."last_name" IN (%s, %s), ['Tom', 'Smith', 'Johnson']>
Module sqlbuilder.smartsql.contrib.evaluate¶
Unfortunately, Python supports limited list of operators compared to PostgreSQL.
Many operators like @>
, &>
, -|-
, @-@
and so on are not supported by Python.
You can use method Expr.op()
or class Binary
to solve this problem, for example:
>>> T.user.age.op('<@')(func.int8range(25, 30))
<Binary: "user"."age" <@ INT8RANGE(%s, %s), [25, 30]>
>>> Binary(T.user.age, '<@', func.int8range(25, 30))
<Binary: "user"."age" <@ INT8RANGE(%s, %s), [25, 30]>
But this solution has a lack of readability.
So, sqlbuilder provides module sqlbuilder.smartsql.contrib.evaluate
,
that allows you to mix SQL operators (like @>
, &>
, -|-
, @-@
etc.) and python expressions.
In other words, you can use SQL operators with Python expressions.
For example:
>>> from sqlbuilder.smartsql.contrib.evaluate import e
>>> e("T.user.age <@ func.int4range(25, 30)")
<Binary: "user"."age" <@ INT4RANGE(%s, %s), [25, 30]>
or with kwargs:
>>> from sqlbuilder.smartsql.contrib.evaluate import e
>>> required_range = func.int8range(25, 30)
>>> e("T.user.age <@ required_range", required_range=required_range)
<Binary: "user"."age" <@ INT4RANGE(%s, %s), [25, 30]>
or with context object:
>>> from sqlbuilder.smartsql.contrib.evaluate import e
>>> required_range = func.int8range(25, 30)
>>> e("T.user.age <@ required_range", locals())
<Binary: "user"."age" <@ INT4RANGE(%s, %s), [25, 30]>
You can pre-compile expression, similar re.compile(), to avoid parsing of it each time:
>>> from sqlbuilder.smartsql.contrib.evaluate import compile, e
>>> required_range = func.int8range(25, 30)
>>> compiled_expr = compile("""T.user.age <@ required_range""")
>>> e(compiled_expr, {'required_range': required_range})
<Binary: "user"."age" <@ INT4RANGE(%s, %s), [25, 30]>
Btw, you can even pre-compile expression into sql-string to achieve the fastest result:
>>> from sqlbuilder.smartsql import *
>>> from sqlbuilder.smartsql.contrib.evaluate import e
>>> sql, params = compile(e("T.user.age <@ func.int4range('%(min)s', '%(max)s')"))
>>> sql = sql.replace('%%', '%%%%') % tuple(params)
>>> sql
u'"user"."age" <@ INT4RANGE(%(min)s, %(max)s)'
More complex example:
>>> from sqlbuilder.smartsql import *
>>> from sqlbuilder.smartsql.contrib.evaluate import e
>>> required_range = func.int8range(25, 30)
>>> e("T.user.age <@ required_range AND NOT(T.user.is_staff OR T.user.is_admin)", locals())
<Binary: "user"."age" <@ INT8RANGE(%s, %s) AND NOT ("user"."is_staff" OR "user"."is_admin"), [25, 30]>
Note
Module sqlbuilder.smartsql.contrib.evaluate
uses operator precedence similar
PostgreSQL precedence,
not Python precedence.
Also note, that Power
has
left association similar PostgreSQL,
in contrast Python has right association:
$ python
>>> 2 ** 3 ** 5
14134776518227074636666380005943348126619871175004951664972849610340958208L
$ psql
postgres=# SELECT 2 ^ 3 ^ 5;
?column?
----------
32768
(1 row)
Real operator precedence and association directions you can see in source code of the module.
Query object¶
-
class
sqlbuilder.smartsql.
Query
¶ Query builder class
-
result
¶ Instance of
Result
. See Implementation of execution.
-
__init__
([tables=None, result=None])¶ Parameters: - tables (Table, TableAlias, TableJoin, or None) – Tables for FROM clause of SQL query
- result (Result or None) – Object with implementation of execution
Building methods:
-
distinct
(*args, **opts)¶ Builds DISTINCT [ON (…)] clause. This method has interface similar to
fields()
.- Adds expressions if arguments exist.
- Sets expressions if exists single argument of list/tuple type.
- Gets expressions without arguments.
- Resets expressions with
reset=True
keyword argument.
Also can be used sole argument of boolean type:
True
to apply DISTINCT clauseFalse
to reset DISTINCT clause
Returns: copy of self if arguments exist, else current expression list. Return type: Query or ExprList Example of usage:
>>> # Sole argument of boolean type >>> from sqlbuilder.smartsql import Q, T >>> q = Q().fields('*').tables(T.author) >>> q <Query: SELECT * FROM "author", []> >>> bool(q.distinct()) False >>> q = q.distinct(True) >>> q <Query: SELECT DISTINCT * FROM "author", []> >>> bool(q.distinct()) True >>> q.distinct(False) <Query: SELECT * FROM "author", []> >>> q <Query: SELECT DISTINCT * FROM "author", []> >>> # Expression list >>> from sqlbuilder.smartsql import Q, T >>> q = Q().tables(T.author).fields(T.author.first_name, T.author.last_name, T.author.age) >>> q <Query: SELECT "author"."first_name", "author"."last_name", "author"."age" FROM "author", []> >>> # Add expressions: >>> q = q.distinct(T.author.first_name, T.author.last_name) >>> q <Query: SELECT DISTINCT ON ("author"."first_name", "author"."last_name") "author"."first_name", "author"."last_name", "author"."age" FROM "author", []> >>> q = q.distinct(T.author.age) >>> q <Query: SELECT DISTINCT ON ("author"."first_name", "author"."last_name", "author"."age") "author"."first_name", "author"."last_name", "author"."age" FROM "author", []> >>> # Get expressions: >>> q.distinct() <ExprList: "author"."first_name", "author"."last_name", "author"."age", []> >>> # Set new expressions list: >>> q = q.distinct([T.author.id, T.author.status]) >>> q <Query: SELECT DISTINCT ON ("author"."id", "author"."status") "author"."first_name", "author"."last_name", "author"."age" FROM "author", []> >>> # Reset expressions: >>> q.distinct([]) <Query: SELECT "author"."first_name", "author"."last_name", "author"."age" FROM "author", []> >>> q.distinct(reset=True) <Query: SELECT "author"."first_name", "author"."last_name", "author"."age" FROM "author", []>
-
fields
(*args, **opts)¶ Builds SELECT clause.
- Adds fields if arguments exist.
- Sets fields if exists single argument of list/tuple type.
- Gets fields without arguments.
- Resets fields with
reset=True
keyword argument.
Returns: copy of self if arguments exist, else current field list. Return type: Query or FieldList Example of usage:
>>> from sqlbuilder.smartsql import * >>> q = Q().tables(T.author) >>> # Add fields: >>> q = q.fields(T.author.first_name, T.author.last_name) >>> q <Query: SELECT "author"."first_name", "author"."last_name" FROM "author", []> >>> q = q.fields(T.author.age) >>> q <Query: SELECT "author"."first_name", "author"."last_name", "author"."age" FROM "author", []> >>> # Get fields: >>> q.fields() <FieldList: "author"."first_name", "author"."last_name", "author"."age", []> >>> # Set new fields list: >>> q = q.fields([T.author.id, T.author.status]) >>> q <Query: SELECT "author"."id", "author"."status" FROM "author", []> >>> # Reset fields: >>> q = q.fields([]) >>> q <Query: SELECT FROM "author", []> >>> # Another way to reset fields: >>> q = q.fields(reset=True) >>> q <Query: SELECT FROM "author", []>
-
tables
(tables=None)¶ Builds FROM clause.
Parameters: tables (None, Table or TableJoin) – Can be None, Table or TableJoin instance Returns: copied self with new tables if tables
argument is not None, else current tables.Return type: TableJoin or Query Example of usage:
>>> from sqlbuilder.smartsql import T, Q >>> q = Q().tables(T.author).fields('*') >>> q <Query: SELECT * FROM "author", []> >>> q = q.tables(T.author.as_('author_alias')) >>> q <Query: SELECT * FROM "author" AS "author_alias", []> >>> q.tables() <TableJoin: "author" AS "author_alias", []> >>> q = q.tables((q.tables() + T.book).on(T.book.author_id == T.author.as_('author_alias').id)) >>> q <Query: SELECT * FROM "author" AS "author_alias" LEFT OUTER JOIN "book" ON ("book"."author_id" = "author_alias"."id"), []>
-
where
(cond[, op=operator.and_])¶ Builds WHERE clause.
- Adds new criterias using the
op
operator, ifop
is not None. - Sets new criterias if
op
is None.
Parameters: - cond (Expr) – Selection criterias
- op – Attribute of
operator
module or None,operator.and_
by default
Returns: copy of self with new criteria
Return type: Example of usage:
>>> import operator >>> from sqlbuilder.smartsql import T, Q >>> q = Q().tables(T.author).fields('*') >>> q <Query: SELECT * FROM "author", []> >>> # Add conditions >>> q = q.where(T.author.is_staff.is_(True)) >>> q <Query: SELECT * FROM "author" WHERE "author"."is_staff" IS %s, [True]> >>> q = q.where(T.author.first_name == 'John') >>> q <Query: SELECT * FROM "author" WHERE "author"."is_staff" IS %s AND "author"."first_name" = %s, [True, 'John']> >>> q = q.where(T.author.last_name == 'Smith', op=operator.or_) >>> q <Query: SELECT * FROM "author" WHERE "author"."is_staff" IS %s AND "author"."first_name" = %s OR "author"."last_name" = %s, [True, 'John', 'Smith']> >>> # Set conditions >>> q = q.where(T.author.last_name == 'Smith', op=None) >>> q <Query: SELECT * FROM "author" WHERE "author"."last_name" = %s, ['Smith']>
- Adds new criterias using the
-
group_by
(*args, **opts)¶ Builds GROUP BY clause. This method has interface similar to
fields()
.- Adds expressions if arguments exist.
- Sets expressions if exists single argument of list/tuple type.
- Gets expressions without arguments.
- Resets expressions with
reset=True
keyword argument.
Returns: copy of self if arguments exist, else current expression list. Return type: Query or ExprList Example of usage:
>>> from sqlbuilder.smartsql import T, Q >>> q = Q().tables(T.author).fields('*') >>> q <Query: SELECT * FROM "author", []> >>> # Add expressions: >>> q = q.group_by(T.author.first_name, T.author.last_name) >>> q <Query: SELECT * FROM "author" GROUP BY "author"."first_name", "author"."last_name", []> >>> q = q.group_by(T.author.age) >>> q <Query: SELECT * FROM "author" GROUP BY "author"."first_name", "author"."last_name", "author"."age", []> >>> # Get expressions: >>> q.group_by() <ExprList: "author"."first_name", "author"."last_name", "author"."age", []> >>> # Set new expressions list: >>> q = q.group_by([T.author.id, T.author.status]) >>> q <Query: SELECT * FROM "author" GROUP BY "author"."id", "author"."status", []> >>> # Reset expressions: >>> q = q.group_by([]) >>> q <Query: SELECT * FROM "author", []> >>> # Another way to reset expressions: >>> q = q.group_by(reset=True) >>> q <Query: SELECT * FROM "author", []>
-
having
(cond[, op=operator.and_])¶ Builds HAVING clause. This method has interface similar to
where()
.- Adds new criterias using the
op
operator, ifop
is not None. - Sets new criterias if
op
is None.
Parameters: - cond (Expr) – Selection criterias
- op – Attribute of
operator
module or None,operator.and_
by default
Returns: copy of self with new criteria
Return type: Example of usage:
>>> import operator >>> from sqlbuilder.smartsql import T, Q >>> q = Q().fields('*').tables(T.author).group_by(T.author.status) >>> q <Query: SELECT * FROM "author" GROUP BY "author"."status", []> >>> # Add conditions >>> q = q.having(T.author.is_staff.is_(True)) >>> q <Query: SELECT * FROM "author" GROUP BY "author"."status" HAVING "author"."is_staff" IS %s, [True]> >>> q = q.having(T.author.first_name == 'John') >>> q <Query: SELECT * FROM "author" GROUP BY "author"."status" HAVING "author"."is_staff" IS %s AND "author"."first_name" = %s, [True, 'John']> >>> q = q.having(T.author.last_name == 'Smith', op=operator.or_) >>> q <Query: SELECT * FROM "author" GROUP BY "author"."status" HAVING "author"."is_staff" IS %s AND "author"."first_name" = %s OR "author"."last_name" = %s, [True, 'John', 'Smith']> >>> # Set conditions >>> q = q.having(T.author.last_name == 'Smith', op=None) >>> q <Query: SELECT * FROM "author" GROUP BY "author"."status" HAVING "author"."last_name" = %s, ['Smith']>
- Adds new criterias using the
-
order_by
(*args, **opts)¶ Builds ORDER BY clause. This method has interface similar to
fields()
.- Adds expressions if arguments exist.
- Sets expressions if exists single argument of list/tuple type.
- Gets expressions without arguments.
- Resets expressions with
reset=True
keyword argument.
Returns: copy of self if arguments exist, else current expression list. Return type: Query or ExprList Example of usage:
>>> from sqlbuilder.smartsql import T, Q >>> q = Q().tables(T.author).fields('*') >>> q <Query: SELECT * FROM "author", []> >>> # Add expressions: >>> q = q.order_by(T.author.first_name, T.author.last_name) >>> q <Query: SELECT * FROM "author" ORDER BY "author"."first_name" ASC, "author"."last_name" ASC, []> >>> q = q.order_by(T.author.age.desc()) >>> q >>> # Get expressions: >>> q.order_by() <ExprList: "author"."first_name" ASC, "author"."last_name" ASC, "author"."age" DESC, []> >>> # Set new expressions list: <Query: SELECT * FROM "author" ORDER BY "author"."first_name" ASC, "author"."last_name" ASC, "author"."age" DESC, []> >>> q = q.order_by([T.author.id.desc(), T.author.status]) >>> q <Query: SELECT * FROM "author" ORDER BY "author"."id" DESC, "author"."status" ASC, []> >>> # Reset expressions: >>> q = q.order_by([]) >>> q <Query: SELECT * FROM "author", []> >>> # Another way to reset expressions: >>> q = q.order_by(reset=True) >>> q <Query: SELECT * FROM "author", []>
Executing methods:
-
select
(*args, **opts)¶ Example of usage:
>>> from sqlbuilder.smartsql import Q, T >>> Q(T.author).fields('*').select(for_update=True) ('SELECT * FROM "author" FOR UPDATE', [])
-
count
()¶ Example of usage:
>>> from sqlbuilder.smartsql import Q, T >>> Q(T.author).fields('*').count() ('SELECT COUNT(1) AS "count_value" FROM (SELECT * FROM "author") AS "count_list"', [])
-
insert
([key_values=None, **kw])¶ Parameters: - key_values (dict) – Map of fields (or field names) to it’s values.
- kw – Extra keyword arguments that will be passed to
Insert
instance.
Example of usage:
>>> from sqlbuilder.smartsql import Q, T, func >>> Q(T.stats).insert({ ... T.stats.object_type: 'author', ... T.stats.object_id: 15, ... T.stats.counter: 1 ... }, on_duplicate_key_update={ ... T.stats.counter: T.stats.counter + func.VALUES(T.stats.counter) ... }) ... ('INSERT INTO "stats" ("stats"."counter", "stats"."object_id", "stats"."object_type") VALUES (%s, %s, %s) ON CONFLICT DO UPDATE SET "stats"."counter" = "stats"."counter" + VALUES("stats"."counter")', [1, 15, 'author']) >>> # Keys of dict are strings >>> Q(T.stats).insert({ ... 'object_type': 'author', ... 'object_id': 15, ... 'counter': 1 ... }, on_duplicate_key_update={ ... 'counter': T.stats.counter + func.VALUES(T.stats.counter) ... }) ... ('INSERT INTO "stats" ("object_type", "object_id", "counter") VALUES (%s, %s, %s) ON CONFLICT DO UPDATE SET "counter" = "stats"."counter" + VALUES("stats"."counter")', ['author', 15, 1]) >>> # Use "values" keyword argument >>> Q().fields( ... T.stats.object_type, T.stats.object_id, T.stats.counter ... ).tables(T.stats).insert( ... values=('author', 15, 1), ... on_duplicate_key_update={T.stats.counter: T.stats.counter + func.VALUES(T.stats.counter)} ... ) ... ('INSERT INTO "stats" ("stats"."object_type", "stats"."object_id", "stats"."counter") VALUES %s, %s, %s ON CONFLICT DO UPDATE SET "stats"."counter" = "stats"."counter" + VALUES("stats"."counter")', ['author', 15, 1]) >>> # Insert many >>> Q().fields( ... T.stats.object_type, T.stats.object_id, T.stats.counter ... ).tables(T.stats).insert( ... values=( ... ('author', 15, 1), ... ('author', 16, 1), ... ), ... on_duplicate_key_update={T.stats.counter: T.stats.counter + func.VALUES(T.stats.counter)} ... ) ... ('INSERT INTO "stats" ("stats"."object_type", "stats"."object_id", "stats"."counter") VALUES (%s, %s, %s), (%s, %s, %s) ON CONFLICT DO UPDATE SET "stats"."counter" = "stats"."counter" + VALUES("stats"."counter")', ['author', 15, 1, 'author', 16, 1]) >>> # Insert ignore >>> Q().fields( ... T.stats.object_type, T.stats.object_id, T.stats.counter ... ).tables(T.stats).insert( ... values=('author', 15, 1), ... ignore=True ... ) ... ('INSERT INTO "stats" ("stats"."object_type", "stats"."object_id", "stats"."counter") VALUES %s, %s, %s ON CONFLICT DO NOTHING', ['author', 15, 1]) >>> # INSERT ... SELECT Syntax >>> Q().fields( ... T.stats.object_type, T.stats.object_id, T.stats.counter ... ).tables(T.stats).insert( ... values=Q().fields( ... T.old_stats.object_type, T.old_stats.object_id, T.old_stats.counter ... ).tables(T.old_stats), ... on_duplicate_key_update={ ... T.stats.counter: T.stats.counter + T.old_stats.counter, ... } ... ) ('INSERT INTO "stats" ("stats"."object_type", "stats"."object_id", "stats"."counter") SELECT "old_stats"."object_type", "old_stats"."object_id", "old_stats"."counter" FROM "old_stats" ON CONFLICT DO UPDATE SET "stats"."counter" = "stats"."counter" + "old_stats"."counter"', [])
-
update
([key_values=None, **kw])¶ Parameters: - key_values (dict) – Map of fields (or field names) to it’s values.
- kw – Extra keyword arguments that will be passed to
Update
instance.
Example of usage:
>>> from sqlbuilder.smartsql import Q, T, func >>> Q(T.author).where(T.author.id == 10).update({ ... T.author.first_name: 'John', ... T.author.last_login: func.NOW() ... }) ... ('UPDATE "author" SET "author"."last_login" = NOW(), "author"."first_name" = %s WHERE "author"."id" = %s', ['John', 10]) >>> # Keys of dict are strings >>> Q(T.author).where(T.author.id == 10).update({ ... 'first_name': 'John', ... 'last_login': func.NOW() ... }) ... ('UPDATE "author" SET "first_name" = %s, "last_login" = NOW() WHERE "author"."id" = %s', ['John', 10]) >>> # Use "values" keyword argument >>> Q(T.author).fields( ... T.author.first_name, T.author.last_login ... ).where(T.author.id == 10).update( ... values=('John', func.NOW()) ... ) ... ('UPDATE "author" SET "author"."first_name" = %s, "author"."last_login" = NOW() WHERE "author"."id" = %s', ['John', 10])
-
delete
(**kw)¶ Parameters: kw – Extra keyword arguments that will be passed to Delete
instance.Example of usage:
>>> from sqlbuilder.smartsql import Q, T >>> Q(T.author).where(T.author.id == 10).delete() ('DELETE FROM "author" WHERE "author"."id" = %s', [10])
-
as_table
(alias)¶ Returns current query as table reference.
Parameters: kw (str) – alias name. Example of usage:
>>> from sqlbuilder.smartsql import T, Q >>> author_query_alias = Q(T.author).fields(T.author.id).where( ... T.author.status == 'active' ... ).as_table('author_query_alias') >>> Q().fields(T.book.id, T.book.title).tables( ... (T.book & author_query_alias ... ).on( ... T.book.author_id == author_query_alias.id ... )) ... <Query: SELECT "book"."id", "book"."title" FROM "book" INNER JOIN (SELECT "author"."id" FROM "author" WHERE "author"."status" = %s) AS "author_query_alias" ON ("book"."author_id" = "author_query_alias"."id"), ['active']>
-
as_set
([all=False])¶ Returns current query as set, to can be combined with other queries using the set operations union, intersection, and difference.
Parameters: all (bool) – eliminates duplicate rows from result, if all is False. Example of usage:
>>> from sqlbuilder.smartsql import T, Q >>> q1 = Q(T.book1).fields(T.book1.id, T.book1.title).where(T.book1.author_id == 10) >>> q2 = Q(T.book2).fields(T.book2.id, T.book2.title).where(T.book2.author_id == 10) >>> q1.as_set() | q2 <Union: (SELECT "book1"."id", "book1"."title" FROM "book1" WHERE "book1"."author_id" = %s) UNION (SELECT "book2"."id", "book2"."title" FROM "book2" WHERE "book2"."author_id" = %s), [10, 10]> >>> q1.as_set() | q2 <Union: (SELECT "book1"."id", "book1"."title" FROM "book1" WHERE "book1"."author_id" = %s) UNION (SELECT "book2"."id", "book2"."title" FROM "book2" WHERE "book2"."author_id" = %s), [10, 10]> >>> q1.as_set() & q2 <Intersect: (SELECT "book1"."id", "book1"."title" FROM "book1" WHERE "book1"."author_id" = %s) INTERSECT (SELECT "book2"."id", "book2"."title" FROM "book2" WHERE "book2"."author_id" = %s), [10, 10]> >>> q1.as_set() - q2 <Except: (SELECT "book1"."id", "book1"."title" FROM "book1" WHERE "book1"."author_id" = %s) EXCEPT (SELECT "book2"."id", "book2"."title" FROM "book2" WHERE "book2"."author_id" = %s), [10, 10]> >>> q1.as_set(all=True) | q2 <Union: (SELECT "book1"."id", "book1"."title" FROM "book1" WHERE "book1"."author_id" = %s) UNION ALL (SELECT "book2"."id", "book2"."title" FROM "book2" WHERE "book2"."author_id" = %s), [10, 10]> >>> q1.as_set(all=True) & q2 <Intersect: (SELECT "book1"."id", "book1"."title" FROM "book1" WHERE "book1"."author_id" = %s) INTERSECT ALL (SELECT "book2"."id", "book2"."title" FROM "book2" WHERE "book2"."author_id" = %s), [10, 10]> >>> q1.as_set(all=True) - q2 <Except: (SELECT "book1"."id", "book1"."title" FROM "book1" WHERE "book1"."author_id" = %s) EXCEPT ALL (SELECT "book2"."id", "book2"."title" FROM "book2" WHERE "book2"."author_id" = %s), [10, 10]>
-
Subquery¶
Query
extends Expr
, so, it can be used as usual expression:
>>> from sqlbuilder.smartsql import Q, T
>>> # Subquery as condition
>>> sub_q = Q().fields(T.author.id).tables(T.author).where(T.author.status == 'active')
>>> Q().fields(T.book.id).tables(T.book).where(T.book.author_id.in_(sub_q))
<Query: SELECT "book"."id" FROM "book" WHERE "book"."author_id" IN (SELECT "author"."id" FROM "author" WHERE "author"."status" = %s), ['active']>
>>> # Subquery as field
>>> sub_q = Q().fields(
... T.book.id.count().as_("book_count")
... ).tables(T.book).where(
... T.book.pub_date > '2015-01-01'
... ).group_by(T.book.author_id)
...
>>> Q().fields(
... T.author.id, sub_q.where(T.book.author_id == T.author.id)
... ).tables(T.author).where(
... T.author.status == 'active'
... )
...
<Query: SELECT "author"."id", (SELECT COUNT("book"."id") AS "book_count" FROM "book" WHERE "book"."pub_date" > %s AND "book"."author_id" = "author"."id" GROUP BY "book"."author_id") FROM "author" WHERE "author"."status" = %s, ['2015-01-01', 'active']>
>>> # Subquery as alias
>>> alias = Q().fields(
... T.book.id.count()
... ).tables(T.book).where(
... (T.book.pub_date > '2015-01-01') &
... (T.book.author_id == T.author.id)
... ).group_by(
... T.book.author_id
... ).as_("book_count")
...
>>> Q().fields(
... T.author.id, alias
... ).tables(T.author).where(
... T.author.status == 'active'
... ).order_by(alias.desc())
...
<Query: SELECT "author"."id", (SELECT COUNT("book"."id") FROM "book" WHERE "book"."pub_date" > %s AND "book"."author_id" = "author"."id" GROUP BY "book"."author_id") AS "book_count" FROM "author" WHERE "author"."status" = %s ORDER BY "book_count" DESC, ['2015-01-01', 'active']>
See also method Query.as_table()
.
Implementation of execution¶
Class Query
uses “Bridge pattern” for implementation of execution.
Module sqlbuilder.smartsql
has default implementation in class Result
for demonstration purposes, that does only one thing - returns a tuple with SQL string and parameters.
You can develop your own implementation, or, at least, specify what same compiler to use, for example:
>>> from sqlbuilder.smartsql import T, Q, Result
>>> from sqlbuilder.smartsql.dialects.mysql import compile as mysql_compile
>>> Q(result=Result(compile=mysql_compile)).fields(T.author.id, T.author.name).tables(T.author).select()
('SELECT `author`.`id`, `author`.`name` FROM `author`', [])
See also examples of implementation in Django integration or Ascetic ORM integration
Instance of Query
also delegates all unknown methods and properties to Query.result
. Example:
>>> from sqlbuilder.smartsql import T, Q, Result
>>> from sqlbuilder.smartsql.dialects.mysql import compile as mysql_compile
>>> class CustomResult(Result):
... custom_attr = 5
... def custom_method(self, arg1, arg2):
... return (self._query, arg1, arg2)
... def find_by_name(self, name):
... return self._query.where(T.author.name == name)
...
>>> q = Q(result=CustomResult(compile=mysql_compile)).fields(T.author.id, T.author.name).tables(T.author)
>>> q.custom_attr
5
>>> q.custom_method(5, 10)
(<Query: SELECT "author"."id", "author"."name" FROM "author", []>, 5, 10)
>>> q.find_by_name('John')
<Query: SELECT "author"."id", "author"."name" FROM "author" WHERE "author"."name" = %s, ['John']>
Compilers¶
There are three compilers for three dialects:
-
sqlbuilder.smartsql.
compile
(expr[, state=None])¶ It’s a default compiler for PostgreSQL dialect, instance of
Compiler
. It also used for representation of expressions.Parameters: - expr (Expr) – Expression to be compiled
- state (State or None) – Instance of
State
or None
Returns: If state is None, then returns tuple with SQL string and list of parameters. Else returns None.
Return type: tuple or None
-
sqlbuilder.smartsql.dialects.mysql.
compile
(expr[, state=None])¶ Compiler for MySQL dialect.
-
sqlbuilder.smartsql.dialects.sqlite.
compile
(expr[, state=None])¶ Compiler for SQLite dialect.
Short manual for sqlbuilder.mini¶
The package contains yet another, extremely lightweight sql builder - sqlbuilder.mini
, especially for Raw-SQL fans.
It’s just a hierarchical list of SQL strings, no more.
Such form of presentation allows modify query without syntax analysis.
You can use sqlparse library to parse SQL-string to hierarchical list, see, for example, module sqlbuilder.mini.parser
.
By the way, you can use this library to change SQL directly, - it has very nice API like DOM manipulation.
You can also parse SQL to DOM or etree, to have navigation by XPath.
Also you can use pyparsing library to parse SQL-string to hierarchical list.
>>> from sqlbuilder.mini import P, Q, compile
>>> sql = [
... 'SELECT', [
... 'author.id', 'author.first_name', 'author.last_name'
... ],
... 'FROM', [
... 'author', 'INNER JOIN', ['book as b', 'ON', 'b.author_id = author.id']
... ],
... 'WHERE', [
... 'b.status', '==', P('new')
... ],
... 'ORDER BY', [
... 'author.first_name', 'author.last_name'
... ]
... ]
>>> # Let change query
>>> sql[sql.index('SELECT') + 1].append('author.age')
>>> compile(sql)
('SELECT author.id, author.first_name, author.last_name, author.age FROM author INNER JOIN book as b ON b.author_id = author.id WHERE b.status == %s ORDER BY author.first_name, author.last_name', ['new'])
To facilitate navigation and change SQL, there is helper sqlbuilder.mini.Q
:
>>> sql = [
... 'SELECT', [
... 'author.id', 'author.first_name', 'author.last_name'
... ],
... 'FROM', [
... 'author', 'INNER JOIN', [
... '(', 'SELECT', [
... 'book.title'
... ],
... 'FROM', [
... 'book'
... ],
... ')', 'AS b', 'ON', 'b.author_id = author.id'
... ],
... ],
... 'WHERE', [
... 'b.status', '==', P('new')
... ],
... 'ORDER BY', [
... 'author.first_name', 'author.last_name'
... ]
... ]
>>> sql = Q(sql)
>>> sql.prepend_child(
... ['FROM', 'INNER JOIN', 'SELECT'], # path
... ['book.id', 'book.pages'] # values to append
... )
>>> sql.append_child(
... ['FROM', 'INNER JOIN', 'SELECT'],
... ['book.date']
... )
>>> sql.insert_after(
... ['FROM', 'INNER JOIN', (list, 1), ],
... ['WHERE', ['b.pages', '>', P(100)]]
... )
>>> sql.insert_before(
... ['FROM', 'INNER JOIN', 'WHERE', 'b.pages'],
... ['b.pages', '<', P(500), 'AND']
... )
>>> compile(sql)
('SELECT author.id, author.first_name, author.last_name FROM author INNER JOIN ( SELECT book.id, book.pages, book.title, book.date FROM book WHERE b.pages < %s AND b.pages > %s ) AS b ON b.author_id = author.id WHERE b.status == %s ORDER BY author.first_name, author.last_name', [500, 100, 'new'])
As step of path can be used:
- Exact string: [‘FROM’, ‘INNER JOIN’, ‘SELECT’]
- Callable object, that returns index: [‘FROM’, ‘INNER JOIN’, (lambda i, item, collection: item == ‘SELECT’)]
- Index as integer (from zero): [‘FROM’, ‘INNER JOIN’, 1]
- Slice: [‘FROM’, ‘INNER JOIN’, slice(2, 5)]
- Each item: [‘FROM’, enumerate, ‘SELECT’]
- Compiled Regular Expression Objects: [‘FROM’, ‘INNER JOIN’, re.compile(“^SELECT$”)]
Also it’s possible combine rules.
Filter result by each next rules: [‘FROM’, ‘INNER JOIN’, (‘SELECT’, 0)] is egual to [‘FROM’, ‘INNER JOIN’, Filter(Exact(‘SELECT’), Index(0))]
Union results matched by any rules: [‘FROM’, ‘INNER JOIN’, Union(Exact(‘SELECT’), Index(1))].
Intersect results matched by all rules: [‘FROM’, ‘INNER JOIN’, Intersect(Exact(‘SELECT’), Index(1))].
The difference between Filter and Union in that, Filter handles only collection of matched elements by previous rules, and Union - a full collection.
P.S.: See also article about SQLBuilder in English and in Russian.