12.3.148. crate_anon.crateweb.research.sql_writer

crate_anon/crateweb/research/sql_writer.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/>.

Automatically create/manipulate SQL statements based on our extra knowledge of the fields that can be used to link across tables/databases.

class crate_anon.crateweb.research.sql_writer.SelectElement(column_id: ColumnId | None = None, raw_select: str = '', from_table_for_raw_select: TableId | None = None, alias: str = '')[source]

Class to represent a result column in an SQL SELECT statement.

__init__(column_id: ColumnId | None = None, raw_select: str = '', from_table_for_raw_select: TableId | None = None, alias: str = '')[source]

Parameters:

column_id – a crate_anon.common.sql.ColumnId object; using this will automatically add the column’s table to the FROM clause
raw_select – as an alternative to column_id, raw SQL for the SELECT clause
from_table_for_raw_select – if raw_select is used, a crate_anon.common.sql.TableId that should be added to the FROM clause
alias – alias to be used, i.e. for SELECT something AS alias

from_table() → TableId | None[source]

Returns details of the table to be added to the FROM clause of the SELECT statement.

Returns:: a crate_anon.common.sql.TableId, or None (if raw_select is used and from_table_for_raw_select was not specified)

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

Returns a string form of from_table(), i.e. an SQL identifier for the FROM clause.

Parameters:: grammar – cardinal_pythonlib.sql.sql_grammar.SqlGrammar representing the SQL dialect/grammar in use
Returns:: SQL like from_table
Return type:: str

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

Return the raw SQL for this SELECT result column.

Parameters:: grammar – cardinal_pythonlib.sql.sql_grammar.SqlGrammar representing the SQL dialect/grammar in use
Returns:: SQL like colname or expression or colname AS alias
Return type:: str

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

Returns a full SELECT... FROM... statement.

Parameters:: grammar – cardinal_pythonlib.sql.sql_grammar.SqlGrammar representing the SQL dialect/grammar in use
Returns:: SQL like SELECT colname AS alias FROM from_table
Return type:: str

crate_anon.crateweb.research.sql_writer.add_to_select(sql: str, grammar: SqlGrammar, select_elements: List[SelectElement] | None = None, where_conditions: List[WhereCondition] | None = None, distinct: bool | None = None, where_type: str = 'AND', bracket_where: bool = False, magic_join: bool = True, join_type: str = 'NATURAL JOIN', join_condition: str = '', formatted: bool = True, debug: bool = False, debug_verbose: bool = False) → str[source]

This function encapsulates our query builder’s common operations.

One premise is that SQL parsing is relatively slow, so we should do this only once. We parse; add bits to the parsed structure as required; then re-convert to text.

If you specify table/column, elements will be added to SELECT and FROM unless they already exist.

If you specify where_expression, elements will be added to WHERE. In this situation, you should also specify where_table; if the where_table isn’t yet in the FROM clause, this will be added as well.

Parsing is SLOW, so we should do as much as possible in a single call to this function.

Parameters:

sql – existing SQL statement
grammar – cardinal_pythonlib.sql.sql_grammar.SqlGrammar representing the SQL dialect/grammar in use
select_elements – optional list of SelectElement objects representing things to add to the SELECT clause of the SELECT statement (i.e. results columns)
where_conditions – optional list of crate_anon.common.sql.WhereCondition representing conditions to add to the WHERE clause of the SELECT statement
distinct – if True, make the SELECT statement a SELECT DISTINCT; if False, remove any DISTINCT; if None, leave the DISTINCT status as it is.
where_type – logical operator with which to join multiple parts of the WHERE expression, typically AND (but maybe OR, etc.)
bracket_where – put brackets () around each new part of the WHERE expression?
magic_join – perform a “magic join”, i.e. join the new table in based on our knowledge of the research database structure?
join_type – if not magic_join, this is an SQL string specifying the join type, e.g. "INNER JOIN"
join_condition – if not magic_join, this is an SQL string specifying the join condition, e.g. "ON x = y"
formatted – reformat the SQL to look pretty?
debug – show debugging information
debug_verbose – show verbose debugging information

Returns:

SQL statement

Return type:

str

Raises:

DatabaseStructureNotUnderstood` if the relevant schema informatio –
cannot be looked up. –

crate_anon.crateweb.research.sql_writer.get_join_info(grammar: SqlGrammar, parsed: ParseResults, jointable: TableId, magic_join: bool = False, nonmagic_join_type: str = 'INNER JOIN', nonmagic_join_condition: str = '') → List[JoinInfo][source]

Works out how to join a new table into an existing SQL SELECT query.

Parameters:

grammar – cardinal_pythonlib.sql.sql_grammar.SqlGrammar representing the SQL dialect/grammar in use
parsed – existing pyparsing.ParseResults representing the SELECT statement so far
jointable – crate_anon.common.sql.TableId representing the table to be joined in
magic_join – perform a “magic join”, i.e. join the new table in based on our knowledge of the research database structure?
nonmagic_join_type – if not magic_join, this is an SQL string specifying the join type, e.g. "INNER JOIN"
nonmagic_join_condition – if not magic_join, this is an SQL string specifying the join condition, e.g. "ON x = y"

Returns:

a list of crate_anon.common.sql.JoinInfo objects, e.g. [JoinInfo("tablename", "INNER JOIN", "WHERE somecondition")].

Raises:

DatabaseStructureNotUnderstood` if the relevant schema informatio –
cannot be looked up. –

Notes:

INNER JOIN etc. is part of ANSI SQL

crate_anon.crateweb.research.sql_writer.reparse_select(p: ParseResults, grammar: SqlGrammar) → ParseResults[source]

Internal function for when we get desperate trying to hack around the results of pyparsing’s efforts.

takes a pyparsing.ParseResults
converts it to an SQL string
parses the string as a SELECT statement
returns the resulting pyparsing.ParseResults