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
('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']),
pgmock.table('c')],
None,
),
raises=pgmock.exceptions.SelectorChainingError)])