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 ask_for_locale(self): locales = [ PyQt4.QtCore.QLocale(PyQt4.QtCore.QLocale.Swedish, PyQt4.QtCore.QLocale.Sweden), PyQt4.QtCore.QLocale(PyQt4.QtCore.QLocale.English, PyQt4.QtCore.QLocale.UnitedStates) ] locale_names = [localeobj.name() for localeobj in locales] locale_names.append(locale.getdefaultlocale()[0]) locale_names = list(set(locale_names)) question = utils.NotFoundQuestion( dialogtitle=ru( QCoreApplication.translate(u'NewDb', u'User input needed')), msg=ru( QCoreApplication.translate( u'NewDb', u'Supply locale for the database.\nCurrently, only locale sv_SE has special meaning,\nall other locales will use english.' )), existing_list=locale_names, default_value=u'', combobox_label=ru(QCoreApplication.translate(u'newdb', u'Locales')), button_names=[u'Cancel', u'Ok']) answer = question.answer submitted_value = ru(question.value) if answer == u'cancel': raise utils.UserInterruptError() elif answer == u'ok': return submitted_value
def parse_filesettings(self, filename): """ :param filename: Parses the file settings of an interlab4 file :return: a tuple like (file_error, version, encoding, decimalsign, quotechar) """ version = None quotechar = False decimalsign = None file_error = False encoding = None #First find encoding for test_encoding in ['utf-16', 'utf-8', 'iso-8859-1']: try: with io.open(filename, 'r', encoding=test_encoding) as f: for rawrow in f: if '#tecken=' in rawrow.lower(): row = rawrow.lstrip('#').rstrip('\n').lower() cols = row.split('=') encoding = cols[1] break if not rawrow.startswith('#'): break except UnicodeError: continue if encoding is None: encoding = utils.ask_for_charset( default_charset='utf-16', msg=ru( QCoreApplication.translate( u'Interlab4Import', u'Give charset used in the file %s')) % filename) if encoding is None or not encoding: utils.MessagebarAndLog.info(bar_msg=ru( QCoreApplication.translate(u'Interlab4Import', u'Charset not given, stopping.'))) raise utils.UserInterruptError() #Parse the filedescriptor with io.open(filename, 'r', encoding=encoding) as f: for rawrow in f: if not rawrow.startswith('#'): if any(x is None for x in (version, decimalsign, quotechar)): file_error = True break row = rawrow.lstrip('#').rstrip('\n').lower() cols = row.split(u'=') if cols[0].lower() == u'version': version = cols[1] elif cols[0].lower() == u'decimaltecken': decimalsign = cols[1] elif cols[0].lower() == u'textavgränsare': if cols[1].lower() == 'ja': quotechar = '"' return (file_error, version, encoding, decimalsign, quotechar)
def create_temporary_table_for_import(self, temptable_name, fieldnames_types, geometry_colname_type_srid=None): if not temptable_name.startswith(u'temp_'): temptable_name = u'temp_%s' % temptable_name existing_names = tables_columns(dbconnection=self).keys() while temptable_name in existing_names: #this should only be needed if an earlier import failed. if so, propose to rename the temporary import-table reponse = PyQt4.QtGui.QMessageBox.question( None, utils.returnunicode( QCoreApplication.translate( u'DbConnectionManager', u"Warning - Table name confusion!")), utils.returnunicode( QCoreApplication.translate( u'midv_data_importer', u'''The temporary import table '%s' already exists in the current DataBase. This could indicate a failure during last import. Please verify that your table contains all expected data and then remove '%s'.\n\nMeanwhile, do you want to go on with this import, creating a temporary table '%s_2' in database?''' )) % (self.temptable_name, self.temptable_name, self.temptable_name), PyQt4.QtGui.QMessageBox.Yes | PyQt4.QtGui.QMessageBox.No) if reponse == PyQt4.QtGui.QMessageBox.Yes: self.temptable_name = '%s_2' % self.temptable_name else: raise utils.UserInterruptError() if self.dbtype == u'spatialite': temptable_name = u'mem.' + temptable_name self.execute(u"""ATTACH DATABASE ':memory:' AS mem""") if geometry_colname_type_srid is not None: geom_column = geometry_colname_type_srid[0] geom_type = geometry_colname_type_srid[1] srid = geometry_colname_type_srid[2] fieldnames_types.append(u'geometry %s' % geometry_colname_type_srid[0]) sql = u"""CREATE table %s (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, %s)""" % ( temptable_name, u', '.join(fieldnames_types)) self.execute(sql) sql = u"""SELECT RecoverGeometryColumn('%s','%s',%s,'%s',2) from %s AS a""" % ( temptable_name, geom_column, srid, geom_type, temptable_name) self.execute(sql) else: sql = u"""CREATE table %s (%s)""" % ( temptable_name, u', '.join(fieldnames_types)) self.execute(sql) else: self.execute(u"""CREATE TEMPORARY table %s (%s)""" % (temptable_name, u', '.join(fieldnames_types))) if geometry_colname_type_srid is not None: geom_column = geometry_colname_type_srid[0] geom_type = geometry_colname_type_srid[1] srid = geometry_colname_type_srid[2] sql = u"""ALTER TABLE %s ADD COLUMN %s geometry(%s, %s);""" % ( temptable_name, geom_column, geom_type, srid) self.execute(sql) return temptable_name
def select_files(self): self.charsetchoosen = utils.ask_for_charset( default_charset=self.default_charset) if not self.charsetchoosen: raise utils.UserInterruptError() files = utils.select_files(only_one_file=False, extension="csv (*.csv)") return files
def ask_for_CRS(self, set_locale): # USER MUST SELECT CRS FIRST!! if set_locale == 'sv_SE': default_crs = 3006 else: default_crs = 4326 EPSGID = qgis.PyQt.QtWidgets.QInputDialog.getInt(None, ru(QCoreApplication.translate('NewDb', "Select CRS")), ru(QCoreApplication.translate('NewDb', "Give EPSG-ID (integer) corresponding to\nthe CRS you want to use in the database:")),default_crs) if not EPSGID[1]: raise utils.UserInterruptError() return EPSGID
def select_files_and_load_gui(self): self.files = self.select_files() if not self.files: raise utils.UserInterruptError() self.date_time_filter = DateTimeFilter(calendar=True) self.add_row(self.date_time_filter.widget) self.add_row(get_line()) self.skip_rows = CheckboxAndExplanation(QCoreApplication.translate('DiverofficeImport', u'Skip rows without water level'), QCoreApplication.translate('DiverofficeImport', u'Checked = Rows without a value for columns Water head[cm] or Level[cm] will be skipped.')) self.skip_rows.checked = True self.add_row(self.skip_rows.widget) self.add_row(get_line()) self.confirm_names = CheckboxAndExplanation(QCoreApplication.translate('DiverofficeImport', u'Confirm each logger obsid before import'), QCoreApplication.translate('DiverofficeImport', u'Checked = The obsid will be requested of the user for every file.\n\n') + QCoreApplication.translate('DiverofficeImport', u'Unchecked = the location attribute, both as is and capitalized, in the\n') + QCoreApplication.translate('DiverofficeImport', u'file will be matched against obsids in the database.\n\n') + QCoreApplication.translate('DiverofficeImport', u'In both case, obsid will be requested of the user if no match in the database is found.')) self.confirm_names.checked = True self.add_row(self.confirm_names.widget) self.add_row(get_line()) self.import_all_data = CheckboxAndExplanation(QCoreApplication.translate('DiverofficeImport', u'Import all data'), QCoreApplication.translate('DiverofficeImport', u'Checked = any data not matching an exact datetime in the database\n') + QCoreApplication.translate('DiverofficeImport', u'for the corresponding obsid will be imported.\n\n') + QCoreApplication.translate('DiverofficeImport', u'Unchecked = only new data after the latest date in the database,\n') + QCoreApplication.translate('DiverofficeImport', u'for each observation point, will be imported.')) self.import_all_data.checked = False self.add_row(self.import_all_data.widget) self.close_after_import = PyQt4.QtGui.QCheckBox(ru(QCoreApplication.translate(u'DiverofficeImport', u'Close dialog after import'))) self.close_after_import.setChecked(True) self.gridLayout_buttons.addWidget(self.close_after_import, 0, 0) self.start_import_button = PyQt4.QtGui.QPushButton(QCoreApplication.translate('DiverofficeImport', u'Start import')) self.gridLayout_buttons.addWidget(self.start_import_button, 1, 0) self.connect(self.start_import_button, PyQt4.QtCore.SIGNAL("clicked()"), lambda : self.start_import(files=self.files, skip_rows_without_water_level=self.skip_rows.checked, confirm_names=self.confirm_names.checked, import_all_data=self.import_all_data.checked, from_date=self.date_time_filter.from_date, to_date=self.date_time_filter.to_date)) self.gridLayout_buttons.setRowStretch(2, 1) self.show()
def ask_for_stored_settings(self, stored_settings): old_string = utils.anything_to_string_representation( stored_settings, itemjoiner=',\n', pad=' ', dictformatter='{\n%s}', listformatter='[\n%s]', tupleformatter='(\n%s, )') msg = ru( QCoreApplication.translate( 'DrillreportUi', 'Replace the settings string with a new settings string.')) new_string = qgis.PyQt.QtWidgets.QInputDialog.getText( None, ru( QCoreApplication.translate('DrillreportUi', "Edit settings string")), msg, qgis.PyQt.QtWidgets.QLineEdit.Normal, old_string) if not new_string[1]: raise utils.UserInterruptError() new_string_text = ru(new_string[0]) if not new_string_text: return {} try: as_dict = ast.literal_eval(new_string_text) except Exception as e: utils.MessagebarAndLog.warning(bar_msg=ru( QCoreApplication.translate( 'DrillreportUi', 'Translating string to dict failed, see log message panel') ), log_msg=str(e)) raise utils.UsageError() else: return as_dict
def __init__(self, db_settings=None): """ Manuals for db connectors: https://github.com/qgis/QGIS/blob/master/python/plugins/db_manager/db_plugins/connector.py https://github.com/qgis/QGIS/blob/master/python/plugins/db_manager/db_plugins/postgis/connector.py https://github.com/qgis/QGIS/blob/master/python/plugins/db_manager/db_plugins/spatialite/connector.py """ self.conn = None self.cursor = None self.connector = None if db_settings is None: db_settings = QgsProject.instance().readEntry( "Midvatten", "database")[0] if isinstance(db_settings, str): #Test if the db_setting is an old database if os.path.isfile(db_settings): db_settings = {'spatialite': {'dbpath': db_settings}} else: if not db_settings: raise utils.UsageError( ru( QCoreApplication.translate( 'DbConnectionManager', 'Database setting was empty. Check DB tab in Midvatten settings.' ))) else: try: db_settings = ast.literal_eval(db_settings) except: raise utils.UsageError( ru( QCoreApplication.translate( 'DbConnectionManager', 'Database could not be set. Check DB tab in Midvatten settings.' ))) elif isinstance(db_settings, dict): # Assume it the dict is a valid db_settings dict. pass else: raise Exception( ru( QCoreApplication.translate( 'DbConnectionManager', "DbConnectionManager programming error: db_settings must be either a dict like {'spatialite': {'dbpath': 'x'} or a string representation of it. Was: %s" )) % ru(db_settings)) db_settings = ru(db_settings, keep_containers=True) self.db_settings = db_settings self.dbtype = list(self.db_settings.keys())[0] self.connection_settings = list(self.db_settings.values())[0] self.uri = QgsDataSourceUri() if self.dbtype == 'spatialite': self.dbpath = ru(self.connection_settings['dbpath']) if not os.path.isfile(self.dbpath): raise utils.UsageError( ru( QCoreApplication.translate( 'DbConnectionManager', 'Database error! File "%s" not found! Check db tab in Midvatten settings!' )) % self.dbpath) self.check_db_is_locked() #Create the database if it's not existing self.uri.setDatabase(self.dbpath) try: self.connector = spatialite_connector.SpatiaLiteDBConnector( self.uri) except Exception as e: utils.MessagebarAndLog.critical(bar_msg=ru( QCoreApplication.translate( 'DbConnectionManager', 'Connecting to spatialite db %s failed! Check that the file or path exists.' )) % self.dbpath, log_msg=ru( QCoreApplication.translate( 'DbConnectionManager', 'msg %s')) % str(e)) raise elif self.dbtype == 'postgis': connection_name = self.connection_settings['connection'].split( '/')[0] self.postgis_settings = get_postgis_connections()[connection_name] self.uri.setConnection(self.postgis_settings['host'], self.postgis_settings['port'], self.postgis_settings['database'], self.postgis_settings['username'], self.postgis_settings['password']) try: self.connector = postgis_connector.PostGisDBConnector(self.uri) except Exception as e: print(str(e)) if 'no password supplied' in str(e): utils.MessagebarAndLog.warning(bar_msg=ru( QCoreApplication.translate( 'DbConnectionManager', 'No password supplied for postgis connection'))) raise utils.UserInterruptError() else: raise if self.connector is not None: self.conn = self.connector.connection self.cursor = self.conn.cursor()
def create_temporary_table_for_import(self, temptable_name, fieldnames_types, geometry_colname_type_srid=None): if not temptable_name.startswith('temp_'): temptable_name = 'temp_%s' % temptable_name existing_names = list(tables_columns(dbconnection=self).keys()) while temptable_name in existing_names: #this should only be needed if an earlier import failed. if so, propose to rename the temporary import-table reponse = qgis.PyQt.QtWidgets.QMessageBox.question( None, ru( QCoreApplication.translate( 'DbConnectionManager', "Warning - Table name confusion!")), ru( QCoreApplication.translate( 'midv_data_importer', '''The temporary import table '%s' already exists in the current DataBase. This could indicate a failure during last import. Please verify that your table contains all expected data and then remove '%s'.\n\nMeanwhile, do you want to go on with this import, creating a temporary table '%s_2' in database?''' )) % (self.temptable_name, self.temptable_name, self.temptable_name), qgis.PyQt.QtWidgets.QMessageBox.Yes | qgis.PyQt.QtWidgets.QMessageBox.No) if reponse == qgis.PyQt.QtWidgets.QMessageBox.Yes: self.temptable_name = '%s_2' % self.temptable_name else: raise utils.UserInterruptError() if self.dbtype == 'spatialite': temptable_name = 'mem.' + temptable_name self.execute("""ATTACH DATABASE ':memory:' AS mem""") if geometry_colname_type_srid is not None: geom_column = geometry_colname_type_srid[0] geom_type = geometry_colname_type_srid[1] srid = geometry_colname_type_srid[2] fieldnames_types.append('geometry %s' % geometry_colname_type_srid[0]) sql = """CREATE table %s (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, %s)""" % ( temptable_name, ', '.join(fieldnames_types)) self.execute(sql) self.conn.commit() # This sql doesnt work for some reason, error msg "RecoverGeometryColumn() error: table 'mem.temp_temporary_section_line' does not exist" # Doesn't seem to work with memory databases. It doesn't seem to be needed for us though. try: sql = """SELECT RecoverGeometryColumn('%s','%s',%s,'%s',2) from %s AS a""" % ( temptable_name, geom_column, srid, geom_type, temptable_name) self.execute(sql) except: pass else: sql = """CREATE table %s (%s)""" % ( temptable_name, ', '.join(fieldnames_types)) self.execute(sql) else: self.execute("""CREATE TEMPORARY table %s (%s)""" % (temptable_name, ', '.join(fieldnames_types))) if geometry_colname_type_srid is not None: geom_column = geometry_colname_type_srid[0] geom_type = geometry_colname_type_srid[1] srid = geometry_colname_type_srid[2] sql = """ALTER TABLE %s ADD COLUMN %s geometry(%s, %s);""" % ( temptable_name, geom_column, geom_type, srid) self.execute(sql) return temptable_name
def create_new_spatialite_db(self, verno, user_select_CRS='y', EPSG_code='4326', delete_srids=True): #CreateNewDB(self, verno): """Open a new DataBase (create an empty one if file doesn't exists) and set as default DB""" utils.stop_waiting_cursor() set_locale = self.ask_for_locale() utils.start_waiting_cursor() if user_select_CRS=='y': utils.stop_waiting_cursor() EPSGID=str(self.ask_for_CRS(set_locale)[0]) utils.start_waiting_cursor() else: EPSGID=EPSG_code if EPSGID=='0' or not EPSGID: raise utils.UserInterruptError() # If a CRS is selectd, go on and create the database #path and name of new db utils.stop_waiting_cursor() dbpath = ru(utils.get_save_file_name_no_extension(parent=None, caption="New DB", directory="midv_obsdb.sqlite", filter="Spatialite (*.sqlite)")) utils.start_waiting_cursor() if os.path.exists(dbpath): utils.MessagebarAndLog.critical( bar_msg=ru(QCoreApplication.translate('NewDb', 'A database with the chosen name already existed. Cancelling...'))) utils.stop_waiting_cursor() return '' #Create the database conn = db_utils.connect_with_spatialite_connect(dbpath) conn.close() self.db_settings = ru(utils.anything_to_string_representation({'spatialite': {'dbpath': dbpath}})) #dbconnection = db_utils.DbConnectionManager(self.db_settings) try: # creating/connecting the test_db dbconnection = db_utils.DbConnectionManager(self.db_settings) dbconnection.execute("PRAGMA foreign_keys = ON") #Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database dbconnection separately. except Exception as e: utils.MessagebarAndLog.critical(bar_msg=ru(QCoreApplication.translate('NewDb', "Impossible to connect to selected DataBase, see log message panel")), log_msg=ru(QCoreApplication.translate('NewDb', 'Msg:\n') + str(e))) #utils.pop_up_info("Impossible to connect to selected DataBase") utils.stop_waiting_cursor() return '' d =dbconnection.connector #First, find spatialite version versionstext = dbconnection.execute_and_fetchall('select spatialite_version()')[0][0] # load sql syntax to initialise spatial metadata, automatically create GEOMETRY_COLUMNS and SPATIAL_REF_SYS # then the syntax defines a Midvatten project db according to the loaded .sql-file if not int(versionstext[0][0]) > 3: # which file to use depends on spatialite version installed utils.pop_up_info(ru(QCoreApplication.translate('NewDb', "Midvatten plugin needs spatialite4.\nDatabase can not be created"))) utils.stop_waiting_cursor() return '' filenamestring = "create_db.sql" SQLFile = os.path.join(os.sep,os.path.dirname(__file__),"..","definitions",filenamestring) qgisverno = Qgis.QGIS_VERSION#We want to store info about which qgis-version that created the db replace_word_replace_with = [('CHANGETORELEVANTEPSGID', ru(EPSGID)), ('CHANGETOPLUGINVERSION', ru(verno)), ('CHANGETOQGISVERSION', ru(qgisverno)), ('CHANGETODBANDVERSION', 'SpatiaLite version %s'%ru(versionstext)), ('CHANGETOLOCALE', ru(set_locale)), (('SPATIALITE ', ''))] with open(SQLFile, 'r') as f: f.readline() # first line is encoding info.... lines = [ru(line) for line in f] sql_lines = ['{};'.format(l) for l in ' '.join(lines).split(';') if l] for line in sql_lines: if all([line, not line.startswith("#"), 'POSTGIS' not in line]): sql = self.replace_words(line, replace_word_replace_with) try: dbconnection.execute(sql) except: try: print(str(sql)) except: pass raise if delete_srids: db_utils.delete_srids(dbconnection, EPSGID) self.insert_datadomains(set_locale, dbconnection) execute_sqlfile(get_full_filename("insert_obs_points_triggers.sql"), dbconnection) execute_sqlfile(get_full_filename('qgis3_obsp_fix.sql'), dbconnection) self.add_metadata_to_about_db(dbconnection) #FINISHED WORKING WITH THE DATABASE, CLOSE CONNECTIONS dbconnection.commit() dbconnection.vacuum() dbconnection.commit_and_closedb() #create SpatiaLite Connection in QGIS QSettings settings=qgis.PyQt.QtCore.QSettings() settings.beginGroup('/SpatiaLite/dbconnections') settings.setValue('%s/sqlitepath'%os.path.basename(dbpath),'%s'%dbpath) settings.endGroup() """ #The intention is to keep layer styles in the database by using the class AddLayerStyles but due to limitations in how layer styles are stored in the database, I will put this class on hold for a while. #Finally add the layer styles info into the data base AddLayerStyles(dbpath) """ utils.stop_waiting_cursor()
def populate_postgis_db(self, verno, user_select_CRS='y', EPSG_code='4326'): dbconnection = db_utils.DbConnectionManager() db_settings = dbconnection.db_settings if not isinstance(db_settings, str): self.db_settings = ru(utils.anything_to_string_representation(dbconnection.db_settings)) else: self.db_settings = ru(db_settings) if dbconnection.dbtype != 'postgis': raise utils.UsageError('Database type postgis not selected, check Midvatten settings!') dbconnection.execute('CREATE EXTENSION IF NOT EXISTS postgis;') result = dbconnection.execute_and_fetchall('select version(), PostGIS_full_version();') versionstext = ', '.join(result[0]) utils.stop_waiting_cursor() set_locale = self.ask_for_locale() utils.start_waiting_cursor() if user_select_CRS=='y': utils.stop_waiting_cursor() EPSGID=str(self.ask_for_CRS(set_locale)[0]) utils.start_waiting_cursor() else: EPSGID=EPSG_code if EPSGID=='0' or not EPSGID: raise utils.UserInterruptError() filenamestring = "create_db.sql" SQLFile = os.path.join(os.sep,os.path.dirname(__file__),"..","definitions",filenamestring) qgisverno = Qgis.QGIS_VERSION#We want to store info about which qgis-version that created the db replace_word_replace_with = [ ('CHANGETORELEVANTEPSGID', ru(EPSGID)), ('CHANGETOPLUGINVERSION', ru(verno)), ('CHANGETOQGISVERSION', ru(qgisverno)), ('CHANGETODBANDVERSION', 'PostGIS version %s' % ru(versionstext)), ('CHANGETOLOCALE', ru(set_locale)), ('double', 'double precision'), ('"', ''), ('rowid as rowid', 'CTID as rowid'), ('POSTGIS ', '')] created_tables_sqls = {} with open(SQLFile, 'r') as f: f.readline() # first line is encoding info.... lines = [ru(line) for line in f] sql_lines = ['{};'.format(l) for l in ' '.join(lines).split(';') if l] for linenr, line in enumerate(sql_lines): if all([line, not line.startswith("#"), 'InitSpatialMetadata' not in line, 'SPATIALITE' not in line, line.replace(';', '').strip().replace('\n', '').replace('\r', '')]): sql = self.replace_words(line, replace_word_replace_with) try: dbconnection.execute(sql) except: try: print(str(sql)) print("numlines: " + str(len(sql_lines))) print("Error on line nr {}".format(str(linenr))) print("before " + sql_lines[linenr - 1]) if linenr + 1 < len(sql_lines): print("after " + sql_lines[linenr + 1 ]) except: pass raise else: _sql = sql.lstrip('\r').lstrip('\n').lstrip() if _sql.startswith('CREATE TABLE'): tablename = ' '.join(_sql.split()).split()[2] created_tables_sqls[tablename] = sql #lines = [self.replace_words(line.decode('utf-8').rstrip('\n').rstrip('\r'), replace_word_replace_with) for line in f if all([line,not line.startswith("#"), 'InitSpatialMetadata' not in line])] #db_utils.sql_alter_db(lines) self.insert_datadomains(set_locale, dbconnection) execute_sqlfile(get_full_filename('insert_obs_points_triggers_postgis.sql'), dbconnection) execute_sqlfile(get_full_filename('insert_functions_postgis.sql'), dbconnection) self.add_metadata_to_about_db(dbconnection, created_tables_sqls) dbconnection.vacuum() dbconnection.commit_and_closedb() """ #The intention is to keep layer styles in the database by using the class AddLayerStyles but due to limitations in how layer styles are stored in the database, I will put this class on hold for a while. #Finally add the layer styles info into the data base AddLayerStyles(dbpath) """ utils.stop_waiting_cursor()
def __init__(self, db_settings=None): """ Manuals for db connectors: https://github.com/qgis/QGIS/blob/master/python/plugins/db_manager/db_plugins/connector.py https://github.com/qgis/QGIS/blob/master/python/plugins/db_manager/db_plugins/postgis/connector.py https://github.com/qgis/QGIS/blob/master/python/plugins/db_manager/db_plugins/spatialite/connector.py """ self.conn = None self.cursor = None if db_settings is None: db_settings = QgsProject.instance().readEntry( "Midvatten", "database")[0] if isinstance(db_settings, basestring): #Test if the db_setting is an old database if os.path.isfile(db_settings): db_settings = {u'spatialite': {u'dbpath': db_settings}} else: if not db_settings: # TODO: Something feels off here. It should not return None, as that will just cause other hard to solve errors. # TODO An exception feels better but is uglier for the user. utils.MessagebarAndLog.critical(bar_msg=utils.returnunicode( QCoreApplication.translate( u'DbConnectionManager', u'Database not chosen correctly. Check DB tab in Midvatten settings.' ))) return None else: try: db_settings = ast.literal_eval(db_settings) except: #TODO: Something feels off here. It should not return None, as that will just cause other hard to solve errors. #TODO An exception feels better but is uglier for the user. utils.MessagebarAndLog.critical( bar_msg=utils.returnunicode( QCoreApplication.translate( u'DbConnectionManager', u'Database connection failed. Try reset settings.' ))) return None elif isinstance(db_settings, dict): pass else: raise Exception( utils.returnunicode( QCoreApplication.translate( u'DbConnectionManager', u"DbConnectionManager error: db_settings must be either a dict like {u'spatialite': {u'dbpath': u'x'} or a string representation of it. Was: %s" )) % utils.returnunicode(db_settings)) db_settings = utils.returnunicode(db_settings, keep_containers=True) self.db_settings = db_settings self.dbtype = self.db_settings.keys()[0] self.connection_settings = self.db_settings.values()[0] self.uri = QgsDataSourceURI() if self.dbtype == u'spatialite': self.dbpath = utils.returnunicode( self.connection_settings[u'dbpath']) self.check_db_is_locked() #Create the database if it's not existing self.uri.setDatabase(self.dbpath) try: self.conn = sqlite.connect(self.dbpath, detect_types=sqlite.PARSE_DECLTYPES | sqlite.PARSE_COLNAMES) except: utils.MessagebarAndLog.critical(bar_msg=utils.returnunicode( QCoreApplication.translate( u'DbConnectionManager', u'Connecting to spatialite db %s failed! Check that the file or path exists.' )) % self.dbpath) self.conn = None else: try: self.connector = spatialite_connector.SpatiaLiteDBConnector( self.uri) except: pass self.cursor = self.conn.cursor() elif self.dbtype == u'postgis': connection_name = self.connection_settings[u'connection'].split( u'/')[0] self.postgis_settings = get_postgis_connections()[connection_name] self.uri.setConnection(self.postgis_settings[u'host'], self.postgis_settings[u'port'], self.postgis_settings[u'database'], self.postgis_settings[u'username'], self.postgis_settings[u'password']) try: self.connector = postgis_connector.PostGisDBConnector(self.uri) except Exception as e: if u'no password supplied' in str(e): utils.MessagebarAndLog.warning(bar_msg=utils.returnunicode( QCoreApplication.translate( u'DbConnectionManager', u'No password supplied for postgis connection'))) raise utils.UserInterruptError() else: raise self.conn = self.connector.connection self.cursor = self.connector._get_cursor()