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