def test_patch_subquery_from_file(transacted_postgresql_db, tmpdir):
"""Tests reading a subquery from a file and testing a patched version of it"""
# Create the example file
file_name = tmpdir.join('file.sql')
file_name.write('SELECT sub.c1, sub.c2 FROM (SELECT * FROM test_table) sub;')
# Read the subquery 'sub' from the file
subquery = pgmock.sql_file(str(file_name), pgmock.subquery('sub'))
assert subquery == 'SELECT * FROM test_table'
# Patch the table of the subquery and verify it returns the proper results
patched = pgmock.sql(subquery, pgmock.patch(
pgmock.table('test_table'),
[('v1', 'v2'), ('v3', 'v4')],
['c1', 'c2']
))
assert patched == "SELECT * FROM (VALUES ('v1','v2'),('v3','v4')) AS test_table(c1,c2)"
# Patches can also be applied with list of dictionaries, filling in only what's needed.
# Column names must still be provided. null values will be filled for all missing columns
patched = pgmock.sql(subquery, pgmock.patch(
pgmock.table('test_table'),
[{'c1': 'v1'}, {'c2': 'v4'}],
['c1', 'c2']
))
assert patched == "SELECT * FROM (VALUES ('v1',null),(null,'v4')) AS test_table(c1,c2)"
results = list(transacted_postgresql_db.connection.execute(patched))
assert results == [('v1', None), (None, 'v4')]
def test_table_patch(transacted_postgresql_db, query, table, alias, values):
"""Tests patching tables when selecting"""
patch = pgmock.patch(pgmock.table(table, alias=alias), values, ['c1'])
sql = pgmock.sql(query, patch)
res = transacted_postgresql_db.connection.execute(sql)
assert list(res) == values
def test_table_patch_w_type_hints(rows, columns, expected,
transacted_postgresql_db):
"""Tests patching tables when selecting"""
patch = pgmock.patch(pgmock.table('t'), rows, columns)
sql = pgmock.sql('select * from t', patch)
res = transacted_postgresql_db.connection.execute(sql)
assert list(res) == expected
def test_cte_patch(transacted_postgresql_db, query, alias):
"""Tests patching a CTE"""
patch = pgmock.patch(pgmock.cte(alias), [('val1.1', 'val2.1'),
('val1.2', 'val2.2')],
['c1', 'c2'])
sql = pgmock.sql(query, patch)
res = transacted_postgresql_db.connection.execute(sql)
assert list(res) == [('val1.1', 'val2.1'), ('val1.2', 'val2.2')]
def test_select_schema_table_col_from_table_patch(transacted_postgresql_db):
"""Tests the case of selecting a schema.table.column and patching it"""
sql = 'SELECT schema.table_name.col1 from schema.table_name'
patch = pgmock.patch(pgmock.table('schema.table_name'), [('val1.1', ),
('val1.2', ),
('val1.3', )],
['col1'])
sql = pgmock.sql(sql, patch)
res = transacted_postgresql_db.connection.execute(sql)
assert list(res) == [('val1.1', ), ('val1.2', ), ('val1.3', )]
def test_multi_table_patch(transacted_postgresql_db, query, table, table_alias,
join, join_alias):
"""Tests patching tables when selecting and joining"""
cols = ['c1']
patch = pgmock.patch(pgmock.table(table, alias=table_alias),
[('val1.1', ), ('val1.2', )], cols)
patch = patch.patch(pgmock.table(join, alias=join_alias), [('val1.1', ),
('val1.2', ),
('val1.3', )],
cols)
sql = pgmock.sql(query, patch)
res = transacted_postgresql_db.connection.execute(sql)
assert list(res) == [('val1.1', ), ('val1.2', )]
def test_patched_types_serialized_properly(transacted_postgresql_db):
"""Ensures that different python types are serialized into VALUES properly"""
rows = [
(1, 1, "wes's string", dt.datetime(2012, 1, 2, 12),
dt.datetime(2012, 1, 2, tzinfo=dt.timezone.utc), dt.time(12, 1, 1),
dt.date(2012, 1, 2), {
'json': 'field'
}, True),
(2, 2.5, "other string", dt.datetime(2012, 1, 2, 2),
dt.datetime(2012, 1, 1, tzinfo=dt.timezone.utc), dt.time(12, 1, 2),
dt.date(2012, 1, 3), {
'json': 'field'
}, False),
]
cols = [
'int', 'float', 'str', 'timestamp', 'timestamptz', 'time', 'date',
'json', 'bool'
]
sql = pgmock.sql('select * from t1',
pgmock.patch(pgmock.table('t1'), rows, cols))
res = list(transacted_postgresql_db.connection.execute(sql))
assert res == rows
def test_insert_into(query, table, expected):
"""Tests getting an "insert into" statement from a query"""
sql = pgmock.sql(query, pgmock.insert_into(table))
assert sql == expected
def test_patch_alias_wo_columns():
"""Tests that an error is raised when trying to patch an expression that has an alias
without providing columns"""
with pytest.raises(pgmock.exceptions.ColumnsNeededForPatchError):
pgmock.sql('select t1.c1 from t1',
pgmock.table('t1').patch(rows=[('val', )]))
def test_statement(query, start, end, expected):
"""Tests obtaining statements from a query"""
sql = pgmock.sql(query, pgmock.statement(start, end))
assert sql == expected
def test_body(query, selectors, expected):
"""Tests getting the patchable body from a selector"""
sql = pgmock.sql(query, *selectors)
assert sql == expected
def test_unpatchable():
"""Tests that an error is raised when something is not patchable"""
with pytest.raises(pgmock.exceptions.UnpatchableError):
pgmock.sql('create table t1',
pgmock.statement(0).patch(rows=[], cols=['c1']))
def test_create_table_as_patched(query, table):
"""Tests patching an "create table as" statement from a query"""
sql = pgmock.sql(
query,
pgmock.create_table_as(table).patch(rows=[(1, ), (2, )], cols=['a']))
assert sql == 'create table a as SELECT * FROM (VALUES (1),(2)) AS pgmock(a)'
def test_cte(query, alias, expected_select):
"""Tests getting a CTE from an SQL statement"""
sql = pgmock.sql(query, pgmock.cte(alias))
assert sql == expected_select
def test_subquery(query, alias, expected_select):
"""Tests getting a subquery from an SQL statement"""
sql = pgmock.sql(query, pgmock.subquery(alias))
assert sql == expected_select
def test_nested_selection(transacted_postgresql_db, sql, selector):
"""Verifies that nested selections result in an error"""
with pytest.raises(pgmock.exceptions.NestedMatchError):
pgmock.sql(sql, selector)
def test_multiple_match_patching(transacted_postgresql_db, sql, selector,
expected_sql):
"""Tests patching occurences of multiple matches"""
sql = pgmock.sql(sql, selector)
assert sql == expected_sql
def test_chaining(sql, selectors, expected_sql):
"""
Ensures that selectors can either be chained or passed in separately
(which will chain them underneath the hood)
"""
assert pgmock.sql(sql, *selectors) == expected_sql
def test_insert_into_patched(query, table):
"""Tests patching an "insert into" statement from a query"""
sql = pgmock.sql(
query,
pgmock.insert_into(table).patch(rows=[(1, ), (2, )], cols=['a']))
assert sql == 'insert into a VALUES (1),(2)'
def test_create_table_as(query, table, expected):
"""Tests getting an "create table as" statement from a query"""
sql = pgmock.sql(query, pgmock.create_table_as(table))
assert sql == expected
def test_safe_mode(sql, selector, safe_mode, expected):
"""Tests various selections with safe mode on and off"""
sql = pgmock.sql(sql, selector, safe_mode=safe_mode)
assert sql == expected
