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
andSQLContainer
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 aPlaceholder
. 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()
andload_table()
methods of aDatabaseManager
.- 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 aPlaceholder
is set toTrue
.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 apandas.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 becausereturn_new_placeholders=False
forp3
.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 theSQLContainer.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.
See also
SQLContainer
: A container of SQL statements.
Examples
Creating a
Placeholder
and accessing its attributes.>>> p1 = pandemy.Placeholder( ... placeholder=':itemid', ... replacements=[1, 2, 3] ... ) >>> p1 Placeholder(placeholder=':itemid', replacements=[1, 2, 3], return_new_placeholders=True) >>> p2 = pandemy.Placeholder( ... placeholder=':itemname', ... replacements='A%', ... return_new_placeholders=False ... ) >>> p2 Placeholder(placeholder=':itemname', replacements='A%', return_new_placeholders=False) >>> p1.placeholder ':itemid' >>> p2.replacements 'A%' >>> p2.return_new_placeholders False