# buildpg
[](https://github.com/samuelcolvin/buildpg/actions?query=event%3Apush+branch%3Amaster+workflow%3Aci)
[](https://codecov.io/gh/samuelcolvin/buildpg)
[](https://pypi.python.org/pypi/buildpg)
[](https://github.com/samuelcolvin/buildpg)
[](https://github.com/samuelcolvin/buildpg/blob/master/LICENSE)
Query building for the postgresql prepared statements and asyncpg.
Lots of more powerful features, including full clause construction, multiple values, logic functions,
query pretty-printing and different variable substitution - below is just a very quick summary.
Please check the code and tests for examples.
## Building Queries
Simple variable substitution:
```py
from buildpg import render
render('select * from mytable where x=:foo and y=:bar', foo=123, bar='whatever')
>> 'select * from mytable where x=$1 and y=$2', [123, 'whatever']
```
Use of `V` to substitute constants:
```py
from buildpg import V, render
render('select * from mytable where :col=:foo', col=V('x'), foo=456)
>> 'select * from mytable where x=$1', [456]
```
Complex logic:
```py
from buildpg import V, funcs, render
where_logic = V('foo.bar') == 123
if spam_value:
where_logic &= V('foo.spam') <= spam_value
if exclude_cake:
where_logic &= funcs.not_(V('foo.cake').in_([1, 2, 3]))
render('select * from foo :where', where=where_logic)
>> 'select * from foo foo.bar = $1 AND foo.spam <= $2 AND not(foo.cake in $3)', [123, 123, ['x', 'y']]
```
Values usage:
```py
from buildpg import Values, render
render('insert into the_table (:values__names) values :values', values=Values(a=123, b=456, c='hello'))
>> 'insert into the_table (a, b, c) values ($1, $2, $3)', [123, 456, 'hello']
```
## With asyncpg
As a wrapper around *asyncpg*:
```py
import asyncio
from buildpg import asyncpg
async def main():
async with asyncpg.create_pool_b('postgres://postgres@localhost:5432/db') as pool:
await pool.fetchval_b('select spam from mytable where x=:foo and y=:bar', foo=123, bar='whatever')
>> 42
asyncio.run(main())
```
Both the pool and connections have `*_b` variants of all common query methods:
- `execute_b`
- `executemany_b`
- `fetch_b`
- `fetchval_b`
- `fetchrow_b`
- `cursor_b`
## Operators
| Python operator/function | SQL operator |
| ------------------------ | ------------ |
| `&` | `AND` |
| `|` | `OR` |
| `=` | `=` |
| `!=` | `!=` |
| `<` | `<` |
| `<=` | `<=` |
| `>` | `>` |
| `>=` | `>=` |
| `+` | `+` |
| `-` | `-` |
| `*` | `*` |
| `/` | `/` |
| `%` | `%` |
| `**` | `^` |
| `-` | `-` |
| `~` | `not(...)` |
| `sqrt` | `|/` |
| `abs` | `@` |
| `contains` | `@>` |
| `contained_by` | `<@` |
| `overlap` | `&&` |
| `like` | `LIKE` |
| `ilike` | `ILIKE` |
| `cat` | `||` |
| `in_` | `in` |
| `from_` | `from` |
| `at_time_zone` | `AT TIME ZONE` |
| `matches` | `@@` |
| `is_` | `is` |
| `is_not` | `is not` |
| `for_` | `for` |
| `factorial` | `!` |
| `cast` | `::` |
| `asc` | `ASC` |
| `desc` | `DESC` |
| `comma` | `,` |
| `on` | `ON` |
| `as_` | `AS` |
| `nulls_first` | `NULLS FIRST` |
| `nulls_last` | `NULLS LAST` |
Usage:
```py
from buildpg import V, S, render
def show(component):
sql, params = render(':c', c=component)
print(f'sql="{sql}" params={params}')
show(V('foobar').contains([1, 2, 3]))
#> sql="foobar @> $1" params=[[1, 2, 3]]
show(V('foobar') == 4)
#> sql="foobar = $1" params=[4]
show(~V('foobar'))
#> sql="not(foobar)" params=[]
show(S(625).sqrt())
#> sql="|/ $1" params=[625]
show(V('foo').is_not('true'))
#> sql="foo is not true" params=[]
```
## Functions
| Python function | SQL function |
| ------------------------------------------- | ------------- |
| `AND(*args)` | `<arg1> and <arg2> ...` |
| `OR(*args)` | `<arg1> or <arg2> ...` |
| `NOT(arg)` | `not(<arg>)` |
| `comma_sep(*args)` | `<arg1>, <arg2>, ...` |
| `count(expr)` | `count(expr)` |
| `any(arg)` | `any(<arg1>)` |
| `now()` | `now()` |
| `cast(v, cast_type)` | `<v>::<cast_type>` |
| `upper(string)` | `upper(<string>)` |
| `lower(string)` | `lower(<string>)` |
| `length(string)` | `length(<string>)` |
| `left(string, n)` | `left(<string>, <n>)` |
| `right(string, n)` | `right(<string>, <n>)` |
| `extract(expr)` | `extract(<expr>)` |
| `sqrt(n)` | `|/<n>` |
| `abs(n)` | `@<n>` |
| `factorial(n)` | `!<n>` |
| `position(substring, string)` | `position(<substring> in <st`... |
| `substring(string, pattern, escape-None)` | `substring(<string> from <pa`... |
| `to_tsvector(arg1, document-None)` | `to_tsvector(<arg1>)` |
| `to_tsquery(arg1, text-None)` | `to_tsquery(<arg1>)` |
Usage:
```py
from buildpg import V, render, funcs
def show(component):
sql, params = render(':c', c=component)
print(f'sql="{sql}" params={params}')
show(funcs.AND(V('x') == 4, V('y') > 6))
#> sql="x = $1 AND y > $2" params=[4, 6]
show(funcs.position('foo', 'this has foo in it'))
#> sql="position($1 in $2)" params=['foo', 'this has foo in it']
```
Raw data
{
"_id": null,
"home_page": "https://github.com/samuelcolvin/buildpg",
"name": "buildpg",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.6",
"maintainer_email": "",
"keywords": "",
"author": "Samuel Colvin",
"author_email": "s@muelcolvin.com",
"download_url": "https://files.pythonhosted.org/packages/48/f2/ff0e51a3c2390538da6eb4f85e30d87aafbcc6d057c6c9bb9fa222c8f2fc/buildpg-0.4.tar.gz",
"platform": "",
"description": "# buildpg\n\n[](https://github.com/samuelcolvin/buildpg/actions?query=event%3Apush+branch%3Amaster+workflow%3Aci)\n[](https://codecov.io/gh/samuelcolvin/buildpg)\n[](https://pypi.python.org/pypi/buildpg)\n[](https://github.com/samuelcolvin/buildpg)\n[](https://github.com/samuelcolvin/buildpg/blob/master/LICENSE)\n\nQuery building for the postgresql prepared statements and asyncpg.\n\nLots of more powerful features, including full clause construction, multiple values, logic functions,\nquery pretty-printing and different variable substitution - below is just a very quick summary.\nPlease check the code and tests for examples.\n\n## Building Queries\n\nSimple variable substitution:\n\n```py\nfrom buildpg import render\n\nrender('select * from mytable where x=:foo and y=:bar', foo=123, bar='whatever')\n>> 'select * from mytable where x=$1 and y=$2', [123, 'whatever']\n```\n\n\nUse of `V` to substitute constants:\n\n```py\nfrom buildpg import V, render\n\nrender('select * from mytable where :col=:foo', col=V('x'), foo=456)\n>> 'select * from mytable where x=$1', [456]\n```\n\nComplex logic:\n\n```py\nfrom buildpg import V, funcs, render\n\nwhere_logic = V('foo.bar') == 123\nif spam_value:\n where_logic &= V('foo.spam') <= spam_value\n\nif exclude_cake:\n where_logic &= funcs.not_(V('foo.cake').in_([1, 2, 3]))\n\nrender('select * from foo :where', where=where_logic)\n>> 'select * from foo foo.bar = $1 AND foo.spam <= $2 AND not(foo.cake in $3)', [123, 123, ['x', 'y']]\n```\n\nValues usage:\n\n```py\nfrom buildpg import Values, render\n\nrender('insert into the_table (:values__names) values :values', values=Values(a=123, b=456, c='hello'))\n>> 'insert into the_table (a, b, c) values ($1, $2, $3)', [123, 456, 'hello']\n```\n\n## With asyncpg\n\nAs a wrapper around *asyncpg*:\n\n```py\nimport asyncio\nfrom buildpg import asyncpg\n\nasync def main():\n async with asyncpg.create_pool_b('postgres://postgres@localhost:5432/db') as pool:\n await pool.fetchval_b('select spam from mytable where x=:foo and y=:bar', foo=123, bar='whatever')\n >> 42\n\nasyncio.run(main())\n```\n\n\nBoth the pool and connections have `*_b` variants of all common query methods:\n\n- `execute_b`\n- `executemany_b`\n- `fetch_b`\n- `fetchval_b`\n- `fetchrow_b`\n- `cursor_b`\n\n\n## Operators\n\n| Python operator/function | SQL operator |\n| ------------------------ | ------------ |\n| `&` | `AND` |\n| `|` | `OR` |\n| `=` | `=` |\n| `!=` | `!=` |\n| `<` | `<` |\n| `<=` | `<=` |\n| `>` | `>` |\n| `>=` | `>=` |\n| `+` | `+` |\n| `-` | `-` |\n| `*` | `*` |\n| `/` | `/` |\n| `%` | `%` |\n| `**` | `^` |\n| `-` | `-` |\n| `~` | `not(...)` |\n| `sqrt` | `|/` |\n| `abs` | `@` |\n| `contains` | `@>` |\n| `contained_by` | `<@` |\n| `overlap` | `&&` |\n| `like` | `LIKE` |\n| `ilike` | `ILIKE` |\n| `cat` | `||` |\n| `in_` | `in` |\n| `from_` | `from` |\n| `at_time_zone` | `AT TIME ZONE` |\n| `matches` | `@@` |\n| `is_` | `is` |\n| `is_not` | `is not` |\n| `for_` | `for` |\n| `factorial` | `!` |\n| `cast` | `::` |\n| `asc` | `ASC` |\n| `desc` | `DESC` |\n| `comma` | `,` |\n| `on` | `ON` |\n| `as_` | `AS` |\n| `nulls_first` | `NULLS FIRST` |\n| `nulls_last` | `NULLS LAST` |\n\nUsage:\n\n```py\nfrom buildpg import V, S, render\n\ndef show(component):\n sql, params = render(':c', c=component)\n print(f'sql=\"{sql}\" params={params}')\n\nshow(V('foobar').contains([1, 2, 3]))\n#> sql=\"foobar @> $1\" params=[[1, 2, 3]]\nshow(V('foobar') == 4)\n#> sql=\"foobar = $1\" params=[4]\nshow(~V('foobar'))\n#> sql=\"not(foobar)\" params=[]\nshow(S(625).sqrt())\n#> sql=\"|/ $1\" params=[625]\nshow(V('foo').is_not('true'))\n#> sql=\"foo is not true\" params=[]\n```\n\n## Functions\n\n| Python function | SQL function |\n| ------------------------------------------- | ------------- |\n| `AND(*args)` | `<arg1> and <arg2> ...` |\n| `OR(*args)` | `<arg1> or <arg2> ...` |\n| `NOT(arg)` | `not(<arg>)` |\n| `comma_sep(*args)` | `<arg1>, <arg2>, ...` |\n| `count(expr)` | `count(expr)` |\n| `any(arg)` | `any(<arg1>)` |\n| `now()` | `now()` |\n| `cast(v, cast_type)` | `<v>::<cast_type>` |\n| `upper(string)` | `upper(<string>)` |\n| `lower(string)` | `lower(<string>)` |\n| `length(string)` | `length(<string>)` |\n| `left(string, n)` | `left(<string>, <n>)` |\n| `right(string, n)` | `right(<string>, <n>)` |\n| `extract(expr)` | `extract(<expr>)` |\n| `sqrt(n)` | `|/<n>` |\n| `abs(n)` | `@<n>` |\n| `factorial(n)` | `!<n>` |\n| `position(substring, string)` | `position(<substring> in <st`... |\n| `substring(string, pattern, escape-None)` | `substring(<string> from <pa`... |\n| `to_tsvector(arg1, document-None)` | `to_tsvector(<arg1>)` |\n| `to_tsquery(arg1, text-None)` | `to_tsquery(<arg1>)` |\n\nUsage:\n\n```py\nfrom buildpg import V, render, funcs\n\ndef show(component):\n sql, params = render(':c', c=component)\n print(f'sql=\"{sql}\" params={params}')\n\nshow(funcs.AND(V('x') == 4, V('y') > 6))\n#> sql=\"x = $1 AND y > $2\" params=[4, 6]\nshow(funcs.position('foo', 'this has foo in it'))\n#> sql=\"position($1 in $2)\" params=['foo', 'this has foo in it']\n```\n\n\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Query building for the postgresql prepared statements and asyncpg.",
"version": "0.4",
"project_urls": {
"Homepage": "https://github.com/samuelcolvin/buildpg"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "315ac5ecd08a0c9b4dfece3b41aeefc3770968b4a2da1784941c9c8dd1c65347",
"md5": "fe9ed8f194b65e8323991cbc74f0e8eb",
"sha256": "20d539976c81ea6f5529d3930016b0482ed0ff06def3d6da79d0fc0a3bbaeeb1"
},
"downloads": -1,
"filename": "buildpg-0.4-py3-none-any.whl",
"has_sig": false,
"md5_digest": "fe9ed8f194b65e8323991cbc74f0e8eb",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.6",
"size": 11746,
"upload_time": "2022-03-01T17:00:52",
"upload_time_iso_8601": "2022-03-01T17:00:52.190963Z",
"url": "https://files.pythonhosted.org/packages/31/5a/c5ecd08a0c9b4dfece3b41aeefc3770968b4a2da1784941c9c8dd1c65347/buildpg-0.4-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "48f2ff0e51a3c2390538da6eb4f85e30d87aafbcc6d057c6c9bb9fa222c8f2fc",
"md5": "1e3c523df94be37397684d65dcba8e05",
"sha256": "3a6c1f40fb6c826caa819d84727e36a1372f7013ba696637b492e5935916d479"
},
"downloads": -1,
"filename": "buildpg-0.4.tar.gz",
"has_sig": false,
"md5_digest": "1e3c523df94be37397684d65dcba8e05",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.6",
"size": 12493,
"upload_time": "2022-03-01T17:00:53",
"upload_time_iso_8601": "2022-03-01T17:00:53.993200Z",
"url": "https://files.pythonhosted.org/packages/48/f2/ff0e51a3c2390538da6eb4f85e30d87aafbcc6d057c6c9bb9fa222c8f2fc/buildpg-0.4.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2022-03-01 17:00:53",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "samuelcolvin",
"github_project": "buildpg",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "buildpg"
}
JSON
