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. theSELECT
or anything afterCREATE TABLE AS
) can be returned by chaining thebody()
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 aSELECT * FROM VALUES ... AS pgmock(columns...)
. This is because it is illegal to doVALUES ... 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.gWITH 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: 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 therenderings
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 ofpgmock.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. IfNone
, uses the globally-configured value that defaults toTrue
. More information on this can be found atpgmock.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
andcols
are not provided. Each side effect is rendered on each subsequent rendering of the patch. Side effects must be instantiated withpgmock.data
and the arguments arerows
andcols
. Note: providingNone
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 patchedExamples
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. IfNone
, defaults to the globally configured value (which defaults toFalse
). More information on this can be viewed atpgmock.config.set_safe_mode
.
Returns: The rendered SQL
Return type: 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. IfNone
, defaults to the globally configured value (which defaults toFalse
). More information on this can be viewed atpgmock.config.set_safe_mode
.
Returns: The rendered SQL
Return type: 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: 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. theSELECT
or anything afterINSERT INTO
) can be returned by chaining thebody()
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 theINSERT 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
orJOIN
keyword. If the table has an alias but the alias isn’t provided, aNoMatchError
will be thrown.Parameters: 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
isTrue
, all selectors will be applied to a stripped version of SQL that excludes comments and string literals. This improves the accuracy ofpgmock
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 intoVALUES
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 aVALUES
list, it is impossible to alias theVALUES
list withschema.table_name
since.
is an invalid alias character. To get around this,pgmock
makes the alias of theVALUES
list be the table name without the schema name. When this setting is turned on, it will ensure that theSELECT 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.
withtable_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): ...