def list_to_table(self, dbconnection, destination_table, file_data, primary_keys_for_concat):
fieldnames_types = ['{} TEXT'.format(field_name) for field_name in file_data[0]]
self.temptable_name = dbconnection.create_temporary_table_for_import(destination_table + '_temp', fieldnames_types)
placeholder_sign = db_utils.placeholder_sign(dbconnection)
concat_cols = [file_data[0].index(pk) for pk in primary_keys_for_concat]
added_rows = set()
numskipped = 0
sql = """INSERT INTO %s VALUES (%s)""" % (self.temptable_name, ', '.join([placeholder_sign for x in range(len(file_data[0]))]))
for row in file_data[1:]:
if primary_keys_for_concat:
concatted = '|'.join([ru(row[idx]) for idx in concat_cols])
if concatted in added_rows:
numskipped += 1
continue
else:
added_rows.add(concatted)
args = tuple([None if any([r is None, (isinstance(r, str) and not r.strip()) if r is not None else None])
else r for r in row])
dbconnection.cursor.execute(sql, args)
#TODO: Let's see what happens without commit
#dbconnection.commit()
if numskipped:
utils.MessagebarAndLog.warning(bar_msg=ru(QCoreApplication.translate('midv_data_importer', 'Import warning, duplicates skipped')), log_msg=ru(QCoreApplication.translate('midv_data_importer', "%s nr of duplicate rows in file was skipped while importing."))%str(numskipped))
def list_to_table(self, dbconnection, file_data, primary_keys_for_concat):
fieldnames_types = ['{} TEXT'.format(field_name) for field_name in file_data[0]]
self.temptable_name = dbconnection.create_temporary_table_for_import(self.temptable_name, fieldnames_types)
placeholder_sign = db_utils.placeholder_sign(dbconnection)
concat_cols = [file_data[0].index(pk) for pk in primary_keys_for_concat]
added_rows = set()
numskipped = 0
sql = """INSERT INTO %s VALUES (%s)""" % (self.temptable_name, ', '.join([placeholder_sign for x in range(len(file_data[0]))]))
for row in file_data[1:]:
if primary_keys_for_concat:
concatted = '|'.join([row[idx] for idx in concat_cols])
if concatted in added_rows:
numskipped += 1
continue
else:
added_rows.add(concatted)
args = tuple([None if any([r is None, not r.strip() if r is not None else None]) else r for r in row])
dbconnection.cursor.execute(sql, args)
#TODO: Let's see what happens without commit
#dbconnection.commit()
if numskipped:
utils.MessagebarAndLog.warning(bar_msg=ru(QCoreApplication.translate('midv_data_importer', 'Import warning, duplicates skipped')), log_msg=ru(QCoreApplication.translate('midv_data_importer', "%s nr of duplicate rows in file was skipped while importing."))%str(numskipped))
def drop_db_views(self):
# TODO: Update to support PostGIS
sql1="delete from views_geometry_columns where view_name = 'strat_obs_p_for_qgsi2threejs'"
sql2="drop view if exists strat_obs_p_for_qgsi2threejs"
db_utils.sql_alter_db(sql1, dbconnection=self.dbconnection)
db_utils.sql_alter_db(sql2, dbconnection=self.dbconnection)
placeholder_sign = db_utils.placeholder_sign(self.dbconnection)
sql1="delete from views_geometry_columns where view_name = %s"%placeholder_sign
sql2="drop view if exists "
for key in self.strat_layers_dict:
db_utils.sql_alter_db(sql1, dbconnection=self.dbconnection, all_args=[(key,)])
db_utils.sql_alter_db(sql2 + key, dbconnection=self.dbconnection)
def drop_db_views(self):
sql1 = "delete from views_geometry_columns where view_name = 'strat_obs_p_for_qgsi2threejs'"
sql2 = "drop view if exists strat_obs_p_for_qgsi2threejs"
db_utils.sql_alter_db(sql1, dbconnection=self.dbconnection)
db_utils.sql_alter_db(sql2, dbconnection=self.dbconnection)
placeholder_sign = db_utils.placeholder_sign(self.dbconnection)
sql1 = "delete from views_geometry_columns where view_name = %s" % placeholder_sign
sql2 = "drop view if exists "
for key in self.strat_layers_dict:
db_utils.sql_alter_db(sql1,
dbconnection=self.dbconnection,
all_args=[(key, )])
db_utils.sql_alter_db(sql2 + key, dbconnection=self.dbconnection)