def load_files(self):
charset = utils.ask_for_charset()
if not charset:
raise utils.UserInterruptError()
filename = utils.select_files(only_one_file=True, extension=ru(QCoreApplication.translate('GeneralCsvImportGui', "Comma or semicolon separated csv file %s;;Comma or semicolon separated csv text file %s;;Comma or semicolon separated file %s"))%('(*.csv)', '(*.txt)', '(*.*)'))
if isinstance(filename, (list, tuple)):
filename = filename[0]
filename = ru(filename)
delimiter = utils.get_delimiter(filename=filename, charset=charset, delimiters=[',', ';'])
self.file_data = self.file_to_list(filename, charset, delimiter)
header_question = utils.Askuser(question="YesNo", msg=ru(QCoreApplication.translate('GeneralCsvImportGui', """Does the file contain a header?""")))
utils.start_waiting_cursor()
if header_question.result:
# Remove duplicate header entries
header = self.file_data[0]
seen = set()
seen_add = seen.add
remove_cols = [idx for idx, x in enumerate(header) if x and (x in seen or seen_add(x))]
self.file_data = [[col for idx, col in enumerate(row) if idx not in remove_cols] for row in self.file_data]
self.table_chooser.file_header = self.file_data[0]
else:
header = ['Column ' + str(colnr) for colnr in range(len(self.file_data[0]))]
self.table_chooser.file_header = header
self.file_data.reverse()
self.file_data.append(header)
self.file_data.reverse()
utils.stop_waiting_cursor()
def SanityCheckVacuumDB(self, dbconnection=None):
if dbconnection is None:
dbconnection = db_utils.DbConnectionManager()
sanity = utils.Askuser(
"YesNo",
ru(
QCoreApplication.translate(
u'midv_data_importer',
"""It is a strong recommendation that you do vacuum the database now, do you want to do so?\n(If unsure - then answer "yes".)"""
)),
ru(
QCoreApplication.translate(u'midv_data_importer',
'Vacuum the database?')))
if sanity.result == 1:
PyQt4.QtGui.QApplication.setOverrideCursor(
PyQt4.QtCore.Qt.WaitCursor)
dbconnection.vacuum(
) # since a temporary table was loaded and then deleted - the db may need vacuuming
PyQt4.QtGui.QApplication.restoreOverrideCursor()
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_askuser_dateshift_cancel(self):
question = utils.Askuser('DateShift')
assert question.result == 'cancel'
def test_askuser_dateshift(self):
question = utils.Askuser('DateShift')
assert question.result == ['-1', 'hours']
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