Interface¶

pgmock¶

Primary pgmock interface

pgmock.create_table_as(table)[source]¶

Obtains a selector for a CREATE TABLE AS statement.

Searches for CREATE TABLE table_name(optional columns) AS and returns the entire statement. The body of the statement (e.g. the SELECT or anything after CREATE TABLE AS) can be returned by chaining the body() selector.

Parameters:	table (str) – The name of the table as referenced in the expression
Returns:	A chainable SQL selector.
Return type:	Selector
Raises:	`NoMatchError` – When the expression cannot be found during rendering. `MultipleMatchError` – When multiple expressions are found during rendering.

Examples

Obtain the CREATE TABLE AS of table “t”

ctas = pgmock.sql(sql_string, pgmock.create_table_as('t'))

Obtain the body of the CREATE TABLE AS of table “t”

ctas_body = pgmock.sql(sql_string, pgmock.create_table_as('t').body())

Note

When patching CREATE TABLE AS statements, the entire body of the statement is patched with a SELECT * FROM VALUES ... AS pgmock(columns...). This is because it is illegal to do VALUES ... AS ... after a “create table as” statement.

pgmock.cte(alias)[source]¶

Obtains a selector for a common table expression (CTE)

CTEs are matched by searching for a WITH cte_name AS or for searching for a CTE after a comma (e.g WITH cte_name1 AS ..., cte_name2 AS ...)

Parameters:	alias (str) – The alias of the CTE
Returns:	A chainable SQL selector
Return type:	Selector
Raises:	`NoMatchError` – When the CTE cannot be found during rendering. `MultipleMatchError` – When multiple CTEs are found during rendering. `NestedMatchError` – When nested subquery matches are found during rendering. `InvalidSQLError` – When enclosing parentheses for a CTE cannot be found.

Examples

Obtain the CTE that has the alias “a”

cte = pgmock.sql(sql_string, pgmock.cte('a'))

pgmock.data(rows=None, cols=None)[source]¶

Creates patch data for a side effect.

Parameters:	rows (List[tuple], optional) – A list of tuples of values to patch for each row. Each row must have the same length. If `None`, defaults to an empty list. cols (List[str]) – A list of columns.
Returns:	A data object that can be used as input to a side effect of a patch, for example `pgmock.patch(side_effect=[pgmock.data(rows=..., cols=...)])`
Return type:	Data

pgmock.mock(connectable, replace_new_patch_aliases=None)[source]¶

Creates a mock selector that can be patched.

This is intended to be used as a context manager with a given SQLAlchemy connectable (e.g. an engine, session, connection, etc). For example:

with pgmock.mock(engine) as mocker:
    mocker.patch(pgmock.table('my_table'), rows, cols)
    mocker.patch(pgmock.table('other_table'), rows, cols)

    # Run SQLAlchemy queries...

    # Assert the mocker was rendered with as many queries executed
    assert len(mocker.renderings) == num_expected_queries

Any queries executed inside of the context manager will be patched by SQLAlchemy’s before_cursor_execute event. Renderings of patched SQL can be obtained by examining the renderings property of the object, which is a list of tuples of the original and modified SQL of every query.

If any of the patches cannot be matched during query execution, the relevant exceptions are raised. Specific patches can be applied to specific queries by using the side_effect argument of pgmock.patch.

Parameters:	connectable (SQLAlchemy connectable object) – The connectable SQLAlchemy object (e.g engine, session, connection, etc) replace_new_patch_aliases (bool, optional) – If `True`, will replace any references to patch aliases when they differ from the original alias. If `None`, uses the globally-configured value that defaults to `True`. More information on this can be found at `pgmock.config.set_replace_new_patch_aliases`
Returns:	A chainable mock object that can be patched.
Return type:	Mock
Raises:	Any error that can happen during rendering.

pgmock.patch(selector=None, rows=None, cols=None, side_effect=None)[source]¶

Applies a patch to a selector.

Parameters:	selector (Selector, optional) – relevant SQL. rows (List[tuple], optional) – A list of tuples of values to patch for each row. Each row must have the same length. If `None`, patching is ignored. cols (List[str]) – A list of columns. If more columns are provided than the length of the rows, `null` values are filled in for the missing values. side_effect (List[pgmock.data]) – A list of side effects. Side effects can only be provided when `rows` and `cols` are not provided. Each side effect is rendered on each subsequent rendering of the patch. Side effects must be instantiated with `pgmock.data` and the arguments are `rows` and `cols`. Note: providing `None` as a side effect will ignore the patch for the rendering.
Returns:	A chainable mock object that can be patched.
Return type:	Mock
Raises:	`UnpatchableError` – When the selector cannot be patched

Examples

Patch a table “schema.table_name” with values

patch = pgmock.patch(pgmock.table('schema.table_name'),
                     rows=[(1, 2), (3, 4)],
                     cols=['a', 'b'])
patched_query = pgmock.sql(sql_string, patch)

Patch a table “schema.table_name” with a side effect while using SQLAlchemy

with pgmock.mock(connetion) as mocker:
    mocker.patch(pgmock.table('schema.table_name'),
                 side_effect=[
                     None,
                     pgmock.data([(1, 2), (3, 4)], ['a', 'b'])
                 ])
    # Do no patching on the first execution of the SQLAlchemy
    # connection since the side effect returns ``None`` the
    # first time
    connection.execute(...)
    # Now apply the patch the second time
    connection.execute(...)

pgmock.sql(query, *selectors, safe_mode=None)[source]¶

Renders SQL from a query and selector.

Parameters:	query (str) – The SQL query selectors (Selector) – The selector(s) of the query to render. If multiple selectors are provided as positional arguments, they are automatically chained together. safe_mode* (bool, optional) – If `True`, used stripped SQL when using selectors. This has a performance hit but can result in more accurate searching. If `None`, defaults to the globally configured value (which defaults to `False`). More information on this can be viewed at `pgmock.config.set_safe_mode`.
Returns:	The rendered SQL
Return type:	str
Raises:	Any exceptions that can be thrown by the selector during rendering

Examples

Render the SQL from a subquery aliased with “a”

subquery_sql = pgmock.sql(sql_string, pgmock.subquery('a'))

pgmock.sql_file(file_name, *selectors, safe_mode=None)[source]¶

Renders SQL from a sql file and selector.

Parameters:	file_name (str) – The SQL file name selectors (Selector) – The selector(s) of the query to render. If multiple selectors are provided as positional arguments, they are automatically chained together. safe_mode* (bool, optional) – If `True`, used stripped SQL when using selectors. This has a performance hit but can result in more accurate searching. If `None`, defaults to the globally configured value (which defaults to `False`). More information on this can be viewed at `pgmock.config.set_safe_mode`.
Returns:	The rendered SQL
Return type:	str
Raises:	Any exceptions that can be thrown by the selector during rendering

Examples

Render the SQL from a file that has a subquery aliased with “a”

subquery_sql = pgmock.sql_file(sql_file_path, pgmock.subquery('a'))

pgmock.statement(start, end=None)[source]¶

Obtains a statement selector.

Statements are naively parsed by splitting SQL based on the semicolon. If any semicolons exist in the comments or literal strings, this selector has undefined behavior.

Parameters:	start (int) – The starting statement. If `end` is `None`, obtain a single statement end (int, optional) – The ending statement (exclusive)
Returns:	A chainable SQL selector.
Return type:	Selector
Raises:	`StatementParseError` – When the statement range is invalid for the parsed statements.

Examples

Obtain the first statement in a SQL string

statement = pgmock.sql(sql_string, pgmock.statement(0))

Obtain the second and third statements in a SQL string

statement = pgmock.sql(sql_string, pgmock.statement(1, 3))

pgmock.insert_into(table)[source]¶

Obtains a selector for an INSERT INTO expression.

Searches for INSERT INTO table_name(optional columns) and returns the entire statement. The body of the statement (e.g. the SELECT or anything after INSERT INTO) can be returned by chaining the body() selector.

Parameters:	table (str) – The table of the expression.
Returns:	A chainable SQL selector.
Return type:	Selector
Raises:	`NoMatchError` – When the expression cannot be found during rendering. `MultipleMatchError` – When multiple expressions are found during rendering.

Examples

Obtain the INSERT INTO of table “t”

insert_into = pgmock.sql(sql_string, pgmock.insert_into('t'))

Obtain the body of the INSERT INTO of table “t”

insert_into_body = pgmock.sql(sql_string, pgmock.insert_into('t').body())

Note

When patching INSERT INTO statements, the entire body of the statement after the INSERT INTO is patched

pgmock.subquery(alias)[source]¶

Obtains a selector for a subquery

Subqueries are matched by an alias preceeded by an enclosing parenthesis. Once matched, the SQL is search for the starting parenthesis.

Parameters:	alias (str) – The alias of the subquery.
Returns:	A chainable SQL selector.
Return type:	Selector
Raises:	`NoMatchError` – When the expression cannot be found during rendering. `MultipleMatchError` – When multiple expressions are found during rendering. `NestedMatchError` – When nested subquery matches are found during rendering. `InvalidSQLError` – When enclosing parentheses for a subquery cannot be found.

Examples

Obtain the subquery that has the alias “a”

subquery = pgmock.sql(sql_string, pgmock.subquery('a'))

Todo

Support for subqueries without an alias (e.g. after an “in” keyword)

pgmock.table(name, alias=None)[source]¶

Obtains a selector for a table

Tables are matched by searching for their name and optional aliases after a FROM or JOIN keyword. If the table has an alias but the alias isn’t provided, a NoMatchError will be thrown.

Parameters:	name (str) – The name of the table (including the schema if in the query) alias (str, optional) – The alias of the table if it exists
Returns:	A chainable SQL selector.
Return type:	Selector
Raises:	`NoMatchError` – When the expression cannot be found during rendering. `MultipleMatchError` – When multiple expressions are found during rendering.

Examples

Obtain the table with no alias that has the name “schema.table_name”

table = pgmock.sql(sql_string, pgmock.table('schema.table_name'))

Obtain the table with the name “schema.table_name” that has the alias “a”

table = pgmock.sql(sql_string, pgmock.table('schema.table_name', 'a'))

Todo

Support lateral joins and other joins that have keywords after the JOIN keyword

pgmock.config¶

Configuration functions for pgmock

pgmock.config.get_safe_mode()¶: Returns the configured safe mode

pgmock.config.set_safe_mode(safe_mode)¶

Sets whether safe mode is turned on or off in pgmock.

If safe_mode is True, all selectors will be applied to a stripped version of SQL that excludes comments and string literals. This improves the accuracy of pgmock matching but comes with a performance hit.

Safe mode is set to False by default.

Examples

# Set the global setting
pgmock.config.set_safe_mode(True)

# Only set the configuration while in use of the
# context manager. Revert it back to the original
# value when the context manager exits
with pgmock.config.set_safe_mode(False):
   ...

pgmock.config.get_replace_new_patch_aliases()¶: Returns the configured replacement of new patch aliases

pgmock.config.set_replace_new_patch_aliases(replace_new_patch_aliases)¶

Sets whether new patch aliases should be replaced in SQL when found

Since pgmock turns expressions into VALUES expressions when patching, it is not always possible to preserve the original name of what’s being patched.

If the name of the expression being patched cannot be used as a valid patch alias (e.g. a table with a schema name in it), this setting ensures that all references to the new patch alias will be updated.

This setting primarily applies to SQL in this style:

SELECT schema.table_name.col from schema.table_name

When patching schema.table_name with a VALUES list, it is impossible to alias the VALUES list with schema.table_name since . is an invalid alias character. To get around this, pgmock makes the alias of the VALUES list be the table name without the schema name. When this setting is turned on, it will ensure that the SELECT schema.table_name.col will also be valid. The patch will look like this when the setting is on:

SELECT table_name.col from (VALUES(...) AS table_name)

and it will look like this when off:

SELECT schema.table_name.col from (VALUES(...) AS table_name)

The latter example is invalid SQL, so this setting should be turned on if the full schema name is present when referencing columns.

Note

This setting incurs a performance overhead (10-20% slower depending on the SQL length) only when using the pgmock.table selector.

This setting does a global search and replace on the query. In the example above, it would replace every instance of schema.table_name. with table_name.. Keep this in mind as it could potentially have adverse side effects on other SQL that might reference the schema and table name followed by a period.

This setting does not handle the case when double quotes are used to reference anything (e.g. "schema"."table_name"."col").

Examples

# Set the global setting
pgmock.config.set_replace_new_patch_aliases(True)

# Only set the configuration while in use of the
# context manager. Revert it back to the original
# value when the context manager exits
with pgmock.config.set_replace_new_patch_aliases(False):
    ...