Parameters

dbapix has gone to a great effort to normalize parameter binding, and especially in emulating “magic” f-string syntax that pulls values from the execution scope.

There are currently 3 general styles of parameter binding that are automatically picked from based on their appearance in a query, and the passing of explicit params:

• If there are usable curly brace pairs, and no params, F-String Style is used.

  foo = 123
  cur.execute('SELECT {foo}')
  assert next(cur)[0] == 123
  

• If there are usable curly brace pairs, and params, Format Style is used.

  # Named:
  cur.execute('SELECT {foo}', dict(foo=456))
  assert next(cur)[0] == 456
  
  # Positional params:
  cur.execute('SELECT {}', (123, ))
  assert next(cur)[0] == 123
  

• Otherwise, the Wrapped Style is used.

  cur.execute('SELECT ?', (123, ))
  assert next(cur)[0] == 123
  

Format Style

This style mimics the str.format method, as described by Format String Syntax in the Python docs.

It is not identical, however. The practical differences are:

• For non-integer fields, the expression is evaluated directly (like f-strings). The incompatibility is that string keys need to be quoted, e.g. field['key'] instead of field[key].

• The conversions via ! no longer work.

• The format spec language has been replaced, and is much simpler:

Spec	Meaning
i, ident, or identifier	The value should be quoted as an identifier by Engine.quote_identifier().
t, or type	The value should be converted to the engine’s syntax by Engine.adapt_type().
l, or literal	The value should be inserted as-is, without any quoting.
v, or values	The value should be formatted as a VALUES tuple.
vl, or values_list	The value should be formatted as a list of VALUES tuples.

Field examples:

# Implicit position:
cur.execute('''SELECT {}, {}''', (1, 2))
assert next(cur) == (1, 2)

# Explicit position:
cur.execute('''SELECT {1}, {0}''', (1, 2))
assert next(cur) == (2, 1)

# Named:
cur.execute('''SELECT {foo}''', dict(foo=123))
assert next(cur)[0] == 123

# Sub fields:
cur.execute('''SELECT {foo[0]}, {bar['baz']}''', dict(foo=(1, 2, 3), bar=dict(baz=4)))
assert next(cur) == (1, 4)

# Expressions:
cur.execute('''SELECT {foo + bar}''', dict(foo=1, bar=2))
assert next(cur)[0] == 3

Format spec examples:

# Identifiers:
cur.execute('''SELECT * FROM {:i}''', ['foo'])
assert next(cur)[0] == 123

# Types:
cur.execute('''CREATE TABLE type_example (id {'SERIAL PRIMARY KEY':type}, value INTEGER)''')

# Literals:
cur.execute('''SELECT {:literal}''', ['''date('now')'''])

# Values:
cur.execute('''INSERT INTO bar VALUES {:values}''', [(1, 2, 3)])

# Values list:
cur.execute('''INSERT INTO bar VALUES {:values_list}''', [[(1, 2, 3), (4, 5, 6)]])

F-String Style

This style is an extension of the Format Style and mimics the f'{foo}' style described by Formatted string literals in the Python docs; it evalutes the expressions in the namespace that execute (or similar) was called:

foo = 123
cur.execute('''SELECT {foo}''')  # NOTE no params passed here!
assert next(cur)[0] == 123

Note that unlike Python f-strings, this is only able to pull values from the calling scope and it’s global scope. It is not able to bind to values in other enclosing scopes:

foo = 1

def outer():

    # This is not visible as a parameter.
    bar = 2

    def inner():

        baz = 3

        # This will fail due to `bar`.
        cur.execute('''SELECT {foo}, {bar}, {baz}''')

    inner()

Warning

You must not use the f prefix on the strings. These are not actually f-strings, but merely mimic them. If you accidentally use f-strings directly you will expose your code to SQL injection vulnerabilities.

Wrapped Style

This is the style employed by the wrapped database, and depends on the Cursor.paramstyle attribute.

In the case of SQLite, this is '?':

cur.execute('SELECT ?', (123, ))
assert next(cur)[0] == 123

If you want to use curly braces in a wrapped-style query, they must be escaped by doubling:

cur.execute('SELECT "{{0}}", ?', (123, ))
assert next(cur) == ('{0}', 123)