Metadata-Version: 1.1
Name: embrace
Version: 4.0.0
Summary: Embrace SQL keeps your SQL queries in SQL files. An anti-ORM inspired by HugSQL and PugSQL
Home-page: https://hg.sr.ht/~olly/embrace-sql
Author: Oliver Cope
Author-email: oliver@redgecko.org
License: Apache
Description: 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();
        
        
        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.
        
Keywords: sql hugsql pugsql orm anti-orm files dapper
Platform: UNKNOWN
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: Apache Software License
Classifier: Operating System :: OS Independent
Classifier: Topic :: Database
Classifier: Topic :: Database :: Front-Ends
Classifier: Topic :: Software Development
Classifier: Programming Language :: Python :: 3
