SQL Server Toolkit

The SQL Server Toolkit provides functions to help using pysdmx with SQL Server.

Create a SQL table out of structural metadata

pysdmx.toolkit.sqlsrv.create_table(structure, schema_name='dbo', table_name=None, pk_fields=None, index_fields=None, extra_columns=None)

Return a CREATE statement for the supplied structure.

Parameters:

  • structure (Union[DataflowInfo, DataStructureDefinition, Schema]) – The structure for which a database table must be created.

  • schema_name (str) – The name of the schema to which the new table belongs. If it is not supplied, the table will be created in the default schema.

  • table_name (Optional[str]) – The name of the table to be created. If it is not supplied, the structure ID will be used as table name.

  • pk_fields (Optional[str]) – The field(s) to be used for the (composite) primary key. If it is not supplied, the primary key will be a composite key combining the dimension values.

  • index_fields (Optional[Collection[str]]) – The columns for which an index should be created. If it is not supplied, indexes will be created for every dimension in the supplied structure.

  • extra_columns (Optional[Collection[Column]]) – Any additional column required for the table, beyond the ones (i.e. the components) already defined in the structure.

Return type:

str

Returns:

The CREATE statement for the supplied structure, as a string.

Create prepared statements from pysdmx queries

pysdmx.toolkit.sqlsrv.get_select_statement(table_name, schema_name='dbo', filters=None, columns=None, sort=None, offset=0, limit=None)

Return a SQL SELECT statement based on the provided input.

Parameters:

  • table_name (str) – The name of the table from which to fetch data.

  • schema_name (str) – The name of the schema to which the table belongs.

  • filters (Union[BooleanFilter, DateTimeFilter, MultiFilter, NotFilter, NullFilter, NumberFilter, TextFilter, None]) – The filters to be considered in the SQL WHERE clause.

  • columns (Optional[Collection[str]]) – The columns from which to fetch data.

  • sort (Optional[Collection[SortBy]]) – How to sort data.

  • offset (int) – The number of rows to skip before starting to return rows.

  • limit (Optional[int]) – The maximum number of rows to return after the offset.

Return type:

tuple[str, list[Any]]

Returns: A tuple containing:
  • A string representing the SELECT statement corresponding to the

    supplied input, as a prepared statement.

  • A list of values to replace the placeholders in the prepared

    statement.

Lower-level functions

There are also lower-level functions available, if necessary, but they are typically abstracted via the get_select_statement function described above.

pysdmx.toolkit.sqlsrv.get_select_columns(columns)

Return the columns to be selected from a table.

This will list the escaped IDs of the columns to be returned or will return * if no columns are supplied.

Parameters:

columns (Optional[Collection[str]]) – The columns from which to return data, or * if no columns are supplied.

Return type:

str

Returns:

The string to be used directly after SELECT in the SQL query.

pysdmx.toolkit.sqlsrv.get_sort_clause(sort)

Return a SQL ORDER BY clause from the provided sort criteria, if any.

Parameters:

sort (Optional[Collection[SortBy]]) – The various sort criteria, if any.

Return type:

str

Returns:

The ORDER BY clause, to sort the data to be return according to the

sort criteria provided, if any. Else, an empty string is returned.

pysdmx.toolkit.sqlsrv.get_pagination_clause(offset, limit=None)

Return a string to control pagiunation of the records to be returned.

Parameters:

  • offset (int) – The number of rows to skip before starting to return rows.

  • limit (Optional[int]) – The maximum number of rows to return after the offset.

Return type:

str

Returns:

The OFFSET and FETCH string required to paginate results in

SQL Server, or an empty string otherwise.

pysdmx.toolkit.sqlsrv.get_where_clause(filters)

Return the SQL WHERE clause representing the supplied filters, if any.

Parameters:

filters (Union[BooleanFilter, DateTimeFilter, MultiFilter, NotFilter, NullFilter, NumberFilter, TextFilter, None]) – The filters to be considered in the SQL WHERE clause.

Returns:

  • A string representing the SQL WHERE clause with placeholders for the

    filters, or an empty string if no filters are provided.

  • A list of values to replace the placeholders in the prepared

    statement.

Return type:

A tuple containing

pysdmx.toolkit.sqlsrv.get_sql_data_type(c)

Infer the appropriate SQL Server type for the supplied component.

This function maps the SDMX data types to the SQL Server ones, and uses the max_length facet (if any), to infer the column size.

Return type:

str

Returns:

The appropriate SQL Server type for the supplied component.