Skip to content

Module zillion.sql_utils

aggregation_to_sqla_func

zillion.sql_utils.aggregation_to_sqla_func(aggregation)

Convert an AggregationType string to a SQLAlchemy function

check_metadata_url

zillion.sql_utils.check_metadata_url(url, confirm_exists=False)

Check validity of the metadata URL

column_fullname

zillion.sql_utils.column_fullname(column, prefix=None)

Get a fully qualified name for a column

Parameters:

  • column - (SQLAlchemy column) A SQLAlchemy column object to get the full name for
  • prefix - (str, optional) If specified, a manual prefix to prepend to the output string. This will automatically be separted with a ".".

Returns:

(str) - A fully qualified column name. The exact format will vary depending on your SQLAlchemy metadata, but an example would be: schema.table.column

comment

zillion.sql_utils.comment(self, c)

See https://github.com/sqlalchemy/sqlalchemy/wiki/CompiledComments

contains_aggregation

zillion.sql_utils.contains_aggregation(sql)

Determine whether a SQL query contains aggregation functions.

Warning:

This relies on a non-exhaustive list of SQL aggregation functions to look for. This will likely need updating.

Parameters:

  • sql - (str or sqlparse result) The SQL query to check for aggregation functions

Returns:

(bool) - True if the SQL string contains aggregation

contains_sql_keywords

zillion.sql_utils.contains_sql_keywords(sql)

Determine whether a SQL query contains special SQL keywords (DML, DDL, etc.)

Parameters:

  • sql - (str or sqlparse result) The SQL query to check for keywords

Returns:

(bool) - True if the SQL string contains keywords

filter_dialect_schemas

zillion.sql_utils.filter_dialect_schemas(schemas, dialect)

Filter out a set of baseline/system schemas for a dialect

Parameters:

  • schemas - (list) A list of schema names
  • dialect - (str) The name of a SQLAlchemy dialect

Returns:

(list) - A filtered list of schema names

get_postgres_pid

zillion.sql_utils.get_postgres_pid(conn)

Helper to get the PostgreSQL connection PID

get_postgres_schemas

zillion.sql_utils.get_postgres_schemas(conn)

Helper to list PostgreSQL schemas

get_schema_and_table_name

zillion.sql_utils.get_schema_and_table_name(table)

Extract the schema and table name from a full table name. If the table name is not schema-qualified, return None for the schema name

get_schemas

zillion.sql_utils.get_schemas(engine)

Inspect the SQLAlchemy engine to get a list of schemas

get_sqla_criterion_expr

zillion.sql_utils.get_sqla_criterion_expr(column, criterion, negate=False)

Create a SQLAlchemy criterion expression

Parameters:

  • column - (SQLAlchemy column) A SQLAlchemy column object to be used in the expression
  • criterion - (3-item iterable) A 3-item tuple or list of the format [field, operation, value(s)]. See core.CRITERIA_OPERATIONS for supported operations. The value item may take on different formats depending on the operation. In most cases passing an iterable will result in multiple criteria of that operation being formed. For example, ("my_field", "=", [1,2,3]) would logically or 3 conditions of equality to the 3 values in the list. The "between" operations expect each value to be a 2-item iterable representing the lower and upper bound of the criterion.
  • negate - (bool, optional) Negate the expression

Returns:

(SQLAlchemy expression) - A SQLALchemy expression representing the criterion

Notes:

Postgresql "like" is case sensitive, but mysql "like" is not. Postgresql also supports "ilike" to specify case insensitive, so one option is to look at the dialect to determine the function, but that is not supported yet.

infer_aggregation_and_rounding

zillion.sql_utils.infer_aggregation_and_rounding(column)

Infer the aggregation and rounding settings based on the column type. This is just a rough / best guess based on the column type, precision and rounding settings.

Parameters:

  • column - (SQLAlchemy column) The column to analyze

Returns:

(AggregationType, int) - A 2-item tuple of the aggregation type and rounding to use

is_numeric_type

zillion.sql_utils.is_numeric_type(type)

Determine if this is a numeric SQLAlchemy type

is_probably_metric

zillion.sql_utils.is_probably_metric(column, formula=None, nlp_column_info=None)

Determine if a column is probably a metric. This is used when trying to automatically init/reflect a datasource and determine the field types for columns. The logic is very coarse, and should not be relied on for more than quick/convenient use cases.

Parameters:

  • column - (SQLAlchemy column) The column to analyze
  • formula - (str, optional) A formula to calculate the column
  • nlp_column_info - (dict, optional) Column attributes inferred from natural language processing of the table/column definitions

Returns:

(bool) - True if the column is probably a metric

printexpr

zillion.sql_utils.printexpr(expr)

Print a SQLAlchemy expression

sqla_compile

zillion.sql_utils.sqla_compile(expr)

Compile a SQL expression

Parameters:

  • expr - (SQLAlchemy expression) The SQLAlchemy expression to compile

Returns:

(str) - The compiled expression string

to_duckdb_type

zillion.sql_utils.to_duckdb_type(type)

Compile into a DuckDB SQLAlchemy type

to_generic_sa_type

zillion.sql_utils.to_generic_sa_type(type)

Return a generic SQLAlchemy type object from a type that may be dialect- specific. This will attempt to preserve common type settings such as specified field length, scale, and precision. On error it will fall back to trying to init the generic type with no params.

to_mysql_type

zillion.sql_utils.to_mysql_type(type)

Compile into a MySQL SQLAlchemy type

to_postgresql_type

zillion.sql_utils.to_postgresql_type(type)

Compile into a PostgreSQL SQLAlchemy type

to_sqlite_type

zillion.sql_utils.to_sqlite_type(type)

Compile into a SQLite SQLAlchemy type

type_string_to_sa_type

zillion.sql_utils.type_string_to_sa_type(type_string)

Convert a field type string to a SQLAlchemy type. The type string will be evaluated as a python statement or class name to init from the SQLAlchemy top level module. Dialect-specific SQLAlchemy types are not currently supported.

Parameters:

  • type_string - (str) A string representing a SQLAlchemy type, such as "Integer", or "String(32)". This does a case-insensitive search and will return the first matching SQLAlchemy type.

Returns:

(SQLAlchemy type object) - An init'd SQLAlchemy type object