Metadata-Version: 1.1
Name: embrace
Version: 2.1.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.
        
        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')
        
        Your query would be specified like this::
        
            -- :name list_users :many
            select * from users where active = :active order by :identifier:order_by
        
        
        Embrace returns rows using the underlying db-api cursor. Most db-api
        libraries have cursor types that return dicts or namedtuples. For example in
        Postgresql you could do this::
        
            conn = psycopg2.connect(
                "postgresql:///mydb",
                cursor_factory=psycopg2.extras.NamedTupleCursor)
            )
        
        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 normally contain special comments to specify the query name and
        result type, and an SQL query:
        
        ::
        
            -- :name get_user_count
            -- :result :scalar
            SELECT count(1) FROM users
        
        If a result type is omitted, it will default to ``cursor``. Also, the result type
        can be included directly after the name:
        
        ::
        
            -- :name get_user_count :scalar
        
        If ``:name`` is omitted, it will default to the filename without the file extension.
        
        A single file may contain multiple 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.
        ========================= ======================================================
        
        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``
        
        
        
        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 (:values*: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?
        ------------------------------------------
        
        Embrace doesn't handle connections for you. You must open and maintain
        connection objects outside of EmbraceSQL. For PostgreSQL you can use
        ``psycopg2.extras.connection_pooling`` to help do this efficiently. Once you
        have a connection object, you must pass it every time you call a query.
        
        For convenience you can run queries inside a transaction with the following syntax:
        
        ::
        
            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 to catch exceptions, for example:
        
        ::
        
            try:
                queries.execute("SELECT 1.0 / 0.0")
            except embrace.exceptions.DataError:
                pass
        
        The original exception will be available in the ``__cause__`` attribute of the
        embrace exception object.
        
Keywords: sql hugsql pugsql orm anti-orm files
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
