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)