def _upload_one_all_columns(
cursor,
table_name,
table_id,
columns,
input_dict,
overwrite,
print_string,
verbose,
**kwargs,
):
"""Insert or overwrite a list of things in a table."""
to_insert = [table_id] + columns
if overwrite:
sql_com = build_update(
table=table_name, to_set=columns, where=[table_id]
)
else:
sql_com = build_insert(table=table_name, to_insert=to_insert)
# Upload or modify data
insert_dict = {key: input_dict.get(key) for key in to_insert}
try:
cursor.execute(sql_com, insert_dict)
except sqlite3.Error as e:
raise type(e)(
f"Error inserting dict {insert_dict}. Original error:\n {e}"
)
if verbose:
# Print success
logger.info(f"{print_string} uploaded {insert_dict.get(table_id)}")
def isotherm_to_db(
isotherm,
db_path=None,
autoinsert_material=True,
autoinsert_adsorbate=True,
verbose=True,
**kwargs
):
"""
Uploads isotherm to the database.
If overwrite is set to true, the isotherm is overwritten.
Overwrite is done based on isotherm.iso_id
Parameters
----------
isotherm : Isotherm
Isotherm, PointIsotherm or ModelIsotherm to upload to the database.
db_path : str, None
Path to the database. If none is specified, internal database is used.
autoinsert_material: bool, True
Whether to automatically insert an isotherm material if it is not found
in the database.
autoinsert_adsorbate: bool, True
Whether to automatically insert an isotherm adsorbate if it is not found
in the database.
verbose : bool, True
Extra information printed to console.
"""
cursor = kwargs['cursor']
# Checks
if autoinsert_material:
if isotherm.material not in MATERIAL_LIST:
material_to_db(
isotherm.material if isinstance(isotherm.material, Material)
else Material(isotherm.material),
db_path=db_path,
cursor=cursor
)
if autoinsert_adsorbate:
if isotherm.adsorbate not in ADSORBATE_LIST:
adsorbate_to_db(
isotherm.adsorbate
if isinstance(isotherm.adsorbate, Adsorbate) else
Adsorbate(isotherm.adsorbate),
db_path=db_path,
cursor=cursor
)
# The isotherm is going to be inserted into the database
# Build upload dict
iso_id = isotherm.iso_id
upload_dict = {'id': iso_id}
if isinstance(isotherm, PointIsotherm):
upload_dict['iso_type'] = 'pointisotherm'
elif isinstance(isotherm, ModelIsotherm):
upload_dict['iso_type'] = 'modelisotherm'
elif isinstance(isotherm, BaseIsotherm):
upload_dict['iso_type'] = 'isotherm'
else:
raise ParsingError("Unknown isotherm type.")
iso_dict = isotherm.to_dict()
# attributes which are kept in the database
upload_dict.update({
param: iso_dict.pop(param, None)
for param in BaseIsotherm._required_params
})
# Upload isotherm info to database
db_columns = ["id", "iso_type"] + BaseIsotherm._required_params
try:
cursor.execute(
build_insert(table='isotherms', to_insert=db_columns), upload_dict
)
except sqlite3.Error as e:
raise type(e)(
f"""Error inserting isotherm "{upload_dict["id"]}" base properties. """
f"""Ensure material "{upload_dict["material"]}", and adsorbate "{upload_dict["adsorbate"]}" """
f"""exist in the database already. Original error:\n {e}"""
) from None
# TODO insert multiple
# Upload the other isotherm parameters
for key in iso_dict:
if key not in isotherm._unit_params:
# Deal with bools
val = iso_dict[key]
if isinstance(val, bool):
val = 'TRUE' if val else 'FALSE'
cursor.execute(
build_insert(
table='isotherm_properties',
to_insert=['iso_id', 'type', 'value']
), {
'iso_id': iso_id,
'type': key,
'value': val
}
)
# Then, the isotherm data/model will be uploaded into the data table
# Build sql request
sql_insert = build_insert(
table='isotherm_data', to_insert=['iso_id', 'type', 'data']
)
if isinstance(isotherm, PointIsotherm):
# Insert standard data fields:
cursor.execute(
sql_insert, {
'iso_id': iso_id,
'type': 'pressure',
'data': isotherm.pressure().tobytes()
}
)
cursor.execute(
sql_insert, {
'iso_id': iso_id,
'type': 'loading',
'data': isotherm.loading().tobytes()
}
)
# Update or insert other fields:
for key in isotherm.other_keys:
cursor.execute(
sql_insert, {
'iso_id': iso_id,
'type': key,
'data': isotherm.other_data(key).tobytes()
}
)
elif isinstance(isotherm, ModelIsotherm):
# Insert model parameters
cursor.execute(
sql_insert, {
'iso_id': iso_id,
'type': 'model',
'data': json.dumps(isotherm.model.to_dict())
}
)
if verbose:
# Print success
logger.info(f"Isotherm uploaded: '{isotherm.iso_id}'")
def material_to_db(
material,
db_path=None,
overwrite=False,
verbose=True,
**kwargs,
):
"""
Upload a material to the database.
If overwrite is set to true, the material is overwritten.
Overwrite is done based on material.name
Parameters
----------
material : Material
Material class to upload to the database.
db_path : str, None
Path to the database. If none is specified, internal database is used.
overwrite : bool
Whether to upload the material or overwrite it.
WARNING: Overwrite is done on ALL fields.
verbose : bool
Extra information printed to console.
"""
cursor = kwargs['cursor']
# If we need to overwrite, we find the id of existing adsorbate.
if overwrite:
ids = cursor.execute(
build_select(table='materials', to_select=['id'], where=['name']),
{
'name': material.name
}
).fetchone()
if ids is None:
raise sqlite3.IntegrityError(
f"Material to overwrite ({material.name}) does not exist in database."
)
mat_id = ids[0]
# If overwrite is not specified, we upload it to the adsorbates table
else:
cursor.execute(
build_insert(table="materials", to_insert=['name']),
{'name': material.name}
)
mat_id = cursor.lastrowid
# Upload or modify data in material_properties table
properties = material.to_dict()
del properties['name'] # no need for this
if properties:
if overwrite:
# Delete existing properties
_delete_by_id(
cursor,
'material_properties',
'mat_id',
mat_id,
'material properties',
verbose,
)
for prop, val in properties.items():
sql_insert = build_insert(
table='material_properties',
to_insert=['mat_id', 'type', 'value'],
)
if not isinstance(val, (list, set, tuple)):
val = [val]
for vl in val:
try:
cursor.execute(
sql_insert, {
'mat_id': mat_id,
'type': prop,
'value': vl
}
)
except sqlite3.InterfaceError as e:
raise type(e)(
f"Cannot process property {prop}: {vl}"
f"Original error:\n{e}"
) from None
# Add to existing list
if overwrite:
if material in MATERIAL_LIST:
MATERIAL_LIST.remove(material.name)
MATERIAL_LIST.append(material)
if verbose:
# Print success
logger.info(f"Material uploaded: '{material.name}'")
def test_insert():
insert = r'INSERT INTO "table" (a, b) VALUES (:a, :b)'
assert insert == squ.build_insert(tb, s1)