Tutorial¶
This tutorial is created directly from an ipython notebook. If you’d like to interactively run this tutorial, do the following:
# Go to your pgmock directory with the cloned code
cd pgmock
make setup
jupyter notebook Tutorial.ipynb
# Follow the instructions to open the notebook in your browser
The setup for this ipython notebook is below and includes the creation of the testing database and required imports.
import testing.postgresql
import sqlalchemy
import pgmock
import pgmock.exceptions
test_db = testing.postgresql.Postgresql()
test_engine = sqlalchemy.create_engine(test_db.url())
Terminology and Key Concepts¶
It’s useful to understand some commonly-used terminology and key
concepts before going into the pgmock
tutorial.
Selectors - pgmock
selectors are objects used to obtain
portions of SQL within a query. Selectors can represent subqueries,
tables, select statements, and other types of SQL expressions. All
selectors in pgmock
are chainable, meaning they can be called after
one another to refine a selection.
Patching - pgmock
patching is concerned with converting SQL
select expressions or tables into postgres VALUES
expressions. For
example, this statement:
SELECT c1 from test_table
could have test_table
patched to be:
SELECT c1 from (VALUES ('hi!'),('hello!') AS test_table(c1)
Patching can also take place on joins, subqueries, INSERT INTO
expressions, and other SQL expressions that allow postgres VALUES
.
Rendering - Whenever SQL is obtained from a query or modified with a patch, it is rendered.
Obtaining Expressions in a Query¶
Obtaining specific portions of a query can be useful when wanting to execute smaller parts of your SQL or for (later in the tutorial) patching out portions of your SQL in tests.
This part of the tutorial demonstrates obtaining expressions inside a
query using the pgmock.sql
function. pgmock.sql
takes a SQL
string and a selector as input. The expression in the query referenced
by the selector is then rendered and returned as a string. Uses of
different selectors to obtain expressions is shown in the following.
Obtaining Statements¶
The pgmock.statement
selector can be used to render ranges of
statements in a query as shown below.
query = '''SELECT * from table1;
SELECT * from table2;
SELECT * from table3
'''
# Obtain the first statement
print(pgmock.sql(query, pgmock.statement(0)))
SELECT * from table1
# Obtain the first three statements using a range of 0 - 3 (3 is the exclusive ending index)
print(pgmock.sql(query, pgmock.statement(0, 3)))
SELECT * from table1;
SELECT * from table2;
SELECT * from table3
# Going out of range will throw an exception
try:
pgmock.sql(query, pgmock.statement(4))
except pgmock.exceptions.StatementParseError as exc:
print(exc)
Found 3 statements. Range of [4:5] is out of bounds. The following SQL was used:
SELECT * from table1;
SELECT * from table2;
SELECT * from table3
View the docs for this exception at https://pgmock.readthedocs.io/en/latest/exceptions.html for more information.
Note
Rendering statements splits the SQL with the semicolon character. If the semicolon appears in any comments or string literals, this can interfere with obtaining statements. Use safe_mode=True
to pgmock.sql
in order to fix this issue if it happens. This comes at a performance cost, and more details can be read at pgmock.config.set_safe_mode
.
Obtaining Subqueries¶
The pgmock.subquery
selector can be used to render subqueries in SQL
as shown below.
query = 'SELECT sub.c1, sub.c2 FROM (SELECT * FROM test_table) sub;'
# Obtain the subquery named "sub"
print(pgmock.sql(query, pgmock.subquery('sub')))
SELECT * FROM test_table
# An exception will be raised if the subquery alias cannot be found
try:
pgmock.sql(query, pgmock.subquery('bad'))
except pgmock.exceptions.NoMatchError as exc:
print(exc)
No subquery found for alias "bad". The following SQL was used:
SELECT sub.c1, sub.c2 FROM (SELECT * FROM test_table) sub;
View the docs for this exception at https://pgmock.readthedocs.io/en/latest/exceptions.html for more information.
# pgmock does not handle the case when the same subquery alias is used twice or nested
query = 'SELECT sub.c1, sub.c2 FROM (SELECT * FROM (SELECT * FROM test_table) sub) sub;'
try:
pgmock.sql(query, pgmock.subquery('sub'))
except pgmock.exceptions.MultipleMatchError as exc:
print(exc)
Nested matches were found in your selection. The following multiple matches of SQL were used:
SELECT * FROM test_table
---
SELECT * FROM (SELECT * FROM test_table) sub
View the docs for this exception at https://pgmock.readthedocs.io/en/latest/exceptions.html for more information.
Note
Almost all pgmock
selectors can only render exactly one match like the subquery selector above. These cases will not be illustrated for later examples. Note that there is a distinction between “rendering” selectors with pgmock.sql
like above and patching them with pgmock.patch
. pgmock.patch
supports patching multiple occurences in a selection
Obtaining Insert Into Expressions¶
The pgmock.insert_into
selector can be used to render
INSERT INTO
expressions in SQL as shown below.
query = '''INSERT INTO table_a
SELECT * FROM other_table;
INSERT INTO table_b
SELECT * FROM other_table'''
# The insert_into selector takes the table name that is inserted into
print(pgmock.sql(query, pgmock.insert_into('table_a')))
INSERT INTO table_a
SELECT * FROM other_table
In order to obtain the body of the expression, the body
selector can
be chained to the insert_into
selector.
print(pgmock.sql(query, pgmock.insert_into('table_a').body()))
SELECT * FROM other_table
Note
All selectors can be chained like the above example where it makes sense. For example, one could obtain the INSERT INTO
expression of the first statement with pgmock.statement(0).insert_into('table_a')
Along with that, multiple selectors can also be provided to pgmock.sql
or pgmock.sql_file
, which in turn will chain them underneath the hood. For example, doing:
pgmock.sql(query, pgmock.insert_into('table_a'), pgmock.body())
is equivalent to:
pgmock.sql(query, pgmock.insert_into('table_a').body())
It is up to the user to pick which style they prefer. pgmock
suggests using multiple arguments when applying multiple pgmock.patch
selectors and using chaining syntax for all other cases.
Obtaining Create Table As Expressions¶
The pgmock.create_table_as
selector can be used to render
CREATE TABLE AS
expressions in SQL as shown below. It’s similar to
pgmock.insert_into
.
query = '''CREATE TABLE table_a AS (
SELECT * FROM other_table
);
CREATE TABLE table_b AS SELECT * FROM other_table'''
# The insert_into selector takes the table name that is inserted into
print(pgmock.sql(query, pgmock.create_table_as('table_a')))
CREATE TABLE table_a AS (
SELECT * FROM other_table
)
In order to obtain the body of the CREATE TABLE AS
expression, the
body
selector can be chained to the create_table_as
selector.
print(pgmock.sql(query, pgmock.create_table_as('table_a').body()))
(
SELECT * FROM other_table
)
Obtaining Common Table Expressions (CTEs)¶
The pgmock.cte
selector can be used to render common table
expressions (CTEs) in SQL as shown below. It’s similar to
pgmock.subquery
.
query = '''
WITH cte1 AS (
SELECT * FROM table1
), cte2 AS (
SELECT * FROM table2
)
'''
# Obtain the CTE aliased "cte1"
print(pgmock.sql(query, pgmock.cte('cte1')))
SELECT * FROM table1
# Obtain the "cte2" CTE
print(pgmock.sql(query, pgmock.cte('cte2')))
SELECT * FROM table2
Patching Expressions in a Query¶
As mentioned before, patching parts of a query will transform the relevant expression into Postgres VALUES. Why is this useful?
- When using
VALUES
lists, there is no need to create database tables and data before executing the query - Testing queries will run much faster in automated tests since there is no overhead of database setup and teardown
- Only data that is relevant to the test can be patched, resulting in
smaller and more readable tests.
pgmock
allows other useless columns to be filled in with nulls by default if desired
Below are some illustrations of patching queries and running some assertions on those queries. This section uses the test engine that we created at the beginning of the tutorial.
Note
In an automated pytest test case, we’d use the fixtures from pytest-pgsql when testing our queries
Patching Tables and Joins on Tables¶
Tables, whether those tables are being selected or joined, can be
patched with pgmock
by using the pgmock.table
selector. Some
examples of patching tables and joins are below.
# Create a query and filter a column
query = "SELECT c2 FROM my_table WHERE c1 = 'value'"
# Create a patch for the table. The patch takes a selector,
# rows (a list of lists for each column or a list of dictionaries keyed on column),
# and column names
patch = pgmock.patch(pgmock.table('my_table'),
[('dummy_data', 'data'), ('value', 'hello'), ('value', 'hi')],
['c1', 'c2'])
# Render the patched SQL so that we can execute it
sql = pgmock.sql(query, patch)
print(sql)
SELECT c2 FROM (VALUES ('dummy_data','data'),('value','hello'),('value','hi')) AS my_table(c1,c2) WHERE c1 = 'value'
# Execute the SQL and verify that filtering happened correctly
results = list(test_engine.execute(sql))
assert results == [('hello',), ('hi',)]
Note
pgmock.patch
accepts lists of dictionaries as rows of input as well. For example,
patch = pgmock.patch(pgmock.table('my_table'),
[{'c1': 'dummy_data', 'c2': 'data'}, {'c1': 'value', 'c2': 'hello'}, {'c1': 'value', 'c2': 'hi'}],
['c1', 'c2'])
is equivalent to the example from above.
Using this format allows for only specifying values for columns that matter. All missing columns will be filled with null values. Both formats of patching will be used throughout the rest of the tutorial.
Similar to selectors, patches are also chainable or can be provided as
multiple arguments to pgmock.sql
or pgmock.sql_file
. This is
useful for the case of patching multiple expressions in a query. For
example, we can patch a table and a join on another table like so.
# Create a query with a join
query = 'SELECT one.c1 FROM t1 one JOIN t2 two ON one.c1 = two.c1'
# When making the patches, keep in mind these tables have aliases and
# the alias must also be provided when obtaining the table
t1_patch = pgmock.patch(
pgmock.table('t1', alias='one'),
[('val1.1',), ('val1.2',)],
['c1']
)
t2_patch = pgmock.patch(
pgmock.table('t2', alias='two'),
[('val1.1',), ('val1.2',), ('val1.3',)],
['c1']
)
# Render the SQL that has both tables patched
sql = pgmock.sql(query, t1_patch, t2_patch)
print(sql)
SELECT one.c1 FROM (VALUES ('val1.1'),('val1.2')) AS one(c1) JOIN (VALUES ('val1.1'),('val1.2'),('val1.3')) AS two(c1) ON one.c1 = two.c1
# Execute the SQL and verify that the join happened correctly
results = list(test_engine.execute(sql))
assert results == [('val1.1',), ('val1.2',)]
Patching Multiple Occurrences of Tables¶
If a table appears multiple times in your SQL, it will be patched in all occurrences by default. This holds true for any selector. If you want to only patch a specific occurrence or range of tables, use list syntax. For example:
# Create a query and filter a column
query = "SELECT c2 FROM my_table; SELECT c3 from my_table"
# Patch both occurrences of the table
patch = pgmock.patch(pgmock.table('my_table'),
[('dummy_data', 'data'), ('value', 'hello'), ('value', 'hi')],
['c2', 'c3'])
# Render the patched SQL so that we can execute it
sql = pgmock.sql(query, patch)
print(sql)
# Use list syntax to only patch the second occurrence of the table
patch = pgmock.patch(pgmock.table('my_table')[1],
[('dummy_data', 'data'), ('value', 'hello'), ('value', 'hi')],
['c2', 'c3'])
print(pgmock.sql(query, patch))
SELECT c2 FROM (VALUES ('dummy_data','data'),('value','hello'),('value','hi')) AS my_table(c2,c3); SELECT c3 from (VALUES ('dummy_data','data'),('value','hello'),('value','hi')) AS my_table(c2,c3)
SELECT c2 FROM my_table; SELECT c3 from (VALUES ('dummy_data','data'),('value','hello'),('value','hi')) AS my_table(c2,c3)
Patching Subqueries¶
Patching subqueries (and almost all other expressions) works in the same way as patching tables. Create a selector you want to patch and provide the data to be patched.
# Create a query with a subquery
query = "SELECT sub.c1, sub.c2 FROM (SELECT * FROM test_table) sub;"
# Create a patch for the subquery. Similar to patching tables, provide a subquery selector and the data for the subquery
patch = pgmock.patch(pgmock.subquery('sub'),
[('val1', 'val2'), ('val3', 'val4')],
['c1', 'c2'])
# Render the patched SQL so that we can execute it
sql = pgmock.sql(query, patch)
print(sql)
SELECT sub.c1, sub.c2 FROM (VALUES ('val1','val2'),('val3','val4')) AS sub(c1,c2);
# Execute the SQL and verify that the subquery was patched properly
results = list(test_engine.execute(sql))
assert results == [('val1', 'val2'), ('val3', 'val4')]
Patching CTEs, Create Table As, Insert Into, and Other Expressions¶
pgmock
can patch almost every selector available in the library,
such as pgmock.insert_into
, pgmock.create_table_as
, and
pgmock.cte
. Patching these expressions result in different types of
patches depending on what is being patched.
For example, patching an INSERT INTO
statement will result in
replacing the body of the INSERT INTO
with a VALUES
list that
has no alias (Postgres doesn’t support the syntax of
INSERT INTO table (VALUES ..) AS ...
).
When patching CREATE TABLE AS
or a CTE, the patch will insert a
SELECT * FROM (VALUES ...) AS ...
. Doing this syntax allows column
names of the patch to be preserved and gets around the restriction of
not being able to do CREATE TABLE AS (VALUES ...) AS ...
.
Note
Keep in mind that when patching statements when the table structure is defined, such as CREATE TABLE t(col1, col2) AS
or WITH cte_name(col1, col2) AS
, the columns provided to the patch need to be in the same order as they are defined in the alias definition.
Below is an example of patching a CTE
# Create an example of selecting from a CTE
query = '''
WITH cte_name AS (
SELECT * from some_other_table
)
SELECT c1, c2, c3 from cte_name;
'''
# Patch the CTE with the data you want returned
patch = pgmock.patch(
pgmock.cte('cte_name'),
[('val1', 'val2', 'val3')],
['c1', 'c2', 'c3']
)
sql = pgmock.sql(query, patch)
print(sql)
WITH cte_name AS ( SELECT * FROM (VALUES ('val1','val2','val3')) AS pgmock(c1,c2,c3))
SELECT c1, c2, c3 from cte_name;
results = list(test_engine.execute(sql))
assert results == [('val1', 'val2', 'val3')]
Patching and Executing Smaller Components of Queries¶
Sometimes one may only be interested in testing a small part of their
SQL. This is especially true in testing the selects of an
INSERT INTO
or a subquery. An example of pulling out a subquery and
testing it is shown below.
# Create a query with a subquery
query = "SELECT sub.c1, sub.c2 FROM (SELECT * FROM test_table where c1 = 'value') sub;"
# Obtain the subquery so that it can be patched and tested
subquery = pgmock.sql(query, pgmock.subquery('sub'))
# Create a patch for the subquery's table. Similar to patching tables, provide a subquery selector and the data for the subquery
patch = pgmock.patch(pgmock.table('test_table'),
[('value', 'val2'), ('val3', 'val4')],
['c1', 'c2'])
# Render the patched SQL so that we can execute it
sql = pgmock.sql(subquery, patch)
print(sql)
SELECT * FROM (VALUES ('value','val2'),('val3','val4')) AS test_table(c1,c2) where c1 = 'value'
# Execute the SQL and verify that the subquery performs its select properly
results = list(test_engine.execute(sql))
assert results == [('value', 'val2')]
Patching Queries Executed by SQLAlchemy¶
Sometimes it’s not always possible to have full control of the SQL that’s being executed. For example, one might want to test code that issues many different SQLAlchemy statements and still want to patch out the underlying tables.
For these cases, pgmock.mock
can be used as a context manager.
pgmock.mock
takes the SQLAlchemy connectable as an argument and
listens for any queries executed against the connectable. When queries
are executed, they are patched on the fly before they are executed. Some
examples of this are shown below.
with pgmock.mock(test_engine) as mocker:
# Apply patches to the mocker object we created. For this example, we are going to
# patch "test_table"
mocker.patch(pgmock.table('test_table'), [('val1', 'val2', 'val3')], ['c1', 'c2', 'c3'])
# When executing this query, it will be patched on the fly with the values provided
results = list(test_engine.execute('SELECT * from test_table'))
assert results == [('val1', 'val2', 'val3')]
Patching Multiple Queries with Side Effects¶
In most testing situations, one will have more complex SQLAlchemy code that may issue multiple queries. For example, lets take the previous test example and put our SQLAlchemy code in a function that executes two different queries.
def my_sqla_func(engine):
"""A function that issues a couple different queries that we want to test"""
cursors = engine.execute('SELECT * from pg_cursors')
# Do something important with the cursors...
# Now return results from a table
return list(engine.execute('SELECT * from test_table'))
If we try to test this function the same way as before, an error will happen.
with pgmock.mock(test_engine) as mocker:
# Apply patches to the mocker object we created. For this example, we are going to
# patch "test_table"
mocker.patch(pgmock.table('test_table'), [('val1', 'val2', 'val3')], ['c1', 'c2', 'c3'])
# When executing this query, it will be patched on the fly with the values provided
try:
results = my_sqla_func(test_engine)
assert results == [('val1', 'val2', 'val3')]
except pgmock.exceptions.NoMatchError as exc:
print(exc)
No table "test_table" found. The following SQL was used:
SELECT * from pg_cursors
View the docs for this exception at https://pgmock.readthedocs.io/en/latest/exceptions.html for more information.
In the above, running my_sqla_func
with the patched “test_table”
threw a NoMatchError
. When looking at the error message, it appears
that this error occurred on our first query of our function
(SELECT * from pg_cursors
).
This happens because the patch on “test_table” will be applied to every
single query that’s issued, including the first query that cannot be
patched. Instead of silently continuing, pgmock
will raise errors
anytime something cannot be matched.
In order to get around this, use a side_effect
argument to the patch
instead of a single return value. A side effect is a list of return
values to use every time the patch is applied to a query. The first side
effect will be applied to the first query issued and so forth. If more
queries are issued than the number of side effects, a
SideEffectExhaustedError
will be raised. If None
is provided as
a return value, the patch will be completely ignored for the query.
The previous example can be changed to use a side effect in the following way.
with pgmock.mock(test_engine) as mocker:
# Apply patches to the mocker object we created. For this example, we are going to
# patch "test_table" on the second query that is issued by using a side effect
mocker.patch(
pgmock.table('test_table'),
side_effect=[
# Ignore patching test_table for the first query
None,
# Use pgmock.data to construct rows and columns of return data for
# the second query
pgmock.data([('val1', 'val2', 'val3')], ['c1', 'c2', 'c3'])
])
# When executing this query, it will be patched on the fly with the values provided
results = my_sqla_func(test_engine)
assert results == [('val1', 'val2', 'val3')]
# As a precaution, it is always good practice to assert that the number of renderings of
# the mocker matches the number of queries you expected your test code to issue
assert len(mocker.renderings) == 2
Advanced Usage¶
Patching Custom Types and Using Type Hinting¶
pgmock
allows the user to provide quite a few different types of
Python objects to patched values lists. Python objects are converted
into their proper postgres type. For example, a datetime object is
converted to a timestamp and a dictionary is converted to a json object.
An example of this is shown below.
import datetime as dt
query = "SELECT * FROM my_table"
# Create a patch for the table. The patch takes a selector, rows (a list of lists for each column), and column names
patch = pgmock.patch(pgmock.table('my_table'),
[(dt.datetime(2017, 6, 14), {'my': 'json_data'}, None)],
['c1', 'c2', 'c3'])
# Render the patched SQL so that we can execute it
sql = pgmock.sql(query, patch)
print(sql)
SELECT * FROM (VALUES ('2017-06-14T00:00:00'::TIMESTAMP,'{"my": "json_data"}'::JSON,null)) AS my_table(c1,c2,c3)
The amount of Python types supported out of the box in pgmock
is
rather limited. Along with that, it’s impossible to specify certain
datatypes one might need for their tests in Python (e.g. a null
datetime). pgmock
allows the user to specify type hints to cast
their values to a particular type. The type is specified by placing
::type_name
after the column name. For example, the following
illustrates how to cast patched values to datetimes and bigints.
# Create a patch for the table. The patch takes a selector, rows (a list of lists for each column), and column names
patch = pgmock.patch(pgmock.table('my_table'),
[('2017, 6, 14', 10000, None)],
['c1::timestamp', 'c2::bigint', 'c3::timestamp'])
# Render the patched SQL so that we can execute it
sql = pgmock.sql(query, patch)
print(sql)
SELECT * FROM (VALUES ('2017, 6, 14'::timestamp,10000::bigint,null::timestamp)) AS my_table(c1,c2,c3)
Note
Type hints can only be used on python strings, floats, and ints. In other words, if you use a “timestamp” type, a string must be used as the value instead of a datetime object. Otherwise a ColumnTypeError
will be raised.
Testing Postgres Arrays¶
Postgres arrays can be modeled in pgmock but cannot be passed in as python lists instead they must be strings in the Postgres array syntax. The syntax is similar to python except for curly brackets. If it’s a text array then each string should be surrounded by double quotes. Remember to cast the field as the correct array type (ex. ::text[] or integer[]).
# Create a patch for the table. The patch takes a selector, rows (a list of lists for each column), and column names
patch = pgmock.patch(pgmock.table('my_table'),
[('2017, 6, 14', 10000, '{"apple", "iphone"}')],
['c1::timestamp', 'c2::bigint', 'c3::text[]'])
# Render the patched SQL so that we can execute it
sql = pgmock.sql(query, patch)
print(sql)
SELECT * FROM (VALUES ('2017, 6, 14'::timestamp,10000::bigint,'{"apple", "iphone"}'::text[])) AS my_table(c1,c2,c3)
Filling in Meaningless Columns with nulls¶
Sometimes the logic in a query only depends on the values of a couple
columns and it isn’t necessary to provide values for all of the other
columns. pgmock
allows users to ignore providing values for columns
and fills in the empty values with null. Below is an example that
illustrates how to do this when passing in rows to pgmock.patch
.
# Create a query that returns many columns
query = "SELECT c1, c2, c3, c4, c5 from test_table where c1 = 'value'"
# When patching out the table, only provide values for "c1" since we're testing the filtering of the select
patch = pgmock.patch(pgmock.table('test_table'), [('value', ), ('not_filtered', )], ['c1', 'c2', 'c3', 'c4', 'c5'])
# Render the patched SQL. All other values for columns will be null
sql = pgmock.sql(query, patch)
print(sql)
SELECT c1, c2, c3, c4, c5 from (VALUES ('value',null,null,null,null),('not_filtered',null,null,null,null)) AS test_table(c1,c2,c3,c4,c5) where c1 = 'value'
# The patch can also take a list of dictionaries that only specifies which column values to use. This is another
# way to fill in meaningless values with nulls
patch = pgmock.patch(pgmock.table('test_table'), [{'c1': 'value'}, {'c1': 'not_filtered'}], ['c1', 'c2', 'c3', 'c4', 'c5'])
# Render the patched SQL. All other values for columns will be null
sql = pgmock.sql(query, patch)
print(sql)
SELECT c1, c2, c3, c4, c5 from (VALUES ('value',null,null,null,null),('not_filtered',null,null,null,null)) AS test_table(c1,c2,c3,c4,c5) where c1 = 'value'
# Only one row should have matched the filter
results = list(test_engine.execute(sql))
assert len(results) == 1
# Be sure to stop the testing DB for this tutorial
test_db.stop()
test_engine.dispose()
Configuring for Accuracy and Performance¶
pgmock
comes with a configuration module (pgmock.config
) that
can be used to set flags that aid in accuracy of selectors / patching.
These flags come with accuracy/performance hits that should be known
before using them.
Safe Mode¶
pgmock
searches SQL with regular expressions. Regular expressions
can get tripped up whenever special characters appear in comments of SQL
or in string literals. For example, -- this is my comment; hello!
will mess up pgmock.statement
since it splits the SQL by the
semicolon character. Turning on safe mode will search a
pre-formatted version of the supplied SQL that is stripped of comments
and string literals.
Safe mode can be turned on with pgmock.config.set_safe_mode
. By
default, it is set to False
because it incurs a major performance
hit when using it. Safe mode doesn’t have to be configured globally with
pgmock.config.set_safe_mode
. It can be passed to pgmock.sql
or
pgmock.sql_file
as an argument. It can also be used in a context
manager so that it is only set during the duration of execution like so:
with pgmock.config.set_safe_mode(True):
# Run SQL that cant natively be searched by pgmock because of regex issues
...
It’s recommended to pass it as an argument to pgmock.sql
when
needing to be modified. For configuring it in a pytest fixture, one can
use the context manager like so:
import pytest
@pytest.fixture(scope='module')
def use_safe_mode():
with pgmock.config.set_safe_mode(True):
yield
Replace New Patch Aliases¶
Since pgmock
turns expressions into VALUES
expressions when
patching, it is not always possible to preserve the original name of
what’s being patched. For example, SELECT * from schema.table_name
is impossible to patch as
SELECT * FROM (VALUES ...) AS schema.table_name
since
schema.table_name
is not a valid alias.
When this case happens in the case of the pgmock.table
selector,
pgmock
will make an alias as the table name and then replace any
refences to the old table name.
For example, SELECT schema.table_name.col FROM schema.table_name
would be replaced with
SELECT table_name.col FROM (VALUES ...) AS table_name(...)
. Note
that this only matters when the full schema and table name is used to
reference columns.
By default, this mode is turned on. To turn it off globally, call
pgmock.config.set_replace_new_patch_aliases(False)
. Similar to
pgmock.config.set_safe_mode
, this function can be used as a context
manager or in a pytest fixture. It can also be given as an argument to
pgmock.mock
since SQLAlchemy will use this style of selects by
default when making a sqlalchemy.insert
object from a table with a
schema.
If users are never using the full schema and table name when referencing
columns, it is safe to turn this option off and will improve
pgmock.table
selector performance by about 20%.
Using pgmock with pytest¶
The examples above illustrate programmatically making a test database
and running assertions. For examples of how to use pgmock
with
pytest, check out the test_examples.py
file
in pgmock
. This file shows how to use pgmock
with
pytest-pgsql. An
example of using the context manager and reading from a SQL file is
provided.