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:

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?

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(
    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 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.