An interface for using plain SQL, in files.
=============================================
Does writing complex queries in an ORM feel like driving with the handbrake on?
Embrace SQL! Put your SQL queries in regular ``.sql`` files, and embrace will
load them.
Installation::
pip install embrace
Usage::
import embrace
# Connect to your database, using any db-api connector.
# If python supports it, so does embrace.
conn = psycopg2.connect("postgresql:///mydb")
# Create a module populated with queries from a collection of *.sql files:
queries = embrace.module("resources/sql")
# Run a query
users = queries.list_users(conn, order_by='created_at')
Add ``resources/sql/list_users.sql`` containing an SQL query::
-- :name list_users :many
select * from users where active = :active order by :identifier:order_by
What is the format of a query SQL file?
----------------------------------------
Embrace-SQL tries to stick close to the format used by HugSQL and PugSQL.
SQL files contain special comments to specify the query name and result type.
::
-- :name get_user_count
-- :result :scalar
SELECT count(1) FROM users
If a result type is omitted, it will default to ``cursor``. The result type
can be included in the same line as the name:
::
-- :name get_user_count :scalar
If ``:name`` is omitted, it will default to the filename without extension.
A single file may contain multiple SQL queries, separated by a structured SQL
comment. For example to create two query objects accessible as
``queries.list_users()`` and ``queries.get_user_by_id()``:
::
-- :name list_users :many
select * from users
-- :name get_user_by_id :one
select * from users where id=:id
But if you *don't* have the separating comment, embrace-sql can run
multiple statements in a single query call, returning the result from just the last one.
Why? Because it makes this possible in MySQL:
::
-- :result :column
insert into users (name, email) values (:name, :email);
select last_insert_id();
Including queries
`````````````````
You may include one query inside another using the ``:include:`` directive. For
example:
::
-- :name select_valid_users
SELECT * FROM users WHERE deleted_at IS NULL;
-- :name select_user_by_email
SELECT * FROM (:include:select_valid_users) WHERE email = :email;
What can queries return?
------------------------------
The following result types are supported:
========================= ======================================================
``:affected``, ``:n`` The number of rows affected
``:first`` The first row, as returned by ``cursor.fetchone()``,
or ``None`` if no row is found.
``:one``, ``:1`` A single row, as returned by ``cursor.fetchone()``,
usually as a tuple (but most db-api modules have
extensions allowing you to access rows as dicts or
named tuples.
If no row is generated by the query,
``embrace.exceptions.NoResultFound`` will be raised.
If more than one row is generated by the query,
``embrace.exceptions.MultipleResultsFound`` will be
raised.
``exactly-one`, ``:=1`` Synonyms for ``:one``, retained for compatibility
``:one-or-none`` As ``one``, but returns None if no row is returned by
the query.
``:many``, ``:*`` An iterator over a number of rows. Each row will be
the value returned by ``cursor.fetchone()``, usually
a tuple.
``:cursor``, ``:raw`` The cursor object.
``:scalar`` The value of the first column of the first row
returned by the query.
If no row is generated by the query, a
``NoResultFound`` will be raised.
``:column`` An iterator over the values in the first column
returned.
``:resultset`` An object supporting access to query results as any of
the above result types.
**This is the default result type if no result type is
specified**
========================= ======================================================
You can override the return type specified by the query from Python code by
using one of the following methods on the ``Query`` object:
- ``affected``
- ``one``
- ``exactlyone``
- ``many``
- ``cursor``
- ``scalar``
- ``column``
- ``resultset``
Use ``resultset`` to get access to both the results and cursor metadata, for example::
result = Query("SELECT * from mytable").resultset(conn)
print(result.many())
print(result.cursor.description)
How do I return rows as dicts, namedtuples or dataclasses?
----------------------------------------------------------
Queries return rows directly from the underlying db-api driver.
Many drivers have options to return data structures other than tuples (for
example ``sqlite3.Row`` or ``psycopg2.extras.DictCursor``). You will need to
configure these at the connection level.
See the next section for how to use ``embrace.query.mapobject`` to map rows
to dicts, namedtuples, dataclasses or your own ORM-style model classes.
How do I map rows onto objects?
-------------------------------
Embrace supports simple ORM style mapping.
Example::
import embrace
from dataclasses import dataclass
@dataclass
class User:
id: int
name: str
query = queries.query("SELECT * from users").returning(User)
users = query.many(conn)
Map multiple classes in a single query::
query = queries.query(
"SELECT * FROM posts JOIN users ON posts.user_id = users.id"
).returning((Post, User))
for post, user in query.many(conn):
…
By default embrace looks for fields named ``id`` (case insensitive) to
split up the row.
If you need to split on different columns, use ``mapobject`` to specify how to
map the returned columns onto objects::
from embrace import mapobject
query = queries.query(
"""
SELECT posts.*, users.*
FROM posts JOIN users ON posts.user_id = users.user_id
"""
).returning(
(
mapobject(Post, split="post_id"),
mapobject(User, split="user_id")
)
)
for post, user in query.many(conn):
…
``mapobject`` can also load columns into dicts, namedtuples and dataclasses::
from embrace import mapobject
query = queries.query(
"""
SELECT posts.*, categories.*, users.*
FROM posts
JOIN categories ON posts.category_id = categories.id
JOIN users ON posts.user_id = users.id
"""
).returning(
(
mapobject.dict(),
mapobject.namedtuple()
mapobject.dataclass()
)
)
for post, category, user in query.many(conn):
…
When mapping dataclasses, you can specify additional fields for the dataclass
by providing a list of fields in the format expected by
``dataclasses.make_dataclass``::
mapobject.dataclass(
[
('owner', typing.Any),
('images', list[Image], dataclasses.fields(default_factory=list))
]
)
Or as keyword arguments::
mapobject.dataclass(
owner=typing.Any,
images=(list[Image], dataclasses.fields(default_factory=list)),
)
``mapobject.passthrough`` passes individual columns through unchanged::
query = queries.query(
"""
SELECT posts.*, count(*) as reply_count
FROM posts JOIN replies ON posts.id = replies.post_id
"""
).returning(
(
mapobject(Post, split="post_id"),
mapobject.passthrough(split="reply_count"),
)
)
for post, reply_count in query.many(conn):
…
You can also tell embrace to populate join relationships::
from embrace import one_to_many
from embrace import one_to_one
query = queries.query(
"""
SELECT users.*, orders.*, products.*
FROM users
JOIN orders ON orders.user_id = users.id
JOIN products ON orders.product_id = products.id
ORDER BY users.id, orders.id
"""
).returning(
# Each row of this query returns data for a User, Order and Product
# object. The `key` parameter tells embrace to map items with identical
# key values to the same python object.
(
mapobject(User, key="id"),
mapobject(Order, key="id"),
mapobject(Product, key="id"),
),
joins=[
# Populate User.orders with the list of Order objects
one_to_many(User, 'orders', Order),
# Populate Order.product with the product object
one_to_one(Order, 'product', Product),
],
)
for user in query.many(conn):
for order in user.order:
product = order.product
…
Note that methods like ``query.one`` operate at the level of the database
cursor.
If you use ``joins`` to consolidate multiple database rows into a single
object,
you will still need to call ``query.many`` even if you only require a
single object to be returned.
How do parameters work?
------------------------
Placeholders inserted using the ``:name`` syntax are escaped by the db-api
driver:
::
-- Outputs `select * from user where name = 'o''brien'`;
select * from users where name = :name
You can interpolate lists and tuples too:
``:tuple:`` creates a placeholder like this ``(?, ?, ?)``
``:value*:`` creates a placeholder like this ``?, ?, ?``
``:tuple*:`` creates a placeholder like this ``(?, ?, ?), (?, ?, ?), …``
(useful for multiple insert queries)
::
-- Call this with `queries.insert_foo(data=(1, 2, 3))`
INSERT INTO foo (a, b, c) VALUES :tuple:data
-- Call this with `queries.get_matching_users(names=("carolyn", "douglas"))`
SELECT * from users WHERE name in (:value*:names)
You can escape identifiers with ``:identifier:``, like this:
::
-- Outputs `select * from "some random table"`
select * from :identifier:table_name
You can pass through raw sql too. This leaves you open to SQL injection attacks if you allow user input into such parameters:
::
-- Outputs `select * from users order by name desc`
select * from users order by :raw:order_clause
How do I handle connections? Transactions?
------------------------------------------
You must pass a db-api connection object every time you call a query.
You can manage these connections yourself, but Embrace also offers a connection
pooling module.
::
from embrace import pool
# Create a connection pool
connection_pool = pool.ConnectionPool(
partial(psycopg2.connect, database='mydb'),
limit=10
)
# Example 1 - explicit calls to getconn/release
conn = connection_pool.getconn()
try:
queries.execute_some_query(conn)
finally:
connection_pool.release(conn)
# Example 2 - context manager
with connection_pool.connect() as conn:
queries.execute_some_query(conn)
Transaction handling may be handled manually by calling ``commit()`` or
``rollback()`` on the connection object, or you can also use the
``transaction`` context run to queries in a transaction:
::
with queries.transaction(conn) as q:
q.increment_counter()
The transaction will be commited when the ``with`` block exits, or rolled back
if an exception occurred.
How do I reload queries when the underlying files change?
---------------------------------------------------------
Pass auto_reload=True when constructing a module:
::
m = module('resources/sql', auto_reload=True)
Exceptions
----------
Exceptions raised from the underlying db-api connection are wrapped in
exception classes from ``embrace.exceptions``, with PEP-249 compliant names.
You can use this like so:
::
try:
queries.execute("SELECT 1.0 / 0.0")
except embrace.exceptions.DataError:
pass
The original exception is available in the ``__cause__`` attribute of the
embrace exception object.
Raw data
{
"_id": null,
"home_page": "https://hg.sr.ht/~olly/embrace-sql",
"name": "embrace",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "sql hugsql pugsql orm anti-orm files dapper",
"author": "Oliver Cope",
"author_email": "oliver@redgecko.org",
"download_url": "https://files.pythonhosted.org/packages/89/96/8b43a6af433e38d96c18c43bc34b2b2bc3d0a883cc37161cd12b832eef4c/embrace-4.3.0.tar.gz",
"platform": null,
"description": "An interface for using plain SQL, in files.\n=============================================\n\nDoes writing complex queries in an ORM feel like driving with the handbrake on?\nEmbrace SQL! Put your SQL queries in regular ``.sql`` files, and embrace will\nload them.\n\nInstallation::\n\n pip install embrace\n\n\nUsage::\n\n import embrace\n\n # Connect to your database, using any db-api connector.\n # If python supports it, so does embrace.\n conn = psycopg2.connect(\"postgresql:///mydb\")\n\n # Create a module populated with queries from a collection of *.sql files:\n queries = embrace.module(\"resources/sql\")\n\n # Run a query\n users = queries.list_users(conn, order_by='created_at')\n\nAdd ``resources/sql/list_users.sql`` containing an SQL query::\n\n -- :name list_users :many\n select * from users where active = :active order by :identifier:order_by\n\n\n\nWhat is the format of a query SQL file?\n----------------------------------------\n\nEmbrace-SQL tries to stick close to the format used by HugSQL and PugSQL.\nSQL files contain special comments to specify the query name and result type.\n\n::\n\n -- :name get_user_count\n -- :result :scalar\n SELECT count(1) FROM users\n\nIf a result type is omitted, it will default to ``cursor``. The result type\ncan be included in the same line as the name:\n\n::\n\n -- :name get_user_count :scalar\n\nIf ``:name`` is omitted, it will default to the filename without extension.\n\nA single file may contain multiple SQL queries, separated by a structured SQL\ncomment. For example to create two query objects accessible as\n``queries.list_users()`` and ``queries.get_user_by_id()``:\n\n::\n\n -- :name list_users :many\n select * from users\n\n -- :name get_user_by_id :one\n select * from users where id=:id\n\nBut if you *don't* have the separating comment, embrace-sql can run\nmultiple statements in a single query call, returning the result from just the last one.\n\nWhy? Because it makes this possible in MySQL:\n\n::\n\n -- :result :column\n insert into users (name, email) values (:name, :email);\n select last_insert_id();\n\n\nIncluding queries\n`````````````````\n\nYou may include one query inside another using the ``:include:`` directive. For\nexample:\n\n\n::\n\n -- :name select_valid_users\n SELECT * FROM users WHERE deleted_at IS NULL;\n\n -- :name select_user_by_email\n SELECT * FROM (:include:select_valid_users) WHERE email = :email;\n\n\n\nWhat can queries return?\n------------------------------\n\nThe following result types are supported:\n\n========================= ======================================================\n``:affected``, ``:n`` The number of rows affected\n\n``:first`` The first row, as returned by ``cursor.fetchone()``,\n or ``None`` if no row is found.\n\n``:one``, ``:1`` A single row, as returned by ``cursor.fetchone()``,\n usually as a tuple (but most db-api modules have\n extensions allowing you to access rows as dicts or\n named tuples.\n\n If no row is generated by the query,\n ``embrace.exceptions.NoResultFound`` will be raised.\n If more than one row is generated by the query,\n ``embrace.exceptions.MultipleResultsFound`` will be\n raised.\n\n``exactly-one`, ``:=1`` Synonyms for ``:one``, retained for compatibility\n\n``:one-or-none`` As ``one``, but returns None if no row is returned by\n the query.\n\n``:many``, ``:*`` An iterator over a number of rows. Each row will be\n the value returned by ``cursor.fetchone()``, usually\n a tuple.\n\n``:cursor``, ``:raw`` The cursor object.\n\n``:scalar`` The value of the first column of the first row\n returned by the query.\n\n If no row is generated by the query, a\n ``NoResultFound`` will be raised.\n\n``:column`` An iterator over the values in the first column\n returned.\n\n``:resultset`` An object supporting access to query results as any of\n the above result types.\n\n **This is the default result type if no result type is\n specified**\n========================= ======================================================\n\nYou can override the return type specified by the query from Python code by\nusing one of the following methods on the ``Query`` object:\n\n - ``affected``\n - ``one``\n - ``exactlyone``\n - ``many``\n - ``cursor``\n - ``scalar``\n - ``column``\n - ``resultset``\n\nUse ``resultset`` to get access to both the results and cursor metadata, for example::\n\n result = Query(\"SELECT * from mytable\").resultset(conn)\n print(result.many())\n print(result.cursor.description)\n\nHow do I return rows as dicts, namedtuples or dataclasses?\n----------------------------------------------------------\n\nQueries return rows directly from the underlying db-api driver.\nMany drivers have options to return data structures other than tuples (for\nexample ``sqlite3.Row`` or ``psycopg2.extras.DictCursor``). You will need to\nconfigure these at the connection level.\n\nSee the next section for how to use ``embrace.query.mapobject`` to map rows\nto dicts, namedtuples, dataclasses or your own ORM-style model classes.\n\nHow do I map rows onto objects?\n-------------------------------\n\nEmbrace supports simple ORM style mapping.\n\nExample::\n\n\n import embrace\n from dataclasses import dataclass\n\n @dataclass\n class User:\n id: int\n name: str\n\n query = queries.query(\"SELECT * from users\").returning(User)\n users = query.many(conn)\n\n\nMap multiple classes in a single query::\n\n query = queries.query(\n \"SELECT * FROM posts JOIN users ON posts.user_id = users.id\"\n ).returning((Post, User))\n for post, user in query.many(conn):\n \u2026\n\n\nBy default embrace looks for fields named ``id`` (case insensitive) to\nsplit up the row.\n\nIf you need to split on different columns, use ``mapobject`` to specify how to\nmap the returned columns onto objects::\n\n from embrace import mapobject\n\n query = queries.query(\n \"\"\"\n SELECT posts.*, users.*\n FROM posts JOIN users ON posts.user_id = users.user_id\n \"\"\"\n ).returning(\n (\n mapobject(Post, split=\"post_id\"),\n mapobject(User, split=\"user_id\")\n )\n )\n for post, user in query.many(conn):\n \u2026\n\n``mapobject`` can also load columns into dicts, namedtuples and dataclasses::\n\n from embrace import mapobject\n\n query = queries.query(\n \"\"\"\n SELECT posts.*, categories.*, users.*\n FROM posts\n JOIN categories ON posts.category_id = categories.id\n JOIN users ON posts.user_id = users.id\n \"\"\"\n ).returning(\n (\n mapobject.dict(),\n mapobject.namedtuple()\n mapobject.dataclass()\n )\n )\n for post, category, user in query.many(conn):\n \u2026\n\n\nWhen mapping dataclasses, you can specify additional fields for the dataclass\nby providing a list of fields in the format expected by\n``dataclasses.make_dataclass``::\n\n mapobject.dataclass(\n [\n ('owner', typing.Any),\n ('images', list[Image], dataclasses.fields(default_factory=list))\n ]\n )\n\nOr as keyword arguments::\n\n mapobject.dataclass(\n owner=typing.Any,\n images=(list[Image], dataclasses.fields(default_factory=list)),\n )\n\n``mapobject.passthrough`` passes individual columns through unchanged::\n\n query = queries.query(\n \"\"\"\n SELECT posts.*, count(*) as reply_count\n FROM posts JOIN replies ON posts.id = replies.post_id\n \"\"\"\n ).returning(\n (\n mapobject(Post, split=\"post_id\"),\n mapobject.passthrough(split=\"reply_count\"),\n )\n )\n for post, reply_count in query.many(conn):\n \u2026\n\nYou can also tell embrace to populate join relationships::\n\n from embrace import one_to_many\n from embrace import one_to_one\n\n query = queries.query(\n \"\"\"\n SELECT users.*, orders.*, products.*\n FROM users\n JOIN orders ON orders.user_id = users.id\n JOIN products ON orders.product_id = products.id\n ORDER BY users.id, orders.id\n \"\"\"\n ).returning(\n # Each row of this query returns data for a User, Order and Product\n # object. The `key` parameter tells embrace to map items with identical\n # key values to the same python object.\n (\n mapobject(User, key=\"id\"),\n mapobject(Order, key=\"id\"),\n mapobject(Product, key=\"id\"),\n ),\n joins=[\n # Populate User.orders with the list of Order objects\n one_to_many(User, 'orders', Order),\n\n # Populate Order.product with the product object\n one_to_one(Order, 'product', Product),\n ],\n )\n\n for user in query.many(conn):\n for order in user.order:\n product = order.product\n \u2026\n\nNote that methods like ``query.one`` operate at the level of the database\ncursor.\nIf you use ``joins`` to consolidate multiple database rows into a single\nobject,\nyou will still need to call ``query.many`` even if you only require a\nsingle object to be returned.\n\nHow do parameters work?\n------------------------\n\nPlaceholders inserted using the ``:name`` syntax are escaped by the db-api\ndriver:\n\n::\n\n -- Outputs `select * from user where name = 'o''brien'`;\n select * from users where name = :name\n\nYou can interpolate lists and tuples too:\n\n``:tuple:`` creates a placeholder like this ``(?, ?, ?)``\n\n``:value*:`` creates a placeholder like this ``?, ?, ?``\n\n``:tuple*:`` creates a placeholder like this ``(?, ?, ?), (?, ?, ?), \u2026``\n(useful for multiple insert queries)\n\n::\n\n -- Call this with `queries.insert_foo(data=(1, 2, 3))`\n INSERT INTO foo (a, b, c) VALUES :tuple:data\n\n -- Call this with `queries.get_matching_users(names=(\"carolyn\", \"douglas\"))`\n SELECT * from users WHERE name in (:value*:names)\n\n\nYou can escape identifiers with ``:identifier:``, like this:\n\n::\n\n -- Outputs `select * from \"some random table\"`\n select * from :identifier:table_name\n\nYou can pass through raw sql too. This leaves you open to SQL injection attacks if you allow user input into such parameters:\n\n::\n\n -- Outputs `select * from users order by name desc`\n select * from users order by :raw:order_clause\n\n\nHow do I handle connections? Transactions?\n------------------------------------------\n\nYou must pass a db-api connection object every time you call a query.\nYou can manage these connections yourself, but Embrace also offers a connection\npooling module.\n\n::\n\n from embrace import pool\n\n # Create a connection pool\n connection_pool = pool.ConnectionPool(\n partial(psycopg2.connect, database='mydb'),\n limit=10\n )\n\n # Example 1 - explicit calls to getconn/release\n conn = connection_pool.getconn()\n try:\n queries.execute_some_query(conn)\n finally:\n connection_pool.release(conn)\n\n # Example 2 - context manager\n with connection_pool.connect() as conn:\n queries.execute_some_query(conn)\n\n\nTransaction handling may be handled manually by calling ``commit()`` or\n``rollback()`` on the connection object, or you can also use the\n``transaction`` context run to queries in a transaction:\n\n::\n\n with queries.transaction(conn) as q:\n q.increment_counter()\n\nThe transaction will be commited when the ``with`` block exits, or rolled back\nif an exception occurred.\n\n\n\nHow do I reload queries when the underlying files change?\n---------------------------------------------------------\n\nPass auto_reload=True when constructing a module:\n\n::\n\n m = module('resources/sql', auto_reload=True)\n\n\nExceptions\n----------\n\nExceptions raised from the underlying db-api connection are wrapped in\nexception classes from ``embrace.exceptions``, with PEP-249 compliant names.\nYou can use this like so:\n\n::\n\n try:\n queries.execute(\"SELECT 1.0 / 0.0\")\n except embrace.exceptions.DataError:\n pass\n\nThe original exception is available in the ``__cause__`` attribute of the\nembrace exception object.\n",
"bugtrack_url": null,
"license": "Apache",
"summary": "Embrace SQL keeps your SQL queries in SQL files. An anti-ORM inspired by HugSQL and PugSQL",
"version": "4.3.0",
"project_urls": {
"Homepage": "https://hg.sr.ht/~olly/embrace-sql"
},
"split_keywords": [
"sql",
"hugsql",
"pugsql",
"orm",
"anti-orm",
"files",
"dapper"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "77efbc49a2f846d8d9f631f36288f022175daf8bfed8a4457a076a09947648f4",
"md5": "e9420b74b6548591d1a54d3182701fc6",
"sha256": "192d5bedc50f3f9383056b96f1985af4b83ec5da40db77021b39664b4015ec19"
},
"downloads": -1,
"filename": "embrace-4.3.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "e9420b74b6548591d1a54d3182701fc6",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 29993,
"upload_time": "2023-07-18T11:31:07",
"upload_time_iso_8601": "2023-07-18T11:31:07.098771Z",
"url": "https://files.pythonhosted.org/packages/77/ef/bc49a2f846d8d9f631f36288f022175daf8bfed8a4457a076a09947648f4/embrace-4.3.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "89968b43a6af433e38d96c18c43bc34b2b2bc3d0a883cc37161cd12b832eef4c",
"md5": "dfa0b9264d286876244e317d9c74cb6c",
"sha256": "21787ea55ba0cdc18233447a4658ead06f2514a05c150be2084c102e703f8766"
},
"downloads": -1,
"filename": "embrace-4.3.0.tar.gz",
"has_sig": false,
"md5_digest": "dfa0b9264d286876244e317d9c74cb6c",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 36560,
"upload_time": "2023-07-18T11:31:08",
"upload_time_iso_8601": "2023-07-18T11:31:08.586143Z",
"url": "https://files.pythonhosted.org/packages/89/96/8b43a6af433e38d96c18c43bc34b2b2bc3d0a883cc37161cd12b832eef4c/embrace-4.3.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-07-18 11:31:08",
"github": false,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"lcname": "embrace"
}
JSON