SQLContainer

The SQLContainer is a storage container for the SQL statements used by a DatabaseManager of an application. It also provides the replace_placeholders() method for pre-processing of placeholders in a SQL statement before it is executed on the database.

class pandemy.SQLContainer[source]

Bases: object

Base class of a container of SQL statements.

Each SQL-dialect will subclass from SQLContainer and SQLContainer is never used on its own, but merely provides methods to work with SQL statements.

Each SQL statement is implemented as a class variable.

static replace_placeholders(stmt, placeholders)[source]

Replace placeholders in a SQL statement.

Replace the placeholders in the SQL statement stmt that are specified by the placeholder parameter of a Placeholder instance, supplied to the placeholders parameter, with their respective replacements in the replacements parameter of a Placeholder. A placeholder in a SQL statement is always prefixed with a colon (:) e.g. :myplaceholder.

The main purpose of the method is to handle parametrized IN statements with a variable number of values. A single placeholder can be placed in the IN statement and later be replaced by new placeholders that match the length of the replacements parameter of a Placeholder instance.

The return values stmt and params can be used as input to the execute() and load_table() methods of a DatabaseManager.

Parameters

stmt (str) – The SQL statement in which to replace placeholders.
placeholders (Placeholder or sequence of Placeholder) – The replacements for each placeholder in stmt.

Returns

stmt (str) – The SQL statement after placeholders have been replaced.
params (dict) – The new placeholders and their replacement values from the replacements parameter of a Placeholder. Entries to params are only written if the parameter return_new_placeholders in a Placeholder is set to True.

Example of a return value: {'v0': 'value1', 'v1': 3.14}. The new placeholders are always named v followed by a sequential number denoting the order (zero-indexed) in which the new placeholder occurs in the returned SQL statement stmt.

The keys of params never contain the prefix colon (:) that is used in the SQL statement to identify a placeholder.

Raises

pandemy.InvalidInputError – If the replacement values in a Placeholder are not valid.

See also

Placeholder : Container of a placeholder and its replacement values.
DatabaseManager.execute() : Execute a SQL statement.
DatabaseManager.load_table() : Load a SQL table into a pandas.DataFrame.

Examples

Replace the placeholders of a SQL statement (stmt) with new placeholders and return a mapping of the new placeholders to the desired values (params).

>>> stmt = 'SELECT * FROM Item WHERE ItemId IN (:itemid);'
>>> p1 = pandemy.Placeholder(placeholder=':itemid',
...                          replacements=[1, 2, 3],
...                          return_new_placeholders=True)
>>> stmt, params = pandemy.SQLContainer.replace_placeholders(stmt=stmt, placeholders=p1)
>>> stmt
'SELECT * FROM Item WHERE ItemId IN (:v0, :v1, :v2);'
>>> params
{'v0': 1, 'v1': 2, 'v2': 3}

If the SQL statement contains more than one placeholder a sequence of Placeholder can be passed.

>>> stmt = ('SELECT * FROM Item '
...         'WHERE ItemId IN (:itemid) AND Description LIKE :desc '
...         'ORDER BY :orderby;')
...
>>> p1 = pandemy.Placeholder(placeholder=':itemid',
...                          replacements=[1, 2, 3],
...                          return_new_placeholders=True)
...
>>> p2 = pandemy.Placeholder(placeholder=':desc',
...                          replacements='A%',
...                          return_new_placeholders=True)
...
>>> p3 = pandemy.Placeholder(placeholder=':orderby',
...                          replacements='ItemName DESC',
...                          return_new_placeholders=False)
...
>>> stmt, params = pandemy.SQLContainer.replace_placeholders(stmt=stmt,
...                                                          placeholders=[p1, p2, p3])
>>> stmt
'SELECT * FROM Item WHERE ItemId IN (:v0, :v1, :v2) AND Description LIKE :v3 ORDER BY ItemName DESC;'
>>> params
{'v0': 1, 'v1': 2, 'v2': 3, 'v3': 'A%'}

Note

The replacement for the ‘:orderby’ placeholder is not part of the returned params dictionary because return_new_placeholders=False for p3.

Warning

Replacing ‘:orderby’ by an arbitrary value that is not a placeholder is not safe against SQL injection attacks the way placeholders are and is therefore discouraged. The feature is there if it is needed, but be aware of its security limitations.

Input to the SQLContainer.replace_placeholders() method.

class pandemy.Placeholder(placeholder, replacements, return_new_placeholders=True)[source]

Bases: object

Container of placeholders and their replacement values for parametrized SQL statements.

The Placeholder handles placeholders and their replacement values when building parametrized SQL statements. A SQL placeholder is always prefixed by a colon (:) e.g. :myplaceholder in the SQL statement. Placeholder is used as input to the SQLContainer.replace_placeholders() method.

Parameters

placeholder (str) – The placeholder to replace in the SQL statement. E.g. ':myplaceholder'.
replacements (str or int or float or sequence of str or int or float) – The value(s) to replace placeholder with.
return_new_placeholders (bool, default True) – If replacements should be mapped to new placeholders in the params return value of the SQLContainer.replace_placeholders() method.