def start_import(self):
"""
TODO: I have NO IDEA where the dummy parameter is coming from. It gets the value False for some reason!
:param dummy:
:return:
"""
if self.file_data is None:
raise utils.UsageError(ru(QCoreApplication.translate('GeneralCsvImportGui', 'Error, must select a file first!')))
translation_dict = self.table_chooser.get_translation_dict()
file_data = copy.deepcopy(self.file_data)
dest_table = self.table_chooser.import_method
foreign_keys = db_utils.get_foreign_keys(dest_table)
foreign_key_obsid_tables = [tname for tname, colnames in foreign_keys.items() for colname in colnames if colname[0] == 'obsid']
if len(foreign_key_obsid_tables) == 1:
foreign_key_obsid_table = foreign_key_obsid_tables[0]
else:
foreign_key_obsid_table = dest_table
for file_column in list(translation_dict.keys()):
alter_colnames = []
new_value = None
# Check if obsid should be set from selection and add an obsid-column if so.
if isinstance(file_column, Obsids_from_selection):
selected = utils.get_selected_features_as_tuple()
if len(selected) != 1:
utils.MessagebarAndLog.critical(bar_msg=ru(QCoreApplication.translate('GeneralCsvImportGui', 'Import error, must select 1 obsid')), duration=60)
return 'cancel'
alter_colnames = ['obsid']
new_value = selected[0]
elif isinstance(file_column, StaticValue):
if translation_dict[file_column]:
alter_colnames = translation_dict[file_column]
new_value = file_column.value
for alter_colname in alter_colnames:
if alter_colnames is not None and new_value is not None:
try:
colindex = file_data[0].index(alter_colname)
except ValueError:
colindex = len(file_data[0])
file_data[0].append(alter_colname)
for row in file_data[1:]:
if colindex + 1 < len(file_data[0]):
row[colindex] = new_value
else:
row.append(new_value)
#[row.insert(obsidindex, selected[0]) if obsidindex + 1 < len(file_data[0]) else row.append(selected[0]) for row in file_data[1:]]
del translation_dict[file_column]
translation_dict[alter_colname] = [alter_colname]
columns_factors = self.table_chooser.get_columns_factors_dict()
#Translate column names and add columns that appear more than once
file_data = self.translate_and_reorder_file_data(file_data, translation_dict)
file_data = self.convert_comma_to_points_for_double_columns(file_data, self.tables_columns_info[dest_table])
if columns_factors:
file_data = self.multiply_by_factor(file_data, columns_factors)
file_data = self.remove_preceding_trailing_spaces_tabs(file_data)
if foreign_key_obsid_table and foreign_key_obsid_table != dest_table and 'obsid' in file_data[0]:
file_data = utils.filter_nonexisting_values_and_ask(file_data, 'obsid', utils.get_all_obsids(foreign_key_obsid_table), try_capitalize=False)
file_data = self.reformat_date_time(file_data)
importer = import_data_to_db.midv_data_importer()
answer = importer.general_import(dest_table=dest_table, file_data=file_data)
utils.stop_waiting_cursor()
if self.close_after_import.isChecked():
self.close()
def test_get_foreign_keys_no_keys(self):
""" """
foreign_keys = db_utils.get_foreign_keys(u'obs_points')
test_string = utils_for_tests.create_test_string(foreign_keys)
reference = u'{}'
assert test_string == reference
def general_import(self, dest_table, file_data, allow_obs_fk_import=False,
_dbconnection=None, dump_temptable=False, source_srid=None,
skip_confirmation=False, binary_geometry=False):
"""General method for importing a list of list to a table
self.temptableName must be the name of the table containing the new data to import.
:param dest_table: The destination table
:param file_data: a list of list with a header list as first row
:param allow_obs_fk_import: True to allow creation of obsids in obs_points and obs_lines.
:param _dbconnection: A db_utils.DbConnectionManager-instance if other than the currently selected in the midvatten
settings dialog.
:param dump_temptable: True to create a csvfile from internal temporary table.
:param source_srid: The srid of the source geometry column if the geometry is a WKT or WKB
:param skip_confirmation: True to not ask the user to import foreign keys.
:param binary_geometry: True if the source geometry column should be parsed as a WKB, else it's parsed as WKT.
:return:
"""
self.temptable_name = None
if skip_confirmation:
self.foreign_keys_import_question = 1
try:
if file_data is None or not file_data:
return
utils.MessagebarAndLog.info(log_msg=ru(QCoreApplication.translate('midv_data_importer', '\nImport to %s starting\n--------------------')) % dest_table)
utils.start_waiting_cursor()
if not isinstance(_dbconnection, db_utils.DbConnectionManager):
dbconnection = db_utils.DbConnectionManager()
else:
dbconnection = _dbconnection
db_utils.activate_foreign_keys(activated=True, dbconnection=dbconnection)
recsinfile = len(file_data[1:])
table_info = db_utils.db_tables_columns_info(table=dest_table, dbconnection=dbconnection)
if not table_info:
raise MidvDataImporterError(ru(QCoreApplication.translate('midv_data_importer', 'The table %s did not exist. Update the database to latest version.')) % dest_table)
else:
table_info = table_info[dest_table]
#POINT and LINESTRING must be cast as BLOB. So change the type to BLOB.
column_headers_types = db_utils.change_cast_type_for_geometry_columns(dbconnection, table_info, dest_table)
primary_keys = [row[1] for row in table_info if int(row[5])] #Not null columns are allowed if they have a default value.
not_null_columns = [row[1] for row in table_info if int(row[3]) and row[4] is None]
#Only use the columns that exists in the goal table.
existing_columns_in_dest_table = [col for col in file_data[0] if col in column_headers_types]
existing_columns_in_temptable = file_data[0]
missing_columns = [column for column in not_null_columns if column not in existing_columns_in_dest_table]
if missing_columns:
raise MidvDataImporterError(ru(QCoreApplication.translate('midv_data_importer', 'Required columns %s are missing for table %s')) % (', '.join(missing_columns), dest_table))
primary_keys_for_concat = [pk for pk in primary_keys if pk in existing_columns_in_temptable]
self.list_to_table(dbconnection, dest_table, file_data, primary_keys_for_concat)
#Delete records from self.temptable where yyyy-mm-dd hh:mm or yyyy-mm-dd hh:mm:ss already exist for the same date.
nr_before = dbconnection.execute_and_fetchall('''select count(*) from %s''' % (self.temptable_name))[0][0]
if 'date_time' in primary_keys:
self.delete_existing_date_times_from_temptable(primary_keys, dest_table, dbconnection)
nr_after = dbconnection.execute_and_fetchall('''select count(*) from %s''' % (self.temptable_name))[0][0]
nr_same_date = nr_after - nr_before
if nr_same_date > 0:
utils.MessagebarAndLog.info(log_msg=ru(QCoreApplication.translate('midv_data_importer', 'In total "%s" rows with the same date \non format yyyy-mm-dd hh:mm or yyyy-mm-dd hh:mm:ss already existed and will not be imported. %s rows remain.'))%(str(nr_same_date), str(nr_after)))
if not nr_after > 0:
utils.MessagebarAndLog.warning(bar_msg=ru(QCoreApplication.translate('midv_data_importer', 'Nothing imported to %s after deleting duplicate date_times')) % dest_table)
return
#Special cases for some tables
if dest_table == 'stratigraphy':
self.check_and_delete_stratigraphy(existing_columns_in_dest_table, dbconnection)
# Dump temptable to csv for debugging
if dump_temptable:
dbconnection.dump_table_2_csv(self.temptable_name)
# Import foreign keys in some special cases
foreign_keys = db_utils.get_foreign_keys(dest_table, dbconnection=dbconnection)
if foreign_keys:
if not allow_obs_fk_import:
for table in ['obs_points', 'obs_lines']:
if table in foreign_keys:
del foreign_keys[table]
if foreign_keys:
if self.foreign_keys_import_question is None:
msg = ru(QCoreApplication.translate('midv_data_importer', """Please note!\nForeign keys will be imported silently into "%s" if needed. \n\nProceed?""")) % (', '.join(list(foreign_keys.keys())))
utils.MessagebarAndLog.info(log_msg=msg)
stop_question = utils.Askuser("YesNo", msg, ru(QCoreApplication.translate('midv_data_importer', "Info!")))
if stop_question.result == 0: # if the user wants to abort
raise UserInterruptError()
else:
self.foreign_keys_import_question = 1
if self.foreign_keys_import_question == 1:
nr_before = nr_after
self.import_foreign_keys(dbconnection, dest_table, self.temptable_name, foreign_keys, existing_columns_in_temptable)
nr_after = dbconnection.execute_and_fetchall('''select count(*) from %s''' % (self.temptable_name))[0][0]
nr_after_foreign_keys = nr_before - nr_after
utils.MessagebarAndLog.info(log_msg=ru(QCoreApplication.translate('midv_data_importer', 'In total "%s" rows were deleted due to foreign keys restrictions and "%s" rows remain.'))%(str(nr_after_foreign_keys), str(nr_after)))
if not nr_after > 0:
raise MidvDataImporterError(ru(QCoreApplication.translate('midv_data_importer', 'Nothing imported, see log message panel')))
#Finally import data:
nr_failed_import = recsinfile - nr_after
if nr_failed_import > 0:
msg = ru(QCoreApplication.translate('midv_data_importer', """Please note!\nThere are %s rows in your data that can not be imported!\nDo you really want to import the rest?\nAnswering yes will start, from top of the imported file and only import the first of the duplicates.\n\nProceed?""" ))% (str(nr_failed_import))
utils.MessagebarAndLog.info(log_msg=msg)
stop_question = utils.Askuser("YesNo", msg, ru(QCoreApplication.translate('midv_data_importer', "Warning!")))
if stop_question.result == 0: # if the user wants to abort
raise UserInterruptError()
# Check if current table has geometry:
geom_columns = db_utils.get_geometry_types(dbconnection, dest_table)
sourcecols = []
for colname in sorted(existing_columns_in_dest_table):
null_replacement = db_utils.cast_null(column_headers_types[colname], dbconnection)
if colname in list(geom_columns.keys()) and colname in existing_columns_in_temptable:
sourcecols.append(self.create_geometry_sql(colname, dest_table, dbconnection, source_srid,
null_replacement, binary_geometry))
else:
sourcecols.append(
"""(CASE WHEN ({colname} !='' AND {colname} !=' ' AND {colname} IS NOT NULL)\n THEN CAST({colname} AS {type}) ELSE {null} END)""".format(
colname=colname,
type=column_headers_types[colname],
null=null_replacement))
sql = """INSERT INTO {dest_table} ({dest_columns})\nSELECT {source_columns}\nFROM {source_table}\n"""
kwargs = {'dest_table': dest_table,
'dest_columns': ', '.join(sorted(existing_columns_in_dest_table)),
'source_table': self.temptable_name,
'source_columns': u',\n '.join(sourcecols)
}
if not_null_columns:
sql += """WHERE {notnullcheck}"""
kwargs['notnullcheck'] = ' AND '.join(['%s IS NOT NULL'%notnullcol
for notnullcol in sorted(not_null_columns)])
sql = sql.format(**kwargs)
recsbefore = dbconnection.execute_and_fetchall('select count(*) from %s' % (dest_table))[0][0]
try:
dbconnection.execute(sql)
except Exception as e:
utils.MessagebarAndLog.info(log_msg=ru(QCoreApplication.translate('midv_data_importer', 'INSERT failed while importing to %s. Using INSERT OR IGNORE instead. Msg:\n')) % dest_table + ru(str(e)))
sql = db_utils.add_insert_or_ignore_to_sql(sql, dbconnection)
try:
dbconnection.execute(sql)
except Exception as e:
try:
str(e)
except UnicodeDecodeError:
utils.MessagebarAndLog.critical(bar_msg=ru(QCoreApplication.translate('midv_data_importer', 'Import failed, see log message panel')),
log_msg=ru(QCoreApplication.translate('midv_data_importer', 'Sql\n%s failed.')) % (sql), duration=999)
else:
utils.MessagebarAndLog.critical(bar_msg=ru(QCoreApplication.translate('midv_data_importer', 'Import failed, see log message panel')),
log_msg=ru(QCoreApplication.translate('midv_data_importer', 'Sql\n%s failed.\nMsg:\n%s')) % (sql, ru(str(e))), duration=999)
recsafter = dbconnection.execute_and_fetchall('select count(*) from %s' % (dest_table))[0][0]
nr_imported = recsafter - recsbefore
nr_excluded = recsinfile - nr_imported
utils.MessagebarAndLog.info(bar_msg=ru(QCoreApplication.translate('midv_data_importer', '%s rows imported and %s excluded for table %s. See log message panel for details'))%(nr_imported, nr_excluded, dest_table),
log_msg='--------------------')
except:
# If an external dbconnection is supplied, do not close it.
if _dbconnection is None:
try:
dbconnection.closedb()
except:
pass
else:
if self.temptable_name is not None:
#try:
dbconnection.drop_temporary_table(self.temptable_name)
#except:
# pass
utils.stop_waiting_cursor()
raise
else:
dbconnection.commit()
# If an external dbconnection is supplied, do not close it.
if _dbconnection is None:
try:
dbconnection.closedb()
except:
pass
else:
if self.temptable_name is not None:
#try:
dbconnection.drop_temporary_table(self.temptable_name)
#except:
# pass
utils.stop_waiting_cursor()
def test_get_foreign_keys(self):
""" """
foreign_keys = db_utils.get_foreign_keys('w_levels')
test_string = utils_for_tests.create_test_string(foreign_keys)
reference = '{obs_points: [(obsid, obsid)]}'
assert test_string == reference
def test_get_foreign_keys(self):
""" """
foreign_keys = db_utils.get_foreign_keys(u'w_levels')
test_string = utils_for_tests.create_test_string(foreign_keys)
reference = u'{obs_points: [(obsid, obsid)]}'
assert test_string == reference
def test_get_foreign_keys_no_keys(self):
""" """
foreign_keys = db_utils.get_foreign_keys('obs_points')
test_string = utils_for_tests.create_test_string(foreign_keys)
reference = '{}'
assert test_string == reference
def general_import(self,
goal_table,
file_data,
allow_obs_fk_import=False,
_dbconnection=None,
dump_temptable=False):
"""General method for importing an sqlite table into a goal_table
self.temptableName must be the name of the table containing the new data to import.
:param goal_table:
:return:
"""
try:
if file_data is None or not file_data:
return
utils.MessagebarAndLog.info(log_msg=ru(
QCoreApplication.translate(
'midv_data_importer',
'\nImport to %s starting\n--------------------')) %
goal_table)
utils.start_waiting_cursor()
self.temptable_name = goal_table + '_temp'
if not isinstance(_dbconnection, db_utils.DbConnectionManager):
dbconnection = db_utils.DbConnectionManager()
else:
dbconnection = _dbconnection
db_utils.activate_foreign_keys(activated=True,
dbconnection=dbconnection)
recsinfile = len(file_data[1:])
table_info = db_utils.db_tables_columns_info(
table=goal_table, dbconnection=dbconnection)
if not table_info:
raise MidvDataImporterError(
ru(
QCoreApplication.translate(
'midv_data_importer',
'The table %s did not exist. Update the database to latest version.'
)) % goal_table)
else:
table_info = table_info[goal_table]
#POINT and LINESTRING must be cast as BLOB. So change the type to BLOB.
column_headers_types = db_utils.change_cast_type_for_geometry_columns(
dbconnection, table_info, goal_table)
primary_keys = [
row[1] for row in table_info if int(row[5])
] #Not null columns are allowed if they have a default value.
not_null_columns = [
row[1] for row in table_info if int(row[3]) and row[4] is None
]
#Only use the columns that exists in the goal table.
existing_columns_in_goal_table = [
col for col in file_data[0] if col in column_headers_types
]
existing_columns_in_temptable = file_data[0]
missing_columns = [
column for column in not_null_columns
if column not in existing_columns_in_goal_table
]
if missing_columns:
raise MidvDataImporterError(
ru(
QCoreApplication.translate(
'midv_data_importer',
'Required columns %s are missing for table %s')) %
(', '.join(missing_columns), goal_table))
primary_keys_for_concat = [
pk for pk in primary_keys
if pk in existing_columns_in_temptable
]
self.list_to_table(dbconnection, file_data,
primary_keys_for_concat)
#Delete records from self.temptable where yyyy-mm-dd hh:mm or yyyy-mm-dd hh:mm:ss already exist for the same date.
nr_before = dbconnection.execute_and_fetchall(
'''select count(*) from %s''' % (self.temptable_name))[0][0]
if 'date_time' in primary_keys:
self.delete_existing_date_times_from_temptable(
primary_keys, goal_table, dbconnection)
nr_after = dbconnection.execute_and_fetchall(
'''select count(*) from %s''' % (self.temptable_name))[0][0]
nr_same_date = nr_after - nr_before
if nr_same_date > 0:
utils.MessagebarAndLog.info(log_msg=ru(
QCoreApplication.translate(
'midv_data_importer',
'In total "%s" rows with the same date \non format yyyy-mm-dd hh:mm or yyyy-mm-dd hh:mm:ss already existed and will not be imported. %s rows remain.'
)) % (str(nr_same_date), str(nr_after)))
if not nr_after > 0:
utils.MessagebarAndLog.warning(bar_msg=ru(
QCoreApplication.translate(
'midv_data_importer',
'Nothing imported to %s after deleting duplicate date_times'
)) % goal_table)
return
#Special cases for some tables
if goal_table == 'stratigraphy':
self.check_and_delete_stratigraphy(
existing_columns_in_goal_table, dbconnection)
# Check if current table has geometry:
geom_columns = db_utils.get_geometry_types(dbconnection,
goal_table)
for geom_col in geom_columns.keys():
if geom_col in existing_columns_in_temptable:
self.calculate_geometry(geom_col, goal_table, dbconnection)
# Dump temptable to csv for debugging
if dump_temptable:
dbconnection.dump_table_2_csv(self.temptable_name)
# Import foreign keys in some special cases
foreign_keys = db_utils.get_foreign_keys(goal_table,
dbconnection=dbconnection)
if foreign_keys:
if not allow_obs_fk_import:
for table in ['obs_points', 'obs_lines']:
if table in foreign_keys:
del foreign_keys[table]
if foreign_keys:
if self.foreign_keys_import_question is None:
msg = ru(
QCoreApplication.translate(
'midv_data_importer',
"""Please note!\nForeign keys will be imported silently into "%s" if needed. \n\nProceed?"""
)) % (', '.join(list(foreign_keys.keys())))
utils.MessagebarAndLog.info(log_msg=msg)
stop_question = utils.Askuser(
"YesNo", msg,
ru(
QCoreApplication.translate(
'midv_data_importer', "Info!")))
if stop_question.result == 0: # if the user wants to abort
raise UserInterruptError()
else:
self.foreign_keys_import_question = 1
if self.foreign_keys_import_question == 1:
nr_before = nr_after
self.import_foreign_keys(
dbconnection, goal_table, self.temptable_name,
foreign_keys, existing_columns_in_temptable)
nr_after = dbconnection.execute_and_fetchall(
'''select count(*) from %s''' %
(self.temptable_name))[0][0]
nr_after_foreign_keys = nr_before - nr_after
utils.MessagebarAndLog.info(log_msg=ru(
QCoreApplication.translate(
'midv_data_importer',
'In total "%s" rows were deleted due to foreign keys restrictions and "%s" rows remain.'
)) % (str(nr_after_foreign_keys), str(nr_after)))
if not nr_after > 0:
raise MidvDataImporterError(
ru(
QCoreApplication.translate(
'midv_data_importer',
'Nothing imported, see log message panel')))
#Finally import data:
nr_failed_import = recsinfile - nr_after
if nr_failed_import > 0:
msg = ru(
QCoreApplication.translate(
'midv_data_importer',
"""Please note!\nThere are %s rows in your data that can not be imported!\nDo you really want to import the rest?\nAnswering yes will start, from top of the imported file and only import the first of the duplicates.\n\nProceed?"""
)) % (str(nr_failed_import))
utils.MessagebarAndLog.info(log_msg=msg)
stop_question = utils.Askuser(
"YesNo", msg,
ru(
QCoreApplication.translate('midv_data_importer',
"Warning!")))
if stop_question.result == 0: # if the user wants to abort
raise UserInterruptError()
sql = """INSERT INTO %s (""" % goal_table
sql += ', '.join(sorted(existing_columns_in_goal_table))
sql += """) SELECT """
sql += ', '.join([
"""(CASE WHEN (%s !='' AND %s !=' ' AND %s IS NOT NULL) THEN CAST(%s AS %s) ELSE %s END)"""
% (colname, colname, colname, colname,
column_headers_types[colname],
db_utils.cast_null(column_headers_types[colname],
dbconnection))
for colname in sorted(existing_columns_in_goal_table)
])
sql += """FROM %s""" % (self.temptable_name)
if not_null_columns:
sql += """ WHERE %s""" % ' AND '.join([
'%s IS NOT NULL' % notnullcol
for notnullcol in sorted(not_null_columns)
])
recsbefore = dbconnection.execute_and_fetchall(
'select count(*) from %s' % (goal_table))[0][0]
try:
dbconnection.execute(sql)
except Exception as e:
utils.MessagebarAndLog.info(log_msg=ru(
QCoreApplication.translate(
'midv_data_importer',
'INSERT failed while importing to %s. Using INSERT OR IGNORE instead. Msg:\n'
)) % goal_table + ru(str(e)))
sql = db_utils.add_insert_or_ignore_to_sql(sql, dbconnection)
try:
dbconnection.execute(sql)
except Exception as e:
try:
str(e)
except UnicodeDecodeError:
utils.MessagebarAndLog.critical(
bar_msg=ru(
QCoreApplication.translate(
'midv_data_importer',
'Import failed, see log message panel')),
log_msg=ru(
QCoreApplication.translate(
'midv_data_importer',
'Sql\n%s failed.')) % (sql),
duration=999)
else:
utils.MessagebarAndLog.critical(
bar_msg=ru(
QCoreApplication.translate(
'midv_data_importer',
'Import failed, see log message panel')),
log_msg=ru(
QCoreApplication.translate(
'midv_data_importer',
'Sql\n%s failed.\nMsg:\n%s')) %
(sql, ru(str(e))),
duration=999)
recsafter = dbconnection.execute_and_fetchall(
'select count(*) from %s' % (goal_table))[0][0]
nr_imported = recsafter - recsbefore
nr_excluded = recsinfile - nr_imported
utils.MessagebarAndLog.info(bar_msg=ru(
QCoreApplication.translate(
'midv_data_importer',
'%s rows imported and %s excluded for table %s. See log message panel for details'
)) % (nr_imported, nr_excluded, goal_table),
log_msg='--------------------')
#If an external dbconnection is supplied, do not close it.
if _dbconnection is None:
dbconnection.commit_and_closedb()
else:
dbconnection.commit()
utils.stop_waiting_cursor()
except:
utils.stop_waiting_cursor()
try:
# If an external dbconnection is supplied, do not close it.
if _dbconnection is None:
dbconnection.closedb()
else:
pass
except NameError():
pass
except:
utils.MessagebarAndLog.warning(bar_msg=ru(
QCoreApplication.translate('midv_data_importer',
'Closing database failed!')))
raise
def add_metadata_to_about_db(self, dbconnection, created_tables_sqls=None):
tables = sorted(db_utils.get_tables(dbconnection=dbconnection, skip_views=True))
#Matches comment inside /* */
#create_table_sql CREATE TABLE meteo /*meteorological observations*/(
table_descr_reg = re.compile(r'/\*(.+)\*/', re.MULTILINE)
#Matches comment after --:
# strata text NOT NULL --clay etc
#, color_mplot text NOT NULL --color codes for matplotlib plots
column_descr_reg = re.compile(r'([A-Za-z_]+)[ ]+[A-Za-z ]*--(.+)', re.MULTILINE)
table_name_reg = re.compile(r'([A-Za-z_]+)[ ]+[A-Za-z ]*--(.+)', re.MULTILINE)
for table in tables:
#Get table and column comments
if created_tables_sqls is None:
table_descr_sql = ("SELECT name, sql from sqlite_master WHERE name = '%s';"%table)
create_table_sql = dbconnection.execute_and_fetchall(table_descr_sql)[0][1]
else:
create_table_sql = created_tables_sqls[table]
table_descr = table_descr_reg.findall(create_table_sql)
try:
table_descr = table_descr[0]
except IndexError:
table_descr = None
else:
table_descr = table_descr.rstrip('\n').rstrip('\r').replace("'", "''")
columns_descr = dict(column_descr_reg.findall(create_table_sql))
table_info = db_utils.get_table_info(table, dbconnection)
foreign_keys_dict = {}
foreign_keys = db_utils.get_foreign_keys(table, dbconnection)
for _table, _from_to in foreign_keys.items():
_from = _from_to[0][0]
_to = _from_to[0][1]
foreign_keys_dict[_from] = (_table, _to)
sql = r"""INSERT INTO about_db (tablename, columnname, description, data_type, not_null, default_value, primary_key, foreign_key) VALUES """
sql += r'({});'.format(', '.join(["""(CASE WHEN '%s' != '' or '%s' != ' ' or '%s' IS NOT NULL THEN '%s' else NULL END)"""%(col, col, col, col) for col in [table, r'*', table_descr, r'', r'', r'', r'', r'']]))
dbconnection.execute(sql)
for column in table_info:
colname = column[1]
data_type = column[2]
not_null = str(column[3]) if str(column[3]) == '1' else ''
default_value = column[4] if column[4] else ''
primary_key = str(column[5]) if str(column[5]) != '0' else ''
_foreign_keys = ''
if colname in foreign_keys_dict:
_foreign_keys = '%s(%s)'%(foreign_keys_dict[colname])
column_descr = columns_descr.get(colname, None)
if column_descr:
column_descr = column_descr.rstrip('\n').rstrip('\r').replace("'", "''")
sql = 'INSERT INTO about_db (tablename, columnname, data_type, not_null, default_value, primary_key, foreign_key, description) VALUES '
sql += '({});'.format(', '.join(["""CASE WHEN '%s' != '' or '%s' != ' ' or '%s' IS NOT NULL THEN '%s' else NULL END"""%(col, col, col, col) for col in [table, colname, data_type, not_null, default_value, primary_key, _foreign_keys, column_descr]]))
try:
dbconnection.execute(sql)
except:
try:
print(sql)
except:
pass
raise
def general_import(self, goal_table, file_data, allow_obs_fk_import=False, _dbconnection=None, dump_temptable=False):
"""General method for importing an sqlite table into a goal_table
self.temptableName must be the name of the table containing the new data to import.
:param goal_table:
:return:
"""
try:
if file_data is None or not file_data:
return
utils.MessagebarAndLog.info(log_msg=ru(QCoreApplication.translate('midv_data_importer', '\nImport to %s starting\n--------------------'))%goal_table)
utils.start_waiting_cursor()
self.temptable_name = goal_table + '_temp'
if not isinstance(_dbconnection, db_utils.DbConnectionManager):
dbconnection = db_utils.DbConnectionManager()
else:
dbconnection = _dbconnection
db_utils.activate_foreign_keys(activated=True, dbconnection=dbconnection)
recsinfile = len(file_data[1:])
table_info = db_utils.db_tables_columns_info(table=goal_table, dbconnection=dbconnection)
if not table_info:
raise MidvDataImporterError(ru(QCoreApplication.translate('midv_data_importer', 'The table %s did not exist. Update the database to latest version.')) % goal_table)
else:
table_info = table_info[goal_table]
#POINT and LINESTRING must be cast as BLOB. So change the type to BLOB.
column_headers_types = db_utils.change_cast_type_for_geometry_columns(dbconnection, table_info, goal_table)
primary_keys = [row[1] for row in table_info if int(row[5])] #Not null columns are allowed if they have a default value.
not_null_columns = [row[1] for row in table_info if int(row[3]) and row[4] is None]
#Only use the columns that exists in the goal table.
existing_columns_in_goal_table = [col for col in file_data[0] if col in column_headers_types]
existing_columns_in_temptable = file_data[0]
missing_columns = [column for column in not_null_columns if column not in existing_columns_in_goal_table]
if missing_columns:
raise MidvDataImporterError(ru(QCoreApplication.translate('midv_data_importer', 'Required columns %s are missing for table %s')) % (', '.join(missing_columns), goal_table))
primary_keys_for_concat = [pk for pk in primary_keys if pk in existing_columns_in_temptable]
self.list_to_table(dbconnection, file_data, primary_keys_for_concat)
#Delete records from self.temptable where yyyy-mm-dd hh:mm or yyyy-mm-dd hh:mm:ss already exist for the same date.
nr_before = dbconnection.execute_and_fetchall('''select count(*) from %s''' % (self.temptable_name))[0][0]
if 'date_time' in primary_keys:
self.delete_existing_date_times_from_temptable(primary_keys, goal_table, dbconnection)
nr_after = dbconnection.execute_and_fetchall('''select count(*) from %s''' % (self.temptable_name))[0][0]
nr_same_date = nr_after - nr_before
if nr_same_date > 0:
utils.MessagebarAndLog.info(log_msg=ru(QCoreApplication.translate('midv_data_importer', 'In total "%s" rows with the same date \non format yyyy-mm-dd hh:mm or yyyy-mm-dd hh:mm:ss already existed and will not be imported. %s rows remain.'))%(str(nr_same_date), str(nr_after)))
if not nr_after > 0:
utils.MessagebarAndLog.warning(bar_msg=ru(QCoreApplication.translate('midv_data_importer', 'Nothing imported to %s after deleting duplicate date_times'))%goal_table)
return
#Special cases for some tables
if goal_table == 'stratigraphy':
self.check_and_delete_stratigraphy(existing_columns_in_goal_table, dbconnection)
# Check if current table has geometry:
geom_columns = db_utils.get_geometry_types(dbconnection, goal_table)
for geom_col in geom_columns.keys():
if geom_col in existing_columns_in_temptable:
self.calculate_geometry(geom_col, goal_table, dbconnection)
# Dump temptable to csv for debugging
if dump_temptable:
dbconnection.dump_table_2_csv(self.temptable_name)
# Import foreign keys in some special cases
foreign_keys = db_utils.get_foreign_keys(goal_table, dbconnection=dbconnection)
if foreign_keys:
if not allow_obs_fk_import:
for table in ['obs_points', 'obs_lines']:
if table in foreign_keys:
del foreign_keys[table]
if foreign_keys:
if self.foreign_keys_import_question is None:
msg = ru(QCoreApplication.translate('midv_data_importer', """Please note!\nForeign keys will be imported silently into "%s" if needed. \n\nProceed?""")) % (', '.join(list(foreign_keys.keys())))
utils.MessagebarAndLog.info(log_msg=msg)
stop_question = utils.Askuser("YesNo", msg, ru(QCoreApplication.translate('midv_data_importer', "Info!")))
if stop_question.result == 0: # if the user wants to abort
raise UserInterruptError()
else:
self.foreign_keys_import_question = 1
if self.foreign_keys_import_question == 1:
nr_before = nr_after
self.import_foreign_keys(dbconnection, goal_table, self.temptable_name, foreign_keys, existing_columns_in_temptable)
nr_after = dbconnection.execute_and_fetchall('''select count(*) from %s''' % (self.temptable_name))[0][0]
nr_after_foreign_keys = nr_before - nr_after
utils.MessagebarAndLog.info(log_msg=ru(QCoreApplication.translate('midv_data_importer', 'In total "%s" rows were deleted due to foreign keys restrictions and "%s" rows remain.'))%(str(nr_after_foreign_keys), str(nr_after)))
if not nr_after > 0:
raise MidvDataImporterError(ru(QCoreApplication.translate('midv_data_importer', 'Nothing imported, see log message panel')))
#Finally import data:
nr_failed_import = recsinfile - nr_after
if nr_failed_import > 0:
msg = ru(QCoreApplication.translate('midv_data_importer', """Please note!\nThere are %s rows in your data that can not be imported!\nDo you really want to import the rest?\nAnswering yes will start, from top of the imported file and only import the first of the duplicates.\n\nProceed?""" ))% (str(nr_failed_import))
utils.MessagebarAndLog.info(log_msg=msg)
stop_question = utils.Askuser("YesNo", msg, ru(QCoreApplication.translate('midv_data_importer', "Warning!")))
if stop_question.result == 0: # if the user wants to abort
raise UserInterruptError()
sql = """INSERT INTO %s ("""%goal_table
sql += ', '.join(sorted(existing_columns_in_goal_table))
sql += """) SELECT """
sql += ', '.join(["""(CASE WHEN (%s !='' AND %s !=' ' AND %s IS NOT NULL) THEN CAST(%s AS %s) ELSE %s END)""" % (colname, colname, colname, colname, column_headers_types[colname], db_utils.cast_null(column_headers_types[colname], dbconnection)) for colname in sorted(existing_columns_in_goal_table)])
sql += """FROM %s""" % (self.temptable_name)
if not_null_columns:
sql += """ WHERE %s"""%' AND '.join(['%s IS NOT NULL'%notnullcol for notnullcol in sorted(not_null_columns)])
recsbefore = dbconnection.execute_and_fetchall('select count(*) from %s' % (goal_table))[0][0]
try:
dbconnection.execute(sql)
except Exception as e:
utils.MessagebarAndLog.info(log_msg=ru(QCoreApplication.translate('midv_data_importer', 'INSERT failed while importing to %s. Using INSERT OR IGNORE instead. Msg:\n')) % goal_table + ru(str(e)))
sql = db_utils.add_insert_or_ignore_to_sql(sql, dbconnection)
try:
dbconnection.execute(sql)
except Exception as e:
try:
str(e)
except UnicodeDecodeError:
utils.MessagebarAndLog.critical(bar_msg=ru(QCoreApplication.translate('midv_data_importer', 'Import failed, see log message panel')),
log_msg=ru(QCoreApplication.translate('midv_data_importer', 'Sql\n%s failed.')) % (sql), duration=999)
else:
utils.MessagebarAndLog.critical(bar_msg=ru(QCoreApplication.translate('midv_data_importer', 'Import failed, see log message panel')),
log_msg=ru(QCoreApplication.translate('midv_data_importer', 'Sql\n%s failed.\nMsg:\n%s')) % (sql, ru(str(e))), duration=999)
recsafter = dbconnection.execute_and_fetchall('select count(*) from %s' % (goal_table))[0][0]
nr_imported = recsafter - recsbefore
nr_excluded = recsinfile - nr_imported
utils.MessagebarAndLog.info(bar_msg=ru(QCoreApplication.translate('midv_data_importer', '%s rows imported and %s excluded for table %s. See log message panel for details'))%(nr_imported, nr_excluded, goal_table),
log_msg='--------------------')
#If an external dbconnection is supplied, do not close it.
if _dbconnection is None:
dbconnection.commit_and_closedb()
else:
dbconnection.commit()
utils.stop_waiting_cursor()
except:
utils.stop_waiting_cursor()
try:
# If an external dbconnection is supplied, do not close it.
if _dbconnection is None:
dbconnection.closedb()
else:
pass
except NameError():
pass
except:
utils.MessagebarAndLog.warning(bar_msg=ru(QCoreApplication.translate('midv_data_importer', 'Closing database failed!')))
raise
def start_import(self):
"""
TODO: I have NO IDEA where the dummy parameter is coming from. It gets the value False for some reason!
:param dummy:
:return:
"""
if self.file_data is None:
raise utils.UsageError(ru(QCoreApplication.translate('GeneralCsvImportGui', 'Error, must select a file first!')))
translation_dict = self.table_chooser.get_translation_dict()
file_data = copy.deepcopy(self.file_data)
goal_table = self.table_chooser.import_method
foreign_keys = db_utils.get_foreign_keys(goal_table)
foreign_key_obsid_tables = [tname for tname, colnames in foreign_keys.items() for colname in colnames if colname[0] == 'obsid']
if len(foreign_key_obsid_tables) == 1:
foreign_key_obsid_table = foreign_key_obsid_tables[0]
else:
foreign_key_obsid_table = goal_table
for file_column in list(translation_dict.keys()):
alter_colnames = []
new_value = None
# Check if obsid should be set from selection and add an obsid-column if so.
if isinstance(file_column, Obsids_from_selection):
selected = utils.get_selected_features_as_tuple()
if len(selected) != 1:
utils.MessagebarAndLog.critical(bar_msg=ru(QCoreApplication.translate('GeneralCsvImportGui', 'Import error, must select 1 obsid')), duration=60)
return 'cancel'
alter_colnames = ['obsid']
new_value = selected[0]
elif isinstance(file_column, StaticValue):
if translation_dict[file_column]:
alter_colnames = translation_dict[file_column]
new_value = file_column.value
for alter_colname in alter_colnames:
if alter_colnames is not None and new_value is not None:
try:
colindex = file_data[0].index(alter_colname)
except ValueError:
colindex = len(file_data[0])
file_data[0].append(alter_colname)
for row in file_data[1:]:
if colindex + 1 < len(file_data[0]):
row[colindex] = new_value
else:
row.append(new_value)
#[row.insert(obsidindex, selected[0]) if obsidindex + 1 < len(file_data[0]) else row.append(selected[0]) for row in file_data[1:]]
del translation_dict[file_column]
translation_dict[alter_colname] = [alter_colname]
columns_factors = self.table_chooser.get_columns_factors_dict()
#Translate column names and add columns that appear more than once
file_data = self.translate_and_reorder_file_data(file_data, translation_dict)
file_data = self.convert_comma_to_points_for_double_columns(file_data, self.tables_columns_info[goal_table])
if columns_factors:
file_data = self.multiply_by_factor(file_data, columns_factors)
file_data = self.remove_preceding_trailing_spaces_tabs(file_data)
if foreign_key_obsid_table and foreign_key_obsid_table != goal_table and 'obsid' in file_data[0]:
file_data = utils.filter_nonexisting_values_and_ask(file_data, 'obsid', utils.get_all_obsids(foreign_key_obsid_table), try_capitalize=False)
file_data = self.reformat_date_time(file_data)
importer = import_data_to_db.midv_data_importer()
answer = importer.general_import(goal_table=goal_table, file_data=file_data)
utils.stop_waiting_cursor()
if self.close_after_import.isChecked():
self.close()
