SQLPuzzle documentation¶

Library for ease of writing SQL queries. For now only for database MySQL & PostgreSQL.

Installation¶

pip install sqlpuzzle

Documentation¶

Note there are also camelCase methods which are only aliases to methods described here. It’s only for backward compatiblity with version 0.x. It will be removed one day.

To all methods you can pass arguments of different types. I know, in this is line saying There should be one– and preferably only one –obvious way to do it. but you can have different type of data for different type of SQL and why to support only one style and force user to deal with it everywhere.

So you should note that you can:

Pass single argument. For example for table, column, …
>>> sqlpuzzle.select('column').from_('table')
<Select: SELECT "column" FROM "table">
Pass several arguments. Where you can pass only one argument, most of there time you can also pass several arguments.
>>> sqlpuzzle.select('c1', 'c2').from_('t1', 't2')
<Select: SELECT "c1", "c2" FROM "t1", "t2">
Pass tuple or list. Because you want for example make aliases. Advantage of tuple is that you can pass it only when you need alias.
>>> sqlpuzzle.select('c1', 'c2').from_('t', ('t', 't2'))
<Select: SELECT "c1", "c2" FROM "t", "t" AS "t2">
Pass dict. Because it’s more sexy than tuples. Usage is same. You just have to be aware that dict is not sorted, so if you need same order, use OrderedDict instead. And also with same key you can have only one value.
>>> sqlpuzzle.select('c').from_('t').where({'x': 1, 'y': 2})
<Select: SELECT "c" FROM "t" WHERE "x" = 1 AND "y" = 2>
Pass named arugments. Which is same as dict above.
>>> sqlpuzzle.select('c').from_('t').where(x=1, y=2)
<Select: SELECT "c" FROM "t" WHERE "x" = 1 AND "y" = 2>

Configuration¶

sqlpuzzle.configure(database)[source]¶

By default sqlpuzzle generates syntax in plain SQL. If you want to change it, you should call this method somewhere on start of your app. For now there is only support of SQLite, MySQL and PostgreSQL.

configure('sqlite')
# or
configure('mysql')
# or
configure('postgresql')

Base methods¶

sqlpuzzle.select(*args, **kwds)[source]¶: Returns Select instance and passed arguments are used for list of columns.

sqlpuzzle.select_from(*args, **kwds)[source]¶: Returns Select instance and passed arguments are used for list of tables. Columns are set to *.

sqlpuzzle.insert()[source]¶: Returns Insert instance. But probably you want to use insert_into() instead.

sqlpuzzle.insert_into(table)[source]¶: Returns Insert instance and passed argument is used for table.

sqlpuzzle.update(table)[source]¶: Returns Update instance and passed argument is used for table.

sqlpuzzle.delete(*tables)[source]¶: Returns Delete instance and passed arguments are used for list of tables from which really data should be deleted. But probably you want to use delete_from() instead.

sqlpuzzle.delete_from(*args, **kwds)[source]¶: Returns Delete instance and passed arguments are used for list of tables.

Helpers¶

sqlpuzzle.Q(*args, **kwds)[source]¶

Use as condition (where, having, …) and pass it to condition. Works like Q object in Django, so you can use it with logical operands (& and |).

sqlpuzzle.where(Q(name='Michael', country=None) | Q(name='Alan'))

sqlpuzzle.C(string='')¶

Or sqlpuzzle.customsql.

Force custom SQL if it’s not supported by sqlpuzzle.

>>> sqlpuzzle.select(sqlpuzzle.C('IFNULL(col, 42) AS col'))
<Select: SELECT IFNULL(col, 42) AS col>
>>> sqlpuzzle.update('t').set(sqlpuzzle.C('`age` = `age` + 1')).where(id=42)
<Update: UPDATE "t" SET `age` = `age` + 1 WHERE "id" = 42>

sqlpuzzle.V(value)¶

Or sqlpuzzle.sqlvalue.

SQL values which are escaped. Like values in conditions. SqlPuzzle by default behave to some arguments automatically as SQL value and to some as SQL reference. Use this when SqlPuzzle uses SQL reference instead of value.

>>> sqlpuzzle.select('a')
<Select: SELECT `a`>
>>> sqlpuzzle.select(sqlpuzzle.V('a'))
<Select: SELECT 'a'>

sqlpuzzle.R(value)¶

Or sqlpuzzle.sqlreference.

SQL reference is some column. SqlPuzzle by default behave to some arguments automatically as SQL value and to some as SQL reference. Use this when SqlPuzzle uses SQL value instead of reference.

>>> sqlpuzzle.select_from('t').where(name='surname')
<Select: SELECT * FROM `t` WHERE `name` = 'surname'>
>>> sqlpuzzle.select_from('t').where(name=sqlpuzzle.R('surname'))
<Select: SELECT * FROM `t` WHERE `name` = `surname`>

Functions¶

sqlpuzzle.exists(expr)[source]¶: Function EXISTS(expr)

sqlpuzzle.avg(expr)[source]¶: Function AVG(expr)

sqlpuzzle.avg_distinct(expr)[source]¶: Function AVG(DICTINCT expr)

sqlpuzzle.count(expr=None)[source]¶: Function COUNT(expr)

sqlpuzzle.count_distinct(expr=None)[source]¶: Function COUNT(DISTINCT expr)

sqlpuzzle.max(expr)[source]¶: Function MAX(expr)

sqlpuzzle.max_distinct(expr)[source]¶: Function MAX(DISTINCT expr)

sqlpuzzle.min(expr)[source]¶: Function MIN(expr)

sqlpuzzle.min_distinct(expr)[source]¶: Function MIN(DISTINCT expr)

sqlpuzzle.sum(expr)[source]¶: Function SUM(expr)

sqlpuzzle.sum_distinct(expr)[source]¶: Function SUM(DISTINCT expr)

sqlpuzzle.concat(*expr)[source]¶: Function CONCAT(expr)

sqlpuzzle.group_concat(*expr)[source]¶

Function GROUP_CONCAT(expr [ORDER BY [SEPARATOR]])

Returns:	`GroupConcat`

sqlpuzzle.convert(expr, type_=None)[source]¶

Function CONVERT(expr, type)

Returns:	`Convert`

class sqlpuzzle._queryparts.functions.GroupConcat(*expr)[source]¶

order_by(*args)[source]¶

Order of concatination.

>>> sqlpuzzle.group_concat('col').order_by('col')
<GroupConcat: GROUP_CONCAT("col" ORDER BY "col")>

separator(separator)[source]¶

Separator of values.

>>> sqlpuzzle.group_concat('col').separator('-')
<GroupConcat: GROUP_CONCAT("col" SEPARATOR '-')>

class sqlpuzzle._queryparts.functions.Convert(expr, type_=None)[source]¶

to(type_)[source]¶

Convert to which type.

>>> sqlpuzzle.convert('col').to('unsigned')
<Convert: CONVERT("col", UNSIGNED)>

>>> sqlpuzzle.convert('col', 'unsigned')
<Convert: CONVERT("col", UNSIGNED)>

Relations¶

Use relations in conditions. For example:

>>> sqlpuzzle.select_from('t').where(name=sqlpuzzle.relations.LIKE('M%'))
<Select: SELECT * FROM "t" WHERE "name" LIKE 'M%'>

class sqlpuzzle.relations.EQ(value)[source]¶

Relation =.

Default relation in most cases.

sqlpuzzle.relations.EQUAL_TO¶: alias of sqlpuzzle.relations.EQ

class sqlpuzzle.relations.GE(value)[source]¶: Relation >=.

sqlpuzzle.relations.GRATHER_THAN¶: alias of sqlpuzzle.relations.GT

sqlpuzzle.relations.GRATHER_THAN_OR_EQUAL_TO¶: alias of sqlpuzzle.relations.GE

class sqlpuzzle.relations.GT(value)[source]¶: Relation >.

class sqlpuzzle.relations.IN(*args)[source]¶

Relation IN.

If you pass None in list, it will behave correctly:

>>> sqlpuzzle.select_from('t').where(col=sqlpuzzle.relations.IN([1,2,None]))
<Select: SELECT * FROM "t" WHERE ("col" IN (1, 2) OR "col" IS NULL)>

class sqlpuzzle.relations.IS(value)[source]¶: Relation IS.

class sqlpuzzle.relations.IS_NOT(value)[source]¶: Relation IS NOT.

class sqlpuzzle.relations.LE(value)[source]¶: Relation <=.

sqlpuzzle.relations.LESS_THAN¶: alias of sqlpuzzle.relations.LT

sqlpuzzle.relations.LESS_THAN_OR_EQUAL_TO¶: alias of sqlpuzzle.relations.LE

class sqlpuzzle.relations.LIKE(value)[source]¶: Relation LIKE.

class sqlpuzzle.relations.LT(value)[source]¶: Relation <.

class sqlpuzzle.relations.NE(value)[source]¶: Relation !=.

sqlpuzzle.relations.NOT_EQUAL_TO¶: alias of sqlpuzzle.relations.NE

class sqlpuzzle.relations.NOT_IN(*args)[source]¶

Relation NOT IN.

If you pass None in list, it will behave correctly:

>>> sqlpuzzle.select_from('t').where(col=sqlpuzzle.relations.NOT_IN([1,2,None]))
<Select: SELECT * FROM "t" WHERE ("col" NOT IN (1, 2) AND "col" IS NOT NULL)>

Changed in version 1.7.0: There was bug that it generated ("col" NOT IN (1, 2) OR "col" IS NULL) instead of correct condition.

class sqlpuzzle.relations.NOT_LIKE(value)[source]¶: Relation NOT LIKE.

class sqlpuzzle.relations.REGEXP(value)[source]¶: Relation REGEXP.

Select¶

class sqlpuzzle._queries.select.Select(*args, **kwds)[source]¶

Examples:

>>> sql = sqlpuzzle.select('id', 'name')
>>> sql.from_('user')
>>> sql.join('address').on('address.user_id', 'user.id')
>>> sql.where({'user.name': sqlpuzzle.relations.LIKE('%Al%'), 'address.city': 'Prague'})
>>> sql.group_by('user.id')
>>> sql.order_by('user.name', ('user.sallary', 'desc'))
>>> sql.limit(20)
<Select:
    SELECT "id", "name"
        FROM "user" JOIN "address" ON "address"."user_id" = "user"."id"
        WHERE "address"."city" = 'Prague' AND "user"."name" LIKE '%Al%'
        GROUP BY "user"."id"
        ORDER BY "user"."name", "user"."sallary" DESC
        LIMIT 20
>

>>> sqlpuzzle.select('name').from_(sql).where(
<Select: SELECT "name" FROM (...)>

__and__(other)[source]¶

Returns sqlpuzzle._queries.union.Union instance, UNION ALL.

>>> sqlpuzzle.select('t') & sqlpuzzle.select('u')
<Union: SELECT "t" UNION ALL SELECT "u">

__or__(other)[source]¶

Returns sqlpuzzle._queries.union.Union instance, UNION.

>>> sqlpuzzle.select('t') | sqlpuzzle.select('u')
<Union: SELECT "t" UNION SELECT "u">

all(allow=True)[source]¶

columns(*args, **kwds)[source]¶

distinct(allow=True)[source]¶

distinctrow(allow=True)[source]¶

fields_terminated_by(fields_terminated_by)[source]¶

for_update(allow=True)[source]¶

from_(*args, **kwds)[source]¶

from_table(table, alias=None)[source]¶

from_tables(*args, **kwds)¶

full_join(table)[source]¶: New in version 1.7.0.

group_by(*args, **kwds)[source]¶

Default ordering is ASC.

group_by accept dict as you would expect, but note that dict does not have same order. Same for named arguments.

>>> sqlpuzzle.select('c').from_('t').group_by('a', ('b', 'desc'))
<Select: SELECT "c" FROM "t" GROUP BY "a", "b" DESC>

has(querypart_name, value=None)[source]¶

Returns True if querypart_name with value is set. For example you can check if you already used condition by sql.has('where').

If you want to check for more information, for example if that condition also contain ID, you can do this by sql.has('where', 'id').

having(*args, **kwds)[source]¶

high_priority(allow=True)[source]¶

inner_join(table)[source]¶

into_outfile(into_outfile)[source]¶

join(table)[source]¶

left_join(table)[source]¶

limit(limit, offset=None)[source]¶

lines_terminated_by(lines_terminated_by)[source]¶

offset(offset)[source]¶

on(*args, **kwds)[source]¶

optionally_enclosed_by(optionally_enclosed_by)[source]¶

order_by(*args, **kwds)[source]¶

Default ordering is ASC.

order_by accept dict as you would expect, but note that dict does not have same order.

>>> sqlpuzzle.select('c').from_('t').order_by('a', ('b', 'desc'))
<Select: SELECT "c" FROM "t" ORDER BY "a", "b" DESC>

right_join(table)[source]¶

sql_big_result(allow=True)[source]¶

sql_buffer_result(allow=True)[source]¶

sql_cache(allow=True)[source]¶

sql_calc_found_rows(allow=True)[source]¶

sql_no_cache(allow=True)[source]¶

sql_small_result(allow=True)[source]¶

straight_join(allow=True)[source]¶

where(*args, **kwds)[source]¶

Union¶

class sqlpuzzle._queries.union.Union(query1, query2, union_type='UNION')[source]¶

Class representing UNION or UNION ALL.

You will get it by calling magic methods on Select:

>>> sqlpuzzle.select('t') & sqlpuzzle.select('u')
<Union: SELECT "t" UNION ALL SELECT "u">
>>> sqlpuzzle.select('t') | sqlpuzzle.select('u')
<Union: SELECT "t" UNION SELECT "u">

__and__(other)[source]¶

Returns sqlpuzzle._queries.union.Union instance, UNION ALL.

>>> sqlpuzzle.select('t') & sqlpuzzle.select('u') & sqlpuzzle.select('v')
<Union: SELECT "t" UNION ALL SELECT "u" UNION ALL SELECT "v">

__or__(other)[source]¶

Returns sqlpuzzle._queries.union.Union instance, UNION.

>>> sqlpuzzle.select('t') | sqlpuzzle.select('u') | sqlpuzzle.select('v')
<Union: SELECT "t" UNION SELECT "u" UNION SELECT "v">

Insert¶

class sqlpuzzle._queries.insert.Insert[source]¶

Example:

>>> sql = sqlpuzzle.insert_into('table')
>>> sql.values(name='Alan', salary=12345.67)
>>> sql.values(name='Bob', age=42)
<Insert: INSERT INTO "table" ("age", "name", "salary") VALUES (NULL, 'Alan', 12345.67000), (42, 'Bob', NULL)>

ignore(allow=True)[source]¶

into(table)[source]¶

on_duplicate_key_update(*args, **kwds)[source]¶

values(*args, **kwds)[source]¶

Update¶

class sqlpuzzle._queries.update.Update(table=None)[source]¶

Example:

>>> sqlpuzzle.update('t').set(name='Alan', sallary=12345.67).where(id=1)
<Update: UPDATE "t" SET "name" = 'Alan', "sallary" = 12345.67000 WHERE "id" = 1>

allow_update_all()[source]¶

Allow query without WHERE condition.

By default update without condition will raise exception ConfirmUpdateAllException. If you want really update all rows without condition, allow it by calling this method.

>>> sqlpuzzle.update('t')
Traceback (most recent call last):
  ...
ConfirmUpdateAllException: Are you sure, that you want update all records?
>>> sqlpuzzle.update('t').set(a=1).allow_update_all()
<Update: UPDATE "t" SET "a" = 1>

forbid_update_all()[source]¶

Forbid query without WHERE condition.

By default update without condition will raise exception ConfirmUpdateAllException. It can be allowed by calling method allow_update_all(). If you want to again forbid it, call this method.

ignore(allow=True)[source]¶

inner_join(table)[source]¶

join(table)[source]¶

left_join(table)[source]¶

on(*args, **kwds)[source]¶

right_join(table)[source]¶

set(*args, **kwds)[source]¶

table(table)[source]¶

where(*args, **kwds)[source]¶

Delete¶

class sqlpuzzle._queries.delete.Delete(*tables)[source]¶

Example:

>>> sqlpuzzle.delete_from('t').where(id=1)
<Delete: DELETE FROM "t" WHERE "id" = 1>

allow_delete_all()[source]¶

Allow query without WHERE condition.

By default delete without condition will raise exception ConfirmDeleteAllException. If you want really delete all rows without condition, allow it by calling this method.

>>> sqlpuzzle.delete_from('t')
Traceback (most recent call last):
  ...
ConfirmDeleteAllException: Are you sure, that you want delete all records?
>>> sqlpuzzle.delete_from('t').allow_delete_all()
<Delete: DELETE FROM "t">

delete(*tables)[source]¶

forbid_delete_all()[source]¶

Forbid query without WHERE condition.

By default delete without condition will raise exception ConfirmDeleteAllException. It can be allowed by calling method allow_delete_all(). If you want to again forbid it, call this method.

from_(*args, **kwds)[source]¶

from_table(table, alias=None)[source]¶

from_tables(*args, **kwds)[source]¶

ignore(allow=True)[source]¶

inner_join(table)[source]¶

join(table)[source]¶

left_join(table)[source]¶

on(*args, **kwds)[source]¶

right_join(table)[source]¶

where(*args, **kwds)[source]¶

Exceptions¶

exception sqlpuzzle.exceptions.ConfirmDeleteAllException[source]¶: Raises when you want render delete SQL without any where condition. It is security before droping all data by mistake.

exception sqlpuzzle.exceptions.ConfirmException[source]¶: Base confirm exception. If you want to handle any confirm exception (of update or delete) in same way, catch this one.

exception sqlpuzzle.exceptions.ConfirmUpdateAllException[source]¶: Raises when you want render update SQL without any where condition. It is security before changing all data by mistake.

exception sqlpuzzle.exceptions.InvalidArgumentException(message='')[source]¶

Raises when you pass invalid argument into SQL puzzle. For example instead of column reference some number and so.

>>> sqlpuzzle.select(True)
Traceback (most recent call last):
  ...
InvalidArgumentException: Invalid argument: column_name cannot be of type <type 'bool'>.

exception sqlpuzzle.exceptions.InvalidQueryException(message='')[source]¶

Specific type of InvalidArgumentException. Raises when you passed good arguments but final query does not make sense.

>>> sqlpuzzle.select_from('t').on('t2')
Traceback (most recent call last):
  ...
InvalidQueryException: Invalid query: You can not set join condition to nothing. Specify join table first.

exception sqlpuzzle.exceptions.SqlPuzzleException(message)[source]¶: Base exception. If you want to handle any exception of SQL puzzle in same way, catch this one.

SQLPuzzle documentation¶

Installation¶

Documentation¶

Configuration¶

Base methods¶

Helpers¶

Functions¶

Relations¶

Select¶

Union¶

Insert¶

Update¶

Delete¶

Exceptions¶

Table Of Contents

This Page