This primer covers the basics of using PugSQL.
To install the latest version of PugSQL, use something like:
pip install pugsql
Writing SQL Files
The premise of PugSQL is that the best way to cope with the reality of database
ownership is to write SQL. In a PugSQL project, you’ll just have a set of
files in a directory. Like this:
$ ls queries/ search_users.sql update_username.sql user_for_id.sql
PugSQL SQL files use special leading comments to specify the names of the queries, and the desired return types. Queries can return a single row:
-- :name user_for_id :one select * from users where user_id = :user_id
Or many rows:
-- :name search_users :many select * from users where username like :pattern
Or they can return the number of affected rows:
-- :name update_username :affected update users set username = :username where user_id = :user_id
:scalar return type returns the first value in the first row:
-- :name get_username :scalar select username from users where user_id = :user_id
:insert return type returns the ID of the row inserted. In engines that
lastrowid, this works:
-- :name update_username :insert insert into users (username) values (:username)
With engines that do not support
:insert falls back to the same
Making a PugSQL Module
The SQL files we’ve created are parsed into a
Module by PugSQL. The
Module object exposes all of your queries as functions, taking keyword parameters. To create a module using the example above,
import pugsql # Load all of the *sql files in the queries/ directory into a single module. queries = pugsql.module('queries/')
More complicated projects can obviously have many modules and sub-modules.
It is safe to call
pugsql.module using the same path multiple times–PugSQL will not recompile the queries every time you do this.
Connecting to a Database
The easiest way to connect to a database is to just call the
connect method on your PugSQL module and give it a SQLAlchemy-compatible connection string.
For more advanced uses, you can also pass a SQLAlchemy Engine object to the
set_engine method on the module instead.
You can call queries like any other python function, passing them keyword parameters.
The return values depend on the result type specified in the SQL file. Records are returned as Python dictionaries, and the number of affected rows is returned as an integer.
You can use the
transaction method on
Module objects to define a transaction block:
with queries.transaction(): c = queries.get_counter(counter_id=1234) queries.update_counter(counter_id=1234, value=c+1)
The return value of the
transaction method is a SQLAlchemy Session object. So, for example, to manually roll back you could write:
with queries.transaction() as t: queries.foo() t.rollback()
Transactions can be nested, when the underlying engine supports
If for some reason you need to reset PugSQL, you can empty it out like so:
import pugsql pugsql.get_modules().clear()
That’s it! Good luck!