def test_5_pyformat_to_named_dollar_collapsed_percent(self):
"""
Test converting from::
SELECT 5 %% %(value)s
to::
SELECT 5 % $value
"""
# Create instance.
query = sqlparams.SQLParams('pyformat',
'named_dollar',
escape_char=True)
# Source SQL and params.
src_sql = """
SELECT 5 %% %(value)s;
"""
src_params = {'value': 2}
# Desired SQL and params.
dest_sql = """
SELECT 5 % $value;
"""
dest_params = copy.deepcopy(src_params)
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_3_multiple(self):
"""
Test converting a numeric parameter where it occurs multiple times.
"""
# Create instance.
query = sqlparams.SQLParams('numeric', 'named')
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE id = :1 OR name = :2 OR altid = :1 OR altname = :2;
"""
id, name = 3, "Kili"
seq_params = [id, name]
int_params = {1: id, 2: name}
str_params = {'1': id, '2': name}
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE id = :_1 OR name = :_2 OR altid = :_1 OR altname = :_2;
"""
dest_params = {'_1': id, '_2': name}
for src_params, src in zip([seq_params, int_params, str_params],
['seq', 'int', 'str']):
with self.subTest(src=src):
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_4_numeric_escape_char_disabled(self):
"""
Test disabling escaping of a numeric parameter.
"""
# Create instance.
query = sqlparams.SQLParams('numeric', 'named', escape_char=False)
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE name = :1 AND tag IN (':Y2941', '::2941');
"""
name = "Bilbo"
src_params = [name]
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE name = :_1 AND tag IN (':Y2941', '::2941');
"""
dest_params = {'_1': name}
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_04_formatmany_bytes(self):
"""
Test byte strings when formatting many for a query.
"""
# Create instance.
query = sqlparams.SQLParams('named', 'qmark')
# Source SQL and params.
src_sql = b"""
SELECT *
FROM users
WHERE id = :id;
"""
src_params = [{'id': 10}, {'id': 11}, {'id': 12}]
# Desired SQL and params.
dest_sql = b"""
SELECT *
FROM users
WHERE id = ?;
"""
dest_params = [[10], [11], [12]]
# Format SQL with params.
sql, many_params = query.formatmany(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(many_params, dest_params)
def test_1_numeric_to_numeric_dollar_many(self):
"""
Test converting from::
... WHERE name = :1
to::
... WHERE name = $1
"""
# Create instance.
query = sqlparams.SQLParams('numeric', 'numeric_dollar')
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE id = :2 OR name = :1;
"""
base_params = [
{
'id': 11,
'name': "Bofur"
},
{
'id': 1,
'name': "Dwalin"
},
{
'id': 5,
'name': "Dori"
},
]
seq_params = [[__row['name'], __row['id']] for __row in base_params]
int_params = [{
1: __row['name'],
2: __row['id']
} for __row in base_params]
str_params = [{
'1': __row['name'],
'2': __row['id']
} for __row in base_params]
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE id = $1 OR name = $2;
"""
dest_params = [[__row['id'], __row['name']] for __row in base_params]
for src_params, src in zip([seq_params, int_params, str_params],
['seq', 'int', 'str']):
with self.subTest(src=src):
# Format SQL with params.
sql, many_params = query.formatmany(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(many_params, dest_params)
def test_00_named_to_format(self):
"""
Tests converting from::
... WHERE name = :name
to::
... WHERE name = %s
"""
# Create instance.
query = sqlparams.SQLParams('named', 'format')
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE id = :id OR name = :name;
"""
src_params = {'id': 4, 'name': 'Fili'}
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE id = %s OR name = %s;
"""
dest_params = [4, 'Fili']
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_00_named_to_qmark(self):
"""
Tests converting from::
... WHERE name = :name
to::
... WHERE name = ?
"""
# Create instance.
query = sqlparams.SQLParams('named', 'qmark')
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE id = :id OR name = :name;
"""
src_params = {'id': 1, 'name': "Dwalin"}
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE id = ? OR name = ?;
"""
dest_params = [1, "Dwalin"]
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_2_expand_tuples_many(self):
"""
Test expanding many tuples.
"""
# Create instance.
query = sqlparams.SQLParams('named', 'qmark', expand_tuples=True)
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE race = :race AND name IN :names;
"""
src_params = [
{'names': ("Dwalin", "Balin"), 'race': "Dwarf"},
{'names': ("Kili", "Fili"), 'race': "Dwarf"},
{'names': ("Oin", "Gloin"), 'race': "Dwarf"},
]
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE race = ? AND name IN (?,?);
"""
dest_params = [[__row['race']] + list(__row['names']) for __row in src_params]
# Format SQL with params.
sql, many_params = query.formatmany(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(many_params, dest_params)
def test_3_multiple(self):
"""
Test converting a named parameter where it occurs multiple times.
"""
# Create instance.
query = sqlparams.SQLParams('named', 'qmark')
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE id = :id OR name = :name OR altid = :id OR altname = :name;
"""
src_params = {'id': 4, 'name': "Fili"}
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE id = ? OR name = ? OR altid = ? OR altname = ?;
"""
dest_params = [src_params['id'], src_params['name'], src_params['id'], src_params['name']]
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_2_expand_tuples_default(self):
"""
Test the default behavior for expanding tuples. An ordinal out-style
should be enabled by default.
"""
# Create instance.
query = sqlparams.SQLParams('named', 'qmark')
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE race = :race AND name IN :names;
"""
src_params = {'names': ("Dwalin", "Balin"), 'race': "Dwarf"}
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE race = ? AND name IN (?,?);
"""
dest_params = [src_params['race']] + list(src_params['names'])
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_2_expand_tuples_disabled(self):
"""
Test ignoring tuples.
"""
# Create instance.
query = sqlparams.SQLParams('named', 'qmark', expand_tuples=False)
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE race = :race AND name IN :names;
"""
src_params = {'names': ("Dwalin", "Balin"), 'race': "Dwarf"}
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE race = ? AND name IN ?;
"""
dest_params = [src_params['race'], src_params['names']]
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_1_pyformat_to_format(self):
"""
Test converting from::
... WHERE name = %(name)s
to::
... WHERE name = %s
"""
# Create instance.
query = sqlparams.SQLParams('pyformat', 'format')
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE id = %(id)s OR name = %(name)s;
"""
src_params = {'id': 1, 'name': "Dwalin"}
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE id = %s OR name = %s;
"""
dest_params = [src_params['id'], src_params['name']]
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_4_postgresql_timestamp(self):
"""
Test a PostgreSQL query containing an inline timestamp.
"""
# Create instance.
query = sqlparams.SQLParams('named', 'qmark')
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE time = '12:00:00'
"""
src_params = {}
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE time = '12:00:00'
"""
dest_params = []
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_1_named_dollar_to_pyformat(self):
"""
Test converting from::
... WHERE name = :name
to::
... WHERE name = %(name)s
"""
# Create instance.
query = sqlparams.SQLParams('named_dollar', 'pyformat')
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE id = $id OR name = $name;
"""
src_params = {'id': 2, 'name': "Balin"}
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE id = %(id)s OR name = %(name)s;
"""
dest_params = copy.deepcopy(src_params)
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_2_expand_tuples_many_fail_type(self):
"""
Test many tuples with wrong types.
"""
# Create instance.
query = sqlparams.SQLParams('numeric', 'qmark', expand_tuples=True)
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE race = :1 AND name IN :2;
"""
base_params = [
{'names': ("Dori", "Ori", "Nori"), 'race': "Dwarf"},
{'names': "Thorin", 'race': "Dwarf"},
]
seq_params = [[__row['race'], __row['names']] for __row in base_params]
int_params = [{1: __row['race'], 2: __row['names']} for __row in base_params]
str_params = [{'1': __row['race'], '2': __row['names']} for __row in base_params]
for src_params, src in zip([seq_params, int_params, str_params], ['seq', 'int', 'str']):
with self.subTest(src=src):
# Format SQL with params.
with self.assertRaisesRegex(TypeError, "was expected to be a tuple.$"):
query.formatmany(src_sql, src_params)
def test_3_multiple_many(self):
"""
Test converting a named parameter where it occurs multiple times.
"""
# Create instance.
query = sqlparams.SQLParams('named', 'qmark')
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE id = :id OR name = :name OR altid = :id OR altname = :name;
"""
src_params = [
{'id': 12, 'name': "Bombur"},
{'id': 3, 'name': "Kili"},
{'id': 6, 'name': "Nori"},
]
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE id = ? OR name = ? OR altid = ? OR altname = ?;
"""
dest_params = [[__row['id'], __row['name'], __row['id'], __row['name']] for __row in src_params]
# Format SQL with params.
sql, many_params = query.formatmany(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(many_params, dest_params)
def test_5_numeric_to_qmark_unescaped_percent(self):
"""
Test converting from::
SELECT 5 % :1
to::
SELECT 5 % ?
"""
# Create instance.
query = sqlparams.SQLParams('numeric', 'qmark')
# Source SQL and params.
src_sql = """
SELECT 5 % :1;
"""
value = 2
src_params = [value]
# Desired SQL and params.
dest_sql = """
SELECT 5 % ?;
"""
dest_params = [value]
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_4_named_escape_char(self):
"""
Test escaping a named parameter.
"""
# Create instance.
query = sqlparams.SQLParams('named', 'qmark', escape_char=True)
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE name = :name AND tag IN ('::Y2941', '::2941');
"""
name = "Bilbo"
src_params = {'name': name}
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE name = ? AND tag IN (':Y2941', ':2941');
"""
dest_params = [name]
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_01_tuple(self):
"""
Tests converting with tuples from::
... WHERE name IN :names
to::
... WHERE name IN (?, ...)
"""
# Create instance.
query = sqlparams.SQLParams('named', 'qmark')
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE id IN :ids OR name IN :names;
"""
src_params = {'ids': (5, 6, 7), 'names': ("Dori", "Nori", "Ori")}
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE id IN (?,?,?) OR name IN (?,?,?);
"""
dest_params = [5, 6, 7, "Dori", "Nori", "Ori"]
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_4_pyformat_escape_char_disabled(self):
"""
Test disabling escaping of a pyformat parameter.
"""
# Create instance.
query = sqlparams.SQLParams('pyformat', 'qmark', escape_char=False)
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE name = %(name)s AND tag IN ('%%(Y2941)s', '%(2941)s');
"""
name = "Bilbo"
src_params = {'name': name}
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE name = ? AND tag IN ('%%(Y2941)s', '%(2941)s');
"""
dest_params = [name]
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_04_format_bytes(self):
"""
Test byte strings when formatting a query.
"""
# Create instance.
query = sqlparams.SQLParams('named', 'qmark')
# Source SQL and params.
src_sql = b"""
SELECT *
FROM users
WHERE id = :id;
"""
src_params = {'id': 1}
# Desired SQL and params.
dest_sql = b"""
SELECT *
FROM users
WHERE id = ?;
"""
dest_params = [1]
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_5_named_to_format_escaped_percent(self):
"""
Test converting from::
SELECT 5 % :value
to::
SELECT 5 %% %s
"""
# Create instance.
query = sqlparams.SQLParams('named', 'format')
# Source SQL and params.
src_sql = """
SELECT 5 % :value;
"""
value = 2
src_params = {'value': value}
# Desired SQL and params.
dest_sql = """
SELECT 5 %% %s;
"""
dest_params = [value]
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_00_pyformat_to_qmark(self):
"""
Tests converting from::
... WHERE name = %(name)s
to::
... WHERE name = ?
"""
# Create instance.
query = sqlparams.SQLParams('pyformat', 'qmark')
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE id = %(id)s OR name = %(name)s;
"""
src_params = {'id': 3, 'name': 'Kili'}
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE id = ? OR name = ?;
"""
dest_params = [3, 'Kili']
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_5_pyformat_to_qmark_collapsed_percent(self):
"""
Test converting from::
SELECT 5 %% %(pyformat)s
to::
SELECT 5 % ?
"""
# Create instance.
query = sqlparams.SQLParams('pyformat', 'qmark', escape_char=True)
# Source SQL and params.
src_sql = """
SELECT 5 %% %(value)s;
"""
value = 2
src_params = {'value': value}
# Desired SQL and params.
dest_sql = """
SELECT 5 % ?;
"""
dest_params = [value]
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_2_expand_tuples_disabled(self):
"""
Test ignoring tuples.
"""
# Create instance.
query = sqlparams.SQLParams('numeric', 'named', expand_tuples=False)
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE race = :1 AND name IN :2;
"""
names, race = ("Kili", "Fili"), "Dwarf"
seq_params = [race, names]
int_params = {1: race, 2: names}
str_params = {'1': race, '2': names}
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE race = :_1 AND name IN :_2;
"""
dest_params = {'_1': race, '_2': names}
for src_params, src in zip([seq_params, int_params, str_params],
['seq', 'int', 'str']):
with self.subTest(src=src):
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_1_named_dollar_to_qmark(self):
"""
Test converting from::
... WHERE name = $name
to::
... WHERE name = ?
"""
# Create instance.
query = sqlparams.SQLParams('named_dollar', 'qmark')
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE name = $name OR id = $id;
"""
src_params = {'id': 5, 'name': "Dori"}
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE name = ? OR id = ?;
"""
dest_params = [src_params['name'], src_params['id']]
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_4_numeric_dollar_escape_char(self):
"""
Test escaping a numeric dollar parameter.
"""
# Create instance.
query = sqlparams.SQLParams('numeric_dollar',
'named',
escape_char=True)
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE name = $1 AND tag IN ('$$Y2941', '$$2941');
"""
name = "Bilbo"
src_params = [name]
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE name = :_1 AND tag IN ('$Y2941', '$2941');
"""
dest_params = {'_1': name}
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_2_expand_tuples_default(self):
"""
Test the default behavior for expanding tuples. An ordinal out-style
should be enabled by default.
"""
# Create instance.
query = sqlparams.SQLParams('numeric', 'qmark')
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE race = :1 AND name IN :2;
"""
names, race = ("Kili", "Fili"), "Dwarf"
seq_params = [race, names]
int_params = {1: race, 2: names}
str_params = {'1': race, '2': names}
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE race = ? AND name IN (?,?);
"""
dest_params = [race] + list(names)
for src_params, src in zip([seq_params, int_params, str_params], ['seq', 'int', 'str']):
with self.subTest(src=src):
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_5_numeric_dollar_to_pyformat_escaped_percent(self):
"""
Test converting from::
SELECT 5 % $1
to::
SELECT 5 %% %(_1)s
"""
# Create instance.
query = sqlparams.SQLParams('numeric_dollar', 'pyformat')
# Source SQL and params.
src_sql = """
SELECT 5 % $1;
"""
value = 2
src_params = [value]
# Desired SQL and params.
dest_sql = """
SELECT 5 %% %(_1)s;
"""
dest_params = {'_1': value}
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
def test_4_pyformat_escape_char(self):
"""
Test escaping a pyformat parameter.
"""
# Create instance.
query = sqlparams.SQLParams('pyformat', 'named', escape_char=True)
# Source SQL and params.
src_sql = """
SELECT *
FROM users
WHERE name = %(name)s AND tag IN ('%%(Y2941)s', '%%(2941)s');
"""
name = "Bilbo"
src_params = {'name': name}
# Desired SQL and params.
dest_sql = """
SELECT *
FROM users
WHERE name = :name AND tag IN ('%(Y2941)s', '%(2941)s');
"""
dest_params = copy.deepcopy(src_params)
# Format SQL with params.
sql, params = query.format(src_sql, src_params)
# Make sure desired SQL and parameters are created.
self.assertEqual(sql, dest_sql)
self.assertEqual(params, dest_params)
