14.2.18. crate_anon.common.sql

crate_anon/common/sql.py

Copyright (C) 2015, University of Cambridge, Department of Psychiatry. Created by Rudolf Cardinal (rnc1001@cam.ac.uk).

This file is part of CRATE.

CRATE is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

CRATE is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with CRATE. If not, see <https://www.gnu.org/licenses/>.

Low-level SQL manipulation functions.

class crate_anon.common.sql.ColumnId(db: str = '', schema: str = '', table: str = '', column: str = '')[source]

Represents a database column.

__init__(db: str = '', schema: str = '', table: str = '', column: str = '') → None[source]

Parameters:

db – database name
schema – schema name
table – table name
column – column name

property column: str: Returns the column part.

property db: str: Returns the database part.

property has_table_and_column: bool: Do we know about a table and a column?

property is_valid: bool: Do we know about a table and a column, at least?

property schema: str: Returns the schema part.

property schema_id: SchemaId: Returns a SchemaId for the schema of our column.

property table: str: Returns the table part.

property table_id: TableId: Returns a TableId for our table.

class crate_anon.common.sql.IndexCreationInfo(index_name: str, column: str | List[str], unique: bool = False)[source]

__init__(index_name: str, column: str | List[str], unique: bool = False) → None

column: str | List[str]: Column name(s) to index

index_name: str: Name of the index

unique: bool = False: Make a unique index?

class crate_anon.common.sql.JoinInfo(table: str, join_type: str = 'INNER JOIN', join_condition: str = '')[source]

Object to represent a SQL join condition in a simple way.

__init__(table: str, join_type: str = 'INNER JOIN', join_condition: str = '') → None[source]

Parameters:

table – table to be joined in
join_type – join method, e.g. "INNER JOIN"
join_condition – join condition, e.g. "ON x = y"

class crate_anon.common.sql.ReflectedColumnInfo(column: Column, override_comment: str | None = None, crate_annotation: str | None = None, values_info: str | None = None)[source]

Provides information about a column reflected from a database, with optional additional information from a CRATE data dictionary, +/- a description of values in that column (for researcher reports).

__init__(column: Column, override_comment: str | None = None, crate_annotation: str | None = None, values_info: str | None = None) → None

property comment: str: The database comment, if present, or another that has been supplied.

property crate_annotation_str: str: Human-oriented version for report.

get_column_source_description(with_fk: bool = True) → str[source]

Returns a description of where the column is from, used as a suffix for data dictionary comment generation.

Parameters:: with_fk – Include foreign key descriptions (helpful because CRATE doesn’t reproduce FK relationships in the destination DDL).

property values_info_str: str: Human-oriented version for report.

class crate_anon.common.sql.SchemaId(db: str = '', schema: str = '')[source]

Represents a database schema. This is a bit complex:

In SQL Server, schemas live within databases. Tables can be referred to as table, schema.table, or database.schema.table.
- https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/ownership-and-user-schema-separation-in-sql-server
- The default schema is named dbo.
In PostgreSQL, schemas live within databases. Tables can be referred to as table, schema.table, or database.schema.table.
- https://www.postgresql.org/docs/current/static/ddl-schemas.html
- The default schema is named public.
In MySQL, “database” and “schema” are synonymous. Tables can be referred to as table or database.table (= schema.table).
- https://stackoverflow.com/questions/11618277/difference-between-schema-database-in-mysql

__init__(db: str = '', schema: str = '') → None[source]

Parameters:

db – database name
schema – schema name

column_id(table: str, column: str) → ColumnId[source]

Returns a ColumnId combining this schema and the specified table/column.

Parameters:

table – name of the table
column – name of the column

property db: str: Returns the database part.

classmethod from_schema_tag(tag: str) → SchemaId[source]: Returns a SchemaId from a tag of the form db.schema.

identifier(grammar: SqlGrammar) → str[source]

Returns an SQL identifier for this schema using the specified SQL grammar, quoting it if need be.

Parameters:: grammar – cardinal_pythonlib.sql.sql_grammar.SqlGrammar

is_blank() → bool[source]: Is this a blank/nonfunctional schema, with no database or schema part?

is_present() → bool[source]: Is this a blank/nonfunctional schema, with no database or schema part?

property schema: str: Returns the schema part.

property schema_tag: str

String suitable for encoding the SchemaId e.g. in a single HTML form. Takes the format database.schema.

The __init__() function has already checked the assumption of no '.' characters in either part.

table_id(table: str) → TableId[source]

Returns a TableId combining this schema and the specified table.

Parameters:: table – name of the table

class crate_anon.common.sql.TableId(db: str = '', schema: str = '', table: str = '')[source]

Represents a database table.

__init__(db: str = '', schema: str = '', table: str = '') → None[source]

Parameters:

db – database name
schema – schema name
table – table name

column_id(column: str) → ColumnId[source]

Returns a ColumnId combining this table and the specified column.

Parameters:: column – name of the column

database_schema_part(grammar: SqlGrammar) → str[source]

Returns an SQL identifier for this table’s database/schema (without the table part) using the specified SQL grammar, quoting it if need be.

Parameters:: grammar – cardinal_pythonlib.sql.sql_grammar.SqlGrammar

property db: str: Returns the database part.

identifier(grammar: SqlGrammar) → str[source]

Returns an SQL identifier for this table using the specified SQL grammar, quoting it if need be.

Parameters:: grammar – cardinal_pythonlib.sql.sql_grammar.SqlGrammar

property schema: str: Returns the schema part.

property schema_id: SchemaId: Returns a SchemaId for the schema of our table.

property table: str: Returns the table part.

table_part(grammar: SqlGrammar) → str[source]

Returns an SQL identifier for this table’s table name (only) using the specified SQL grammar, quoting it if need be.

Parameters:: grammar – cardinal_pythonlib.sql.sql_grammar.SqlGrammar

class crate_anon.common.sql.TransactionSizeLimiter(session: Session, max_rows_before_commit: int | None = None, max_bytes_before_commit: int | None = None)[source]

Class to allow us to limit the size of database transactions.

__init__(session: Session, max_rows_before_commit: int | None = None, max_bytes_before_commit: int | None = None) → None[source]

Parameters:

session – SQLAlchemy database Session
max_rows_before_commit – how many rows should we insert before triggering a COMMIT? None for no limit.
max_bytes_before_commit – how many bytes should we insert before triggering a COMMIT? None for no limit.

commit() → None[source]

Performs a database COMMIT and resets our counters.

(Measures some timing information, too.)

notify(n_rows: int, n_bytes: int, force_commit: bool = False) → None[source]

Use this function to notify the limiter of data that you’ve inserted into the database. If the total number of rows or bytes exceeds a limit that we’ve set, this will trigger a COMMIT.

Parameters:

n_rows – number of rows inserted
n_bytes – number of bytes inserted
force_commit – force a COMMIT?

class crate_anon.common.sql.ViewMaker(viewname: str, engine: Engine, basetable: str, existing_to_lower: bool = False, rename: Dict[str, str] | None = None, userobj: Any | None = None, enforce_same_n_rows_as_base: bool = True, insert_basetable_columns: bool = True)[source]

View-building assistance class.

__init__(viewname: str, engine: Engine, basetable: str, existing_to_lower: bool = False, rename: Dict[str, str] | None = None, userobj: Any | None = None, enforce_same_n_rows_as_base: bool = True, insert_basetable_columns: bool = True) → None[source]

Parameters:

viewname – name of the view
engine – SQLAlchemy database Engine
basetable – name of the single base table that this view draws from
existing_to_lower – translate column names to lower case in the view?
rename – optional dictionary mapping from_name: to_name to translate column names in the view
userobj – optional object (e.g. argparse.Namespace, dictionary…), not used by this class, and purely to store information for others’ benefit
enforce_same_n_rows_as_base – ensure that the view produces the same number of rows as its base table?
insert_basetable_columns – start drafting the view by including all columns from the base table?

add_from(element: str) → None[source]: Add an element to the FROM clause of the draft view’s SQL statement.

add_select(element: str) → None[source]: Add an element to the SELECT clause of the the draft view’s SQL (meaning: add e.g. a result column).

add_where(element: str) → None[source]: Add an element to the WHERE clause of the draft view’s SQL statement.

create_view(engine: Engine) → None[source]

Creates the view.

Whether we act or just print is conditional on previous calls to set_print_not_execute().

If enforce_same_n_rows_as_base is set, check the number of rows returned matches the base table.

Parameters:: engine – SQLAlchemy database Engine

drop_view(engine: Engine) → None[source]

Drops the view.

Whether we act or just print is conditional on previous calls to set_print_not_execute().

Parameters:: engine – SQLAlchemy database Engine

get_index_request_dict() → Dict[str, List[str]][source]: Returns all our recorded index requests, as a dictionary mapping each table name to a list of column names to be indexed. See request_index().

get_lookup_tables() → List[str][source]: Returns all lookup tables that we have recorded. See record_lookup_table().

get_sql() → str[source]: Returns the view-creation SQL.

record_lookup_table(table: str) → None[source]

Keep a record of a lookup table. The framework may wish to suppress these from a data dictionary later (e.g. create a view, suppress the messier raw data). See get_lookup_tables().

Parameters:: table – table name

record_lookup_table_keyfield(table: str, keyfield: str | Iterable[str]) → None[source]

Makes a note that a table is a lookup table, and its key field(s) should be indexed. See get_lookup_tables(), get_index_request_dict().

Parameters:

table – table name
keyfield – field name, or iterable (e.g. list) of them

record_lookup_table_keyfields(table_keyfield_tuples: Iterable[Tuple[str, str | Iterable[str]]]) → None[source]

Make a note of a whole set of lookup table / key field groups. See record_lookup_table_keyfield().

Parameters:: table_keyfield_tuples – iterable (e.g. list) of tuples of the format tablename, keyfield. Each will be passed to record_lookup_table_keyfield().

request_index(table: str, column: str) → None[source]

Note a request that a specific column be indexed. The framework can use the ViewMaker to keep a note of these requests, and then add index hints to a data dictionary if it wishes. See get_index_request_dict().

Parameters:

table – table name
column – column name

class crate_anon.common.sql.WhereCondition(column_id: ColumnId | None = None, op: str = '', datatype: str = '', value_or_values: Any | None = None, raw_sql: str = '', from_table_for_raw_sql: TableId | None = None)[source]

Ancillary class for building SQL WHERE expressions from our web forms.

The essence of it is WHERE column op value_or_values.

__init__(column_id: ColumnId | None = None, op: str = '', datatype: str = '', value_or_values: Any | None = None, raw_sql: str = '', from_table_for_raw_sql: TableId | None = None) → None[source]

Parameters:

column_id – ColumnId for the column
op – operation (e.g. =, <, <=, etc.)
datatype – data type string that must match values in our querybuilder.js; see source code. We use this to know how to build SQL literal values. (Not terribly elegant, but it works; SQL injection isn’t a particular concern because we let our users run any SQL they want and ensure the connection is made read-only.)
value_or_values – None, single value, or list of values. Which is appropriate depends on the operation. For example, IS NULL takes no value; = takes one; IN takes many.
raw_sql – override any thinking we might wish to do, and just return this raw SQL
from_table_for_raw_sql – if we are using raw SQL, provide a TableId for the relevant table here

property column_id: ColumnId: Returns the ColumnId provided at creation.

sql(grammar: SqlGrammar) → str[source]

Returns the WHERE clause (without WHERE itself!) for our condition, in the specified SQL grammar. Some examples might be:

somecol = 3
othercol IN (6, 7, 8)
thirdcol IS NOT NULL
textcol LIKE '%paracetamol%'
MATCH (fulltextcol AGAINST 'paracetamol') (MySQL)
CONTAINS(fulltextcol, 'paracetamol') (SQL Server)

Parameters:: grammar – cardinal_pythonlib.sql.sql_grammar.SqlGrammar

property table_id: TableId

Returns a TableId:

for raw SQL, our from_table_for_raw_sql attribute
otherwise, the table ID extracted from our column_id attribute

table_str(grammar: SqlGrammar) → str[source]

Returns the table identifier in the specified SQL grammar.

Parameters:: grammar – cardinal_pythonlib.sql.sql_grammar.SqlGrammar

crate_anon.common.sql.add_columns(engine: Engine, table: Table, columns: List[Column]) → None[source]

Adds columns to a table.

Whether we act or just print is conditional on previous calls to set_print_not_execute().

Parameters:

engine – SQLAlchemy database Engine
table – SQLAlchemy Table object
columns – SQLAlchemy Column objects to add to the table

Behaviour of different database systems:

ANSI SQL: add one column at a time: ALTER TABLE ADD [COLUMN] coldef
- i.e. “COLUMN” optional, one at a time, no parentheses
- https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
MySQL: ALTER TABLE ADD [COLUMN] (a INT, b VARCHAR(32));
- i.e. “COLUMN” optional, parentheses required for >1, multiple OK
- https://dev.mysql.com/doc/refman/5.7/en/alter-table.html
MS SQL Server: ALTER TABLE ADD COLUMN a INT, B VARCHAR(32);
- i.e. no “COLUMN”, no parentheses, multiple OK
- https://msdn.microsoft.com/en-us/library/ms190238.aspx
- https://msdn.microsoft.com/en-us/library/ms190273.aspx
- https://stackoverflow.com/questions/2523676

This function therefore operates one at a time.

SQLAlchemy doesn’t provide a shortcut for this.

crate_anon.common.sql.add_indexes(engine: Engine, table: Table, index_info_list: Iterable[IndexCreationInfo]) → None[source]

Adds indexes to a table.

Whether we act or just print is conditional on previous calls to set_print_not_execute().

Parameters:

engine – SQLAlchemy database Engine
table – SQLAlchemy Table object
index_info_list – Index(es) to create: list of IndexCreationInfo objects.

crate_anon.common.sql.assert_view_has_same_num_rows(engine: Engine, basetable: str, viewname: str) → None[source]

Ensures that a view gives the same number of rows as a table. (For use in situations where this should hold; views don’t have to do this in general!)

Parameters:

engine – SQLAlchemy database Engine
basetable – name of the table that this view should have a 1:1 relationship to
viewname – view name

Raises:

AssertionError –

crate_anon.common.sql.coltype_length_if_text(column_type: str, dialect: str) → int | None[source]

Find the length of an sql text column type.

Parameters:

column_type – SQL column type as a string, e.g. "VARCHAR(50)"
dialect – the sql dialect the column type is from

Returns:

length of the column or None if it’s not a text column.

crate_anon.common.sql.columns_to_table_column_hierarchy(columns: List[ColumnId], sort: bool = True) → List[Tuple[TableId, List[ColumnId]]][source]

Converts a list of column IDs :param columns: list of ColumnId objects :param sort: sort by table, and column within table?

Returns:: a list of tuples, each table, columns, where table is a TableId and columns is a list of ColumnId

crate_anon.common.sql.create_view(engine: Engine, viewname: str, select_sql: str) → None[source]

Creates a view.

Whether we act or just print is conditional on previous calls to set_print_not_execute().

Parameters:

engine – SQLAlchemy database Engine
viewname – view name
select_sql – SQL SELECT statement for this view

crate_anon.common.sql.drop_columns(engine: Engine, table: Table, column_names: Iterable[str]) → None[source]

Drops columns from a table.

Whether we act or just print is conditional on previous calls to set_print_not_execute().

Parameters:

engine – SQLAlchemy database Engine
table – SQLAlchemy Table object
column_names – names of columns to drop

Columns are dropped one by one.

crate_anon.common.sql.drop_indexes(engine: Engine, table: Table, index_names: Iterable[str]) → None[source]

Drops indexes from a table.

Whether we act or just print is conditional on previous calls to set_print_not_execute().

Parameters:

engine – SQLAlchemy database Engine
table – SQLAlchemy Table object
index_names – names of indexes to drop

crate_anon.common.sql.drop_view(engine: Engine, viewname: str, quiet: bool = False) → None[source]

Drops a view.

Whether we act or just print is conditional on previous calls to set_print_not_execute().

Parameters:

engine – SQLAlchemy database Engine
viewname – view name
quiet – don’t announce this to the Python log

crate_anon.common.sql.dumb_make_identifier(database: str | None = None, schema: str | None = None, table: str | None = None, column: str | None = None) → str[source]

Makes an SQL-style identifier by joining all the parts with ., without bothering to quote them.

Parameters:

database – database name
schema – schema name
table – table name
column – column name

Returns:

a string as above in the order “database, schema, table, column”, but omitting any missing parts

crate_anon.common.sql.ensure_columns_present(engine: Engine, tablename: str, column_names: Iterable[str]) → None[source]

Ensure all these columns are present in a table, or raise an exception.

Operates in case-insensitive fashion.

Parameters:

engine – SQLAlchemy database Engine
tablename – name of the table
column_names – names of required columns

Raises:

ValueError –

crate_anon.common.sql.escape_percent_for_python_dbapi(sql: str) → str[source]: Escapes % by converting it to %%. Use this for SQL within Python where % characters are used for argument placeholders.

crate_anon.common.sql.escape_percent_in_literal(sql: str) → str[source]

Escapes % by converting it to \%. Use this for LIKE clauses.

https://dev.mysql.com/doc/refman/5.7/en/string-literals.html

crate_anon.common.sql.escape_quote_in_literal(s: str) → str[source]: Escape '. We could use '' or \'. Let’s use \. for consistency with percent escaping.

crate_anon.common.sql.escape_sql_string_literal(s: str) → str[source]: Escapes SQL string literal fragments against quotes and parameter substitution.

crate_anon.common.sql.escape_sql_string_or_int_literal(s: str | int) → str[source]: Converts an integer or a string into an SQL literal (with single quotes and escaping in the case of a string).

crate_anon.common.sql.execute(engine: Engine, sql: str) → None[source]

Executes SQL.

Whether we act or just print is conditional on previous calls to set_print_not_execute().

Parameters:

engine – SQLAlchemy database Engine
sql – raw SQL to execute (or print)

crate_anon.common.sql.format_sql_for_print(sql: str) → str[source]

Very simple SQL formatting.

Remove blank lines and trailing spaces from an SQL statement. Converts tabs to spaces.

crate_anon.common.sql.get_column_fk_description(c: Column) → str[source]

Standardized description of a column’s foreign keys.

Parameters:: c – SQLAlchemy Column

crate_anon.common.sql.get_column_names(engine: Engine, tablename: str, to_lower: bool = False, sort: bool = False) → List[str][source]

Reads columns names afresh from the database, for a specific table (in case metadata is out of date).

Parameters:

engine – SQLAlchemy database Engine
tablename – name of the table
to_lower – convert view names to lower case?
sort – sort view names?

Returns:

list of column names

crate_anon.common.sql.get_first_from_table(parsed: ParseResults, match_db: str = '', match_schema: str = '', match_table: str = '') → TableId[source]

Given a set of parsed results from a SELECT statement, returns the db, schema, table tuple representing the first table in the FROM clause.

Optionally, the match may be constrained with the match* parameters.

Parameters:

parsed – a pyparsing.ParseResults result
match_db – optional database name to constrain the result to
match_schema – optional schema name to constrain the result to
match_table – optional table name to constrain the result to

Returns:

a TableId, which will be empty in case of failure

crate_anon.common.sql.get_index_names(engine: Engine, tablename: str, to_lower: bool = False, sort: bool = False) → List[str][source]

Reads index names from the database, for a specific table.

Parameters:

engine – SQLAlchemy database Engine
tablename – name of the table
to_lower – convert index names to lower case?
sort – sort index names?

Returns:

list of index names

crate_anon.common.sql.get_table_names(engine: Engine, to_lower: bool = False, sort: bool = False) → List[str][source]

Returns all table names for the database.

Parameters:

engine – SQLAlchemy database Engine
to_lower – convert table names to lower case?
sort – sort table names?

Returns:

list of table names

crate_anon.common.sql.get_view_names(engine: Engine, to_lower: bool = False, sort: bool = False) → List[str][source]

Returns all view names for the database.

Parameters:

engine – SQLAlchemy database Engine
to_lower – convert view names to lower case?
sort – sort view names?

Returns:

list of view names

crate_anon.common.sql.is_sql_column_type_textual(column_type: str, min_length: int = 1) → bool[source]

Does an SQL column type look textual?

Parameters:

column_type – SQL column type as a string, e.g. "VARCHAR(50)"
min_length – what’s the minimum string length we’ll say “yes” to?

Returns:

is it a textual column (of the minimum length or more)?

Note:

For SQL Server’s NVARCHAR(MAX), crate_anon.crateweb.research.research_db_info._schema_query_microsoft() returns “NVARCHAR(-1)”

crate_anon.common.sql.make_identifier(grammar: SqlGrammar, database: str | None = None, schema: str | None = None, table: str | None = None, column: str | None = None) → str[source]

Makes an SQL identifier by quoting its elements according to the style of the specific SQL grammar, and then joining them with ..

Parameters:

grammar – cardinal_pythonlib.sql.sql_grammar.SqlGrammar
database – database name
schema – schema name
table – table name
column – column name

Returns:

a string as above in the order “database, schema, table, column”, but omitting any missing parts

crate_anon.common.sql.make_string_literal(s: str) → str[source]: Converts a Python string into an SQL single-quoted (and escaped) string literal.

crate_anon.common.sql.matches_fielddef(table: str, field: str, fielddef: str | List[str]) → bool[source]

Does the table/field name match the wildcard-based field definition?

Parameters:

table – table name
field – fieldname
fielddef – fnmatch-style pattern (e.g. "system_table.*" or "*.nhs_number"), or list of them

crate_anon.common.sql.matches_tabledef(table: str, tabledef: str | List[str]) → bool[source]

Does the table name match the wildcard-based table definition?

Parameters:

table – table name
tabledef – fnmatch-style pattern (e.g. "patient_address_table_*"), or list of them

crate_anon.common.sql.parser_add_from_tables(parsed: ParseResults, join_info_list: List[JoinInfo], grammar: SqlGrammar) → ParseResults[source]

Takes a parsed SQL statement of the form

SELECT a, b, c
FROM sometable
WHERE conditions;

and adds one or more join columns, e.g. JoinInfo("othertable", "INNER JOIN", "ON table.key = othertable.key"), to give

SELECT a, b, c
FROM sometable
INNER JOIN othertable ON table.key = othertable.key
WHERE conditions;

Presupposes that there at least one table already in the FROM clause.

Parameters:

parsed – a pyparsing.ParseResults result
join_info_list – list of JoinInfo objects
grammar – cardinal_pythonlib.sql.sql_grammar.SqlGrammar

Returns:

a pyparsing.ParseResults result

crate_anon.common.sql.parser_add_result_column(parsed: ParseResults, column: str, grammar: SqlGrammar) → ParseResults[source]

Takes a parsed SQL statement of the form

SELECT a, b, c
FROM sometable
WHERE conditions;

and adds a result column, e.g. d, to give

SELECT a, b, c, d
FROM sometable
WHERE conditions;

Presupposes that there is at least one column already in the SELECT statement.

Parameters:

parsed – a pyparsing.ParseResults result
column – column name
grammar – cardinal_pythonlib.sql.sql_grammar.SqlGrammar

Returns:

a pyparsing.ParseResults result

crate_anon.common.sql.set_distinct(sql: str, grammar: SqlGrammar, action: str = 'set', formatted: bool = True, debug: bool = False, debug_verbose: bool = False) → str[source]

Takes an SQL statement (as a string) and modifies its DISTINCT status.

Parameters:

sql – SQL statment as text
grammar – cardinal_pythonlib.sql.sql_grammar.SqlGrammar
action – one of "set", "clear", "toggle"; see set_distinct_within_parsed()
formatted – pretty-format the result?
debug – show debugging information to the Python log
debug_verbose – be verbose when debugging

Returns:

the modified SQL statment, as a string

crate_anon.common.sql.set_distinct_within_parsed(p: ParseResults, action: str = 'set') → None[source]

Modifies (in place) the DISTINCT status of a parsed SQL statement.

Parameters:

p – a pyparsing.ParseResults result
action – "set" to turn DISTINCT on; "clear" to turn it off; or "toggle" to toggle it.

crate_anon.common.sql.set_print_not_execute(print_not_execute: bool) → None[source]

Sets a nasty global flag: should we print DDL, rather than executing it, when we issue DDL commands from this module?

Parameters:: print_not_execute – print (not execute)?

crate_anon.common.sql.split_db_schema_table(db_schema_table: str) → TableId[source]

Converts a simple SQL-style identifier string into a TableId.

Parameters:: db_schema_table – one of: database.schema.table, schema.table, table
Returns:: a TableId
Raises:: ValueError –

crate_anon.common.sql.split_db_schema_table_column(db_schema_table_col: str) → ColumnId[source]

Converts a simple SQL-style identifier string into a ColumnId.

Parameters:: db_schema_table_col – one of: database.schema.table.column, schema.table.column, table.column, column
Returns:: a ColumnId
Raises:: ValueError –

crate_anon.common.sql.sql_fragment_cast_to_int(expr: str, big: bool = True, dialect: Dialect | None = None, viewmaker: ViewMaker | None = None) → str[source]

Takes an SQL expression and coerces it to an integer. For Microsoft SQL Server.

Parameters:

expr – starting SQL expression
big – use BIGINT, not INTEGER?
dialect – optional sqlalchemy.engine.interfaces.Dialect. If None and we have a viewmaker, use the viewmaker’s dialect. Otherwise, assume SQL Server.
viewmaker – optional ViewMaker

Returns:

modified SQL expression

Notes

Conversion to INT:

https://stackoverflow.com/questions/2000045
https://stackoverflow.com/questions/14719760 (this one in particular!)
https://stackoverflow.com/questions/14692131
- see LIKE example.
- see ISNUMERIC(); https://msdn.microsoft.com/en-us/library/ms186272.aspx; but that includes non-integer numerics
https://msdn.microsoft.com/en-us/library/ms174214(v=sql.120).aspx; relates to the SQL Server Management Studio “Find and Replace” dialogue box, not to SQL itself!
https://stackoverflow.com/questions/29206404/mssql-regular-expression

Note that the regex-like expression supported by LIKE is extremely limited.

https://msdn.microsoft.com/en-us/library/ms179859.aspx

The only things supported are:

%   any characters
_   any single character
[]  single character in range or set, e.g. [a-f], [abcdef]
[^] single character NOT in range or set, e.g. [^a-f], [abcdef]

SQL Server does not support a REGEXP command directly.

So the best bet is to have the LIKE clause check for a non-integer:

CASE
    WHEN something LIKE '%[^0-9]%' THEN NULL
    ELSE CAST(something AS BIGINT)
END

… which doesn’t deal with spaces properly, but there you go. Could also strip whitespace left/right:

CASE
    WHEN LTRIM(RTRIM(something)) LIKE '%[^0-9]%' THEN NULL
    ELSE CAST(something AS BIGINT)
END

That only works for positive integers.

LTRIM/RTRIM are not ANSI SQL. Nor are unusual LIKE clauses; see https://stackoverflow.com/questions/712580/list-of-special-characters-for-sql-like-clause

The other, for SQL Server 2012 or higher, is TRY_CAST:

TRY_CAST(something AS BIGINT)

… which returns NULL upon failure; see https://msdn.microsoft.com/en-us/library/hh974669.aspx

Therefore, our method is as follows:

If the database supports TRY_CAST, use that.
Otherwise if we’re using SQL Server, use a CASE/CAST construct.
Otherwise, raise ValueError as we don’t know what to do.

crate_anon.common.sql.toggle_distinct(sql: str, grammar: SqlGrammar, formatted: bool = True, debug: bool = False, debug_verbose: bool = False) → str[source]

Takes an SQL statement and toggles its DISTINCT status.

Parameters:

sql – SQL statment as text
grammar – cardinal_pythonlib.sql.sql_grammar.SqlGrammar
formatted – pretty-format the result?
debug – show debugging information to the Python log
debug_verbose – be verbose when debugging

Returns:

the modified SQL statment, as a string

crate_anon.common.sql.translate_sql_qmark_to_percent(sql: str) → str[source]

This function translates SQL using ? placeholders to SQL using %s placeholders, without breaking literal '?' or '%', e.g. inside string literals.

Notes

MySQL likes ? as a placeholder.
- https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html
Python DBAPI allows several: %s, ?, :1, :name, %(name)s.
- https://www.python.org/dev/peps/pep-0249/#paramstyle
Django uses %s.
- https://docs.djangoproject.com/en/1.8/topics/db/sql/
Microsoft like ?, @paramname, and :paramname.
- https://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.110).aspx
We need to parse SQL with argument placeholders.
- See cardinal_pythonlib.sql.sql_grammar.SqlGrammar classes, particularly: bind_parameter

I prefer ?, because % is used in LIKE clauses, and the databases we’re using like it.

So:

We use %s when using cursor.execute() directly, via Django.
We use ? when talking to users, and cardinal_pythonlib.sql.sql_grammar.SqlGrammar objects, so that the visual appearance matches what they expect from their database.