| single |
python-sql
==========
python-sql is a library to write SQL queries in a pythonic way.
Nutshell
--------
Import::
>>> from sql import *
>>> from sql.aggregate import *
>>> from sql.conditionals import *
Simple selects::
>>> user = Table('user')
>>> select = user.select()
>>> tuple(select)
('SELECT * FROM "user" AS "a"', ())
>>> select = user.select(user.name)
>>> tuple(select)
('SELECT "a"."name" FROM "user" AS "a"', ())
>>> select = user.select(Count(Literal(1)))
>>> tuple(select)
('SELECT COUNT(%s) FROM "user" AS "a"', (1,))
>>> select = user.select(user.name, distinct=True)
>>> tuple(select)
('SELECT DISTINCT "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.where = user.name == user.login
>>> tuple(select)
('SELECT "a"."id", "a"."name" FROM "user" AS "a" WHERE ("a"."name" =
"a"."login")', ())
Select with join::
>>> join = user.join(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 = user.join(Table('user'))
>>> join2 = join1.join(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 output name::
>>> tuple(user.select(user.name.as_('First Name')))
('SELECT "a"."name" AS "First Name" FROM "user" AS "a"', ())
Select with order_by::
>>> tuple(user.select(order_by=user.date))
('SELECT * FROM "user" AS "a" ORDER BY "a"."date"', ())
>>> tuple(user.select(order_by=Asc(user.date)))
('SELECT * FROM "user" AS "a" ORDER BY "a"."date" ASC', ())
>>> tuple(user.select(order_by=(user.date.asc, user.id.desc)))
('SELECT * 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)
|