python-sql: select examples

README

+python-sql
+==========
+
+python-sql is a library to write SQL queries in a pythonic way.
+
+Nutshell
+--------
+
+Import::
+
+    >>> from sql import *
+    >>> from sql.aggregate import *
+
+Simple selects::
+
+    >>> user = Table('user')
+    >>> select = user.select()
+    >>> tuple(select)
+    ('SELECT "a".* FROM "user" AS "a"', ())
+
+    >>> select = user.select(user.name)
+    >>> tuple(select)
+    ('SELECT "a"."name" FROM "user" AS "a"', ())
+
+    >>> select = user.select(user.id, user.name)
+    >>> tuple(select)
+    ('SELECT "a"."id", "a"."name" FROM "user" AS "a"', ())
+
+Select with where condition::
+
+    >>> select.where = user.name == 'foo'
+    >>> tuple(select)
+    ('SELECT "a"."id", "a"."name" FROM "user" AS "a" WHERE ("a"."name" = %s)', ('foo',))
+
+    >>> select.where = (user.name == 'foo') & (user.active == True)
+    >>> tuple(select)
+    ('SELECT "a"."id", "a"."name" FROM "user" AS "a" WHERE (("a"."name" = %s) AND ("a"."active" = %s))', ('foo', True))
+
+Select with join::
+
+    >>> join = Join(user, Table('user_group'))
+    >>> join.condition = join.right.user == user.id
+    >>> select = join.select(user.name, join.right.group)
+    >>> tuple(select)
+    ('SELECT "a"."name", "b"."group" FROM "user" AS "a" INNER JOIN "user_group" AS "b" ON ("b"."user" = "a"."id")', ())
+
+Select with multiple joins::
+
+    >>> join1 = Join(user, Table('user'))
+    >>> join2 = Join(join1, Table('user'))
+    >>> select = join2.select(user.id, join1.right.id, join2.right.id)
+    >>> tuple(select)
+    ('SELECT "a"."id", "b"."id", "c"."id" FROM "user" AS "a" INNER JOIN "user" AS "b" INNER JOIN "user" AS "c"', ())
+
+Select with group_by::
+
+    >>> invoice = Table('invoice')
+    >>> select = invoice.select(Sum(invoice.amount), invoice.currency,
+    ...         group_by=invoice.currency)
+    >>> tuple(select)
+    ('SELECT SUM("a"."amount"), "a"."currency" FROM "invoice" AS "a" GROUP BY "a"."currency"', ())
+
+Select with order_by::
+
+    >>> tuple(user.select(order_by=user.date))
+    ('SELECT "a".* FROM "user" AS "a" ORDER BY "a"."date"', ())
+    >>> tuple(user.select(order_by=Asc(user.date)))
+    ('SELECT "a".* FROM "user" AS "a" ORDER BY "a"."date" ASC', ())
+    >>> tuple(user.select(order_by=(Asc(user.date), Desc(user.id))))
+    ('SELECT "a".* FROM "user" AS "a" ORDER BY "a"."date" ASC, "a"."id" DESC', ())
+
+
+Select with sub-select::
+
+    >>> user_group = Table('user_group')
+    >>> subselect = user_group.select(user_group.user,
+    ...     where=(user_group.active == True))
+    >>> user = Table('user')
+    >>> tuple(user.select(user.id, where=(user.id.in_(subselect))))
+    ('SELECT "a"."id" FROM "user" AS "a" WHERE ("a"."id" IN (SELECT "a"."user" FROM "user_group" AS "a" WHERE ("a"."active" = %s)))', (True,))
+
+Insert query with default values::
+
+    >>> tuple(user.insert())
+    ('INSERT INTO "user" DEFAULT VALUES', ())
+
+Insert query with values::
+
+    >>> tuple(user.insert(columns=[user.name, user.login],
+    ...         values=['Foo', 'foo']))
+    ('INSERT INTO "user" ("name", "login") VALUES (%s, %s)', ('Foo', 'foo'))
+    >>> tuple(user.insert(columns=[user.name, user.login],
+    ...         values=[['Foo', 'foo'], ['Bar', 'bar']]))
+    ('INSERT INTO "user" ("name", "login") VALUES (%s, %s), (%s, %s)', ('Foo', 'foo', 'Bar', 'bar'))
+
+Insert query with query::
+
+    >>> passwd = Table('passwd')
+    >>> select = passwd.select(passwd.login, passwd.passwd)
+    >>> tuple(user.insert(values=select))
+    ('INSERT INTO "user" VALUES (SELECT "a"."login", "a"."passwd" FROM "passwd" AS "a")', ())
+
+Delete query::
+
+    >>> tuple(user.delete())
+    ('DELETE FROM "user"', ())
+
+Delete query with where condition::
+
+    >>> tuple(user.delete(where=(user.name == 'foo')))
+    ('DELETE FROM "user" WHERE ("name" = %s)', ('foo',))
+
+Delete query with sub-query::
+
+    >>> tuple(user.delete(where=(
+    ...             user.id.in_(user_group.select(user_group.user)))))
+    ('DELETE FROM "user" WHERE ("id" IN (SELECT "a"."user" FROM "user_group" AS "a"))', ())