# SQL Composer
SQL Composer is SQL query builder for people who love raw SQL and just need a
little "something" to make it composable. SQL Composer is this little
"something".
With SQL builder you write raw SQL queries in ordinary .sql files and add
composable bits which you enable with help of SQL composer.
Any variables which you need to substitute should be passed to SQL Composer
when enabling a part of your query. This way SQL can _do-the-right-thing_ and
substitute them with database-specific named placeholders, which will be
properly escaped by the underlying database. When using this technique, you
don't have to worry about SQL Injection attacks.
Below are some scenarios where SQL builder is especially useful.
_NOTE_: we're using sqlite dialect of SQL in below examples.
## Preparing Query Builder
First we need to create some SQL scripts. They can be ordinary raw SQL files,
but this would be no fun, as they could be simply read and sent to the
engine.
Instead we'll annotate our scripts with _subqueries_. Subqueries are
replacements, typically written at the top of the script, which SQL Composer
uses to replace parts of the query.
Subqueries are SQL comments, starting with two dashes `--`, followed by the
`sub` string, followed by the subquery name, followed by the colon `:`,
followed by the actual SQL string. For example:
```sql
-- insert_feeds.sql
-- sub values: {binds}
-- sub and_return: RETURNING id, name, rss
INSERT INTO feeds(name, rss)
VALUES {values}
{and_return}
```
Above script has two subqueries which might be replaced: `values` and
`and_return`. Let's also create a second query which we'll need later:
```sql
-- get_feeds.sql
-- sub where_rss_is: AND rss = :url
-- sub where_name_is: AND name = :name
-- sub where_name_is_in: AND name IN ({names})
-- sub order_by: ORDER BY {what}
SELECT id, name, rss
FROM feeds
WHERE
true
{where_rss_is}
{where_name_is}
{where_name_is_in}
{order_by}
```
Now we need to initialize instance of `QueryLoader` and tell it
where to search for the scripts.
Depending on how you'd like to distribute your application, `QueryLoader`
might load scripts either from a package or ordinary directory.
### Loading from the Package
Let's say that you'd like to distribute SQL scripts together with your
application in a single Python package. In that case, you should create a
sub-package inside your application's package. You do this by creating a
subdirectory in the source tree of your application and by putting
`__init__.py` file inside there. You might end up with a directory structure
like this:
```
.
+- src/
+- myapp/
+- __init__.py
+- app.py
+- queries/
+- __init__.py
+- insert_feeds.sql
+- get_feeds.sql
+- pyproject.toml
```
In this case you should initialize QueryLoader like this:
```python
from sqlcomposer import QueryLoader
loader = QueryLoader(package="myapp.queries")
```
### Loading from the Path
Alternatively you can pass a path to the directory which contains your SQL
scripts. Both absolute and relative paths are accepted.
```python
from sqlcomposer import QueryLoader
loader = QueryLoader(path="/home/user/myapp/src/myapp/queries")
```
## Preparing the query
To load a query, simply call a method on QueryLoader's instance with the name
of the file:
```python
get_feeds = loader.get_feeds()
```
Now we can easily compose parts of this query. For example if we'd like to
filter only feeds with specific names and make sure they're ordered by name:
```python
get_feeds = loader.get_feeds()
get_feeds.where_name_is(name="foo").order_by(what="name")
cursor.execute(get_feeds.sql(), get_feeds.params)
```
`get_feeds.sql()` produces the following query:
```sql
SELECT id, name, rss
FROM feeds
WHERE
true
AND name = :name
ORDER BY name
```
Alternatively we may decide to fetch feeds with names from a set of values:
```python
from sqlcomposer import QueryLoader, Another
get_feeds = loader.get_feeds()
get_feeds.where_name_is_in(names=Another("foo", "bar", "baz")).order_by(what="name")
cursor.execute(get_feeds.sql(), get_feeds.params)
# or alternatively:
get_feeds = loader.get_feeds()
for name in ["foo", "bar", "baz"]:
get_feeds.where_name_is_in(names=Another("foo"))
get_feeds.order_by(what="name")
cursor.execute(get_feeds.sql(), get_feeds.params)
```
Here we wrap the set of values in `Another()` object. This is a way of
telling SQL Composer that we want to create a separate placeholder for each
value inside it and put them in place of `{names}` substitution in
`where_name_is_in` subquery. We can repeat adding more values wrapped in
`Another()` object and they will be added to the values added previously, as
presented in the alternative form of this query.
If we called `where_name_is(names="foo")` (i.e. with a value not wrapped
inside `Another()` object), it'd overwrite the previous setup.
Above code produces the following query:
```sql
SELECT id, name, rss
FROM feeds
WHERE
true
AND name IN (:names_p1_0, :names_p1_1, :names_p1_2)
ORDER BY name
```
If you inspect the `get_feeds.params`, you'll see that it is a dictionary
with your values mapped to the names of placeholders in produced SQL query.
```python
{ 'names_p1_0': 'foo', 'names_p1_1': 'bar', 'names_p1_2': 'baz' }
```
## Bulk Inserts
Bulk operations (like inserts) requre a little different syntax, where each
inserted row is enclosed in parentheses. SQL Composer supports this with
by enclosing the row values in `AnotherGroup()` object. Here's the example:
```python
objects_to_insert = [
{"name": "foo", "rss": "https://example.com/foo.xml"},
{"name": "bar", "rss": "https://example.com/bar.xml"},
]
insert_feeds = loader.insert_feeds().and_return()
for obj in objects_to_insert:
insert_feeds.values(binds=AnotherGroup(obj["name"], obj["rss"]))
cursor.execute(insert_feeds.sql(), insert_feeds.params)
```
This produces the following query:
```sql
INSERT INTO feeds(name, rss)
VALUES (:names_p1_0, :names_p1_1), (:names_p2_0, :names_p2_1)
RETURNING id, name, rss
```
## Simple Initialization
Some simple queries might not require any substitutions, but for clarity or
other reasons you might want to use SQL Composer for them anyway. Suppose we
have this script:
```sql
-- add_user.sql
INSERT INTO users(name, password)
VALUES :name, :username
```
You can use by passing necessary variables to the method which initializes
the script (`add_user()` here). It is much simpler and clearer than embedding
the query inside your code.
```python
add_user = loader.add_user(name="alice", password="hunter1")
cursor.execute(add_user.sql(), add_user.params)
```
Alternatively:
```python
add_user = loader.add_user()
add_user.update_params(name="alice", password="hunter1")
cursor.execute(add_user.sql(), add_user.params)
```
## Simplifying SQL
Normally `sql()` function doesn't touch the formatting of the output query.
If you pass `simplify=True` to it, it'll remove all lines which start with a
double dash (comments) and will put the whole query in a single line.
```python
add_user = loader.add_user(name="alice", password="hunter1")
print(add_user.sql())
```
This should output:
```
INSERT INTO users(name, password) VALUES :name, :username
```
Keep in mind that this won't remove any inline comments, so you might end up
with accidentally breaking half of your query if you use them.
## Query Copying
Sometimes you might want to execute some very similar queries but with
different values passed to them. This is especially useful when your database
engine has a limit on number of rows you can insert or on the size of the
query. Usually in these cases big queries are split into many smaller
queries.
If you'd use a single query, then in some cases, especially if you use
`Another()` and `AnotherGroup()` features, you could end up with duplicates
in your database from earlier queries.
Solution to this is to initialize common pieces of query once and then copy
it when needed. For example:
```python
insert_feeds = loader.insert_feeds().and_return()
for chunk in split_list(very_large_list_of_feeds):
insert_copy = insert_feeds.copy()
for obj in chunk:
insert_copy.values(binds=AnotherGroup(obj["name"], obj["rss"]))
cursor.execute(insert_copy.sql(), insert_copy.params)
```
## Dialects
Different databases and their drivers might use a different flavours of SQL.
For this reason SQL Composer allows specifying a dialect which it should use
for some parts of built query.
```python
loader = QueryLoader(package=queries, dialect="postgres")
```
Supported dialects are:
- sqlite (default)
- named placeholders have form `:name`
- postgres
- named placeholders have form `%(name)s`
Raw data
{
"_id": null,
"home_page": "https://git.goral.net.pl/sqlcomposer.git/about",
"name": "sqlcomposer",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.9,<4.0",
"maintainer_email": "",
"keywords": "sql,query builder",
"author": "Michal Goral",
"author_email": "dev@goral.net.pl",
"download_url": "https://files.pythonhosted.org/packages/34/52/6224389d9e310d656dca5448364847b24932f529213a02a8e60e2e4c652b/sqlcomposer-2.0.0.tar.gz",
"platform": null,
"description": "# SQL Composer\n\nSQL Composer is SQL query builder for people who love raw SQL and just need a\nlittle \"something\" to make it composable. SQL Composer is this little\n\"something\".\n\nWith SQL builder you write raw SQL queries in ordinary .sql files and add\ncomposable bits which you enable with help of SQL composer.\n\nAny variables which you need to substitute should be passed to SQL Composer\nwhen enabling a part of your query. This way SQL can _do-the-right-thing_ and\nsubstitute them with database-specific named placeholders, which will be\nproperly escaped by the underlying database. When using this technique, you\ndon't have to worry about SQL Injection attacks.\n\nBelow are some scenarios where SQL builder is especially useful.\n\n_NOTE_: we're using sqlite dialect of SQL in below examples.\n\n## Preparing Query Builder\n\nFirst we need to create some SQL scripts. They can be ordinary raw SQL files,\nbut this would be no fun, as they could be simply read and sent to the\nengine.\n\nInstead we'll annotate our scripts with _subqueries_. Subqueries are\nreplacements, typically written at the top of the script, which SQL Composer\nuses to replace parts of the query.\n\nSubqueries are SQL comments, starting with two dashes `--`, followed by the\n`sub` string, followed by the subquery name, followed by the colon `:`,\nfollowed by the actual SQL string. For example:\n\n```sql\n-- insert_feeds.sql\n\n-- sub values: {binds}\n-- sub and_return: RETURNING id, name, rss\nINSERT INTO feeds(name, rss)\nVALUES {values}\n{and_return}\n```\n\nAbove script has two subqueries which might be replaced: `values` and\n`and_return`. Let's also create a second query which we'll need later:\n\n```sql\n-- get_feeds.sql\n\n-- sub where_rss_is: AND rss = :url\n-- sub where_name_is: AND name = :name\n-- sub where_name_is_in: AND name IN ({names})\n-- sub order_by: ORDER BY {what}\nSELECT id, name, rss\nFROM feeds\nWHERE\n true\n {where_rss_is}\n {where_name_is}\n {where_name_is_in}\n{order_by}\n```\n\n\nNow we need to initialize instance of `QueryLoader` and tell it\nwhere to search for the scripts.\n\nDepending on how you'd like to distribute your application, `QueryLoader`\nmight load scripts either from a package or ordinary directory.\n\n### Loading from the Package\n\nLet's say that you'd like to distribute SQL scripts together with your\napplication in a single Python package. In that case, you should create a\nsub-package inside your application's package. You do this by creating a\nsubdirectory in the source tree of your application and by putting\n`__init__.py` file inside there. You might end up with a directory structure\nlike this:\n\n```\n.\n+- src/\n +- myapp/\n +- __init__.py\n +- app.py\n +- queries/\n +- __init__.py\n +- insert_feeds.sql\n +- get_feeds.sql\n+- pyproject.toml\n```\n\nIn this case you should initialize QueryLoader like this:\n\n```python\nfrom sqlcomposer import QueryLoader\n\nloader = QueryLoader(package=\"myapp.queries\")\n```\n\n### Loading from the Path\n\nAlternatively you can pass a path to the directory which contains your SQL\nscripts. Both absolute and relative paths are accepted.\n\n```python\nfrom sqlcomposer import QueryLoader\n\nloader = QueryLoader(path=\"/home/user/myapp/src/myapp/queries\")\n```\n\n## Preparing the query\n\nTo load a query, simply call a method on QueryLoader's instance with the name\nof the file:\n\n```python\nget_feeds = loader.get_feeds()\n```\n\nNow we can easily compose parts of this query. For example if we'd like to\nfilter only feeds with specific names and make sure they're ordered by name:\n\n```python\nget_feeds = loader.get_feeds()\nget_feeds.where_name_is(name=\"foo\").order_by(what=\"name\")\n\ncursor.execute(get_feeds.sql(), get_feeds.params)\n```\n\n`get_feeds.sql()` produces the following query:\n\n```sql\nSELECT id, name, rss\nFROM feeds\nWHERE\n true\n AND name = :name\nORDER BY name\n```\n\nAlternatively we may decide to fetch feeds with names from a set of values:\n\n```python\nfrom sqlcomposer import QueryLoader, Another\n\nget_feeds = loader.get_feeds()\nget_feeds.where_name_is_in(names=Another(\"foo\", \"bar\", \"baz\")).order_by(what=\"name\")\ncursor.execute(get_feeds.sql(), get_feeds.params)\n\n# or alternatively:\n\nget_feeds = loader.get_feeds()\nfor name in [\"foo\", \"bar\", \"baz\"]:\n get_feeds.where_name_is_in(names=Another(\"foo\"))\nget_feeds.order_by(what=\"name\")\n\ncursor.execute(get_feeds.sql(), get_feeds.params)\n```\n\nHere we wrap the set of values in `Another()` object. This is a way of\ntelling SQL Composer that we want to create a separate placeholder for each\nvalue inside it and put them in place of `{names}` substitution in\n`where_name_is_in` subquery. We can repeat adding more values wrapped in\n`Another()` object and they will be added to the values added previously, as\npresented in the alternative form of this query.\n\nIf we called `where_name_is(names=\"foo\")` (i.e. with a value not wrapped\ninside `Another()` object), it'd overwrite the previous setup.\n\nAbove code produces the following query:\n\n```sql\nSELECT id, name, rss\nFROM feeds\nWHERE\n true\n AND name IN (:names_p1_0, :names_p1_1, :names_p1_2)\nORDER BY name\n```\n\nIf you inspect the `get_feeds.params`, you'll see that it is a dictionary\nwith your values mapped to the names of placeholders in produced SQL query.\n\n```python\n{ 'names_p1_0': 'foo', 'names_p1_1': 'bar', 'names_p1_2': 'baz' }\n```\n\n## Bulk Inserts\n\nBulk operations (like inserts) requre a little different syntax, where each\ninserted row is enclosed in parentheses. SQL Composer supports this with\nby enclosing the row values in `AnotherGroup()` object. Here's the example:\n\n```python\nobjects_to_insert = [\n {\"name\": \"foo\", \"rss\": \"https://example.com/foo.xml\"},\n {\"name\": \"bar\", \"rss\": \"https://example.com/bar.xml\"},\n]\n\ninsert_feeds = loader.insert_feeds().and_return()\nfor obj in objects_to_insert:\n insert_feeds.values(binds=AnotherGroup(obj[\"name\"], obj[\"rss\"]))\n\ncursor.execute(insert_feeds.sql(), insert_feeds.params)\n```\n\nThis produces the following query:\n\n```sql\nINSERT INTO feeds(name, rss)\nVALUES (:names_p1_0, :names_p1_1), (:names_p2_0, :names_p2_1)\nRETURNING id, name, rss\n```\n\n## Simple Initialization\n\nSome simple queries might not require any substitutions, but for clarity or\nother reasons you might want to use SQL Composer for them anyway. Suppose we\nhave this script:\n\n```sql\n-- add_user.sql\nINSERT INTO users(name, password)\nVALUES :name, :username\n```\n\nYou can use by passing necessary variables to the method which initializes\nthe script (`add_user()` here). It is much simpler and clearer than embedding\nthe query inside your code.\n\n```python\nadd_user = loader.add_user(name=\"alice\", password=\"hunter1\")\ncursor.execute(add_user.sql(), add_user.params)\n```\n\nAlternatively:\n\n```python\nadd_user = loader.add_user()\nadd_user.update_params(name=\"alice\", password=\"hunter1\")\ncursor.execute(add_user.sql(), add_user.params)\n```\n\n## Simplifying SQL\n\nNormally `sql()` function doesn't touch the formatting of the output query.\nIf you pass `simplify=True` to it, it'll remove all lines which start with a\ndouble dash (comments) and will put the whole query in a single line.\n\n```python\nadd_user = loader.add_user(name=\"alice\", password=\"hunter1\")\nprint(add_user.sql())\n```\n\nThis should output:\n\n```\nINSERT INTO users(name, password) VALUES :name, :username\n```\n\nKeep in mind that this won't remove any inline comments, so you might end up\nwith accidentally breaking half of your query if you use them.\n\n## Query Copying\n\nSometimes you might want to execute some very similar queries but with\ndifferent values passed to them. This is especially useful when your database\nengine has a limit on number of rows you can insert or on the size of the\nquery. Usually in these cases big queries are split into many smaller\nqueries.\n\nIf you'd use a single query, then in some cases, especially if you use\n`Another()` and `AnotherGroup()` features, you could end up with duplicates\nin your database from earlier queries.\n\nSolution to this is to initialize common pieces of query once and then copy\nit when needed. For example:\n\n```python\ninsert_feeds = loader.insert_feeds().and_return()\n\nfor chunk in split_list(very_large_list_of_feeds):\n insert_copy = insert_feeds.copy()\n for obj in chunk:\n insert_copy.values(binds=AnotherGroup(obj[\"name\"], obj[\"rss\"]))\n cursor.execute(insert_copy.sql(), insert_copy.params)\n```\n\n## Dialects\n\nDifferent databases and their drivers might use a different flavours of SQL.\nFor this reason SQL Composer allows specifying a dialect which it should use\nfor some parts of built query.\n\n```python\nloader = QueryLoader(package=queries, dialect=\"postgres\")\n```\n\nSupported dialects are:\n\n- sqlite (default)\n - named placeholders have form `:name`\n- postgres\n - named placeholders have form `%(name)s`\n",
"bugtrack_url": null,
"license": "GPL-3.0-only",
"summary": "Compose raw SQL queries in ORM-like fashion",
"version": "2.0.0",
"project_urls": {
"Homepage": "https://git.goral.net.pl/sqlcomposer.git/about",
"Repository": "https://git.goral.net.pl/sqlcomposer.git"
},
"split_keywords": [
"sql",
"query builder"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "14ff2cbb28487fed08105dac6fb0c2c5c6d8bd799d936676163e66309fc003c0",
"md5": "78b550e14ab998cc18c92e11917fe95b",
"sha256": "cae267b53fcde602e9900f69370781f0317fd39db858855b3af321057ed3cd9b"
},
"downloads": -1,
"filename": "sqlcomposer-2.0.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "78b550e14ab998cc18c92e11917fe95b",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.9,<4.0",
"size": 19233,
"upload_time": "2023-08-11T21:08:10",
"upload_time_iso_8601": "2023-08-11T21:08:10.358775Z",
"url": "https://files.pythonhosted.org/packages/14/ff/2cbb28487fed08105dac6fb0c2c5c6d8bd799d936676163e66309fc003c0/sqlcomposer-2.0.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "34526224389d9e310d656dca5448364847b24932f529213a02a8e60e2e4c652b",
"md5": "dac8bd91f91f4b0c0f45e7dc697dfb70",
"sha256": "4d19f72207443f3c5a3e5fec25c53faa5fca34489571561ae8d3cd1c7b34f9ae"
},
"downloads": -1,
"filename": "sqlcomposer-2.0.0.tar.gz",
"has_sig": false,
"md5_digest": "dac8bd91f91f4b0c0f45e7dc697dfb70",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.9,<4.0",
"size": 23817,
"upload_time": "2023-08-11T21:08:12",
"upload_time_iso_8601": "2023-08-11T21:08:12.027296Z",
"url": "https://files.pythonhosted.org/packages/34/52/6224389d9e310d656dca5448364847b24932f529213a02a8e60e2e4c652b/sqlcomposer-2.0.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-08-11 21:08:12",
"github": false,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"lcname": "sqlcomposer"
}
JSON