14.3.140. crate_anon.crateweb.research.models

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


class crate_anon.crateweb.research.models.ArchiveAttachmentAudit(*args, **kwargs)[source]

Audit log for access to an archive attachment.

exception DoesNotExist
exception MultipleObjectsReturned
class crate_anon.crateweb.research.models.ArchiveTemplateAudit(*args, **kwargs)[source]

Audit log for access to an archive template.

exception DoesNotExist
exception MultipleObjectsReturned
class crate_anon.crateweb.research.models.Highlight(*args, **kwargs)[source]

Represents the highlighting of a query.

exception DoesNotExist
exception MultipleObjectsReturned
activate() None[source]

Mark this highlight as active.

static as_ordered_dict(highlight_list: Iterable[crate_anon.crateweb.research.models.Highlight]) Dict[int, List[crate_anon.crateweb.research.models.Highlight]][source]

Converts a iterable of Highlight objects into a dictionary that collects them by highlight number.

Parameters

highlight_list – list of Highlight objects

Returns

an OrderedDict whose keys are highlight colour numbers (in ascending order), and whose values are lists of all the Highlight objects using that highlight colour number

deactivate() None[source]

Mark this highlight as inactive.

static get_active_highlights(request: django.http.request.HttpRequest) django.db.models.query.QuerySet[source]

Return all active highlights for the current user.

Parameters

request – the django.http.request.HttpRequest

Returns

a django.db.models.QuerySet of the Highlight objects

get_safe_colour() int[source]

Returns our colour attribute, coerced to the range [0, N_CSS_HIGHLIGHT_CLASSES - 1] (inclusive).

class crate_anon.crateweb.research.models.PatientExplorer(*args, **kwargs)[source]

Class to explore the research database on a per-patient basis.

exception DoesNotExist
exception MultipleObjectsReturned
__init__(*args, **kwargs) None[source]
activate() None[source]

Activates this PatientExplorer (and deactivates any others).

all_queries(mrids: Optional[List[Any]] = None) List[crate_anon.crateweb.research.models.TableQueryArgs][source]

Returns all queries from our patient_multiquery. See PatientMultiQuery.all_queries()

Parameters

mrids – list of MRIDs; if this is None or empty, use the patients fetched (live) by our patient_multiquery’s PatientMultiQuery.patient_id_query().

Returns

a list of TableQueryArgs objects (q.v.)

Return type

list

audit(count_only: bool = False, n_records: int = 0, failed: bool = False, fail_msg: str = '') None[source]

Audit the execution of this query:

  • insert an audit entry referring to this query

  • mark the query as having been audited (so it’s not deleted)

Parameters
  • count_only – did we know (in advance) that this was a COUNT()-only query?

  • n_records – how many records were returned?

  • failed – did the query fail?

  • fail_msg – if the query failed, the associated failure message

property data_finder_excel: bytes

Returns an XSLX (Excel) file containing summary (count) information for each table.

See PatientMultiQuery.gen_data_finder_queries().

delete_if_permitted() None[source]

Delete the query.

  • If a query has been executed and therefore audited, it isn’t properly deleted; it’s just marked as deleted.

  • If a query has never been executed, we can delete it entirely.

static get_active_pe_id_or_none(request: django.http.request.HttpRequest) Optional[int][source]
Parameters

request – the django.http.request.HttpRequest

Returns

The integer PK of the active PatientExplorer for the user, or None.

static get_active_pe_or_none(request: django.http.request.HttpRequest) Optional[crate_anon.crateweb.research.models.PatientExplorer][source]
Parameters

request – the django.http.request.HttpRequest

Returns

The active PatientExplorer for the user, or None.

static get_executed_cursor(sql: str, args: Optional[List[Any]] = None) django.db.backends.utils.CursorWrapper[source]

Executes a query (via the research database) and returns its cursor.

Parameters
  • sql – SQL text

  • args – arguments to SQL query

Returns

a django.db.backends.utils.CursorWrapper, which is a context manager that behaves as the executed cursor and also closes it on completion

get_patient_id_query(with_order_by: bool = True) str[source]

Returns SQL from our patient_multiquery’s PatientMultiQuery.patient_id_query() (q.v.).

Parameters

with_order_by – see PatientMultiQuery.patient_id_query()

get_patient_mrids() List[int][source]

Returns all MRIDs from our patient_multiquery’s PatientMultiQuery.patient_id_query().

get_xlsx_binary() bytes[source]

Returns an XLSX (Excel) file containing spreadsheets, one for each table in our patient_multiquery.

Other notes:

get_zipped_tsv_binary() bytes[source]

Returns a ZIP file containing TSVs, one for each table in our patient_multiquery.

property has_output_columns: bool

Does our our patient_multiquery have output columns?

See PatientMultiQuery.has_output_columns().

property has_patient_id_query: bool

Does our our patient_multiquery have a patient ID query?

See PatientMultiQuery.has_patient_id_query().

mark_audited() None[source]

Mark the query as having been executed and audited. (This prevents it from being wholly deleted.)

mark_deleted() None[source]

Mark the query as deleted.

This will stop it being shown. It will not delete it from the database.

We use this deletion method for queries that have been executed, so need an audit trail.

save(*args, **kwargs) None[source]

Custom save method. Ensures that only one PatientExplorer has active == True for a given user. Also sets the hash.

property summary_html: str

Return HTML summarizing this object.

class crate_anon.crateweb.research.models.PatientExplorerAudit(*args, **kwargs)[source]

Audit log for a PatientExplorer.

exception DoesNotExist
exception MultipleObjectsReturned
class crate_anon.crateweb.research.models.PatientMultiQuery(output_columns: Optional[List[crate_anon.common.sql.ColumnId]] = None, patient_conditions: Optional[List[crate_anon.common.sql.WhereCondition]] = None, manual_patient_id_query: str = '')[source]

Represents a set of queries across many tables relating to one or several patients (but the same patients across all the tables).

Used for the Patient Explorer.

Development notes:

  • Patient ID query

    • Single database is easy; we can use RID or TRID, and therefore TRID for performance.

      Note that UNION gives only DISTINCT results by default (UNION ALL gives everything); see https://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all.

      -- Clear, but extensibility of boolean logic less clear:
      SELECT trid
          FROM diagnosis_table
          WHERE diagnosis LIKE 'F20%'
      INTERSECT
      SELECT trid
          FROM progress_note_table
          WHERE note LIKE '%schizophreni%' OR note LIKE '%depression%'
      ORDER BY trid
      -- ... logic across tables requires careful arrangement of UNION vs. INTERSECT
      -- ... logic for multiple fields within one table can be done with AND/OR
      
      -- Slower (?), but simpler to manipulate logic?
      SELECT DISTINCT something.trid
      FROM diagnosis_table INNER JOIN progress_note_table
      ON diagnosis_table.trid = progress_note_table.trid
      WHERE
          diagnosis_table.diagnosis LIKE 'F20%'
          AND (progress_note_table.note LIKE '%schizophreni%'
               OR progress_note_table.notenote LIKE '%depression%')
      ORDER BY something.trid
      -- ... boolean logic can all be encapsulated in a single WHERE clause
      -- ... can also share existing join code
      -- ... ?reasonable speed since the TRID fields will be indexed
      -- ... preferable.
      
  • Which ID for the patient ID query?

    • the TRID (for speed, inc. sorting) of the first database

    • can use the TRID from the first “where clause” table (don’t have to join to a master patient table)

    • join everything across databases as before

  • Results queries

    -- Something like:
    
    SELECT rid, date_of_note, note
    FROM progress_note_table
    WHERE trid IN ( ... patient_id_query ... )
    ORDER BY trid
    
    SELECT rid, date_of_diagnosis, diagnosis, diagnosis_description
    FROM diagnosis_table
    WHERE trid IN ( ... patient_id_query ... )
    ORDER BY trid
    

    This means we will repeat the patient_id_query, which may be inefficient. Options:

  • Display

    • One patient per page, with multiple results tables.

  • Boolean logic on patient selection

    • … within

__init__(output_columns: Optional[List[crate_anon.common.sql.ColumnId]] = None, patient_conditions: Optional[List[crate_anon.common.sql.WhereCondition]] = None, manual_patient_id_query: str = '')[source]
Parameters
add_output_column(column_id: crate_anon.common.sql.ColumnId) None[source]

Adds a database column to the output.

add_patient_condition(where: crate_anon.common.sql.WhereCondition) None[source]

Adds a patient WHERE condition.

Parameters

where – a crate_anon.common.sql.WhereCondition

property all_full_queries: List[crate_anon.crateweb.research.models.TableQueryArgs]

Returns all final queries. This is a list of multiple SQL queries, each retrieving information from one table, and all retrieving information for the same patient(s).

The patients we use are defined by our patient_id_query().

Returns

a list of TableQueryArgs objects (q.v.)

Return type

list

all_queries(mrids: Optional[List[Any]] = None) List[crate_anon.crateweb.research.models.TableQueryArgs][source]

Returns all final queries. This is a list of multiple SQL queries, each retrieving information from one table, and all retrieving information for the same patient(s).

The patients we use are defined either by the MRID list given, or if that is empty or blank, our patient_id_query().

Parameters

mrids – list of MRIDs; if this is None or empty, use the patients fetched (live) by our patient_id_query().

Returns

a list of TableQueryArgs objects (q.v.)

Return type

list

all_queries_specific_patients(mrids: List[int]) List[crate_anon.crateweb.research.models.TableQueryArgs][source]

Returns all final queries. This is a list of multiple SQL queries, each retrieving information from one table, and all retrieving information for the same patient(s).

The patients we use are defined by the MRID list given.

Parameters

mrids – list of MRIDs

Returns

a list of TableQueryArgs objects (q.v.)

Return type

list

clear_output_columns() None[source]

Removes all output columns from the multiquery.

clear_patient_conditions() None[source]

Removes all WHERE conditions on the patient.

gen_data_finder_queries(mrids: Optional[List[Any]] = None) Generator[crate_anon.crateweb.research.models.TableQueryArgs, None, None][source]

Generates a set of queries that, when executed, return the following summary columns from each of our tables, filtered for patients by our where_patient_clause(), and grouped by master_research_id (MRID):

master_research_id,
table_name,
COUNT(*) AS n_records,
MIN(date_column) AS min_date,  -- NULL if no date column
MAX(date_column) AS max_date   -- NULL if no date column

These queries can be used to see quickly which tables have interesting information in.

Parameters

mrids – list of MRIDs; if this is None or empty, use the patients fetched (live) by our patient_id_query().

Yields

TableQueryArgs objects (q.v.)

gen_monster_queries(mrids: Optional[List[int]] = None) Generator[crate_anon.crateweb.research.models.TableQueryArgs, None, None][source]

Generates a set of queries that, when executed, return SELECT * from each of our tables, filtered for patients by our where_patient_clause(). So it’s like the basic Patient Explorer but with all columns in the output.

These queries are used in the Patient Explorer “Monster Data” view.

Parameters

mrids – list of MRIDs; if this is None or empty, use the patients fetched (live) by our patient_id_query().

Yields

TableQueryArgs objects (q.v.)

property has_output_columns: bool

Does this multiquery have any output columns?

property has_patient_id_query: bool

Does this multiquery have a patient ID query? This can either be one that the user has specified manually, or one built from WHERE conditions that appears to refer to an MRID.

property hash64: int

Return an integer (non-cryptographic) hash of the query.

make_query(table_id: crate_anon.common.sql.TableId, columns: List[crate_anon.common.sql.ColumnId], mrids: Optional[List[Any]] = None) crate_anon.crateweb.research.models.TableQueryArgs[source]

Returns an SQL query to retrieve information from a single table for certain patients. This query is similar to SELECT a, b, c FROM sometable WHERE sometable.mrid IN (1, 2, 3) or SELECT a, b, c FROM sometable WHERE sometable.mrid IN (SELECT mrid FROM masterpatienttable). This then forms one query from (potentially) many for our patient(s).

Parameters
Returns

a TableQueryArgs object (q.v.)

property manual_patient_id_query: str

Returns the manual override SQL for the patient ID query.

property ok_to_run: bool

Is this OK to run, i.e. does it have a patient ID query and some output columns?

property output_cols_html: str

Returns all our output columns in HTML format.

property output_columns: List[crate_anon.common.sql.ColumnId]

Returns the output columns, as a list of crate_anon.common.sql.ColumnId objects.

property patient_conditions: List[crate_anon.common.sql.WhereCondition]

Returns all WHERE conditions restricting the patient, as a list of crate_anon.common.sql.WhereCondition objects.

patient_id_query(with_order_by: bool = True) str[source]

Returns an SQL SELECT statement based on the list of WHERE conditions already stored, joined with AND by default. (If a manual patient ID query has been specified, return that instead.)

Parameters

with_order_by – add an ORDER BY query on the MRID; such an ordering is important for consistency across runs (but is prohibited by SQL Server in subqueries – “The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, … unless TOP, OFFSET or FOR XML is specified.”)

Returns

SQL

Return type

str

property pt_conditions_html: str

Returns all our patient WHERE conditions in HTML format.

set_override_query(query: str) None[source]

Sets the manual override SQL for the patient ID query.

Parameters

query – raw SQL

This query should return a single column of MRID values that is fetched into Python and used to restrict other queries. Here’s a fictional example to fetch the MRIDs for all patients who have the word “neutrophils” in their notes:

SELECT DISTINCT anonymous_output.patient.nhshash AS _mrid
FROM anonymous_output.patient
INNER JOIN anonymous_output.note ON anonymous_output.note.trid = anonymous_output.patient.trid
WHERE MATCH (anonymous_output.note.note) AGAINST ('neutrophils')
    AND anonymous_output.patient.nhshash IS NOT NULL
ORDER BY _mrid
summary_html(element_counter: crate_anon.crateweb.research.html_functions.HtmlElementCounter) str[source]

Returns an HTML representation of this multiquery.

Parameters

element_counter – a crate_anon.crateweb.research.html_functions.HtmlElementCounter, which will be modified

Returns

HTML

Return type

str

where_patient_clause(table_id: crate_anon.common.sql.TableId, grammar: cardinal_pythonlib.sql.sql_grammar.SqlGrammar, mrids: Optional[List[Any]] = None) Tuple[str, List[Any]][source]

Returns an SQL WHERE clauses similar to sometable.mrid IN (1, 2, 3) or sometable.mridcol IN (SELECT mrid FROM masterpatienttable). The clause is used to restrict patients by MRID.

Parameters
  • table_idcrate_anon.common.sql.TableId for the table whose MRID column we will apply the WHERE clause to

  • grammarcardinal_pythonlib.sql.sql_grammar.SqlGrammar to use

  • mrids – list of MRIDs; if this is None or empty, use the patients fetched (live) by our patient_id_query().

Returns

sql, args

Return type

tuple

class crate_anon.crateweb.research.models.PidLookup(*args, **kwargs)[source]

Lookup class for secret RID-to-PID conversion.

  • Used via one or other of the ‘secret’ database connections.

  • Intended for READ-ONLY access to that table.

  • Since we have fixed the tablenames for the anonymiser, we remove the settings.SECRET_MAP option. See crate_anon.anonymise.models.PatientInfo. Moreover, we fix the maximum length, regardless of the specifics of the config used.

  • Use as e.g. Lookup(pid=XXX).

exception DoesNotExist
exception MultipleObjectsReturned
save(*args, **kwargs) None[source]

Save the current instance. Override this in a subclass if you want to control the saving process.

The ‘force_insert’ and ‘force_update’ parameters can be used to insist that the “save” must be an SQL insert or update (or equivalent for non-SQL backends), respectively. Normally, they should not be set.

class crate_anon.crateweb.research.models.Query(*args, **kwargs)[source]

Class to query the research database.

exception DoesNotExist
exception MultipleObjectsReturned
__init__(*args, **kwargs) None[source]

Initialize our cache.

activate() None[source]

Activate this query (and deactivates any others).

audit(count_only: bool = False, n_records: int = 0, failed: bool = False, fail_msg: str = '') None[source]

Audit the execution of this query:

  • insert an audit entry referring to this query

  • mark the query as having been audited (so it’s not deleted)

Parameters
  • count_only – did we know (in advance) that this was a COUNT()-only query?

  • n_records – how many records were returned?

  • failed – did the query fail?

  • fail_msg – if the query failed, the associated failure message

delete_if_permitted() None[source]

Delete the query.

  • If a query has been executed and therefore audited, it isn’t properly deleted; it’s just marked as deleted.

  • If a query has never been executed, we can delete it entirely.

dictfetchall() List[Dict[str, Any]][source]

Executes the query and returns all results as a list of OrderedDicts (one for each row, mapping column names to values).

Raises

DatabaseError

gen_display_rows() Generator[List[Any], None, None][source]

Generates all filtered rows.

gen_rows() Generator[List[Any], None, None][source]

Generate rows from the query.

Raises

DatabaseError

static get_active_query_id_or_none(request: django.http.request.HttpRequest) Optional[int][source]

Returns the active query’s integer ID for this user, or None.

Parameters

request – the django.http.request.HttpRequest

Returns

the active query’s integer PK, or None.

static get_active_query_or_none(request: django.http.request.HttpRequest) Optional[crate_anon.crateweb.research.models.Query][source]

Returns the active query for this user, or None.

Parameters

request – the django.http.request.HttpRequest

Returns

a Query, or None.

get_column_names() List[str][source]

Returns column names from the query’s cursor.

Raises

DatabaseError

get_display_column_names() List[str][source]

Returns the filtered column names.

get_display_indexes() Optional[List[int]][source]

Returns the indexes of the result columns that we wish to display. Uses _get_display_indexes() and caches it.

Raises

DatabaseError

get_display_list() List[str][source]

Returns a list of columns to display, from our internal JSON representation. Uses _get_display_list() and caches it.

get_display_rows() List[List[Any]][source]

Returns a list of all filtered rows.

get_executed_cursor() django.db.backends.utils.CursorWrapper[source]

Get cursor with a query executed (based on our attributes sql, args, raw, qmark).

Returns

a django.db.backends.utils.CursorWrapper

Do NOT use this with with, as in:

with query.get_executed_cursor() as cursor:
    # do stuff

You could do that (and in general it’s what Django advises) but we are trying to be fancy here and use the cursor more efficiently.

get_rowcount() int[source]

Returns the rowcount from the cursor.

Raises

DatabaseError

get_rows() List[List[Any]][source]

Returns all rows from the query, as a list.

Raises

DatabaseError

get_sql_args_for_django() Tuple[str, Optional[List[Any]]][source]

Get sql/args in a format suitable for Django, with %s placeholders, or as escaped raw SQL.

Returns

sql, args

Return type

tuple

  • If raw is set, return our raw SQL with % escaped to %%;

  • otherwise, if qmark is set, return our raw SQL with ? argument placeholders translated to %s argument placeholders;

  • otherwise, return the raw SQL.

make_excel() bytes[source]

Executes the query and returns an Excel workbook, in binary.

make_tsv() str[source]

Executes the query and returns a TSV result (as a multiline string).

mark_audited() None[source]

Mark the query as having been executed and audited. (This prevents it from being wholly deleted.)

mark_deleted() None[source]

Mark the query as deleted.

This will stop it being shown. It will not delete it from the database.

We use this deletion method for queries that have been executed, so need an audit trail.

save(*args, **kwargs) None[source]

Custom save method. Ensures that only one Query has active == True for a given user. Also sets the hash.

set_display_list(display_list: List[str]) None[source]

Sets the internal JSON field, stored in the database, from a list of column headings to display.

Parameters

display_list – list of columns to display

class crate_anon.crateweb.research.models.QueryAudit(*args, **kwargs)[source]

Audit log for a query.

exception DoesNotExist
exception MultipleObjectsReturned
class crate_anon.crateweb.research.models.QueryBase(*args, **kwargs)[source]

Abstract base class for the two query classes.

get_formatted_sql() str[source]

Getter for ‘formatted_sql’.

get_original_sql() str[source]

Returns the stored raw SQL.

set_formatted_sql(reformat: bool = False) None[source]

Sets ‘formatted_sql’ by highlighting the syntax and possibly reformatting.

class crate_anon.crateweb.research.models.SitewideQuery(*args, **kwargs)[source]

Class representing a site-wide query for research database.

  • Site-wide queries are not attached to any particular user.

  • They are templatized with placeholders.

  • Placeholders begin with [[ and end with ]].

  • The user is asked to fill in values for the placeholders.

exception DoesNotExist
exception MultipleObjectsReturned
property prettified_chunks: List[str]

Returns chunks (see sql_chunks) but with formatting.

save(*args, **kwargs) None[source]

Custom save method. Sets the hash.

property sql_chunks: List[str]

Returns a list of SQL chunks and placeholders made from the original SQL. Placeholders begin with [[ and end with ]].

For example, if the sql is

SELECT * FROM [[table]] WHERE brcid="[[brcid]]";

then sql_chunks will be

[
    'SELECT * FROM ',
    'table',
    ' WHERE brcid="',
    'brcid',
    '";'
]

Note that the first element (and all elements with even [zero-based] list indexes) are SQL, not placeholders. All elements with odd indexes are placeholders.

class crate_anon.crateweb.research.models.TableQueryArgs(table_id: crate_anon.common.sql.TableId, sql: str, args: List[Any])[source]

Represents SQL for a specific table, with arguments for the SQL. Used by PatientMultiQuery.

__init__(table_id: crate_anon.common.sql.TableId, sql: str, args: List[Any]) None[source]
Parameters
crate_anon.crateweb.research.models.debug_query() None[source]

Executes a test query that just selects a constant, using the research database (i.e. connections['research']).

crate_anon.crateweb.research.models.gen_excel_row_elements(worksheet: openpyxl.worksheet.worksheet.Worksheet, row: Iterable) Generator[Any, None, None][source]

Given an Excel worksheet row, generate individual cell contents, cell by cell.

Parameters
  • worksheet – a openpyxl.worksheet.worksheet.Worksheet, which we need in order to find the worksheet’s encoding

  • row – the row to iterate through

Yields

the contents of each cell

Reasons for this function:

  1. We need a tuple/list/generator, as openpyxl checks its types manually.

  • We want to have a Worksheet object from openpyxl, and say something like

    ws.append(row)
    

    where “row” has come from a database query.

  • However, openpyxl doesn’t believe in duck-typing; see Worksheet.append() in openpyxl/worksheet/worksheet.py. So sometimes the plain append works (e.g. from MySQL results), but sometimes it fails, e.g. when the row is of type pyodbc.Row.

  • So we must coerce it to a tuple, list, or generator.

  • A generator will be the most efficient.

  1. If a string fails certain checks, openpyxl will raise an IllegalCharacterError exception. We need to work around that. We’ll use the “forgiveness, not permission” maxim. Specifically, it dislikes strings matching its ILLEGAL_CHARACTERS_RE, which contains unprintable low characters matching this:

    r'[\000-\010]|[\013-\014]|[\016-\037]'
    

    Note the use of octal; \037 is decimal 31.

    openpyxl gets to its Cell.check_string() function for these types:

    STRING_TYPES = (basestring, unicode, bytes)
    

    In Python 3, this means (str, str, bytes). So we should check str and bytes. (For bytes, we’ll follow its method of converting to str in the encoding of the worksheet’s choice.)

crate_anon.crateweb.research.models.get_executed_researchdb_cursor(sql: str, args: Optional[List[Any]] = None) django.db.backends.utils.CursorWrapper[source]

Executes a query on the research database. Returns a wrapped cursor that can be used as a context manager that will close the cursor on completion.

Parameters
  • sql – SQL text

  • args – arguments to SQL query

Returns

a django.db.backends.utils.CursorWrapper, which is a context manager that behaves as the executed cursor and also closes it on completion

Test code:

import os
import django
os.environ['DJANGO_SETTINGS_MODULE'] = 'crate_anon.crateweb.config.settings'
django.setup()
from crate_anon.crateweb.research.models import *
c = get_executed_researchdb_cursor("SELECT 1")
crate_anon.crateweb.research.models.get_executed_researchdb_cursor_qmark_placeholders(sql: str, args: Optional[List[Any]] = None) django.db.backends.utils.CursorWrapper[source]

As for get_executed_researchdb_cursor(), but assumes its SQL may contain question-mark parameter placeholders (?) and translates these to the ones we need internally.

crate_anon.crateweb.research.models.get_mpid(dbinfo: crate_anon.crateweb.research.research_db_info.SingleResearchDatabase, trid: Optional[int] = None, rid: Optional[str] = None, mrid: Optional[str] = None) int[source]

Returns the MPID for a patient, looked up from one of the research IDs.

Parameters
Returns

the integer MPID, or None

Raises

ValueError

crate_anon.crateweb.research.models.get_pid(dbinfo: crate_anon.crateweb.research.research_db_info.SingleResearchDatabase, trid: Optional[int] = None, rid: Optional[str] = None, mrid: Optional[str] = None) int[source]

Returns the PID for a patient, looked up from one of the research IDs.

Parameters
Returns

the integer PID, or None

Raises

ValueError

crate_anon.crateweb.research.models.get_pid_lookup(dbinfo: crate_anon.crateweb.research.research_db_info.SingleResearchDatabase, pid: Optional[Union[int, str]] = None, mpid: Optional[Union[int, str]] = None, trid: Optional[int] = None, rid: Optional[str] = None, mrid: Optional[str] = None) Optional[crate_anon.crateweb.research.models.PidLookup][source]

Looks up a patient in the secret lookup database associated with a database, from one of several possible identifiers.

Parameters
  • dbinfo – a crate_anon.crateweb.research.research_db_info.SingleResearchDatabase

  • pid – optional patient identifier (PID) value

  • mpid – optional master patient identifier (MPID) value

  • trid – optional transient research identifier (TRID) value

  • rid – optional research identifier (RID) value

  • mrid – optional master research identifier (MRID) value

Returns

a crate_anon.crateweb.research.models.PidLookup or None

Raises

ValueError

crate_anon.crateweb.research.models.hack_django_pyodbc_azure_cursorwrapper() None[source]

Monkey-patch part of the sql_server.pyodbc library from django-pyodbc-azure. It replaces the fetchone() method with a version that doesn’t call cursor.nextset() automatically.

It looks like this becomes unnecessary in django-pyodbc-azure==2.0.6.1 or similar, because the call to ``cursor.nextset()`` is now only performed ``if not self.connection.supports_mars``.

Notes

  • I thought I wanted to modify an instance, not a class (https://tryolabs.com/blog/2013/07/05/run-time-method-patching-python/).

  • To modify a class, we do SomeClass.method = newmethod.

  • But to modify an instance, we use instance.method = types.MethodType(newmethod, instance).

  • However, it turned out the instance was actually part of a long chain of cursor wrappers, including the Django debug toolbar. Classes included debug_toolbar.panels.sql.tracking.NormalCursorWrapper; django.db.backends.utils.CursorDebugWrapper. And in any case, modifying the class is a sensible thing.

crate_anon.crateweb.research.models.replacement_sqlserver_pyodbc_cursorwrapper_fetchone(self) List[Any][source]

A function to replace CursorWrapper.fetchone() in sql_server/pyodbc/base.py from django-pyodbc-azure. This replacement function does not call cursor.nextset().