sqlquery

py-sql-query is a basic SQL translation layer in python. You construct queries using mainly python constructs which later can be serialized to a SQL query.

Examples

A simple select statement

>>> from sqlquery.queryapi import select
>>> select("id").on_table("users").where(("id__eq", 2)).sql()
(u'SELECT `a`.`id` FROM `users` AS `a` WHERE (`a`.`id` = %s)', (2,))

A simple update statement

>>> from sqlquery.queryapi import update
>>> update(username="john").on_table("users").where(("id__eq", 2)).sql()
(u'UPDATE `users` AS `a` SET `a`.`username` = %s WHERE (`a`.`id` = %s)', ('john', 2))

A simple insert statement

>>> from sqlquery.queryapi import insert
>>> insert(username="john", id=4).on_table("users").sql()
(u'INSERT INTO `users` (`username`, `id`) VALUES (%s, %s)', ('john', 4))

A simple order by statement

>>> from sqlquery.queryapi import select
>>> select("id").on_table("users").order_by("id").sql()
(u'SELECT `a`.`id` FROM `users` AS `a` ORDER BY `a`.`id`', ())

Using a function

>>> from sqlquery.queryapi import select, IF, Literal
>>> select(IF(("id__lt", Literal(0)), "id", "newID")).on_table("users").order_by("id").sql()
(u'SELECT IF((`a`.`id` < 0), `a`.`id`, `a`.`newID`) FROM `users` AS `a` ORDER BY `a`.`id`', ())

A subquery

>>> from sqlquery.queryapi import select
>>> select(
      "username", "id"
    ).on_table("users").where(
      ("id__in", select("id").on_table("banned_users"))
    ).sql()
(u'SELECT `a`.`username`, `a`.`id` FROM `users` AS `a` WHERE (`a`.`id` IN (SELECT `b`.`id` FROM `banned_users` AS `b`))',
 ())

A more involved statement

>>> from sqlquery.queryapi import select
>>> select(
      "username", "id", "contactinfo.address"
    ).on_table("users").join(
      "contactinfo",
      "id",
    ).where(
      ('id__in', [1, 2, 3, 4]), ('contactinfo.country__eq', 'US')
    ).limit(10).offset(10).order_by("id").sql()
(u'SELECT `a`.`username`, `a`.`id`, `b`.`address` FROM `users` AS `a` INNER JOIN `contactinfo` AS `b` ON `a`.`id` = `b`.`id` WHERE (`a`.`id` IN (%s,%s,%s,%s)) AND (`b`.`country` = %s) ORDER BY `a`.`id` OFFSET %s LIMIT %s',
 (1, 2, 3, 4, 'US', 10, 10))