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.
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|
|:one, :1||A single row, as returned by cursor.fetchone(), usually 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, None will be returned.|
|:exactly-one, :=1||A single row, as returned by cursor.fetchone(), usually 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, None will be returned.|
|: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.|
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)
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.
|Filename, size||File type||Python version||Upload date||Hashes|
|Filename, size embrace-1.0.0-py3-none-any.whl (13.7 kB)||File type Wheel||Python version py3||Upload date||Hashes View|
|Filename, size embrace-1.0.0.tar.gz (9.3 kB)||File type Source||Python version None||Upload date||Hashes View|