# -*- coding: utf_8 -*-
""" 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.
"""
import sqlite3
import re
# Include - what to include when exporting
# Search - what to allow to search for
COLUMNS={
"submissionid": { "type": "int", "include": False, "search": False, "order": 0, "name": "Submission ID" },
"created": { "type": "string", "include": False, "search": False, "order": 1, "name": "Created" },
"name": { "type": "string", "include": False, "search": False, "order": 2, "name": "Name" },
"email": { "type": "string", "include": False, "search": False, "order": 3, "name": "Email Address" },
"museum": { "type": "string", "include": True, "search": True, "order": 4, "name": "Institution/Museum Collection" },
"position": { "type": "string", "include": False, "search": False, "order": 5, "name": "Position" },
"sharedata": { "type": "string", "include": False, "search": False, "order": 6, "name": "Contribute By Sharing" },
"corrfound1": { "type": "string", "include": False, "search": False, "order": 7, "name": "Corrosion Found?" },
"date": { "type": "string", "include": True, "search": True, "order": 8, "name": "Survey Date" },
"instrumentid": { "type": "string", "include": True, "search": True, "order": 9, "name": "Instrument ID" },
"type": { "type": "string", "include": True, "search": False, "order": 10, "name": "Instrument Type" },
"family": { "type": "string", "include": True, "search": True, "order": 11, "name": "Instrument Family" },
"proddate": { "type": "string", "include": True, "search": True, "order": 12, "name": "Date Of Production" },
"prodplace": { "type": "string", "include": True, "search": True, "order": 13, "name": "Place Of Production" },
"prodadd": { "type": "string", "include": False, "search": False, "order": 14, "name": "Additional Information On Production" },
"metaltype1": { "type": "string", "include": True, "search": True, "order": 15, "name": "Type Of Metal At Contact Point" },
"organictype1": { "type": "string", "include": True, "search": True, "order": 16, "name": "Type Of Organic Material At Contact Point" },
"metalloc1": { "type": "string", "include": True, "search": True, "order": 17, "name": "Location Of Metal" },
"organicloc1": { "type": "string", "include": True, "search": True, "order": 18, "name": "Location Of Organic Material" },
"metaldiscolor1": { "type": "int", "include": True, "search": True, "order": 19, "name": "Metal Discoloration" },
"metalsurfalter1": { "type": "int", "include": True, "search": True, "order": 20, "name": "Metal Surface Alteration" },
"metalstructdam1": { "type": "int", "include": True, "search": True, "order": 21, "name": "Metal Structural Damage" },
"metalnochange1": { "type": "int", "include": True, "search": True, "order": 22, "name": "Metal No Change" },
"organicdiscolor1": { "type": "int", "include": True, "search": True, "order": 23, "name": "Organic Material Discoloration" },
"organicsurfalter1": { "type": "int", "include": True, "search": True, "order": 24, "name": "Organic Material Surface Alteration" },
"organicstructdam1": { "type": "int", "include": True, "search": True, "order": 25, "name": "Organic Material Structural Damage" },
"organicnochange1": { "type": "int", "include": True, "search": True, "order": 26, "name": "Organic Material No Change" },
"metalcolor1": { "type": "string", "include": True, "search": True, "order": 27, "name": "Color Of Metal At Contact Point" },
"conditioncp1": { "type": "int", "include": True, "search": True, "order": 28, "name": "Condition Of Contact Point" },
"conditionoverall": { "type": "int", "include": False, "search": False, "order": 29, "name": "Instrument Overall Condition" },
"metalearlierex": { "type": "int", "include": True, "search": True, "order": 30, "name": "Metal Earlier Exhange of Part" },
"metalearliertreat": { "type": "int", "include": True, "search": True, "order": 31, "name": "Metal Earlier Conservation Treatment" },
"metalearliertreatnov": { "type": "int", "include": True, "search": True, "order": 32, "name": "Metal No Earlier Treatment Visible" },
"organicearlierex": { "type": "int", "include": True, "search": True, "order": 33, "name": "Organic Material Earlier Exchange of Part" },
"organicearliertreat": { "type": "int", "include": True, "search": True, "order": 34, "name": "Organic Material Earlier Conservation Treatment" },
"organicearliertreatnov": { "type": "int", "include": True, "search": True, "order": 35, "name": "Organic Material No Earlier Tratment Visible" },
"earlierwaterdam": { "type": "string", "include": True, "search": True, "order": 36, "name": "Traces Of Earlier Water Damage" },
"metalcorrindcp": { "type": "string", "include": True, "search": True, "order": 37, "name": "Metal Corrosion Independent Of Contact Point" },
"rh": { "type": "int", "include": True, "search": True, "order": 38, "name": "Relative Humidity" },
"corrfound2": { "type": "string", "include": True, "search": True, "order": 39, "name": "Another Contact Corrosion On The Same Instrument?" },
"metaltype2": { "type": "string", "include": True, "search": True, "order": 40, "name": "Type Of Metal At Contact Point 2" },
"organictype2": { "type": "string", "include": True, "search": True, "order": 41, "name": "Type Of Organic Material At Contact Point 2" },
"metalloc2": { "type": "string", "include": True, "search": True, "order": 42, "name": "Location Of Metal 2" },
"organicloc2": { "type": "string", "include": True, "search": True, "order": 43, "name": "Location Of Organic Material 2" },
"metaldiscolor2": { "type": "int", "include": True, "search": True, "order": 44, "name": "Metal Discoloration 2" },
"metalsurfalter2": { "type": "int", "include": True, "search": True, "order": 45, "name": "Metal Surface Alteration 2" },
"metalstructdam2": { "type": "int", "include": True, "search": True, "order": 46, "name": "Metal Structural Damage 2" },
"metalnochange2": { "type": "int", "include": True, "search": True, "order": 47, "name": "Metal No Change 2" },
"organicdiscolor2": { "type": "int", "include": True, "search": True, "order": 48, "name": "Organic Material Discoloration 2" },
"organicsurfalter2": { "type": "int", "include": True, "search": True, "order": 49, "name": "Organic Material Surface Alteration 2" },
"organicstructdam2": { "type": "int", "include": True, "search": True, "order": 50, "name": "Organic Material Structural Damage 2" },
"organicnochange2": { "type": "int", "include": True, "search": True, "order": 51, "name": "Organic Material No Change 2" },
"metalcolor2": { "type": "string", "include": True, "search": True, "order": 52, "name": "Color Of Metal At Contact Point 2" },
"conditioncp2": { "type": "int", "include": True, "search": True, "order": 53, "name": "Condition Of Contact Point 2" },
"corrfound3": { "type": "string", "include": True, "search": True, "order": 54, "name": "A Third Contact Corrosion On The Same Instrument?" },
"metaltype3": { "type": "string", "include": True, "search": True, "order": 55, "name": "Type Of Metal At Contact Point 3" },
"organictype3": { "type": "string", "include": True, "search": True, "order": 56, "name": "Type Of Organic Material At Contact Point 3" },
"metalloc3": { "type": "string", "include": True, "search": True, "order": 57, "name": "Location Of Metal 3" },
"organicloc3": { "type": "string", "include": True, "search": True, "order": 58, "name": "Location Of Organic Material 3" },
"metaldiscolor3": { "type": "int", "include": True, "search": True, "order": 59, "name": "Metal Discoloration 3" },
"metalsurfalter3": { "type": "int", "include": True, "search": True, "order": 60, "name": "Metal Surface Alteration 3" },
"metalstructdam3": { "type": "int", "include": True, "search": True, "order": 61, "name": "Metal Structural Damage 3" },
"metalnochange3": { "type": "int", "include": True, "search": True, "order": 62, "name": "Metal No Change 3" },
"organicdiscolor3": { "type": "int", "include": True, "search": True, "order": 63, "name": "Organic Material Discoloration 3" },
"organicsurfalter3": { "type": "int", "include": True, "search": True, "order": 64, "name": "Organic Material Surface Alteration 3" },
"organicstructdam3": { "type": "int", "include": True, "search": True, "order": 65, "name": "Organic Material Structural Damage 3" },
"organicnochange3": { "type": "int", "include": True, "search": True, "order": 66, "name": "Organic Material No Change 3" },
"metalcolor3": { "type": "string", "include": True, "search": True, "order": 67, "name": "Color Of Metal At Contact Point 3" },
"conditioncp3": { "type": "int", "include": True, "search": True, "order": 68, "name": "Condition Of Contact Point 3" },
"corrfound4": { "type": "string", "include": True, "search": True, "order": 69, "name": "A Fourth Contact Corrosion On The Same Instrument?" },
"metaltype4": { "type": "string", "include": True, "search": True, "order": 70, "name": "Type Of Metal At Contact Point 4" },
"organictype4": { "type": "string", "include": True, "search": True, "order": 71, "name": "Type Of Organic Material At Contact Point 4" },
"metalloc4": { "type": "string", "include": True, "search": True, "order": 72, "name": "Location Of Metal 4" },
"organicloc4": { "type": "string", "include": True, "search": True, "order": 73, "name": "Location Of Organic Material 4" },
"metaldiscolor4": { "type": "int", "include": True, "search": True, "order": 74, "name": "Metal Discoloration 4" },
"metalsurfalter4": { "type": "int", "include": True, "search": True, "order": 75, "name": "Metal Surface Alteration 4" },
"metalstructdam4": { "type": "int", "include": True, "search": True, "order": 76, "name": "Metal Structural Damage 4" },
"metalnochange4": { "type": "int", "include": True, "search": True, "order": 77, "name": "Metal No Change 4" },
"organicdiscolor4": { "type": "int", "include": True, "search": True, "order": 78, "name": "Organic Material Discoloration 4" },
"organicsurfalter4": { "type": "int", "include": True, "search": True, "order": 79, "name": "Organic Material Surface Alteration 4" },
"organicstructdam4": { "type": "int", "include": True, "search": True, "order": 80, "name": "Organic Material Structural Damage 4" },
"organicnochange4": { "type": "int", "include": True, "search": True, "order": 81, "name": "Organic Material No Change 4" },
"metalcolor4": { "type": "string", "include": True, "search": True, "order": 82, "name": "Color Of Metal At Contact Point 4" },
"conditioncp4": { "type": "int", "include": True, "search": True, "order": 83, "name": "Condition Of Contact Point 4" },
"comments": { "type": "string", "include": False, "search": False, "order": 84, "name": "Comments" },
"answertime": { "type": "int", "include": False, "search": False, "order": 85, "name": "Answer Time" },
}
COLSOLD={
"datesurvey": { "type": "string", "include": True, "search": True, "order": 0, "name": "Date Survey" },
"pospersiw": { "type": "string", "include": False, "search": False, "order": 1, "name": "Position Person Interview" },
"nmpersiw": { "type": "string", "include": False, "search": False, "order": 2, "name": "Name Person Interview" },
"resppersiw": { "type": "string", "include": False, "search": False, "order": 3, "name": "Responsibility Person Interview" },
"museum": { "type": "string", "include": True, "search": True, "order": 4, "name": "Museum" },
"instrumentid": { "type": "string", "include": True, "search": True, "order": 5, "name": "Instrument ID" },
"family": { "type": "string", "include": True, "search": True, "order": 6, "name": "Instrument Family" },
"type": { "type": "string", "include": True, "search": True, "order": 7, "name": "Instrument Type", },
"condition": { "type": "int", "include": True, "search": True, "order": 8, "name": "Condition Code (0 Best - 4 Worst)" },
"earlierwaterdamage": { "type": "string", "include": True, "search": True, "order": 9, "name": "Earlier Water Damage" },
"earlierwaterdamage2": { "type": "string", "include": True, "search": True, "order": 10, "name": "Earlier Water Damage 2" },
"nocorr": { "type": "string", "include": True, "search": True, "order": 11, "name": "No Corrosion" },
"metalcorrcp": { "type": "string", "include": True, "search": True, "order": 12, "name": "Metal Corrosion Contact Point" },
"corrindepcp": { "type": "string", "include": True, "search": True, "order": 13, "name": "Corr Independent To Contact Point When Corr At Contact Point" },
"corrindeptocp": { "type": "string", "include": True, "search": True, "order": 14, "name": "Corr Independent To Contact Point" },
"contact": { "type": "string", "include": True, "search": True, "order": 15, "name": "Contact/Org. Material Alteration" },
"contact2": { "type": "string", "include": True, "search": True, "order": 16, "name": "Contact/Org. Material Alteration 2" },
"notecontact": { "type": "string", "include": True, "search": True, "order": 17, "name": "Note Contact Point" },
"metalmaterial1": { "type": "string", "include": True, "search": True, "order": 18, "name": "Metal Material 1" },
"metalmaterial2": { "type": "string", "include": True, "search": True, "order": 19, "name": "Metal Material 2" },
"metal": { "type": "string", "include": True, "search": True, "order": 20, "name": "Metal" },
"contactmaterial1": { "type": "string", "include": True, "search": True, "order": 21, "name": "Contact Material 1" },
"contactmaterial2": { "type": "string", "include": True, "search": True, "order": 22, "name": "Contact Material 2" },
"organiccontactmaterial": { "type": "string", "include": True, "search": True, "order": 23, "name": "Organic Contact Material" },
"metalprimsec": { "type": "string", "include": True, "search": True, "order": 24, "name": "Metal Prim/Sec" },
"organicprimsec": { "type": "string", "include": True, "search": True, "order": 25, "name": "Organic/Contact Material Prim/Sec" },
"earliertreat": { "type": "string", "include": True, "search": True, "order": 26, "name": "Earlier Treatments" },
"sampletaken": { "type": "string", "include": True, "search": True, "order": 27, "name": "Sample Taken" },
"samplemetalloc": { "type": "string", "include": True, "search": True, "order": 28, "name": "Sample Metal (Location)" },
"samplecorrloc": { "type": "string", "include": True, "search": True, "order": 29, "name": "Sample Corrosion (Location)" },
"sampleorganicloc": { "type": "string", "include": True, "search": True, "order": 30, "name": "Sample Organic Material (Location)" },
"sampleother": { "type": "string", "include": True, "search": True, "order": 31, "name": "Sample Other" },
"date": { "type": "string", "include": True, "search": True, "order": 32, "name": "Date" },
"dateprod": { "type": "string", "include": True, "search": True, "order": 33, "name": "Date Production" },
"prov": { "type": "string", "include": True, "search": True, "order": 34, "name": "Provenience" },
"prov2": { "type": "string", "include": True, "search": True, "order": 35, "name": "Provenience 2" },
"maker": { "type": "string", "include": True, "search": True, "order": 36, "name": "Maker" },
"idlocold": { "type": "string", "include": False, "search": False, "order": 37, "name": "Id Location (old)" },
"room": { "type": "string", "include": True, "search": True, "order": 38, "name": "Room/Mounting" },
"rh": { "type": "string", "include": True, "search": True, "order": 39, "name": "RH%" },
"temp": { "type": "string", "include": True, "search": True, "order": 40, "name": "Temperature" },
}
# create column order array
COLORDER=[]
for name in COLUMNS.keys():
# insert array element in the position in the array
# that corresponds to its order position
COLORDER.insert(COLUMNS[name]["order"],name)
# only include columns that are to be included
# when showing/searching the data
COLORDERSEARCH=[]
for name in COLUMNS.keys():
if COLUMNS[name]["search"] == False: continue
COLORDERSEARCH.insert(COLUMNS[name]["order"],name)
# only include columns that are to be included
# when exporting/sharing the data
COLORDERINCL=[]
for name in COLUMNS.keys():
if COLUMNS[name]["include"] == False: continue
COLORDERINCL.insert(COLUMNS[name]["order"],name)
# set which columns contain the museum/org and the instrument id
COLORG = 4 # =4
#COLID = 5 # =9
COLID = 9 #
[docs]
class CorrDB:
""" Class to work with a corrosion project sqlite database.
"""
def __init__(self,filename,field_prefix="data"):
""" Instantiate the CorrDB class.
Args:
filename (str): Name and path of the sqlite database to work with. The file will be created
if it does not exist already.
field_prefix (str): The prefix for field names to use in the database. All field names used
will be prefixed with this string. Optional. Defaults to "data".
Returns:
CorrDB: Instance of CorrDB-class.
"""
self.filename=filename
self.errormsg=""
self.rowcount=0
self.connected=False
self.fieldprefix=field_prefix
[docs]
def prefix(self) -> str:
""" Returns the database field prefix that has been set.
Args:
None is accepted.
Returns:
str: The database field prefix that has been set upon instantiation.
"""
return self.fieldprefix
[docs]
def connect(self) -> bool:
""" Connect to the sqlite database if not already connected.
Args:
None is accepted.
Returns:
bool: Returns True if successful in connecting. False if not. Use the error()-method
for more information in case of failure.
"""
method=__name__+"."+"connect"
if self.connected == False:
conn=None
self.errormsg=""
try:
# trying to connect to database
conn = sqlite3.connect(self.filename)
except Exception as ex:
err=ex.message if hasattr(ex,"message") else ""
# we had an error
self.errormsg=method+": Unable to connect to database: "+err
return False
else:
# everything went ok - tag as connected
self.connected=True
self.conn=conn
# get cursor of db
self.cursor = conn.cursor()
return True
else:
# already connected
self.errormsg=method+": Already connected. Unable to connect again"
return False
[docs]
def disconnect(self) -> bool:
""" Disconnects from a sqlite database.
Args:
None is accepted.
Returns:
bool: True if able to disconnect, False if not. Use the error()-method
to get more information upon failure.
"""
method=__name__+"."+"disconnect"
self.errormsg=""
if self.connected == True:
conn=self.conn
try:
# attempt to disconnect database
conn.close()
except Exception as ex:
err=ex.message if hasattr(ex,"message") else ""
# we failed to disconnect database
self.errormsg=method+": Unable to disconnect from database: "+err
return False
else:
# we disconnected successfully - reset instance data
self.connected=False
self.conn=None
self.cur=None
return True;
else:
# not yet connected
self.errormsg=method+": Not yet connected. Unable to disconnect"
return False
[docs]
def getCursor(self) -> sqlite3.Cursor:
""" Get the sqlite db connection cursor.
Args:
None is accepted.
Returns:
sqlite3.Cursor: Returns the sqlite db connection cursor if possible, if not None.
Use the error()-method to get more information upon a failure.
"""
method=__name__+"."+"getCursor"
self.errormsg=""
# ensure we are connected before returning cursor
if (self.connected):
return self.cursor
else:
# we are not connected - no cursor to return
self.errormsg=method+": Not connected to database. Unable to get cursor"
return None
[docs]
def doSQL(self,query,values=()) -> sqlite3.Cursor:
""" Execute a SQL query and return the result, check for errors and more.
Args:
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:
sqlite3.Cursor: If successful the sqlite3 result cursor is returned
otherwise None. Use the error()-method to get more
information upon a failure.
"""
method=__name__+"."+"doSQL"
self.errormsg=""
cursor = self.cursor
result=None
try:
# check if this is a query with values separate
if (type(values) == tuple or type(values) == list) and len(values) > 0:
# values are separate from query and all of them will be quoted/checked
result = cursor.execute(query,values)
else:
# this is a query without values separate
result = cursor.execute(query)
except Exception as ex:
# something went wrong
err=ex.message if hasattr(ex,"message") else str(ex)
self.errormsg=method+": Unable to execute query: "+query+". Database error: "+err
return None
else:
# query was accepted - return cursor
return cursor
[docs]
def getCardinality(self) -> int:
""" Get the cardinality/number of rows in the database
Args:
None is accepted.
Returns:
int: The number of rows in the database as an int upon success. None if some failure.
"""
method=__name__+"."+"getCardinality"
self.errormsg=""
if self.connected == True:
result=self.doSQL("SELECT count(*) FROM DATA")
if result is not None:
# we have a result, fetch first row with the number of rows
rows=result.fetchone()
rows=rows[0] if rows is not None else 0
return rows
else:
# error already set
return None
else:
self.errormsg=method+":Not connected to any database yet. Unable to get cardinality."
return None
[docs]
def getLastInsertId(self) -> sqlite3.Cursor.lastrowid:
""" Get the last inserted id from a query.
Args:
None is accepted.
Returns:
sqlite3.Cursor.lastrowid: Returns the ID of the last inserted row, if any, otherwise
None. Use the error()-method to get more information upon
a failure.
"""
method=__name__+"."+"getLastInsertId"
self.errormsg=""
if self.connected == True:
# return the last inserted id
return self.cursor.lastrowid
else:
# not connected yet
self.errormsg=method+": Not connected to any database yet. Unable to get last insert id"
return None
[docs]
def commit(self) -> bool:
""" Commit the current SQL transaction.
Args:
None is accepted.
Returns:
bool: Returns True upon success, False upon failure. Use the error()-method to get more
information upon failure.
"""
method=__name__+"."+"commit"
self.errormsg=""
if self.connected == True:
try:
# commit any changes
res=self.conn.commit()
except Exception as ex:
err=ex.message if hasattr(ex,"message") else str(ex)
self.errormsg=method+": Failed to commit to database: "+err
return False
else:
# success
return True
else:
# not connected
self.errormsg=method+": Not connected to database. Unable to commit to database"
return False
[docs]
def rollback(self) -> bool:
""" Attempts rollback of current transaction.
Args:
None accepted.
Returns:
bool: True if successful, False otherwise. Use the error()-method to get more
information upon a failure.
"""
method=__name__+"."+"rollback"
if self.connected == True:
try:
# attempt to rollback any changes
self.conn.rollback()
except Exception as ex:
err=ex.message if hasattr(ex,"message") else str(ex)
self.errormsg=method+": Unable to rollback: "+err
return False
else:
# success
return True
else:
self.errormsg=method+": Not connected to any database yet. Unable to continue"
return False
[docs]
def check_data(self,data):
""" Check if rows in the CorrDB data-structure exists already in the db.
Args:
data (dict): The CorrDB data-structure to check.
Returns:
dict: The resulting and checked CorrDB data-structure.
"""
method=__name__+"."+"check_data"
self.errormsg=""
# get field prefix
prefix=self.prefix()
# get fieldnames of the unique keys of a row
fieldorg=prefix+"_"+COLORDER[COLORG]
fieldid=prefix+"_"+COLORDER[COLID]
# check each row at a time, by looking at organization in
# combination with unique, instrument id.
for rowno in range(1,len(data.keys())+1):
# get the uniquely identifying field's values
org=data[rowno]["cols"][COLORDER[COLORG]]["value"] or ""
id=data[rowno]["cols"][COLORDER[COLID]]["value"] or ""
# skip checking if this exists or not if org or id is missing
# this cannot be inserted as it violates unqiue requirements
if str(org).strip() == "" or str(id).strip() == "":
data[rowno]["invalid"]=True
continue
# check if data-row is a duplicate or not.
result = self.doSQL("SELECT count(*) FROM DATA WHERE "+fieldorg+"=? and "+fieldid+"=?",(org,id))
rows=result.fetchone()
rows=rows[0] if rows is not None else 0
# check if we found the unique combination
if rows > 0:
# tag row as already existing in db
data[rowno]["exists"]=True
return data
# method to search the database.
# lop is the logical operator to use between each search field, either AND or OR
# allowseach sets if search-restricted fields are to be included or not in the input/output
[docs]
def search(self,search={},lop="AND",allowsearch=False) -> dict:
""" Search the corrosion database for rows that match given criteria.
Args:
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:
dict: 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.
"""
method=__name__+"."+"search"
self.errormsg=""
# double-check lop
lop=str(lop).upper()
# we only allow AND or OR
lop=lop if (lop == "AND" or lop == "OR") else "AND"
lop=" "+lop+" "
# get field prefix
prefix=self.prefix() + "_"
# go through search dict and get values we are searching for
# init searchstr with 1 so we do not need to take heed to when to use lop
searchstr="1"
values=[]
for name in search.keys():
# check if name is valid or not?
if name not in COLORDER: continue # not valid, jump to next name in searchstr
# check if input/output is to include this field or not?
if COLUMNS[name]["search"] == False and not allowsearch: continue
# get value part of search term
value = search[name]
valtype = COLUMNS[name]["type"]
# check type
if valtype == "string": value=str(value)
if valtype == "int" or valtype == "float":
sres=re.search("\\d+",str(value))
if sres is None:
# skip this search value since it does not
# contain any valid value
continue
else:
# return the part of the string with match
# and ensure conversion to correct type
if valtype == "int": value=int(sres.group())
else: value=float(sres.group())
# cop on an integer or float cannot be wildcard
if valtype != "string": cop=" = "
else:
# this is a string and wildcards are allowed
# check which comparative operator to use, wildcards are *
cop=" = " if re.search("\\*",search[name]) is None else " LIKE "
# replace wildcards in string with SQL %
if cop == " LIKE ": value = str(value).replace("*","%")
# add to values tuple
values.append(value)
# add to search string - use lop, as first part is 1 already
searchstr=searchstr + lop + prefix + name + cop + "?"
# create the resultfields, so we have a set return order of fields
resfields=""
for field in COLORDER:
sep="" if resfields == "" else ","
resfields=resfields+sep+"data_"+field
# generate the search query
searchstr="SELECT "+resfields+" FROM DATA WHERE "+searchstr
data={}
# search database
try:
# add values list to give values for all the ?-marks
result = self.cursor.execute(searchstr,values)
except Exception as ex:
# error
err=ex.message if hasattr(ex,"message") else str(ex)
self.errormsg=method+": Failed to query database: "+err
return None
else:
# retrieve data
rowno=0;
for row in self.cursor:
# get row data - start in row 1
rowno=rowno+1
for pos in range(0,len(row)):
field=COLORDER[pos]
# check if field is allowed to be included or not?
# if COLUMNS[field]["search"] == False and not allowsearch: continue
# add field to row data
if rowno not in data: data[rowno]={}
data[rowno]["exists"]=False
data[rowno]["delete"]=False
data[rowno]["replace"]=False
data[rowno]["invalid"]=False
data[rowno]["skip"]=False
if "cols" not in data[rowno]: data[rowno]["cols"]={}
data[rowno]["cols"][field]={}
data[rowno]["cols"][field]["name"]=field
data[rowno]["cols"][field]["value"]=row[pos]
# return structure - whatever it is
return data
# update the db with data from a dict
[docs]
def update(self,data) -> [bool,int]:
""" Update the database with new rows.
Args:
data (dict): A CorrDB data-structure with the rows to write to the database.
Returns:
[bool,int]: 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.
"""
method=__name__+"."+"update"
self.errormsg=""
# get field prefix
prefix=self.prefix()
# count number of imported rows
importedrows=0
# write all relevant data to db
for rowno in range(1,len(data.keys())+1):
# get settings for this row
exists=data[rowno]["exists"] or False
replace=data[rowno]["replace"] or False
delete=data[rowno]["delete"] or False
invalid=data[rowno]["invalid"] or False
skip=data[rowno]["skip"] or False
# check if row is invalid or skip, if so skip it
if invalid or skip: continue
# construct scope
scopestr=prefix+"_"+COLORDER[COLORG]+"=? AND "+prefix+"_"+COLORDER[COLID]+"=?"
# go through all the fields
fields=""
values=[]
valuesstr="UNIXEPOCH()"
composite=""
for field in COLORDER:
# get value
value=data[rowno]["cols"][field]["value"]
# get the type of the value
valtype = COLUMNS[field]["type"]
nulltrigger=False
# check type and type case it as needed
if valtype == "string":
if value is None:
value="NULL"
nulltrigger=True
else: value=str(value)
if valtype == "int" or valtype == "float":
sres=re.search("\\d+",str(value))
if sres is None:
# not a valid number - set null value
value="NULL"
nulltrigger=True
else:
# return the part of the string with match
# and ensure conversion to correct type
if valtype == "int": value=int(sres.group())
else: value=float(sres.group())
# add value to values list if not null value has been triggered
if not nulltrigger: values.append(value)
# update valuesstr
valuesstr=valuesstr+",?" if not nulltrigger else valuesstr+",NULL"
# also make a composite for the update statement
sep="" if composite == "" else ","
if nulltrigger: composite=composite+sep+prefix+"_"+field+"=NULL"
else: composite=composite+sep+prefix+"_"+field+"=?"
# get primary key values
org=data[rowno]["cols"][COLORDER[COLORG]]["value"]
id=data[rowno]["cols"][COLORDER[COLID]]["value"]
result=None
# increment imported rows counter
importedrows=importedrows+1
# check what to do with the row
if exists and replace:
# replace current values for this row
# add org and id to values
values.append(org)
values.append(id)
result = self.doSQL("UPDATE DATA SET "+composite+" WHERE "+scopestr,values)
elif exists and delete:
# delete current row
result = self.doSQL("DELETE FROM DATA WHERE "+scopestr,[org,id])
elif exists == False:
# just insert data
result = self.doSQL("INSERT INTO DATA VALUES ("+valuesstr+")",values)
# check result of doSQL operation
if result is None or result == False:
dberror=self.error()
# rollback transaction
self.rollback()
# set error message
self.errormsg=method+": Unable to update database with data: "+dberror
# return failure
return [False,0]
# we finished all updates - commit them
if self.commit():
# successful - return number of imported rows
return [True,importedrows]
else:
# error occured
self.errormsg=method+": Unable to commit database changes: "+self.error()
return [False,0]
[docs]
def error(self) -> str:
""" Returns the last error message, if any
Args:
None is accepted.
Returns:
str: The last error message, if any.
"""
return str(self.errormsg)