The Common Queries — mosql.query

This module provides the common Query instances for you.

The basic queries are here:

It also provides various join queries, use them with the above queries:

The non-standard queries are also available:

If you want to build you own, there are all basic bricks you need - All Bricks You Need — mosql.util.

Note

If you are using non-standard SQL, such as MySQL, check The Patches for Non-standard SQLs.

New in version v0.6.

mosql.query.select(table=None, where=None, **clause_args)

It generates the SQL statement, SELECT ... .

Use dict or pairs to represent a where condition:

>>> print(select('person', {'person_id': 'mosky'}))
SELECT * FROM "person" WHERE "person_id" = 'mosky'

>>> print(select('person', (('person_id', 'mosky'), )))
SELECT * FROM "person" WHERE "person_id" = 'mosky'

>>> print(select('person', {'name like': 'Mosky%'}, limit=3, offset=1))
SELECT * FROM "person" WHERE "name" LIKE 'Mosky%' LIMIT 3 OFFSET 1

The operator will be decided automatically:

>>> print(select('person', {'person_id': ('andy', 'bob')}))
SELECT * FROM "person" WHERE "person_id" IN ('andy', 'bob')

>>> print(select('person', {'name': None}))
SELECT * FROM "person" WHERE "name" IS NULL

And also allow to customize:

>>> print(select('person', {'name like': 'Mosky%', 'age >': 20}))
SELECT * FROM "person" WHERE "age" > 20 AND "name" LIKE 'Mosky%'

>>> print(select('person', {('name', 'like'): 'Mosky%', ('age', '>'): 20}))
SELECT * FROM "person" WHERE "age" > 20 AND "name" LIKE 'Mosky%'

>>> print(select('person', {"person_id = '' OR true; --": 'mosky'}))
Traceback (most recent call last):
    ...
OperatorError: this operator is not allowed: "= '' OR TRUE; --"

See also

The operators allowed — mosql.util.allowed_operators.

The ambiguous cases that Query works around for you:

>>> print(select('person', {'person_id': ()}))
SELECT * FROM "person" WHERE FALSE

>>> print(select('person', where=None))
SELECT * FROM "person"

>>> print(select('person', where={}))
SELECT * FROM "person"

See also

How it builds where clause — mosql.util.build_where()

The columns is an alias of standard clause argument, select. Any simple iterable represents the list in SQL. It also understands the . (dot) and even AS in your string.

>>> print(select('person', select=('person.person_id', 'person.name')))
SELECT "person"."person_id", "person"."name" FROM "person"

>>> print(select('person', columns=('person.person_id', 'person.name')))
SELECT "person"."person_id", "person"."name" FROM "person"

>>> print(select('person', columns=('person.person_id as id', 'person.name')))
SELECT "person"."person_id" AS "id", "person"."name" FROM "person"

See also

How it handles identifier including AS — mosql.util.identifier_as()

Specify group_by, having, order_by, limit, and offset in keyword arguments:

>>> print(select('person', {'name like': 'Mosky%'}, order_by=('age', )))
SELECT * FROM "person" WHERE "name" LIKE 'Mosky%' ORDER BY "age"

>>> print(select('person', {'name like': 'Mosky%'}, order_by=('age desc', )))
SELECT * FROM "person" WHERE "name" LIKE 'Mosky%' ORDER BY "age" DESC

>>> print(select('person', {'name like': 'Mosky%'}, order_by=('age ; DROP person; --', )))  # doctest: +SKIP
Traceback (most recent call last):
    ...
DirectionError: this direction is not allowed: '; DROP PERSON; --'

See also

The directions allowed — mosql.util.allowed_directions.

The prepare statement is also available with mosql.util.param. If you want to build a named parameter with the same name of the column, you can use a special token, mosql.util.autoparam. It also has a shortcut, mosql.util.___:

>>> print(select('table', {'custom_param': param('my_param'), 'auto_param': autoparam, 'using_alias': ___}))
SELECT * FROM "table" WHERE "auto_param" = %(auto_param)s AND "using_alias" = %(using_alias)s AND "custom_param" = %(my_param)s

If you want to use functions, wrap it with mosql.util.raw:

>>> print(select('person', columns=raw('count(*)'), group_by=('age', )))
SELECT count(*) FROM "person" GROUP BY "age"

Warning

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

The PostgreSQL-specific FOR, OF and NOWAIT are also supported:

>>> print(select('person', {'person_id': 1}, for_='update', of=('person', ), nowait=True))
SELECT * FROM "person" WHERE "person_id" = 1 FOR UPDATE OF "person" NOWAIT

See also

Check PostgreSQL SELECT - The locking Clause for more detail.

The MySQL-specific FOR UPDATE and LOCK IN SHARE MODE are also available:

>>> print(select('person', {'person_id': 1}, for_update=True))
SELECT * FROM "person" WHERE "person_id" = 1 FOR UPDATE

>>> print(select('person', {'person_id': 1}, lock_in_share_mode=True))
SELECT * FROM "person" WHERE "person_id" = 1 LOCK IN SHARE MODE

See also

Check MySQL Locking Reads for more detail.

Print it for the full usage:

>>> print(select)
select(table=None, where=None, *, select=None, from=None, joins=None, where=None, group_by=None, having=None, order_by=None, limit=None, offset=None, for=None, of=None, nowait=None, for_update=None, lock_in_share_mode=None)

The last tip, echo the SQL to debug:

>>> select.enable_echo()
>>> sql = select()
SELECT *
>>> print sql
SELECT *

Changed in version 0.9: Added FOR UPDATE, LOCK IN SHARE MODE, FOR, OF, and NOWAIT.

mosql.query.insert(table=None, set=None, **clause_args)

It generates the SQL statement, INSERT INTO ....

The following usages generate the same SQL statement:

>>> print(insert('person', {'person_id': 'mosky', 'name': 'Mosky Liu'}))  # doctest: +SKIP
INSERT INTO "person" ("person_id", "name") VALUES ('mosky', 'Mosky Liu')

>>> print(insert('person', (('person_id', 'mosky'), ('name', 'Mosky Liu'))))
INSERT INTO "person" ("person_id", "name") VALUES ('mosky', 'Mosky Liu')

>>> print(insert('person', columns=('person_id', 'name'), values=('mosky', 'Mosky Liu')))
INSERT INTO "person" ("person_id", "name") VALUES ('mosky', 'Mosky Liu')

Sometimes we don’t need the columns:

>>> print(insert('person', values=('mosky', 'Mosky Liu')))
INSERT INTO "person" VALUES ('mosky', 'Mosky Liu')

The values allows values-list:

>>> print(insert('person', values=[('mosky', 'Mosky Liu'), ('yiyu', 'Yi-Yu Liu')]))
INSERT INTO "person" VALUES ('mosky', 'Mosky Liu'), ('yiyu', 'Yi-Yu Liu')

All of the insert(), update() and delete() support returning:

>>> print(insert('person', {'person_id': 'mosky', 'name': 'Mosky Liu'}, returning=raw('*')))
INSERT INTO "person" ("person_id", "name") VALUES ('mosky', 'Mosky Liu') RETURNING *

The MySQL-specific ON DUPLICATE KEY UPDATE is also supported:

>>> print(insert('person', values=('mosky', 'Mosky Liu'), on_duplicate_key_update={'name': 'Mosky Liu'}))
INSERT INTO "person" VALUES ('mosky', 'Mosky Liu') ON DUPLICATE KEY UPDATE "name"='Mosky Liu'

Print it for the full usage:

>>> print(insert)
insert(table=None, set=None, *, insert_into=None, columns=None, values=None, returning=None, on_duplicate_key_update=None)

Changed in version 0.10: Let values supports values-list.

mosql.query.replace(table=None, set=None, **clause_args)

It generates the SQL statement, REPLACE INTO...:

>>> print(replace('person', {'person_id': 'mosky', 'name': 'Mosky Liu'}))
REPLACE INTO "person" ("person_id", "name") VALUES ('mosky', 'Mosky Liu')

It is almost same as insert().

Print it for the full usage:

>>> print(replace)
replace(table=None, set=None, *, replace_into=None, columns=None, values=None)
mosql.query.update(table=None, where=None, set=None, **clause_args)

It generates the SQL statement, UPDATE ... .

The following usages generate the same SQL statement.

>>> print(update('person', {'person_id': 'mosky'}, {'name': 'Mosky Liu'}))
UPDATE "person" SET "name"='Mosky Liu' WHERE "person_id" = 'mosky'

>>> print(update('person', (('person_id', 'mosky'), ), (('name', 'Mosky Liu'),) ))
UPDATE "person" SET "name"='Mosky Liu' WHERE "person_id" = 'mosky'

Print it for the full usage:

>>> print(update)
update(table=None, where=None, set=None, *, update=None, set=None, where=None, returning=None)

See also

How it builds the set clause — mosql.util.build_set()

mosql.query.delete(table=None, where=None, **clause_args)

It generates the SQL statement, DELETE FROM ... .

The following usages generate the same SQL statement.

>>> print(delete('person', {'person_id': 'mosky'}))
DELETE FROM "person" WHERE "person_id" = 'mosky'

>>> print(delete('person', (('person_id', 'mosky'), )))
DELETE FROM "person" WHERE "person_id" = 'mosky'

Print it for the full usage:

>>> print(delete)
delete(table=None, where=None, *, delete_from=None, where=None, returning=None)
mosql.query.join(table=None, on=None, **clause_args)

It generates the SQL statement, ... JOIN ... .

If you don’t give type, nor on or using, the type will be NATURAL; otherwise type will be INNER.

>>> print(select('person', joins=join('detail')))
SELECT * FROM "person" NATURAL JOIN "detail"

>>> print(select('person', joins=join('detail', {'person.person_id': 'detail.person_id'})))
SELECT * FROM "person" INNER JOIN "detail" ON "person"."person_id" = "detail"."person_id"

>>> print(select('person', joins=join('detail', using=('person_id', ))))
SELECT * FROM "person" INNER JOIN "detail" USING ("person_id")

>>> print(select('person', joins=join('detail', using=('person_id', ), type='left')))
SELECT * FROM "person" LEFT JOIN "detail" USING ("person_id")

>>> print(select('person', joins=join('detail', type='cross')))
SELECT * FROM "person" CROSS JOIN "detail"

Print it for the full usage:

>>> print(join)
join(table=None, on=None, *, type=None, join=None, on=None, using=None)

See also

How it builds the on clause — mosql.util.build_on()

mosql.query.left_join(table=None, on=None, **clause_args)

It generates the SQL statement, LEFT JOIN ... .

>>> print(select('person', joins=left_join('detail', using=('person_id', ))))
SELECT * FROM "person" LEFT JOIN "detail" USING ("person_id")
mosql.query.right_join(table=None, on=None, **clause_args)

It generates the SQL statement, RIGHT JOIN ... .

>>> print(select('person', joins=right_join('detail', using=('person_id', ))))
SELECT * FROM "person" RIGHT JOIN "detail" USING ("person_id")
mosql.query.cross_join(table=None, on=None, **clause_args)

It generates the SQL statement, CROSS JOIN ... .

>>> print(select('person', joins=cross_join('detail')))
SELECT * FROM "person" CROSS JOIN "detail"