How to use quma¶

quma reads sql files from the file system and makes them accessible as script objects. It passes the content of the files to a connected database management system (DBMS) when these objects are called.

Throughout this document we assume a directory with the following structure:

Scripts                │   Content
───────────────────────│─────────────────────────────────────────
path/to/sql/scripts    │
├─ users               │
│   ├─ all.sql         │   SELECT * FROM users
│   ├─ by_city.sql     │   SELECT * FROM users WHERE city = :city
│   ├─ by_id.sql       │   SELECT * FROM users WHERE id = :id
│   ├─ remove.sql      │   DELETE FROM users WHERE id = :id
│   └─ rename.sql      │   UPDATE TABLE users
│                      │       SET name = :name WHERE id = :id
└─ get_admin.sql       │   SELECT * FROM users WHERE admin = 1

After initialization you can run these scripts by calling members of a Database or a Cursor instance. Using the example above the following members are available:

# 'cur' is a cursor instance
cur.users.all(...
cur.users.by_city(...
cur.users.by_id(...
cur.users.rename(...
cur.users.remove(...
cur.get_admin(...

Read on to see how this works.

Opening a connection¶

To connect to a DBMS you need to instantiate an object of the Database class and provide a connection string and either a single path or a list of paths to your SQL scripts.

from quma import Database
db = Database('sqlite3:///:memory:', '/path/to/sql-scripts')

Note: Database instances are threadsafe.

For more connection examples (e. g. PostgreSQL or MySQL/MariaDB) and parameters see Connecting. quma also supports connection pooling.

From now on we assume an established connection in the examples.

Creating a cursor¶

DBAPI libs like psycopg2 or sqlite3 have the notion of a cursor, which is used to manage the context of a fetch operation. quma is similar in that way. To execute queries you need to create a cursor instance.

quma provides two ways to create a cursor object. Either by using a context manager:

with db.cursor as cur:
    ...

Or by calling the cursor method of the Database instance:

try:
    cur = db.cursor()
finally:
    cur.close()

Running queries¶

To run the query in a sql script from the path(s) you passed to the Database constructor you call members of the Database instance or the cursor (db and cur from now on).

Scripts and directories at the root of the path are translated to direct members of db or cur. After initialisation of our example dir above, the script /get_admin.sql is available as Script instance db.get_admin or cur.get_admin and the directory /users as instance of Namespace, i. e. db.users or cur.users. Scripts in subfolders will create script objects as members of the corresponding namespace: /users/all will be db.users.all or cur.users.all.

When you call a Script object, as in cur.user.all() where all is the mentioned object, you get back a Query instance. The simplest use is to iterate over it (see below for more information about the Query class):

with db.cursor as cur:
    all_users = cur.users.all()
    for user in all_users:
        print(user['name'])

The same using the db API:

with db.cursor as cur:
    all_users = db.users.all(cur)

To learn what you can do with Query objects see The Query class.

Note

As you can see cur provides a nicer API where you don’t have to pass the cursor when you call a script or a method. Then again the db API has the advantage of being around 30% faster. But this should only be noticeable if you run hundreds or thousands of queries in a row for example in a loop.

If you have cloned the quma repository from github you can see the difference when you run the script bin/cursor_vs_db.py.

Committing changes and rollback¶

quma does not automatically commit by default. You have to manually commit all changes as well as rolling back if an error occurs using the commit() and rollback() methods of the cursor.

try:
    cur.users.remove(id=13).run()
    cur.users.rename(id=14, name='New Name').run()
    cur.commit()
except Exception:
    cur.rollback()

If db is initialized with the flag contextcommit set to True and a context manager is used, quma will automatically commit when the context manager ends. So you don’t need to call cur.commit(). If the block raises an exception, quma rolls back instead.

db = Database('sqlite3:///:memory:', contextcommit=True)

with db.cursor as cur:
    cur.users.remove(id=13).run()
    cur.users.rename(id=14, name='New Name').run()
    # no need to call cur.commit()

Note: quma also rolls back when the block raises an exception that signals success, such as a redirect raised as Pyramid’s HTTPFound. Commit before raising it:

with db.cursor as cur:
    cur.users.rename(id=14, name='New Name').run()
    cur.commit()
    raise HTTPFound(location='/users/14')

Note: If you are using MySQL or SQLite some statements will automatically cause a commit. See the MySQL docs and SQLite docs

Autocommit¶

If you pass autocommit=True when you initialize a cursor, each query will be executed in its own transaction that is implicitly committed.

with db(autocommit=True).cursor as cur:
    cur.users.remove(id=13).run()

try:
    cur = db.cursor(autocommit=True)
    cur.users.remove(id=13).run()
finally:
    cur.close()

To make every cursor of a database work like that, pass autocommit=True to Database. Statements then commit on their own, and transaction blocks group the ones that belong together:

db = Database('psycopg2+pool://user:secret@localhost/app', sqldir,
              autocommit=True)

with db.cursor as cur:
    user = cur.users.by_id(id=13).one()
    with cur.transaction():
        cur.users.rename(id=13, name='New Name').run()
        cur.users.log_rename(id=13).run()

An exception no longer undoes statements that ran outside a block, so it is a choice for an application as a whole. It suits new applications, and the default suits code that relies on commit().

Transaction blocks¶

The method transaction() of a cursor runs a block atomically. It commits at the end of the block and rolls back if an exception leaves it:

with db.cursor as cur, cur.transaction():
    user = cur.users.by_id(id=13).one()
    cur.users.rename(id=13, name='New Name').run()

A block inside another block uses a savepoint: an exception that leaves it undoes only its own work, and the outermost block still decides about the whole transaction. So a function can use a block whether or not its caller has one:

def reserve(cur, item):
    with cur.transaction():
        cur.stock.reduce(item=item).run()
        cur.stock.log(item=item).run()

with db.cursor as cur, cur.transaction():
    cur.orders.add(item=1).run()
    try:
        reserve(cur, 1)
    except StockError:
        cur.orders.backorder(item=1).run()

Cursors on one carrier share a connection, so a block opened through one of them contains the statements of the others, and their blocks nest.

The outermost block owns its transaction, which has some consequences:

A block can’t start while a transaction is open that no block started, because it could neither commit nor undo that work on its own. quma.APIError tells you. Without autocommit, a statement opens a transaction: any statement on PostgreSQL, one that reads a table on MySQL and MariaDB, a write on SQLite. Start the block before the first statement, or use autocommit, where only blocks open transactions.
commit() and rollback() raise quma.APIError inside a block. Raise an exception to roll back. An exception that signals success, like Pyramid’s HTTPFound, rolls back too: raise it after the block.
With contextcommit, a block commits at its own end, and the cursor commits what ran after it. A carried cursor that ends inside another cursor’s block leaves the transaction to the block.
Closing a cursor or returning its connection inside a block rolls the transaction back, and the end of the block raises quma.APIError. With persist=True all cursors share one connection, so closing any of them does that.
PostgreSQL aborts a transaction when a statement fails. If you catch that error in the block, the end of the block rolls back and raises quma.APIError instead of pretending to commit. To continue after a statement that may fail, wrap it in a nested block.
MySQL and MariaDB commit on their own for some statements, like CREATE TABLE, see the note above. That ends the transaction of a block: a nested block then fails at its end, an outermost one can’t tell.

Executing literal statements¶

Database instances provide the method execute(). You can pass arbitrary sql strings. Each call will be automatically committed. If there is a result it will be returned otherwise it returns None.

db.execute('CREATE TABLE users ...')
users = db.execute('SELECT * FROM users')
for user in users:
    print(user.name)

If you want to execute statements in the context of a transaction use the execute() method of the cursor:

with db.cursor as cur:
    cur.execute('DELETE FROM users WHERE id = 13');
    cur.commit()

Both pass the SQL to the driver unchanged, so they take the driver’s placeholders. For ad hoc queries with :name placeholders use cur.query() (see Passing parameters).

Accessing the DBAPI cursor and connection¶

The underlying DBAPI connection and cursor objects are available as members of the cursor instance. The connection object as raw_conn and the cursor as raw_cursor.cursor.

# The connection
cur.raw_conn.autocommit = True
dbapi_cursor = cur.raw_conn.cursor()

# The cursor
cur.raw_cursor.cursor.execute('SELECT * FROM users;')
users = cur.raw_cursor.cursor.fetchall()
# raw_cursor wraps the real cursor. This would work as well
cur.raw_cursor.execute('SELECT * FROM users;')
users = cur.raw_cursor.fetchall()

All members of the raw_cursor.cursor object are also available as members of cur. Hence there should be no need to use it directly:

cur.execute('SELECT * FROM users;')
users = cur.fetchall()

How to use quma¶

Opening a connection¶

Creating a cursor¶

Running queries¶

Committing changes and rollback¶

Autocommit¶

Transaction blocks¶

Executing literal statements¶

Accessing the DBAPI cursor and connection¶

Table of Contents

Previous topic

Next topic

This Page