embrace


Nameembrace JSON
Version 4.1.0 PyPI version JSON
download
home_pagehttps://hg.sr.ht/~olly/embrace-sql
SummaryEmbrace SQL keeps your SQL queries in SQL files. An anti-ORM inspired by HugSQL and PugSQL
upload_time2022-04-14 16:19:54
maintainer
docs_urlNone
authorOliver Cope
requires_python
licenseApache
keywords sql hugsql pugsql orm anti-orm files dapper
VCS
bugtrack_url
requirements No requirements were recorded.
Travis-CI No Travis.
coveralls test coverage No coveralls.
            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 or named tuples?
----------------------------------------------

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
on to namedtuples, dicts 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.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 and namedtuples::

    from embrace import mapobject

    query = queries.query(
        """
        SELECT posts.*, users.*
        FROM posts JOIN users ON posts.user_id = users.id
        """
    ).returning(
        (
            mapobject.dict(split="post_id"),
            mapobject.namedtuple(split="user_id")
        )
    )
    for post, user in query.many(conn):
        …

and pass 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/2a/0e/1d355916d45b7681c84c67af49badbe34dcd1e7a5deafa33bdda0b428047/embrace-4.1.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 or named tuples?\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\non to namedtuples, dicts 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.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 and namedtuples::\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.id\n        \"\"\"\n    ).returning(\n        (\n            mapobject.dict(split=\"post_id\"),\n            mapobject.namedtuple(split=\"user_id\")\n        )\n    )\n    for post, user in query.many(conn):\n        \u2026\n\nand pass 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\n\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.1.0",
    "split_keywords": [
        "sql",
        "hugsql",
        "pugsql",
        "orm",
        "anti-orm",
        "files",
        "dapper"
    ],
    "urls": [
        {
            "comment_text": "",
            "digests": {
                "md5": "de5229e096cd55da528744ad46dd1a7f",
                "sha256": "67c473d3c6e11b74a87c2179e2d219d15dc5436a24c25d6636f3af7bbef6072d"
            },
            "downloads": -1,
            "filename": "embrace-4.1.0-py3-none-any.whl",
            "has_sig": false,
            "md5_digest": "de5229e096cd55da528744ad46dd1a7f",
            "packagetype": "bdist_wheel",
            "python_version": "py3",
            "requires_python": null,
            "size": 28115,
            "upload_time": "2022-04-14T16:19:52",
            "upload_time_iso_8601": "2022-04-14T16:19:52.395347Z",
            "url": "https://files.pythonhosted.org/packages/f1/b6/210b2ce4e558d05709add9bbc84a3879646b598f59d3382d1e100469ab03/embrace-4.1.0-py3-none-any.whl",
            "yanked": false,
            "yanked_reason": null
        },
        {
            "comment_text": "",
            "digests": {
                "md5": "8de5a60e737e22df162f4d5143cad972",
                "sha256": "117479468f258589264587028cbf67f6d449b2ed9ce082c3340adb72fa3c003f"
            },
            "downloads": -1,
            "filename": "embrace-4.1.0.tar.gz",
            "has_sig": false,
            "md5_digest": "8de5a60e737e22df162f4d5143cad972",
            "packagetype": "sdist",
            "python_version": "source",
            "requires_python": null,
            "size": 27880,
            "upload_time": "2022-04-14T16:19:54",
            "upload_time_iso_8601": "2022-04-14T16:19:54.395951Z",
            "url": "https://files.pythonhosted.org/packages/2a/0e/1d355916d45b7681c84c67af49badbe34dcd1e7a5deafa33bdda0b428047/embrace-4.1.0.tar.gz",
            "yanked": false,
            "yanked_reason": null
        }
    ],
    "upload_time": "2022-04-14 16:19:54",
    "github": false,
    "gitlab": false,
    "bitbucket": false,
    "lcname": "embrace"
}
        
Elapsed time: 0.34114s