Direct SQL · Asqueel

Scene 01

One execution path

Table queries and table writes end in one method: db dot execute. You can call it with your own SQL text. The statement joins the transaction of the selected connection, as in lesson 27. It returns the open cursor of the statement. Nothing is committed unless you ask for it.

Example & screen notes

Slide "Direct SQL". Bullets:

db.execute(sql, sqlargs=None, cursor=None, cursorname=None, autocommit=False, dbtable=None, storename=None, _adaptArguments=True)
db.executemany(query, params_seq)
db.analyze(), db.vacuum(table='', full=False), db.setConstraintsDeferred()

Scene 02

Parameters and cursors

Direct SQL uses physical names. The schema is invc, and the table is invc underscore region, because the package declares sqlprefix. Parameters are written as colon followed by a name. The second argument maps the names to values. Never put values into the SQL text yourself. The cursor has description, rowcount and arraysize. It fetches rows with fetchone, fetchmany, fetchall or iteration. A row is a NamedList, readable by column name or by position. Close the cursor when you no longer need it.

Example & screen notes

Code block and output.

cursor = db.execute(
    "INSERT INTO invc.invc_region (code, name) VALUES (:code, :name)",
    {'code': 'NO', 'name': 'North'})
cursor.rowcount          # 1
cursor.close()

cursor = db.execute("SELECT code, name FROM invc.invc_region WHERE code = :code",
                    {'code': 'NO'})
row = cursor.fetchone()
row['code'], row[1]      # ('NO', 'North')
cursor.close()
db.commit()

Bullets:

description, rowcount, arraysize
fetchone(), fetchmany(size), fetchall(), iteration, close()
Rows are NamedList: by name or by position

Scene 03

Colons in SQL text

A double colon is not a parameter, so PostgreSQL casts work unchanged. A single colon followed by a name is a parameter everywhere in the text. That includes the inside of a string literal. The third statement fails, because colon x inside the quotes became a parameter. To write a literal colon before a name, escape it with a backslash.

Example & screen notes

Code block and output.

db.execute("SELECT '5'::int AS n").fetchone()        # [n=5]
db.execute(r"SELECT '\:x' AS s").fetchone()          # [s=:x]
db.execute("SELECT ':x' AS s", {'x': 'bound'})
# psycopg.errors.IndeterminateDatatype

Scene 04

Environment parameters and compiled queries

A parameter named env underscore something reads that key from the environment of lesson 17. Here env underscore user reads the user of the session. An explicit value with the same name wins over the environment. db dot execute also accepts a CompiledQuery. The compiled property of a table query returns one. A compiled query already carries its parameters. Passing a second mapping raises TypeError.

Example & screen notes

Two code blocks.

Top:

with session(db, user='batch'):
    db.execute('SELECT :env_user AS who').fetchone()['who']                         # 'batch'
    db.execute('SELECT :env_user AS who', {'env_user': 'admin'}).fetchone()['who']  # 'admin'

Bottom:

query = db.table('invc.region').query(columns='$code,$name', where='$code = :c', c='NO')
db.execute(query.compiled).fetchall()      # [[code=NO,name=North]]
db.execute(query.compiled, {'c': 'SO'})
# TypeError: Parameters are already part of a CompiledQuery

Scene 05

executemany, autocommit, server-side cursors

db dot executemany runs one statement once for each parameter mapping. Its first argument must be a CompiledQuery. A string raises TypeError. A CompiledQuery built by hand contains the SQL for the driver. On PostgreSQL, the placeholders are percent, name in parentheses, s. autocommit equal True calls db dot commit right after the statement. It commits all the pending work of that connection, not only this statement. cursorname asks PostgreSQL for a server-side cursor. An asterisk generates the name. The cursor then fetches rows from the server in batches.

Example & screen notes

Three code blocks.

from asqueel import CompiledQuery

insert = CompiledQuery(
    'INSERT INTO invc.invc_region (code, name) VALUES (%(code)s, %(name)s)')
db.executemany(insert, [{'code': 'SO', 'name': 'South'},
                        {'code': 'EA', 'name': 'East'}]).close()
db.commit()

db.execute("UPDATE invc.invc_region SET name = 'Northern' WHERE code = 'NO'",
           autocommit=True)
db.rollback()            # the update is already committed

cursor = db.execute('SELECT code FROM invc.invc_region ORDER BY code', cursorname='*')
cursor.arraysize = 2
cursor.fetchmany()       # [[code=EA], [code=NO]]
cursor.close()

Scene 06

Errors and refused arguments

A driver error rolls the connection back before it reaches you, as in lesson 27. The insert of West is gone, and the database is usable. Three arguments of the signature are refused in this release. storename, cursor and underscore adaptArguments equal False raise UnsupportedFeatureError. dbtable is accepted, and only names the table.

Example & screen notes

Code block, then bullets.

db.execute("INSERT INTO invc.invc_region (code, name) VALUES ('WE', 'West')")
db.execute('SELECT * FROM invc.missing_table')
# psycopg.errors.UndefinedTable; the insert of West is rolled back

storename, cursor, _adaptArguments=False: UnsupportedFeatureError
dbtable: accepted, names the table only

Scene 07

Maintenance calls

db dot analyze runs ANALYZE inside the current transaction. db dot vacuum runs VACUUM ANALYZE on a separate connection, because VACUUM cannot run in a transaction. table is the SQL name of one table, and full adds FULL. vacuum refuses when the thread has pending work on any connection name. Here the commit after analyze leaves nothing pending, but the execute after it opened a new transaction. setConstraintsDeferred defers the deferrable constraints until the commit of the current transaction.

Example & screen notes

Code block, then bullets.

db.analyze()
db.commit()
db.execute('SELECT 1').close()
db.vacuum()
# TransactionStateError: vacuum requires no pending work on this thread; commit or rollback first
db.rollback()
db.vacuum(table='invc.invc_region')
db.vacuum(full=True)
db.setConstraintsDeferred()

analyze(): ANALYZE, in the current transaction
vacuum(): VACUUM [FULL] ANALYZE [table], on a separate connection
setConstraintsDeferred(): SET CONSTRAINTS ALL DEFERRED, current transaction

Scene 08

What direct SQL skips

Direct SQL is the SQL you wrote, and nothing more. The region hook did not run. The timestamp column of sys fields stays empty. No key is generated, so the customer insert passes its own id. Row policies do not apply either. The table query hides the draft customer. The raw SELECT returns it. Use table methods when the model must act, and direct SQL when it must not.

Example & screen notes

Code block, then bullets.

db.execute("INSERT INTO invc.invc_region (code, name) VALUES ('NO', 'North')")
# trigger_onInserting did not run; __ins_ts is NULL

db.execute("INSERT INTO invc.invc_customer (id, account_name, __is_draft) "
           "VALUES ('c1', 'Draft Ltd', true)")
db.table('invc.customer').query(columns='$account_name').fetch()    # []
db.execute('SELECT account_name FROM invc.invc_customer').fetchall()  # [[account_name=Draft Ltd]]

No hooks, no field triggers, no relation actions
No sys_fields values, no generated keys
No row policies

Scene 09

Recap

db dot execute runs SQL text or a compiled query inside the current transaction. executemany takes a compiled query, and autocommit commits all the pending work. Direct SQL runs no hooks, fills no defaults and applies no row policies. The next lesson creates and changes the database structure.

Example & screen notes

Slide "Recap". Bullets:

db.execute runs SQL text with :name and :env_name parameters, or a CompiledQuery, on the current transaction
executemany takes a CompiledQuery; autocommit=True commits all pending work
Direct SQL runs no hooks, no defaults and no row policies

Next: "Lesson 30 — Import and migrations".