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)
>>> 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 "b"."user" FROM "user_group" AS "b" WHERE ("b"."active" = %s)))', (True,))
>>> tuple(subselect.select(subselect.user))
('SELECT "a"."user" FROM (SELECT "b"."user" FROM "user_group" AS "b" WHERE ("b"."active" = %s)) AS "a"', (True,))
Select on other schema::
>>> other_table = Table('user', 'myschema')
>>> tuple(other_table.select())
('SELECT * FROM "myschema"."user" AS "a"', ())
Insert query with default values::
>>> tuple(user.insert())
('INSERT INTO "user" AS "a" DEFAULT VALUES', ())
Insert query with values::
>>> tuple(user.insert(columns=[user.name, user.login],
... values=[['Foo', 'foo']]))
('INSERT INTO "user" AS "a" ("name", "login") VALUES (%s, %s)', ('Foo', 'foo'))
>>> tuple(user.insert(columns=[user.name, user.login],
... values=[['Foo', 'foo'], ['Bar', 'bar']]))
('INSERT INTO "user" AS "a" ("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" AS "b" SELECT "a"."login", "a"."passwd" FROM "passwd" AS "a"', ())
Update query with values::
>>> tuple(user.update(columns=[user.active], values=[True]))
('UPDATE "user" AS "a" SET "active" = %s', (True,))
>>> tuple(invoice.update(columns=[invoice.total], values=[invoice.amount + invoice.tax]))
('UPDATE "invoice" AS "a" SET "total" = ("a"."amount" + "a"."tax")', ())
Update query with where condition::
>>> tuple(user.update(columns=[user.active], values=[True],
... where=user.active == False))
('UPDATE "user" AS "a" SET "active" = %s WHERE ("a"."active" = %s)', (True, False))
Update query with from list::
>>> group = Table('user_group')
>>> tuple(user.update(columns=[user.active], values=[group.active],
... from_=[group], where=user.id == group.user))
('UPDATE "user" AS "b" SET "active" = "a"."active" FROM "user_group" AS "a" WHERE ("b"."id" = "a"."user")', ())
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"))', ())
Flavors::
>>> select = user.select()
>>> select.offset = 10
>>> Flavor.set(Flavor())
>>> tuple(select)
('SELECT * FROM "user" AS "a" OFFSET 10', ())
>>> Flavor.set(Flavor(max_limit=18446744073709551615))
>>> tuple(select)
('SELECT * FROM "user" AS "a" LIMIT 18446744073709551615 OFFSET 10', ())
>>> Flavor.set(Flavor(max_limit=-1))
>>> tuple(select)
('SELECT * FROM "user" AS "a" LIMIT -1 OFFSET 10', ())
Limit style::
>>> select = user.select(limit=10, offset=20)
>>> Flavor.set(Flavor(limitstyle='limit'))
>>> tuple(select)
('SELECT * FROM "user" AS "a" LIMIT 10 OFFSET 20', ())
>>> Flavor.set(Flavor(limitstyle='fetch'))
>>> tuple(select)
('SELECT * FROM "user" AS "a" OFFSET (20) ROWS FETCH FIRST (10) ROWS ONLY', ())
>>> Flavor.set(Flavor(limitstyle='rownum'))
>>> tuple(select)
('SELECT "a".* FROM (SELECT "b".*, ROWNUM AS "rnum" FROM (SELECT * FROM "user" AS "c") AS "b" WHERE (ROWNUM <= %s)) AS "a" WHERE ("rnum" > %s)', (30, 20))
qmark style::
>>> Flavor.set(Flavor(paramstyle='qmark'))
>>> select = user.select()
>>> select.where = user.name == 'foo'
>>> tuple(select)
('SELECT * FROM "user" AS "a" WHERE ("a"."name" = ?)', ('foo',))
numeric style::
>>> Flavor.set(Flavor(paramstyle='format'))
>>> select = user.select()
>>> select.where = user.name == 'foo'
>>> format2numeric(*select)
('SELECT * FROM "user" AS "a" WHERE ("a"."name" = :0)', ('foo',))
Raw data
{
"_id": null,
"home_page": "https://pypi.org/project/python-sql/",
"name": "python-sql",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.5",
"maintainer_email": "",
"keywords": "SQL database query",
"author": "Tryton",
"author_email": "foundation@tryton.org",
"download_url": "https://files.pythonhosted.org/packages/74/d6/eb5206537c4de3ff25899c64241ab242bf5dd8924dda5a335691c5a927d3/python-sql-1.4.3.tar.gz",
"platform": null,
"description": "python-sql\n==========\n\npython-sql is a library to write SQL queries in a pythonic way.\n\nNutshell\n--------\n\nImport::\n\n >>> from sql import *\n >>> from sql.aggregate import *\n >>> from sql.conditionals import *\n\nSimple selects::\n\n >>> user = Table('user')\n >>> select = user.select()\n >>> tuple(select)\n ('SELECT * FROM \"user\" AS \"a\"', ())\n\n >>> select = user.select(user.name)\n >>> tuple(select)\n ('SELECT \"a\".\"name\" FROM \"user\" AS \"a\"', ())\n\n >>> select = user.select(Count(Literal(1)))\n >>> tuple(select)\n ('SELECT COUNT(%s) FROM \"user\" AS \"a\"', (1,))\n\n >>> select = user.select(user.name, distinct=True)\n >>> tuple(select)\n ('SELECT DISTINCT \"a\".\"name\" FROM \"user\" AS \"a\"', ())\n\n >>> select = user.select(user.id, user.name)\n >>> tuple(select)\n ('SELECT \"a\".\"id\", \"a\".\"name\" FROM \"user\" AS \"a\"', ())\n\nSelect with where condition::\n\n >>> select.where = user.name == 'foo'\n >>> tuple(select)\n ('SELECT \"a\".\"id\", \"a\".\"name\" FROM \"user\" AS \"a\" WHERE (\"a\".\"name\" = %s)', ('foo',))\n\n >>> select.where = (user.name == 'foo') & (user.active == True)\n >>> tuple(select)\n ('SELECT \"a\".\"id\", \"a\".\"name\" FROM \"user\" AS \"a\" WHERE ((\"a\".\"name\" = %s) AND (\"a\".\"active\" = %s))', ('foo', True))\n >>> select.where = user.name == user.login\n >>> tuple(select)\n ('SELECT \"a\".\"id\", \"a\".\"name\" FROM \"user\" AS \"a\" WHERE (\"a\".\"name\" = \"a\".\"login\")', ())\n\nSelect with join::\n\n >>> join = user.join(Table('user_group'))\n >>> join.condition = join.right.user == user.id\n >>> select = join.select(user.name, join.right.group)\n >>> tuple(select)\n ('SELECT \"a\".\"name\", \"b\".\"group\" FROM \"user\" AS \"a\" INNER JOIN \"user_group\" AS \"b\" ON (\"b\".\"user\" = \"a\".\"id\")', ())\n\nSelect with multiple joins::\n\n >>> join1 = user.join(Table('user'))\n >>> join2 = join1.join(Table('user'))\n >>> select = join2.select(user.id, join1.right.id, join2.right.id)\n >>> tuple(select)\n ('SELECT \"a\".\"id\", \"b\".\"id\", \"c\".\"id\" FROM \"user\" AS \"a\" INNER JOIN \"user\" AS \"b\" INNER JOIN \"user\" AS \"c\"', ())\n\nSelect with group_by::\n\n >>> invoice = Table('invoice')\n >>> select = invoice.select(Sum(invoice.amount), invoice.currency,\n ... group_by=invoice.currency)\n >>> tuple(select)\n ('SELECT SUM(\"a\".\"amount\"), \"a\".\"currency\" FROM \"invoice\" AS \"a\" GROUP BY \"a\".\"currency\"', ())\n\nSelect with output name::\n\n >>> tuple(user.select(user.name.as_('First Name')))\n ('SELECT \"a\".\"name\" AS \"First Name\" FROM \"user\" AS \"a\"', ())\n\nSelect with order_by::\n\n >>> tuple(user.select(order_by=user.date))\n ('SELECT * FROM \"user\" AS \"a\" ORDER BY \"a\".\"date\"', ())\n >>> tuple(user.select(order_by=Asc(user.date)))\n ('SELECT * FROM \"user\" AS \"a\" ORDER BY \"a\".\"date\" ASC', ())\n >>> tuple(user.select(order_by=(user.date.asc, user.id.desc)))\n ('SELECT * FROM \"user\" AS \"a\" ORDER BY \"a\".\"date\" ASC, \"a\".\"id\" DESC', ())\n\nSelect with sub-select::\n\n >>> user_group = Table('user_group')\n >>> subselect = user_group.select(user_group.user,\n ... where=user_group.active == True)\n >>> user = Table('user')\n >>> tuple(user.select(user.id, where=user.id.in_(subselect)))\n ('SELECT \"a\".\"id\" FROM \"user\" AS \"a\" WHERE (\"a\".\"id\" IN (SELECT \"b\".\"user\" FROM \"user_group\" AS \"b\" WHERE (\"b\".\"active\" = %s)))', (True,))\n >>> tuple(subselect.select(subselect.user))\n ('SELECT \"a\".\"user\" FROM (SELECT \"b\".\"user\" FROM \"user_group\" AS \"b\" WHERE (\"b\".\"active\" = %s)) AS \"a\"', (True,))\n\nSelect on other schema::\n\n >>> other_table = Table('user', 'myschema')\n >>> tuple(other_table.select())\n ('SELECT * FROM \"myschema\".\"user\" AS \"a\"', ())\n\nInsert query with default values::\n\n >>> tuple(user.insert())\n ('INSERT INTO \"user\" AS \"a\" DEFAULT VALUES', ())\n\nInsert query with values::\n\n >>> tuple(user.insert(columns=[user.name, user.login],\n ... values=[['Foo', 'foo']]))\n ('INSERT INTO \"user\" AS \"a\" (\"name\", \"login\") VALUES (%s, %s)', ('Foo', 'foo'))\n >>> tuple(user.insert(columns=[user.name, user.login],\n ... values=[['Foo', 'foo'], ['Bar', 'bar']]))\n ('INSERT INTO \"user\" AS \"a\" (\"name\", \"login\") VALUES (%s, %s), (%s, %s)', ('Foo', 'foo', 'Bar', 'bar'))\n\nInsert query with query::\n\n >>> passwd = Table('passwd')\n >>> select = passwd.select(passwd.login, passwd.passwd)\n >>> tuple(user.insert(values=select))\n ('INSERT INTO \"user\" AS \"b\" SELECT \"a\".\"login\", \"a\".\"passwd\" FROM \"passwd\" AS \"a\"', ())\n\nUpdate query with values::\n\n >>> tuple(user.update(columns=[user.active], values=[True]))\n ('UPDATE \"user\" AS \"a\" SET \"active\" = %s', (True,))\n >>> tuple(invoice.update(columns=[invoice.total], values=[invoice.amount + invoice.tax]))\n ('UPDATE \"invoice\" AS \"a\" SET \"total\" = (\"a\".\"amount\" + \"a\".\"tax\")', ())\n\nUpdate query with where condition::\n\n >>> tuple(user.update(columns=[user.active], values=[True],\n ... where=user.active == False))\n ('UPDATE \"user\" AS \"a\" SET \"active\" = %s WHERE (\"a\".\"active\" = %s)', (True, False))\n\nUpdate query with from list::\n\n >>> group = Table('user_group')\n >>> tuple(user.update(columns=[user.active], values=[group.active],\n ... from_=[group], where=user.id == group.user))\n ('UPDATE \"user\" AS \"b\" SET \"active\" = \"a\".\"active\" FROM \"user_group\" AS \"a\" WHERE (\"b\".\"id\" = \"a\".\"user\")', ())\n\nDelete query::\n\n >>> tuple(user.delete())\n ('DELETE FROM \"user\"', ())\n\nDelete query with where condition::\n\n >>> tuple(user.delete(where=user.name == 'foo'))\n ('DELETE FROM \"user\" WHERE (\"name\" = %s)', ('foo',))\n\nDelete query with sub-query::\n\n >>> tuple(user.delete(\n ... where=user.id.in_(user_group.select(user_group.user))))\n ('DELETE FROM \"user\" WHERE (\"id\" IN (SELECT \"a\".\"user\" FROM \"user_group\" AS \"a\"))', ())\n\nFlavors::\n\n >>> select = user.select()\n >>> select.offset = 10\n >>> Flavor.set(Flavor())\n >>> tuple(select)\n ('SELECT * FROM \"user\" AS \"a\" OFFSET 10', ())\n >>> Flavor.set(Flavor(max_limit=18446744073709551615))\n >>> tuple(select)\n ('SELECT * FROM \"user\" AS \"a\" LIMIT 18446744073709551615 OFFSET 10', ())\n >>> Flavor.set(Flavor(max_limit=-1))\n >>> tuple(select)\n ('SELECT * FROM \"user\" AS \"a\" LIMIT -1 OFFSET 10', ())\n\nLimit style::\n\n >>> select = user.select(limit=10, offset=20)\n >>> Flavor.set(Flavor(limitstyle='limit'))\n >>> tuple(select)\n ('SELECT * FROM \"user\" AS \"a\" LIMIT 10 OFFSET 20', ())\n >>> Flavor.set(Flavor(limitstyle='fetch'))\n >>> tuple(select)\n ('SELECT * FROM \"user\" AS \"a\" OFFSET (20) ROWS FETCH FIRST (10) ROWS ONLY', ())\n >>> Flavor.set(Flavor(limitstyle='rownum'))\n >>> tuple(select)\n ('SELECT \"a\".* FROM (SELECT \"b\".*, ROWNUM AS \"rnum\" FROM (SELECT * FROM \"user\" AS \"c\") AS \"b\" WHERE (ROWNUM <= %s)) AS \"a\" WHERE (\"rnum\" > %s)', (30, 20))\n\nqmark style::\n\n >>> Flavor.set(Flavor(paramstyle='qmark'))\n >>> select = user.select()\n >>> select.where = user.name == 'foo'\n >>> tuple(select)\n ('SELECT * FROM \"user\" AS \"a\" WHERE (\"a\".\"name\" = ?)', ('foo',))\n\nnumeric style::\n\n >>> Flavor.set(Flavor(paramstyle='format'))\n >>> select = user.select()\n >>> select.where = user.name == 'foo'\n >>> format2numeric(*select)\n ('SELECT * FROM \"user\" AS \"a\" WHERE (\"a\".\"name\" = :0)', ('foo',))\n",
"bugtrack_url": null,
"license": "BSD",
"summary": "Library to write SQL queries",
"version": "1.4.3",
"project_urls": {
"Bug Tracker": "https://bugs.tryton.org/python-sql",
"Download": "https://downloads.tryton.org/python-sql/",
"Forum": "https://discuss.tryton.org/tags/python-sql",
"Homepage": "https://pypi.org/project/python-sql/",
"Source Code": "https://code.tryton.org/python-sql"
},
"split_keywords": [
"sql",
"database",
"query"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "031c472bc723688d73b68c10f3e8f5a710692851a34332a227ae8f9e806d01c2",
"md5": "e4b5f6b0149443bdb1892d7af2e7db87",
"sha256": "0c3feaf1ecf911a339903f8ac56eb8d125cfbc8ce1734d0fefc7b370322b8cb8"
},
"downloads": -1,
"filename": "python_sql-1.4.3-py3-none-any.whl",
"has_sig": false,
"md5_digest": "e4b5f6b0149443bdb1892d7af2e7db87",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.5",
"size": 73274,
"upload_time": "2023-12-30T17:23:32",
"upload_time_iso_8601": "2023-12-30T17:23:32.485774Z",
"url": "https://files.pythonhosted.org/packages/03/1c/472bc723688d73b68c10f3e8f5a710692851a34332a227ae8f9e806d01c2/python_sql-1.4.3-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "74d6eb5206537c4de3ff25899c64241ab242bf5dd8924dda5a335691c5a927d3",
"md5": "8babadf71b81dcb3f2a729b6efa79bad",
"sha256": "6260beff835a35d3a0aec4f1da351b0be9caa1a0d94a6cba940a7cd8dcf66269"
},
"downloads": -1,
"filename": "python-sql-1.4.3.tar.gz",
"has_sig": false,
"md5_digest": "8babadf71b81dcb3f2a729b6efa79bad",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.5",
"size": 33404,
"upload_time": "2023-12-30T17:23:11",
"upload_time_iso_8601": "2023-12-30T17:23:11.892631Z",
"url": "https://files.pythonhosted.org/packages/74/d6/eb5206537c4de3ff25899c64241ab242bf5dd8924dda5a335691c5a927d3/python-sql-1.4.3.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-12-30 17:23:11",
"github": false,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"lcname": "python-sql"
}
JSON