def import_foreign_keys(self, dbconnection, goal_table, temptablename, foreign_keys, existing_columns_in_temptable):
#TODO: Empty foreign keys are probably imported now. Must add "case when...NULL" to a couple of sql questions here
#What I want to do:
# import all foreign keys from temptable that doesn't already exist in foreign key table
# insert into fk_table (to1, to2) select distinct from1(cast as), from2(cast as) from temptable where concatted_from_and_case_when_null not in concatted_to_and_case_when_null
for fk_table, from_to_fields in foreign_keys.items():
from_list = [x[0] for x in from_to_fields]
to_list = [x[1] for x in from_to_fields]
if not all([_from in existing_columns_in_temptable for _from in from_list]):
utils.MessagebarAndLog.warning(bar_msg=ru(QCoreApplication.translate('midv_data_importer', 'Import of foreign keys failed, see log message panel')), log_msg=ru(QCoreApplication.translate('midv_data_importer', 'There were keys missing for importing to fk_table %s, so no import was done.'))%fk_table)
continue
nr_fk_before = dbconnection.execute_and_fetchall('''select count(*) from %s''' % fk_table)[0][0]
table_info = db_utils.db_tables_columns_info(table=fk_table, dbconnection=dbconnection)[fk_table]
column_headers_types = dict([(row[1], row[2]) for row in table_info])
null_replacement_string = 'NULL_NULL_NULL_NULL_NULL_NULL_NULL_NULL_NULL_NULL'
concatted_from_string = '||'.join(["CASE WHEN %s is NULL THEN '%s' ELSE %s END"%(x, null_replacement_string, x) for x in from_list])
concatted_to_string = '||'.join(["CASE WHEN %s is NULL THEN '%s' ELSE %s END"%(x, null_replacement_string, x) for x in to_list])
sql = u'INSERT INTO %s (%s) SELECT DISTINCT %s FROM %s AS b WHERE %s NOT IN (SELECT %s FROM %s) AND %s'%(fk_table,
u', '.join([u'"{}"'.format(k) for k in to_list]),
u', '.join([u'''CAST("b"."%s" as "%s")'''%(k, column_headers_types[to_list[idx]]) for idx, k in enumerate(from_list)]),
temptablename,
concatted_from_string,
concatted_to_string,
fk_table,
' AND '.join([''' b.{} IS NOT NULL and b.{} != '' '''.format(k, k, k) for k in from_list]))
dbconnection.execute(sql)
nr_fk_after = dbconnection.execute_and_fetchall('''select count(*) from %s''' % fk_table)[0][0]
if nr_fk_after > nr_fk_before:
utils.MessagebarAndLog.info(log_msg=ru(QCoreApplication.translate('midv_data_importer', 'In total %s rows were imported to foreign key table %s while importing to %s.'))%(str(nr_fk_after - nr_fk_before), fk_table, goal_table))
def import_foreign_keys(self, dbconnection, dest_table, temptablename, foreign_keys, existing_columns_in_temptable):
#TODO: Empty foreign keys are probably imported now. Must add "case when...NULL" to a couple of sql questions here
#What I want to do:
# import all foreign keys from temptable that doesn't already exist in foreign key table
# insert into fk_table (to1, to2) select distinct from1(cast as), from2(cast as) from temptable where concatted_from_and_case_when_null not in concatted_to_and_case_when_null
for fk_table, from_to_fields in foreign_keys.items():
from_list = [x[0] for x in from_to_fields]
to_list = [x[1] for x in from_to_fields]
if not all([_from in existing_columns_in_temptable for _from in from_list]):
utils.MessagebarAndLog.warning(bar_msg=ru(QCoreApplication.translate('midv_data_importer', 'Import of foreign keys failed, see log message panel')), log_msg=ru(QCoreApplication.translate('midv_data_importer', 'There were keys missing for importing to fk_table %s, so no import was done.'))%fk_table)
continue
nr_fk_before = dbconnection.execute_and_fetchall('''select count(*) from %s''' % fk_table)[0][0]
table_info = db_utils.db_tables_columns_info(table=fk_table, dbconnection=dbconnection)[fk_table]
column_headers_types = dict([(row[1], row[2]) for row in table_info])
null_replacement_string = 'NULL_NULL_NULL_NULL_NULL_NULL_NULL_NULL_NULL_NULL'
concatted_from_string = '||'.join(["CASE WHEN %s is NULL THEN '%s' ELSE %s END"%(x, null_replacement_string, x) for x in from_list])
concatted_to_string = '||'.join(["CASE WHEN %s is NULL THEN '%s' ELSE %s END"%(x, null_replacement_string, x) for x in to_list])
sql = u'INSERT INTO %s (%s) SELECT DISTINCT %s FROM %s AS b WHERE %s NOT IN (SELECT %s FROM %s) AND %s'%(fk_table,
u', '.join([u'"{}"'.format(k) for k in to_list]),
u', '.join([u'''CAST("b"."%s" as "%s")'''%(k, column_headers_types[to_list[idx]]) for idx, k in enumerate(from_list)]),
temptablename,
concatted_from_string,
concatted_to_string,
fk_table,
' AND '.join([''' b.{} IS NOT NULL and b.{} != '' '''.format(k, k, k) for k in from_list]))
dbconnection.execute(sql)
nr_fk_after = dbconnection.execute_and_fetchall('''select count(*) from %s''' % fk_table)[0][0]
if nr_fk_after > nr_fk_before:
utils.MessagebarAndLog.info(log_msg=ru(QCoreApplication.translate('midv_data_importer', 'In total %s rows were imported to foreign key table %s while importing to %s.'))%(str(nr_fk_after - nr_fk_before), fk_table, dest_table))
def test_tables_columns(self):
res = db_utils.db_tables_columns_info()
assert res
assert isinstance(res, dict)
for k, v in res.items():
assert isinstance(k, str)
assert isinstance(v, (tuple, list))
for x in v:
assert isinstance(x, (tuple, list))
assert x
def test_tables_columns(self):
res = db_utils.db_tables_columns_info()
assert res
assert isinstance(res, dict)
for k, v in res.items():
assert isinstance(k, str)
assert isinstance(v, (tuple, list))
for x in v:
assert isinstance(x, (tuple, list))
assert x
def test_tables_columns_info_only_obs_points(self):
""" """
# Assert that obsid is primary key and not null in obs_points
# {'tablename': (ordernumber, name, type, notnull, defaultvalue, primarykey)}
tables_columns_info = db_utils.db_tables_columns_info('obs_points')
col_obsid = [col for col in tables_columns_info['obs_points'] if col[1] == 'obsid'][0]
assert len(tables_columns_info) == 1
assert int(col_obsid[0]) == 0
assert col_obsid[1] == 'obsid'
assert col_obsid[2].lower() == 'text'
assert int(col_obsid[3]) == 1
assert col_obsid[4] is None
assert int(col_obsid[5]) == 1
def test_tables_columns_info_only_obs_points(self):
""" """
# Assert that obsid is primary key and not null in obs_points
# {u'tablename': (ordernumber, name, type, notnull, defaultvalue, primarykey)}
tables_columns_info = db_utils.db_tables_columns_info(u'obs_points')
col_obsid = [
col for col in tables_columns_info[u'obs_points']
if col[1] == u'obsid'
][0]
assert len(tables_columns_info) == 1
assert int(col_obsid[0]) == 1
assert col_obsid[1] == u'obsid'
assert col_obsid[2].lower() == u'text'
assert int(col_obsid[3]) == 1
assert col_obsid[4] is None
assert int(col_obsid[5]) == 1
def load_gui(self):
self.tables_columns_info = {k: v for (k, v) in db_utils.db_tables_columns_info().items() if not k.endswith('_geom')}
self.table_chooser = ImportTableChooser(self.tables_columns_info, file_header=None)
self.main_vertical_layout.addWidget(self.table_chooser.widget)
self.main_vertical_layout.addStretch()
#General buttons
self.select_file_button = qgis.PyQt.QtWidgets.QPushButton(ru(QCoreApplication.translate('GeneralCsvImportGui', 'Load data from file')))
self.gridLayout_buttons.addWidget(self.select_file_button, 0, 0)
self.select_file_button.clicked.connect(lambda x: self.select_file())
self.import_all_features_button = qgis.PyQt.QtWidgets.QPushButton(ru(QCoreApplication.translate('GeneralCsvImportGui', 'Load data from all features\nfrom active layer')))
self.gridLayout_buttons.addWidget(self.import_all_features_button, 1, 0)
self.import_all_features_button.clicked.connect(lambda x: self.import_all_features())
self.import_selected_features_button = qgis.PyQt.QtWidgets.QPushButton(ru(QCoreApplication.translate('GeneralCsvImportGui', 'Load data from selected features\nfrom active layer')))
self.gridLayout_buttons.addWidget(self.import_selected_features_button, 2, 0)
self.import_selected_features_button.clicked.connect(lambda x: self.import_selected_features())
self.gridLayout_buttons.addWidget(get_line(), 3, 0)
tables_columns = db_utils.tables_columns()
self.distinct_value_browser = DistinctValuesBrowser(tables_columns)
self.gridLayout_buttons.addWidget(self.distinct_value_browser.widget, 4, 0)
self.gridLayout_buttons.addWidget(get_line(), 5, 0)
self.close_after_import = qgis.PyQt.QtWidgets.QCheckBox(ru(QCoreApplication.translate('GeneralCsvImportGui', 'Close dialog after import')))
self.close_after_import.setChecked(True)
self.gridLayout_buttons.addWidget(self.close_after_import, 6, 0)
self.start_import_button = qgis.PyQt.QtWidgets.QPushButton(ru(QCoreApplication.translate('GeneralCsvImportGui', 'Start import')))
self.gridLayout_buttons.addWidget(self.start_import_button, 7, 0)
self.start_import_button.clicked.connect(lambda x: self.start_import())
self.gridLayout_buttons.setRowStretch(8, 1)
self.show()
def load_gui(self):
self.tables_columns_info = {k: v for (k, v) in db_utils.db_tables_columns_info().items() if not k.endswith('_geom')}
self.table_chooser = ImportTableChooser(self.tables_columns_info, file_header=None)
self.main_vertical_layout.addWidget(self.table_chooser.widget)
self.main_vertical_layout.addStretch()
#General buttons
self.select_file_button = qgis.PyQt.QtWidgets.QPushButton(ru(QCoreApplication.translate('GeneralCsvImportGui', 'Load data from file')))
self.gridLayout_buttons.addWidget(self.select_file_button, 0, 0)
self.select_file_button.clicked.connect(lambda x: self.select_file())
self.import_all_features_button = qgis.PyQt.QtWidgets.QPushButton(ru(QCoreApplication.translate('GeneralCsvImportGui', 'Load data from all features\nfrom active layer')))
self.gridLayout_buttons.addWidget(self.import_all_features_button, 1, 0)
self.import_all_features_button.clicked.connect(lambda x: self.import_all_features())
self.import_selected_features_button = qgis.PyQt.QtWidgets.QPushButton(ru(QCoreApplication.translate('GeneralCsvImportGui', 'Load data from selected features\nfrom active layer')))
self.gridLayout_buttons.addWidget(self.import_selected_features_button, 2, 0)
self.import_selected_features_button.clicked.connect(lambda x: self.import_selected_features())
self.gridLayout_buttons.addWidget(get_line(), 3, 0)
tables_columns = db_utils.tables_columns()
self.distinct_value_browser = DistinctValuesBrowser(tables_columns)
self.gridLayout_buttons.addWidget(self.distinct_value_browser.widget, 4, 0)
self.gridLayout_buttons.addWidget(get_line(), 5, 0)
self.close_after_import = qgis.PyQt.QtWidgets.QCheckBox(ru(QCoreApplication.translate('GeneralCsvImportGui', 'Close dialog after import')))
self.close_after_import.setChecked(True)
self.gridLayout_buttons.addWidget(self.close_after_import, 6, 0)
self.start_import_button = qgis.PyQt.QtWidgets.QPushButton(ru(QCoreApplication.translate('GeneralCsvImportGui', 'Start import')))
self.gridLayout_buttons.addWidget(self.start_import_button, 7, 0)
self.start_import_button.clicked.connect(lambda x: self.start_import())
self.gridLayout_buttons.setRowStretch(8, 1)
self.show()
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 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 load_gui(self):
self.tables_columns_info = {
k: v
for (k, v) in db_utils.db_tables_columns_info().iteritems()
if not k.endswith(u'_geom')
}
self.table_chooser = ImportTableChooser(self.tables_columns_info,
self.connect,
file_header=None)
self.main_vertical_layout.addWidget(self.table_chooser.widget)
self.main_vertical_layout.addStretch()
#General buttons
self.select_file_button = PyQt4.QtGui.QPushButton(
ru(
QCoreApplication.translate(u'GeneralCsvImportGui',
u'Load data from file')))
self.gridLayout_buttons.addWidget(self.select_file_button, 0, 0)
self.connect(
self.select_file_button, PyQt4.QtCore.SIGNAL("clicked()"),
lambda: map(lambda x: x(), [
lambda: self.load_files(), lambda: self.table_chooser.reload(),
lambda: self.file_data_loaded_popup()
]))
self.import_all_features_button = PyQt4.QtGui.QPushButton(
ru(
QCoreApplication.translate(
u'GeneralCsvImportGui',
u'Load data from all features\nfrom active layer')))
self.gridLayout_buttons.addWidget(self.import_all_features_button, 1,
0)
self.connect(
self.import_all_features_button, PyQt4.QtCore.SIGNAL("clicked()"),
lambda: map(lambda x: x(), [
lambda: self.load_from_active_layer(
only_selected=False), lambda: self.table_chooser.reload(),
lambda: self.file_data_loaded_popup()
]))
self.import_selected_features_button = PyQt4.QtGui.QPushButton(
ru(
QCoreApplication.translate(
u'GeneralCsvImportGui',
u'Load data from selected features\nfrom active layer')))
self.gridLayout_buttons.addWidget(self.import_selected_features_button,
2, 0)
self.connect(
self.import_selected_features_button,
PyQt4.QtCore.SIGNAL("clicked()"),
lambda: map(lambda x: x(), [
lambda: self.load_from_active_layer(
only_selected=True), lambda: self.table_chooser.reload(),
lambda: self.file_data_loaded_popup()
]))
self.gridLayout_buttons.addWidget(get_line(), 3, 0)
tables_columns = db_utils.tables_columns()
self.distinct_value_browser = DistinctValuesBrowser(
tables_columns, self.connect)
self.gridLayout_buttons.addWidget(self.distinct_value_browser.widget,
4, 0)
self.gridLayout_buttons.addWidget(get_line(), 5, 0)
self.close_after_import = PyQt4.QtGui.QCheckBox(
ru(
QCoreApplication.translate(u'GeneralCsvImportGui',
u'Close dialog after import')))
self.close_after_import.setChecked(True)
self.gridLayout_buttons.addWidget(self.close_after_import, 6, 0)
self.start_import_button = PyQt4.QtGui.QPushButton(
ru(
QCoreApplication.translate(u'GeneralCsvImportGui',
u'Start import')))
self.gridLayout_buttons.addWidget(self.start_import_button, 7, 0)
self.connect(self.start_import_button,
PyQt4.QtCore.SIGNAL("clicked()"), self.start_import)
self.gridLayout_buttons.setRowStretch(8, 1)
self.show()
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