Embrace SQL keeps your SQL queries in SQL files. An anti-ORM inspired by HugSQL and PugSQL
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.
pip install embrace
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();
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:
The number of rows affected
The first row, as returned by cursor.fetchone(), or None if no row is found.
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.
Synonyms for :one, retained for compatibility
As one, but returns None if no row is returned by the query.
An iterator over a number of rows. Each row will be the value returned by cursor.fetchone(), usually a tuple.
The cursor object.
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.
An iterator over the values in the first column returned.
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:
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.
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 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.
Release history Release notifications | RSS feed
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.