querying

pdm_utils.functions.querying.append_group_by_clauses(executable, group_by_clauses)

Add GROUP BY SQLAlchemy Column objects to a Select object.

Parameters

• executable (Select) – SQLAlchemy executable query object.

• order_by_clauses (list) – MySQL GROUP BY clause-related SQLAlchemy object(s)

Returns

MySQL expression-related SQLAlchemy exectuable.

Return type

Select

pdm_utils.functions.querying.append_having_clauses(executable, having_clauses)

Add HAVING SQLAlchemy Column objects to a Select object.

Parameters

• executable (Select) – SQLAlchemy executable query object.

• having_clauses – MySQL HAVING clause-related SQLAlchemy object(s).

:returns MySQL expression-related SQLAlchemy executable. :rtype: Select

pdm_utils.functions.querying.append_order_by_clauses(executable, order_by_clauses)

Add ORDER BY SQLAlchemy Column objects to a Select object.

Parameters

• executable (Select) – SQLAlchemy executable query object.

• order_by_clauses (list) – MySQL ORDER BY clause-related SQLAlchemy object(s)

Returns

MySQL expression-related SQLAlchemy exectuable.

Return type

Select

pdm_utils.functions.querying.append_where_clauses(executable, where_clauses)

Add WHERE SQLAlchemy BinaryExpression objects to a Select object.

Parameters

• executable (Select) – SQLAlchemy executable query object.

• where_clauses (list) – MySQL WHERE clause-related SQLAlchemy object(s).

Returns

MySQL expression-related SQLAlchemy exectuable.

Return type

Select

pdm_utils.functions.querying.build_count(db_graph, columns, where=None, add_in=None)

Get MySQL COUNT() expression SQLAlchemy executable.

Parameters

• db_graph (Graph) – SQLAlchemy structured NetworkX Graph object.

• columns (list) – SQLAlchemy Column object(s).

• where (list) – MySQL WHERE clause-related SQLAlchemy object(s).

• add_in (list) – MySQL Column-related inputs to be considered for joining.

Returns

MySQL COUNT() expression-related SQLAlchemy executable.

Return type

Select

pdm_utils.functions.querying.build_distinct(db_graph, columns, where=None, order_by=None, add_in=None)

Get MySQL DISTINCT expression SQLAlchemy executable.

Parameters

• db_graph (Graph) – SQLAlchemy structured NetworkX Graph object.

• columns (list) – SQLAlchemy Column object(s).

• where (list) – MySQL WHERE clause-related SQLAlchemy object(s).

• order_by (list) – MySQL ORDER BY clause-related SQLAlchemy object(s).

• add_in (list) – MySQL Column-related inputs to be considered for joining.

Returns

MySQL DISTINCT expression-related SQLAlchemy executable.

Return type

Select

pdm_utils.functions.querying.build_fromclause(db_graph, columns)

Get a joined table from pathing instructions for joining MySQL Tables. :param db_graph: SQLAlchemy structured NetworkX Graph object. :type db_graph: Graph :param columns: SQLAlchemy Column object(s). :type columns: Column :type columns: list :returns: SQLAlchemy Table object containing left outer-joined tables. :rtype: Table

pdm_utils.functions.querying.build_graph(metadata)

Get a NetworkX Graph object populated from a SQLAlchemy MetaData object.

Parameters

metadata (MetaData) – Reflected SQLAlchemy MetaData object.

Returns

Populated and structured NetworkX Graph object.

Return type

Column

pdm_utils.functions.querying.build_onclause(db_graph, source_table, adjacent_table)

Creates a SQLAlchemy BinaryExpression object for a MySQL ON clause

expression

Parameters

• db_graph (Graph) – SQLAlchemy structured NetworkX Graph object.

• source_table (str) – Case-insensitive MySQL table name.

• adjacent_table – Case-insensitive MySQL table name.

Returns

MySQL foreign key related SQLAlchemy BinaryExpression object.

Return type

BinaryExpression

pdm_utils.functions.querying.build_select(db_graph, columns, where=None, order_by=None, add_in=None, having=None, group_by=None)

Get MySQL SELECT expression SQLAlchemy executable.

Parameters

• db_graph (Graph) – SQLAlchemy structured NetworkX Graph object.

• columns (list) – SQLAlchemy Column object(s).

• where (list) – MySQL WHERE clause-related SQLAlchemy object(s).

• order_by (list) – MySQL ORDER BY clause-related SQLAlchemy object(s).

• add_in (list) – MySQL Column-related inputs to be considered for joining.

• having (list) – MySQL HAVING clause-related SQLAlchemy object(s).

• group_by (list) – MySQL GROUP BY clause-related SQLAlchemy object(s).

Returns

MySQL SELECT expression-related SQLAlchemy executable.

Return type

Select

pdm_utils.functions.querying.build_where_clause(db_graph, filter_expression)

Creates a SQLAlchemy BinaryExpression object from a MySQL WHERE

clause expression.

Parameters

• db_graph (Graph) – SQLAlchemy structured NetworkX Graph object.

• filter_expression (str) – MySQL where clause expression.

Returns

MySQL expression-related SQLAlchemy BinaryExpression object.

Return type

BinaryExpression

pdm_utils.functions.querying.execute(engine, executable, in_column=None, values=[], limit=8000, return_dict=True)

Use SQLAlchemy Engine to execute a MySQL query.

Parameters

• engine (Engine) – SQLAlchemy Engine object used for executing queries.

• executable (str) – Input a executable MySQL query.

• return_dict (Boolean) – Toggle whether execute returns dict or tuple.

Returns

Results from execution of given MySQL query.

Return type

list[dict]

Return type

list[tuple]

pdm_utils.functions.querying.execute_value_subqueries(engine, executable, in_column, source_values, return_dict=True, limit=8000)

Query with a conditional on a set of values using subqueries.

Parameters

• engine (Engine) – SQLAlchemy Engine object used for executing queries.

• executable (str) – Input a executable MySQL query.

• in_column (Column) – SQLAlchemy Column object.

• source_values (list[str]) – Values from specified MySQL column.

• return_dict (Boolean) – Toggle whether to return data as a dictionary.

• limit (int) – SQLAlchemy IN clause query length limiter.

Returns

List of grouped data for each value constraint.

Return type

list

pdm_utils.functions.querying.extract_column(column, check=None)

Get a column from a supported SQLAlchemy Column-related object.

Parameters

• column (UnaryExpression) – SQLAlchemy Column-related object.

• check (<type BinaryExpression>) – SQLAlchemy Column-related object type.

Returns

Corresponding SQLAlchemy Column object.

Return type

Column

pdm_utils.functions.querying.extract_columns(columns, check=None)

Get a column from a supported SQLAlchemy Column-related object(s).

Parameters

• column (UnaryExpression) – SQLAlchemy Column-related object.

• check (<type BinaryExpression>) – SQLAlchemy Column-related object type.

Returns

List of SQLAlchemy Column objects.

Return type

list[Column]

pdm_utils.functions.querying.first_column(engine, executable, in_column=None, values=[], limit=8000)

Use SQLAlchemy Engine to execute and return the first column of fields.

Parameters

• engine (Engine) – SQLAlchemy Engine object used for executing queries.

• executable (str) – Input an executable MySQL query.

Returns

A column for a set of MySQL values.

Return type

list[str]

pdm_utils.functions.querying.first_column_value_subqueries(engine, executable, in_column, source_values, limit=8000)

Query with a conditional on a set of values using subqueries.

Parameters

• engine (Engine) – SQLAlchemy Engine object used for executing queries.

• executable (str) – Input a executable MySQL query.

• in_column (Column) – SQLAlchemy Column object.

• source_values (list[str]) – Values from specified MySQL column.

• return_dict (Boolean) – Toggle whether to return data as a dictionary.

• limit (int) – SQLAlchemy IN clause query length limiter.

Returns

Distinct values fetched from value constraints.

Return type

list

pdm_utils.functions.querying.get_column(metadata, column)

Get a SQLAlchemy Column object, with a case-insensitive input. Input must be formatted {Table_name}.{Column_name}.

Parameters

• metadata (MetaData) – Reflected SQLAlchemy MetaData object.

• table (str) – Case-insensitive column name.

Returns

Corresponding SQLAlchemy Column object.

Return type

Column

pdm_utils.functions.querying.get_table(metadata, table)

Get a SQLAlchemy Table object, with a case-insensitive input.

Parameters

• metadata (MetaData) – Reflected SQLAlchemy MetaData object.

• table (str) – Case-insensitive table name.

Returns

Corresponding SQLAlchemy Table object.

Return type

Table

pdm_utils.functions.querying.get_table_list(columns)

Get a nonrepeating list SQLAlchemy Table objects from Column objects.

Parameters

columns (list) – SQLAlchemy Column object(s).

Returns

List of corresponding SQLAlchemy Table objects.

Return type

list

pdm_utils.functions.querying.get_table_pathing(db_graph, table_list, center_table=None)

Get pathing instructions for joining MySQL Table objects.

Parameters

• db_graph (Graph) – SQLAlchemy structured NetworkX Graph object.

• table_list (list[Table]) – List of SQLAlchemy Table objects.

• center_table (Table) – SQLAlchemy Table object to begin traversals from.

Returns

2-D list containing the center table and pathing instructions.

Return type

list

pdm_utils.functions.querying.join_pathed_tables(db_graph, table_pathing)

Get a joined table from pathing instructions for joining MySQL Tables.

Parameters

• db_graph (Graph) – SQLAlchemy structured NetworkX Graph object.

• table_pathing (list) – 2-D list containing a Table and pathing lists.

Returns

SQLAlchemy Table object containing left outer-joined tables.

Return type

Table

pdm_utils.functions.querying.query(session, db_graph, table_map, where=None)

Use SQLAlchemy session to retrieve ORM objects from a mapped object.

Parameters

• session (Session) – Bound and connected SQLAlchemy Session object.

• table_map – SQLAlchemy ORM map object.

• where (list) – MySQL WHERE clause-related SQLAlchemy object(s).

• order_by (list) – MySQL ORDER BY clause-related SQLAlchemy object(s).

Returns

List of mapped object instances.

Return type

list