Using the SQLContainer
The SQLContainer class is a container for the SQL statements used by an application.
The database managers can optionally be initialized with a SQLContainer through the
keyword argument container. SQLContainer is the base class and provides some useful methods.
If you want to use a SQLContainer in your application you should subclass from
SQLContainer. The SQL statements are stored as class variables on the
SQLContainer. The previously used SQL statements may be stored
in a SQLContainer like this.
# sql_container.py
import pandemy
class SQLiteSQLContainer(pandemy.SQLContainer):
r""""A container of SQLite database statements."""
create_table_item = """
-- The available items in General Stores
CREATE TABLE IF NOT EXISTS Item (
ItemId INTEGER,
ItemName TEXT NOT NULL,
MemberOnly INTEGER NOT NULL,
Description TEXT,
CONSTRAINT ItemPk PRIMARY KEY (ItemId)
);
"""
insert_into_table_item = """
INSERT INTO TABLE Item (ItemId, ItemName, MemberOnly, Description)
VALUES (:itemid, :itemname, :memberonly, :description);
"""
select_all_items = """SELECT * FROM Item ORDER BY ItemId ASC;"""
db = pandemy.SQLiteDb(file='Runescape.db', container=SQLiteSQLContainer)
with db.engine.connect() as conn:
df = db.load_table(sql=db.container.select_all_items, conn=conn, index_col='ItemId')
print(df)
$ python sql_container.py
ItemName MemberOnly Description
ItemId
1 Pot 0 This pot is empty.
2 Jug 0 This jug is empty.
3 Shears 0 For shearing sheep.
4 Bucket 0 It's a wooden bucket.
5 Bowl 0 Useful for mixing things.
6 Amulet of glory 1 A very powerful dragonstone amulet.
7 Tinderbox 0 Useful for lighting a fire.
8 Chisel 0 Good for detailed Crafting.
9 Hammer 0 Good for hitting things.
10 Newcomer map 0 Issued to all new citizens of Gielinor.
11 Unstrung symbol 0 It needs a string so I can wear it.
12 Dragon Scimitar 1 A vicious, curved sword.
13 Amulet of glory 1 A very powerful dragonstone amulet.
14 Ranarr seed 1 A ranarr seed - plant in a herb patch.
15 Swordfish 0 I'd better be careful eating this!
16 Red dragonhide Body 1 Made from 100% real dragonhide.
Replace placeholders
The SQLContainer.replace_placeholders() method is used
to replace placeholders within a parametrized SQL statement. The purpose of this method is to handle the
case of a parametrized query using an IN clause with a variable number of arguments. The IN clause receives
a single placeholder initially which can later be replaced by the correct amount of placeholders once
this is determined. The method can of course be used to replace any placeholder within a SQL statement.
The method takes the SQL statement and a single or a sequence of Placeholder.
It returns the SQL statement with replaced placeholders and a dictionary called params.
Placeholder has 3 parameters:
placeholder: The placeholder to replace e.g.':myplaceholder'.replacements: A value or sequence of values to use for replacingplaceholder.
3. return_new_placeholders : A boolean, where True indicates that replace_placeholders()
should return new placeholders mapped to their respective replacements as a key value pair in the dictionary params.
The dictionary params can be passed to the params keyword argument of the execute()
or load_table() methods of a DatabaseManager.
The default value is True. A value of False causes the replaced placeholder to not appear in the returned params dictionary.
The use of replace_placeholders() and Placeholder
is best illustrated by some examples using the previously created database Runescape.db.
# replace_placeholder.py
import pandemy
class SQLiteSQLContainer(pandemy.SQLContainer):
r""""A container of SQLite database statements."""
# Retrieve items from table Item by their ItemId
get_items_by_id = """
SELECT ItemId, ItemName, MemberOnly, Description
FROM Item
WHERE ItemId IN (:itemid)
ORDER BY ItemId ASC;
"""
items = [1, 3, 5] # The items to retrieve from table Item
# The placeholder with the replacement values
placeholder = pandemy.Placeholder(placeholder=':itemid',
replacements=items,
return_new_placeholders=True)
db = pandemy.SQLiteDb(file='Runescape.db', container=SQLiteSQLContainer)
stmt, params = db.container.replace_placeholders(stmt=db.container.get_items_by_id,
placeholders=placeholder)
print(f'get_items_by_id after replacements:\n{stmt}\n')
print(f'The new placeholders with mapped replacements:\n{params}\n')
with db.engine.connect() as conn:
df = db.load_table(sql=stmt, conn=conn, params=params, index_col='ItemId')
print(f'The DataFrame from the parametrized query:\n{df}')
$ python replace_placeholder.py
get_items_by_id after replacements:
SELECT ItemId, ItemName, MemberOnly, Description
FROM Item
WHERE ItemId IN (:v0, :v1, :v2)
ORDER BY ItemId ASC;
The new placeholders with mapped replacements:
{'v0': 1, 'v1': 3, 'v2': 5}
The DataFrame from the parametrized query:
ItemName MemberOnly Description
ItemId
1 Pot 0 This pot is empty.
3 Shears 0 For shearing sheep.
5 Bowl 0 Useful for mixing things.
In this example the placeholder :itemid of the query get_items_by_id is replaced by
three placeholders: :v0, :v1 and :v2 (one for each of the values in the list items in the order they occur).
Since return_new_placeholders=True the returned dictionary params contains a mapping of the new placeholders to the
values in the list items. If return_new_placeholders=False then params would be an empty dictionary.
The updated version of the query get_items_by_id can then be executed with the parameters in params.
The next example shows how to replace multiple placeholders.
replace_multiple_placeholders.py
# replace_multiple_placeholders.py
import pandemy
class SQLiteSQLContainer(pandemy.SQLContainer):
r""""A container of SQLite database statements."""
get_items_by_id = """
SELECT ItemId, ItemName, MemberOnly, Description
FROM Item
WHERE
ItemId IN (:itemid) AND
MemberOnly = :memberonly AND
Description LIKE :description
ORDER BY :orderby;
"""
items = [10, 12, 13, 14, 16] # The items to retrieve from table Item
# The placeholders with the replacement values
placeholders = [
pandemy.Placeholder(placeholder=':itemid',
replacements=items,
return_new_placeholders=True),
pandemy.Placeholder(placeholder=':memberonly',
replacements=1,
return_new_placeholders=True),
pandemy.Placeholder(placeholder=':description',
replacements='A%',
return_new_placeholders=True),
pandemy.Placeholder(placeholder=':orderby',
replacements='ItemId DESC',
return_new_placeholders=False),
]
db = pandemy.SQLiteDb(file='Runescape.db', container=SQLiteSQLContainer)
stmt, params = db.container.replace_placeholders(stmt=db.container.get_items_by_id,
placeholders=placeholders)
print(f'get_items_by_id after replacements:\n{stmt}\n')
print(f'The new placeholders with mapped replacements:\n{params}\n')
with db.engine.connect() as conn:
df = db.load_table(sql=stmt, conn=conn, params=params, index_col='ItemId')
print(f'The DataFrame from the parametrized query:\n{df}')
$ python replace_multiple_placeholders.py
get_items_by_id after replacements:
SELECT ItemId, ItemName, MemberOnly, Description
FROM Item
WHERE
ItemId IN (:v0, :v1, :v2, :v3, :v4) AND
MemberOnly = :v5 AND
Description LIKE :v6
ORDER BY ItemId DESC;
The new placeholders with mapped replacements:
{'v0': 10, 'v1': 12, 'v2': 13, 'v3': 14, 'v4': 16, 'v5': 1, 'v6': 'A%'}
The DataFrame from the parametrized query:
ItemName MemberOnly Description
ItemId
14 Ranarr seed 1 A ranarr seed - plant in a herb patch.
13 Amulet of glory 1 A very powerful dragonstone amulet.
12 Dragon Scimitar 1 A vicious, curved sword.
Note
The replacement value for the :orderby placeholder is not part of the returned params
dictionary because return_new_placeholders=False for the last placeholder.
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.