All Bricks You Need — mosql.util

It provides basic bricks to build SQLs.

The five functions below are the core functions of MoSQL and abstract the variety of SQL specs. You can override them to let MoSQL support non-standard SQL specs.

escape(s)	It escapes the value.
format_param([s])	It formats the parameter of prepared statement.
stringify_bool(b)	It stringifies the bool.
delimit_identifier(s)	It delimits the identifier.
escape_identifier(s)	It escapes the identifier.

Note

There are two built-in patches: mosql.mysql and mosql.sqlite.

They are the subclasses of str. You can use them to represent simple string but having special SQL meaning:

raw	The qualifier functions do nothing when the input is an instance of this class.
param	The value() builds this type as a parameter for the prepared statement.

The built-in raw instances:

default	The DEFAULT keyword in SQL.
star	The * keyword in SQL.

The functions which are qualifier() functions format the Python objects into SQL strings:

value(x)	A qualifier function which formats Python object as SQL value.
identifier(x)	A qualifier function which formats Python object as SQL identifier.
identifier_as(x)	A qualifier function which formats Python object as SQL identifiers with AS.
identifier_dir(x)	A qualifier function which formats Python object as SQL identifiers with order direction.
paren(x)	A qualifier function which encloses the input with () (paren).

The functions which are joiner() functions concatenate the SQL strings:

concat_by_and(x)	A joiner function which concats the iterable by 'AND'.
concat_by_or(x)	A joiner function which concats the iterable by 'OR'.
concat_by_space(x)	A joiner function which concats the iterable by a space.
concat_by_comma(x)	A joiner function which concats the iterable by , (comma).
build_values_list(x)	A joiner function which builds the values-list for VALUES clause.
build_where(x)	A joiner function which builds the where-list of SQL from a dict or pairs.
build_set(x)	A joiner function which builds the set-list of SQL from a dict or pairs.
build_on(x)	A joiner function which builds the on-list of SQL from a dict or pairs.

The helper functions below fill the gap between the Python objects and SQL:

or_(conditions)	It concats the conditions by OR.
and_(conditions)	It concats the conditions by AND.
dot(s, t)	It treats s and t as identifiers, concats them by ., and then makes the whole string as raw.
as_(s, t)	It treats s and t as identifiers, concats them by AS, and then makes the whole string as raw.
asc(s)	It treats s as an identifier, adds ASC after s, and then makes the whole string as raw.
desc(s)	It treats s as an identifier, adds DESC after s, and then makes the whole string as raw.
subq(s)	It adds parens and makes s as raw.
in_operand(x)	It stringifies x as an right operand for IN.

The main classes let you combine the bricks above to create a final SQL builder:

Clause(name[, formatters, hidden, alias, …])	It represents a clause of SQL.
Statement(clauses[, preprocessor])	It represents a statement of SQL.
Query(statement[, positional_keys, clause_args])	It makes Statement callable and partializeable.

Changed in version 0.1.6: It is rewritten and totally different from old version.

mosql.util.escape(s)

It escapes the value.

By default, it just replaces ' (single-quote) with '' (two single-quotes).

It aims at avoiding SQL injection. Here are some examples:

>>> tmpl = "select * from person where person_id = '%s';"
>>> evil_value = "' or true; -- "

>>> print(tmpl % evil_value)
select * from person where person_id = '' or true; -- ';

>>> print(tmpl % escape(evil_value))
select * from person where person_id = ''' or true; -- ';

Warning

Please use UTF-8 as your connection encoing. Simple escaping will have secuirty risk if you use double-byte connection encoding, such as Big5 or GBK.

Changed in version 0.10: It will raise a ValueError if s contains a null byte (\x00).

mosql.util.format_param(s=u'')

It formats the parameter of prepared statement.

By default, it formats the parameter in pyformat.

>>> print(format_param('name'))
%(name)s

>>> print(format_param())
%s

mosql.util.stringify_bool(b)

It stringifies the bool.

By default, it returns TRUE if b is true, otherwise it returns FALSE.

mosql.util.delimit_identifier(s)

It delimits the identifier.

By default, it conforms the standard to encloses the identifier, s, by " (double quote).

Changed in version 0.10: It’s not disableable anymore. Use raw instead.

mosql.util.escape_identifier(s)

It escapes the identifier.

By default, it just replaces " (double-quote) with "" (two double-quotes).

It also aims at avoid SQL injection. Here are some examples:

>>> tmpl = 'select * from person where "%s" = \'mosky\';'
>>> evil_identifier = 'person_id" = \'\' or true; -- '

>>> print(tmpl % evil_identifier)
select * from person where "person_id" = '' or true; -- " = 'mosky';

>>> print(tmpl % escape_identifier(evil_identifier))
select * from person where "person_id"" = '' or true; -- " = 'mosky';

Warning

Please use UTF-8 as your connection encoing. Simple escaping will have secuirty risk if you use double-byte connection encoding, such as Big5 or GBK.

Changed in version 0.10: It will raise a ValueError if s contains a null byte (\x00).

class mosql.util.raw

The qualifier functions do nothing when the input is an instance of this class. This is a subclass of the built-in text type.

Warning

It’s your responsibility to ensure the security when you use raw string.

class mosql.util.param

The value() builds this type as a parameter for the prepared statement.

>>> print(value(param('')))
%s
>>> print(value(param('name')))
%(name)s

This is a subclass of the built-in text type.

The ___ is an alias of it.

mosql.util.default = raw(u'DEFAULT')

The DEFAULT keyword in SQL.

mosql.util.star = raw(u'*')

The * keyword in SQL.

mosql.util.qualifier(f)

A decorator which makes all items in an iterable apply a qualifier function, f, or simply apply the qualifier function to the input if the input is not an iterable.

The iterable here means the iterable except string.

It also makes a qualifier function returns the input without changes if the input is an instance of raw.

mosql.util.paren(x)

A qualifier function which encloses the input with () (paren).

mosql.util.value(x)

A qualifier function which formats Python object as SQL value.

>>> print(value('normal string'))
'normal string'

>>> print(value(u'normal unicode'))
'normal unicode'

>>> print(value(True))
TRUE

>>> print(value(datetime(2013, 4, 19, 14, 41, 10)))
'2013-04-19 14:41:10'

>>> print(value(date(2013, 4, 19)))
'2013-04-19'

>>> print(value(time(14, 41, 10)))
'14:41:10'

>>> print(value(raw('count(person_id) > 1')))
count(person_id) > 1

>>> print(value(param('myparam')))
%(myparam)s

exception mosql.util.DirectionError(op)

The instance of it will be raised when identifier() detects an invalid direction.

See also

The operators allowed — allowed_directions.

mosql.util.allowed_directions = set([u'ASC', u'DESC'])

The directions which are allowed by identifier_dir().

A DirectionError will be raised if a direction not allowed is found.

Changed in version 0.10: It’s not disableable anymore. Use raw instead.

mosql.util.identifier(x)

A qualifier function which formats Python object as SQL identifier.

It uses the delimit_identifier() and escape_identifier() to qualify the input.

>>> print(identifier('column_name'))
"column_name"

>>> print(identifier('table_name.column_name'))
"table_name"."column_name"

It also supports to use pair in pair-list format.

>>> print(identifier([('table_name', 'column_name')])[0])
"table_name"."column_name"

Changed in version 0.10: Support to use pair-list to represent dot.

Changed in version 0.10: It doesn’t support AS and order directon anymore. Use identifier_as() or identifier_dir() instead.

See also

There is also a dot() function.

mosql.util.identifier_as(x)

A qualifier function which formats Python object as SQL identifiers with AS.

>>> print(identifier_as('column_name as c'))
"column_name" AS "c"

>>> print(identifier_as('table_name.column_name as c'))
"table_name"."column_name" AS "c"

It also supports to use pair in pair-list format. It is a qualifier function, so you have to put the pair in another list.

>>> print(identifier_as([('table_name.column_name', 'c')])[0])
"table_name"."column_name" AS "c"

>>> print(identifier_as([(raw('count(table_name.column_name)'), 'c')])[0])
count(table_name.column_name) AS "c"

It uses identifier() to build normal identifier string without AS.

>>> print(identifier_as('column_name'))
"column_name"

>>> print(identifier_as('table_name.column_name'))
"table_name"."column_name"

See also

There is also an as_() function.

New in version 0.10.

mosql.util.identifier_dir(x)

A qualifier function which formats Python object as SQL identifiers with order direction.

>>> print(identifier_dir('table_name ASC'))
"table_name" ASC

>>> print(identifier_dir('table_name.column_name DESC'))
"table_name"."column_name" DESC

It also supports to use pair in pair-list format. It is a qualifier function, so you have to put the pair in another list.

>>> print(identifier_dir([('table_name.column_name', 'ASC')])[0])
"table_name"."column_name" ASC

>>> print(identifier_dir([(raw('count(table_name.column_name)'), 'DESC')])[0])
count(table_name.column_name) DESC

It uses identifier() to build normal identifier string without order direction.

>>> print(identifier_dir('column_name'))
"column_name"

>>> print(identifier_dir('table_name.column_name'))
"table_name"."column_name"

See also

There are also asc() and desc() functions.

New in version 0.10.

mosql.util.joiner(f)

A decorator which makes the input apply this function only if the input is an iterable, otherwise it just returns the same input.

The iterable here means the iterable except string.

mosql.util.concat_by_comma(x)

A joiner function which concats the iterable by , (comma).

mosql.util.concat_by_and(x)

A joiner function which concats the iterable by 'AND'.

mosql.util.concat_by_space(x)

A joiner function which concats the iterable by a space.

mosql.util.concat_by_or(x)

A joiner function which concats the iterable by 'OR'.

exception mosql.util.OperatorError(op)

The instance of it will be raised when build_where() detects an invalid operator.

See also

The operators allowed — allowed_operators.

mosql.util.allowed_operators = set([u'!=', u'!~', u'!~*', u'<', u'<=', u'<>', u'=', u'>', u'>=', u'IN', u'IS', u'IS NOT', u'LIKE', u'NOT IN', u'NOT LIKE', u'NOT SIMILAR TO', u'SIMILAR TO', u'~', u'~*'])

The operators which are allowed by build_where().

An OperatorError will be raised if an operator not allowed is found.

Changed in version 0.10: It’s not disableable anymore. Use raw instead.

mosql.util.build_values_list(x)

A joiner function which builds the values-list for VALUES clause.

The x can be either

1. iterable values; or
2. iterable values in another iterable.

New in version 0.10.

mosql.util.build_where(x)

A joiner function which builds the where-list of SQL from a dict or pairs.

The x can be a dict or pairs:

>>> print(build_where({'detail_id': 1, 'age >= ': 20, 'created': date(2013, 4, 16)}))  # doctest: +SKIP
"created" = '2013-04-16' AND "detail_id" = 1 AND "age" >= 20

>>> print(build_where((('detail_id', 1), ('age >= ', 20), ('created', date(2013, 4, 16)))))
"detail_id" = 1 AND "age" >= 20 AND "created" = '2013-04-16'

The key can be a pair to include an operator:

>>> print(build_where({'detail_id': 1, ('age', '>='): 20, 'created': date(2013, 4, 16)}))
"age" >= 20 AND "detail_id" = 1 AND "created" = '2013-04-16'

The default operator will be decided by the value.

>>> print(build_where({'name': None}))
"name" IS NULL

>>> print(build_where({'person_id': ['andy', 'bob']}))
"person_id" IN ('andy', 'bob')

>>> print(build_where({'person_id': []}))
FALSE

See also

There are also subq() and in_operand() functions.

It supports all common operators, such as LIKE:

>>> print(build_where({'email like': '%@gmail.com%'}))
"email" LIKE '%@gmail.com%'

See also

By default, the operators are limited. Check the allowed_operators for more information.

If need, wrap key with raw() to use function:

>>> print(build_where({raw('count(person_id) >'): 10}))
count(person_id) > 10

Build prepared where:

>>> print(build_where({'custom_param': param('my_param'), 'auto_param': param, 'using_alias': ___}))
"auto_param" = %(auto_param)s AND "using_alias" = %(using_alias)s AND "custom_param" = %(my_param)s

It does nothing if x is a string:

>>> print(build_where('"detail_id" = 1 AND "age" >= 20 AND "created" = \'2013-04-16\''))
"detail_id" = 1 AND "age" >= 20 AND "created" = '2013-04-16'

Changed in version 0.10: Supports to use pair key to include operator.

Changed in version 0.10: If the value is empty iterable, it translates it into FALSE rather than x IN () which caused syntax error in SQL.

mosql.util.build_set(x)

A joiner function which builds the set-list of SQL from a dict or pairs.

The x can be a dict or pairs:

>>> print(build_set({'a': 1, 'b': True, 'c': date(2013, 4, 16)}))  # doctest: +SKIP
"a"=1, "c"='2013-04-16', "b"=TRUE

>>> print(build_set((('a', 1), ('b', True), ('c', date(2013, 4, 16)))))
"a"=1, "b"=TRUE, "c"='2013-04-16'

Building prepared set:

>>> print(build_set({'custom_param': param('myparam'), 'auto_param': param}))
"auto_param"=%(auto_param)s, "custom_param"=%(myparam)s

It does nothing if x is a string:

>>> print(build_set('"a"=1, "b"=TRUE, "c"=\'2013-04-16\''))
"a"=1, "b"=TRUE, "c"='2013-04-16'

mosql.util.build_on(x)

A joiner function which builds the on-list of SQL from a dict or pairs. The difference from build_where() is the value here will be treated as an identifier.

>>> print(build_on({'person_id': 'friend_id'}))
"person_id" = "friend_id"

>>> print(build_on((('person.person_id', 'detail.person_id'), )))
"person"."person_id" = "detail"."person_id"

>>> print(build_on({'person.age >': raw(20)}))
"person"."age" > 20

mosql.util.or_(conditions)

It concats the conditions by OR.

>>> print(or_(({'person_id': 'andy'}, {'person_id': 'bob'})))
("person_id" = 'andy') OR ("person_id" = 'bob')

Changed in version 0.7.2: It helps you to add parens now.

New in version 0.6.

mosql.util.and_(conditions)

It concats the conditions by AND.

>>> print(and_(({'person_id': 'andy'}, {'name': 'Andy'})))
("person_id" = 'andy') AND ("name" = 'Andy')

New in version 0.7.3.

mosql.util.dot(s, t)

It treats s and t as identifiers, concats them by ., and then makes the whole string as raw.

>>> print(dot('table', 'column'))
"table"."column"

New in version 0.10.

mosql.util.as_(s, t)

It treats s and t as identifiers, concats them by AS, and then makes the whole string as raw.

>>> print(as_('column', 'c'))
"column" AS "c"

>>> print(as_('table.column', 'c'))
"table"."column" AS "c"

New in version 0.10.

mosql.util.asc(s)

It treats s as an identifier, adds ASC after s, and then makes the whole string as raw.

>>> print(asc('score'))
"score" ASC

New in version 0.10.

mosql.util.desc(s)

It treats s as an identifier, adds DESC after s, and then makes the whole string as raw.

>>> print(desc('score'))
"score" DESC

New in version 0.10.

mosql.util.subq(s)

It adds parens and makes s as raw.

>>> print(subq("select person_id from person where join_ts >= '2014-11-27'"))
(select person_id from person where join_ts >= '2014-11-27')

New in version 0.10.

mosql.util.in_operand(x)

It stringifies x as an right operand for IN.

>>> print(in_operand(['a', 'b', 'c']))
('a', 'b', 'c')

If you use MoSQL’s Query, please just put x as value. This function is designed for the case which doesn’t use MoSQL’s Query.

New in version 0.10.

class mosql.util.Clause(name, formatters=(), hidden=False, alias=None, default=None, no_argument=False)

It represents a clause of SQL.

Parameters:

• prefix (str) – the lead word(s) of this clause
• formatters (sequence) – the qualifier or joiner functions
• hidden (bool) – it decides the prefix will be hidden or not
• alias (str) – another name of this clause
• default (str) – it will be used if you pass None to format()
• no_argument (bool) – set True if this clause doesn’t have any argument

Here is an example of using Clause:

>>> values = Clause('values', (value, concat_by_comma, paren))

>>> print(values.format(('a', 'b', 'c')))
VALUES ('a', 'b', 'c')

>>> print(values.format((default, 'b', 'c')))
VALUES (DEFAULT, 'b', 'c')

>>> print(values.format((raw('r'), 'b', 'c')))
VALUES (r, 'b', 'c')

Changed in version 0.9: Added no_argument and made formatters has default.

Changed in version 0.6: Added two arguments, alias and default.

format(x)

Apply x to this clause template.

Return type:str

class mosql.util.Statement(clauses, preprocessor=None)

It represents a statement of SQL.

Parameters:

• clauses (Clause) – the clauses which consist this statement
• preprocessor (function) – a preprocessor for the argument, clause_args, of the format()

Here is an example of using Statement:

>>> insert_into = Clause('insert into', (identifier, ))
>>> columns     = Clause('columns'    , (identifier, concat_by_comma, paren), hidden=True)
>>> values      = Clause('values'     , (value, concat_by_comma, paren))

>>> insert_into_stat = Statement((insert_into, columns, values))

>>> print(insert_into_stat.format({
...     'insert into': 'person',
...     'columns'    : ('person_id', 'name'),
...     'values'     : ('daniel', 'Diane Leonard'),
... }))
INSERT INTO "person" ("person_id", "name") VALUES ('daniel', 'Diane Leonard')

Changed in version 0.6: Added preprocessor.

format(clause_args)

Apply the clause_args to each clauses.

Parameters:clause_args (dict) – the arguments for the clauses Return type:str

Changed in version 0.10: Now it raises TypeError if there is any unused clause argument.

Changed in version 0.10: Now if an argument’s value is false in boolean context, it will ignore that argument.

class mosql.util.Query(statement, positional_keys=None, clause_args=None)

It makes Statement callable and partializeable.

Parameters:

• statement (Statement) – a statement
• positional_keys (sequence) – the positional arguments accepted by stringify().
• clause_args (dict) – the arguments of the clauses you want to predefine

You can use a Query instance like a function:

>>> from mosql.query import insert
>>> print(insert)
insert(table=None, set=None, *, insert_into=None, columns=None, values=None, returning=None, on_duplicate_key_update=None)

New in version 0.6.

breed(clause_args=None)

It merges the clause_args from both this instance and the argument, and then create new Query instance by that.

disable_echo()

Disables echo.

New in version 0.10.

enable_echo()

Enables echo.

New in version 0.10.

format(clause_args=None)

It merges the clause_args from both this instance and the arguments, and then apply to the statement.

stringify(*positional_values, **clause_args)

It is same as the format(), but the parameters are more like a function.

A Query instance is callable. When you call it, it uses this method to handle.