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_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_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_mock_context_manager(transacted_postgresql_db, query, table, alias):
"""Tests patching using the context manager"""
with pgmock.mock(transacted_postgresql_db.connection) as mock:
mock.patch(pgmock.table(table, alias=alias), [('val1.1', ),
('val1.2', )], ['c1'])
res = transacted_postgresql_db.connection.execute(query)
assert list(res) == [('val1.1', ), ('val1.2', )]
assert len(mock.renderings) == 1
def test_table_patching_w_mocker(transacted_postgresql_db, pgmocker):
"""Tests patching a table while using the mocker returned by ``pgmock.mock``"""
pgmocker.patch(pgmock.table('test_table'), [('val1', 'val2'),
('val3', 'val4')],
['c1', 'c2'])
results = list(
transacted_postgresql_db.connection.execute(
'SELECT * from test_table'))
assert results == [('val1', 'val2'), ('val3', 'val4')]
def test_mock_context_manager_side_effect_exhausted(transacted_postgresql_db):
"""Tests patching using the context manager and exhausting a side effect"""
with pgmock.mock(transacted_postgresql_db.connection) as mock:
mock.patch(pgmock.table('t1', alias='a'), side_effect=[None])
transacted_postgresql_db.connection.execute(
'SELECT * from pg_cursors;')
with pytest.raises(pgmock.exceptions.SideEffectExhaustedError):
transacted_postgresql_db.connection.execute(
'SELECT * from pg_cursors;')
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_mock_context_manager_w_side_effects(transacted_postgresql_db):
"""Tests patching using the context manager and a side effect that ignores the first query"""
with pgmock.mock(transacted_postgresql_db.connection) as mock:
mock.patch(pgmock.table('t1', alias='a'),
side_effect=[
None,
pgmock.data(rows=[('val1.1', ), ('val1.2', )],
cols=['c1'])
])
transacted_postgresql_db.connection.execute(
'SELECT * from pg_cursors;')
res = transacted_postgresql_db.connection.execute(
'select a.c1 from t1 AS a')
assert list(res) == [('val1.1', ), ('val1.2', )]
assert len(mock.renderings) == 2
def test_mock_context_manager_w_sqlalchemy_select(transacted_postgresql_db,
replace_new_patch_aliases):
"""Tests patching using the context manager with a SQLAlchemy select statement"""
with pgmock.mock(
transacted_postgresql_db.connection,
replace_new_patch_aliases=replace_new_patch_aliases) as mock:
mock.patch(pgmock.table('schema.table_name'), [('val1.1', ),
('val1.2', )], ['name'])
table = sqla.Table('table_name',
sqla.MetaData(),
sqla.Column('name', sqla.String(50)),
schema='schema')
query = sqla.select([table.c.name])
res = transacted_postgresql_db.connection.execute(query)
assert list(res) == [('val1.1', ), ('val1.2', )]
assert len(mock.renderings) == 1
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_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', )]))
'select * from a'),
('select * from a;select * from b; select * from c', 1, 3,
'select * from b; select * from c'),
pytest.mark.xfail(
('select * from a;select * from b; select * from c', 1, 4, None),
raises=pgmock.exceptions.StatementParseError)])
def test_statement(query, start, end, expected):
"""Tests obtaining statements from a query"""
sql = pgmock.sql(query, pgmock.statement(start, end))
assert sql == expected
@pytest.mark.parametrize(
'sql, selectors, expected_sql',
[('select * from a; select * from b; select * from c',
[pgmock.statement(0), pgmock.table('a')], 'a'),
('select * from a; select * from b; select * from c',
[pgmock.statement(0).table('a')], 'a'),
('select * from a; select * from b; select * from c', [
pgmock.patch(pgmock.table('b'), [[1]], ['c1']),
pgmock.patch(pgmock.table('c'), [[1]], ['c1'])
], ('select * from a; select * from (VALUES (1)) AS b(c1);'
' select * from (VALUES (1)) AS c(c1)')),
('select * from a; select * from b; select * from c', [
pgmock.patch(pgmock.table('b'), [[1]], ['c1']).patch(
pgmock.table('c'), [[1]], ['c1'])
], ('select * from a; select * from (VALUES (1)) AS b(c1);'
' select * from (VALUES (1)) AS c(c1)')),
pytest.mark.xfail((
'select * from a; select * from b; select * from c',
[pgmock.patch(pgmock.table('b'), [[1]], ['c1']),