corrdb module

Module to work with a sqlite database of Corrosion Data

Here is an explanation of some of the important data structures used:

First is the CorrDB dict-based data-structure that are used by several modules in the CorrDB-project.

The CorrDB data-structure is used to represent a set of rows of database data, including options set on the various rows. It is basically a specially structured dict.

It looks like the following:

data {
   1: {
        exists [bool]
        delete [bool]
        replace [bool]
        invalid [bool]
        skip [bool]
        cols: {
            FIELDNAMEa: {
                name [str]
                value [str/int/bool]
            }
            FIELDNAMEb: { ... }
            .
            .
            FIELDNAMEx { ... }
        }
  }
  .
  .
  .
  N: { .... }
}

The first level key is the rowno of the data. It always starts at 1, not 0. Under each row are a set of options/settings for the row:

exists - The row exists already in the database. True or False.
delete - The row is to be deleted when given to the update()-method. True or False.
replace - The row is to be replaced when given to the update()-method. True or False.
invalid - The row of data is invalid, most likely missing a proper set of organization id and instrument id.
skip - The row is to be skipped when updating the database through the update()-method.

The “cols” subkey is the actual fields and values of the data. Keys on this sublevel is the textual field names.

In addition to this structure, the CorrDB class defines which fields and type of the fields that the database uses. The main structure for this is the dict called COLUMNS. It defines all the fields that are to be used, their type and some other attributes of a row that are needed.

The key of the COLUMNS dict is the shortened fieldname that are also used by the database for naming the field. This name in conjunction with the field-prefix (defaults to “data”) will form the field names in the database. So if a field is called “myfield” it will be called data_myfield in the database. Outside of the database, only the shortened COLUMNS’ key-names are used.

These are the attributes of each field in the database:

type - the type of the field. Supported options are: string, int and float.
include - specifies if the field and its data are to be included when exporting data (in a public context).
search - specifies if it is allowable for non-logged on users to use the specific field for search purposes or not?
order - specifies the order in which a field comes in the row - in other words column order.
name - specifies the textual name, long name variant, of the field. Used for display purposes.

So, one row in COLUMNS will look like this:

"SHORT_FIELDNAME": { "type": "int",    "include": True,  "search": True,  "order": 0,  "name": "LONG FIELDNAME" },

Additionally we have the following structural data:

COLORDER - list, specifies the order of the columns and each element is the short name/name attribute of the COLUMNS structure.
COLORDERSEARCH - list, specifies the columns that have the search-attribute set to True.
COLORDERINCL - list, specifies the columns that have the include-attribute set to True.
COLORG - specifies the field number in the columns order that we will find the organizational id/museum.
COLID - specifies the field number in the columns order where we will find the instrument id.

class corrdb.CorrDB(filename, field_prefix='data')[source]

Bases: object

Class to work with a corrosion project sqlite database.

check_data(data)[source]

Check if rows in the CorrDB data-structure exists already in the db.

Parameters:: data (dict) – The CorrDB data-structure to check.
Returns:: The resulting and checked CorrDB data-structure.
Return type:: dict

commit() → bool[source]

Commit the current SQL transaction.

Parameters:

accepted. (None is)

Returns:

Returns True upon success, False upon failure. Use the error()-method to get more: information upon failure.

Return type:

bool

connect() → bool[source]

Connect to the sqlite database if not already connected.

Parameters:

accepted. (None is)

Returns:

Returns True if successful in connecting. False if not. Use the error()-method: for more information in case of failure.

Return type:

bool

disconnect() → bool[source]

Disconnects from a sqlite database.

Parameters:

accepted. (None is)

Returns:

True if able to disconnect, False if not. Use the error()-method: to get more information upon failure.

Return type:

bool

doSQL(query, values=()) → Cursor[source]

Execute a SQL query and return the result, check for errors and more.

Parameters:

query (str) – The query to execute.
values (list) – If values are to be specified separately from the query. The query must then use “?”-marks for the values that are to be filled in. If values is empty list, the doSQL-method will not use values separately from the query, but expect the query to contain the values. It is recommended to use values separately from the query-argument, because this protects against sql injection issues.

Returns:

If successful the sqlite3 result cursor is returned: otherwise None. Use the error()-method to get more information upon a failure.

Return type:

sqlite3.Cursor

error() → str[source]

Returns the last error message, if any

Parameters:: accepted. (None is)
Returns:: The last error message, if any.
Return type:: str

getCardinality() → int[source]

Get the cardinality/number of rows in the database

Parameters:: accepted. (None is)
Returns:: The number of rows in the database as an int upon success. None if some failure.
Return type:: int

getCursor() → Cursor[source]

Get the sqlite db connection cursor.

Parameters:

accepted. (None is)

Returns:

Returns the sqlite db connection cursor if possible, if not None.: Use the error()-method to get more information upon a failure.

Return type:

sqlite3.Cursor

getLastInsertId() → lastrowid[source]

Get the last inserted id from a query.

Parameters:

accepted. (None is)

Returns:

Returns the ID of the last inserted row, if any, otherwise: None. Use the error()-method to get more information upon a failure.

Return type:

sqlite3.Cursor.lastrowid

prefix() → str[source]

Returns the database field prefix that has been set.

Parameters:: accepted. (None is)
Returns:: The database field prefix that has been set upon instantiation.
Return type:: str

rollback() → bool[source]

Attempts rollback of current transaction.

Parameters:

accepted. (None)

Returns:

True if successful, False otherwise. Use the error()-method to get more: information upon a failure.

Return type:

bool

search(search={}, lop='AND', allowsearch=False) → dict[source]

Search the corrosion database for rows that match given criteria.

Parameters:

search (dict) – One-level dict structure that defines the search. Optional. If specified, each key must be a field name and its value the value to search for. The value also accepts wildcards (*).
lop (str) – The logical operator to use between all the search fields specified in the search argument. Optional and defaults to “AND”. Acceptable values are either “AND” or “OR”.
allowsearch (bool) – Allow to search on fields that are specified to not be searchable in the CorrDB library.

Returns:

The row(s) that match the search criteria. If the search failed or some error the: method returns None. Use the error()-method to get more information upon a failure.

Return type:

dict

update(data) → [<class 'bool'>, <class 'int'>][source]

Update the database with new rows.

Parameters:

data (dict) – A CorrDB data-structure with the rows to write to the database.

Returns:

Returns True in the first element of the list if successful, otherwise: False. The second element in the list is the number of rows that were actually written or updated to the database. Use the error()-method to get more information upon a failure.

Return type:

[bool,int]