def backup_db(dbconnection=None): if not isinstance(dbconnection, DbConnectionManager): dbconnection = DbConnectionManager() if dbconnection.dbtype == u'spatialite': curs = dbconnection.cursor curs.execute("begin immediate") bkupname = dbconnection.dbpath + datetime.datetime.now().strftime( '%Y%m%dT%H%M') + '.zip' zf = zipfile.ZipFile(bkupname, mode='w') zf.write(dbconnection.dbpath, compress_type=compression ) # compression will depend on if zlib is found or not zf.close() dbconnection.conn.rollback() utils.MessagebarAndLog.info(bar_msg=utils.returnunicode( QCoreApplication.translate( "backup_db", "Database backup was written to %s ")) % bkupname, duration=15) else: utils.MessagebarAndLog.info(bar_msg=utils.returnunicode( QCoreApplication.translate( "backup_db", u"Backup of PostGIS database not supported yet!")), duration=15) dbconnection.closedb()
def create_test_string(anything): ur""" Turns anything into a string used for testing :param anything: just about anything :return: A unicode string >>> create_test_string(u'123') u'123' >>> create_test_string([1, 2, 3]) u'[1, 2, 3]' >>> create_test_string({3: 'a', 2: 'b', 1: ('c', 'd')}) u'{1: (c, d), 2: b, 3: a}' """ if isinstance(anything, dict): aunicode = u''.join([u'{', u', '.join([u': '.join([create_test_string(k), create_test_string(v)]) for k, v in sorted(anything.iteritems())]), u'}']) elif isinstance(anything, list): aunicode = u''.join([u'[', u', '.join([create_test_string(x) for x in anything]), u']']) elif isinstance(anything, tuple): aunicode = u''.join([u'(', u', '.join([create_test_string(x) for x in anything]), u')']) elif isinstance(anything, (basestring, float, int)): aunicode = utils.returnunicode(anything) elif isinstance(anything, PyQt4.QtCore.QVariant): print("Was varaint") aunicode = utils.returnunicode(anything.toString().data()) else: aunicode = utils.returnunicode(str(anything)) return aunicode
def create_test_string(anything=None): ur""" Turns anything into a string used for testing :param anything: just about anything :return: A unicode string >>> create_test_string(u'123') u'123' >>> create_test_string([1, 2, 3]) u'[1, 2, 3]' >>> create_test_string({3: 'a', 2: 'b', 1: ('c', 'd')}) u'{1: (c, d), 2: b, 3: a}' """ if isinstance(anything, dict): aunicode = u''.join([u'{', u', '.join([u': '.join([create_test_string(k), create_test_string(v)]) for k, v in sorted(anything.iteritems())]), u'}']) elif isinstance(anything, list): aunicode = u''.join([u'[', u', '.join([create_test_string(x) for x in anything]), u']']) elif isinstance(anything, tuple): aunicode = u''.join([u'(', u', '.join([create_test_string(x) for x in anything]), u')']) elif isinstance(anything, (basestring, float, int)): aunicode = utils.returnunicode(anything) elif isinstance(anything, PyQt4.QtCore.QVariant): print("Was variant") aunicode = utils.returnunicode(anything.toString().data()) else: aunicode = utils.returnunicode(str(anything)) return aunicode
def get_spatialite_db_path_from_dbsettings_string(db_settings): if isinstance(db_settings, basestring): # Test if the db_setting is an old database if os.path.isfile(db_settings): return db_settings else: try: db_settings = ast.literal_eval(db_settings) except Exception as e: try: msg = str(e) except: msg = utils.returnunicode( QCoreApplication.translate( u'get_spatialite_db_path_from_dbsettings_string', u'Error message failed! Could not be converted to string!' )) utils.MessagebarAndLog.info(log_msg=utils.returnunicode( QCoreApplication.translate( u'get_spatialite_db_path_from_dbsettings_string', u'%s error msg from db_settings string "%s": %s')) % ( u'get_spatialite_db_path_from_dbsettings_string', db_settings, msg)) return u'' else: return db_settings.get(u'spatialite', {}).get(u'dbpath', u'') elif isinstance(db_settings, dict): return db_settings.get(u'spatialite', {}).get(u'dbpath', u'') else: return u''
def set_combobox(combobox, value): index = combobox.findText(returnunicode(value)) if index != -1: combobox.setCurrentIndex(index) else: combobox.addItem(returnunicode(value)) index = combobox.findText(returnunicode(value)) combobox.setCurrentIndex(index)
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 transform_geometries(tname, column_names, old_table_column_srid_dict, new_table_column_srid_dict): ur""" Transform geometry columns to new chosen SRID The transformation is only done if the chosen srid is not the same as the old srid, and if the geometry column in question exists in both the old and the new database :param tname: The table name :param column_names: a list of columns that will be copied from the old table/database to the new table/database :param old_table_column_srid_dict: a dict like {u'testt': {u'geom': 3006}}. A dict with tables that have geometries. The inner dict is the column and the srid. :param new_table_column_srid_dict: a dict like {u'testt': {u'geom': 3006}}. A dict with tables that have geometries. The inner dict is the column and the srid. :return: A list of columns where the geometry columns are Transformed. >>> ExportData.transform_geometries(u'testt', [u'notgeom', u'geom'], {u'testt': {u'geom': 3006}}, {u'testt': {u'geom': 3006}}) [u'notgeom', u'geom'] >>> ExportData.transform_geometries(u'testt', [u'notgeom', u'geom'], {u'testt': {u'geom': 3006}}, {u'testt': {u'geom': 3010}}) [u'notgeom', u'Transform(geom, 3010)'] >>> ExportData.transform_geometries(u'obs_points', [u'obsid', u'east', u'north', u'geom'], {u'obs_points': {u'geom': 3006}}, {u'obs_points': {u'geom': 3010}}) [u'obsid', u'X(Transform(geom, 3010))', u'Y(Transform(geom, 3010))', u'Transform(geom, 3010)'] """ transformed = False #Make a transformation for column names that are geometries if tname in new_table_column_srid_dict and tname in old_table_column_srid_dict: transformed_column_names = [] for column in column_names: new_srid = new_table_column_srid_dict.get(tname, {}).get(column, None) old_srid = old_table_column_srid_dict.get(tname, {}).get(column, None) if old_srid is not None and new_srid is not None and old_srid != new_srid: transformed_column_names.append(u'Transform(' + column + u', ' + utils.returnunicode(new_srid) + u')') utils.MessagebarAndLog.info(log_msg=u'Transformation for table "' + tname + u'" column "' + column + u'" from ' + str(old_srid) + u" to " + str(new_srid)) transformed = True else: transformed_column_names.append(column) else: transformed_column_names = column_names #Special case for obs_points because of the very special columns east/north if tname == u'obs_points' and transformed: old_geocol_srids = [(k, v) for k, v in old_table_column_srid_dict.get(tname, {}).iteritems()] new_geocol_srids = [(k, v) for k, v in new_table_column_srid_dict.get(tname, {}).iteritems()] if len(old_geocol_srids) != 1 and len(new_geocol_srids) != 1: utils.MessagebarAndLog.critical(bar_msg=u'Export warning!, see Log Message Panel', log_msg=u'Transformation of east/north for table obs_points failed! The number of geometry columns was not == 1!') else: new_srid = new_geocol_srids[0][1] old_geometry_column = old_geocol_srids[0][0] res = [] for column in transformed_column_names: if column == u'east': res.append(u'X(Transform(%s, %s))'%(old_geometry_column, new_srid)) elif column == u'north': res.append(u'Y(Transform(%s, %s))'%(old_geometry_column, new_srid)) else: res.append(column) transformed_column_names = res utils.MessagebarAndLog.info(log_msg=u'transformed_column_names:\n' + utils.returnunicode(transformed_column_names)) return transformed_column_names
def paste_data(self, paste_list=None): if paste_list is None: paste_list = PyQt4.QtGui.QApplication.clipboard().text().split(u'\n') old_text = [returnunicode(self.item(i).text()) for i in xrange(self.count())] new_items = set() new_items.update([returnunicode(x) for x in paste_list]) new_items.update(old_text) self.clear() self.addItems(list(sorted(new_items)))
def locations_sublocations_obsids(self): """ :return: a list like [[obsid.locationsuffix as location, obsid.locationsuffix.sublocationsuffix as sublocation, obsid), ...] """ locations_sublocations_obsids = [(u'.'.join([x for x in [returnunicode(obsid), returnunicode(self.location_suffix)] if x]), u'.'.join([x for x in [returnunicode(obsid), returnunicode(self.location_suffix), returnunicode(self.sublocation_suffix)] if x]), returnunicode(obsid)) for obsid in self._obsid_list.get_all_data()] return locations_sublocations_obsids
def execute_and_fetchall(self, sql): try: self.cursor.execute(sql) except Exception as e: textstring = utils.returnunicode( QCoreApplication.translate( u'sql_load_fr_db', u"""DB error!\n SQL causing this error:%s\nMsg:\n%s""") ) % (utils.returnunicode(sql), utils.returnunicode(str(e))) utils.MessagebarAndLog.warning(bar_msg=utils.sql_failed_msg(), log_msg=textstring) raise return self.cursor.fetchall()
def dict_to_sorted_list(adict): """ Creates a list of a dict of dicts :param adict: a dict that may contain more dicts :return: >>> dict_to_sorted_list({'a': {'o':{'d': 1, 'c': 2}, 'e': ['u']}, 't': (5, 6)}) ['a', 'e', 'u', 'o', 'c', '2', 'd', '1', 't', '5', '6'] >>> dict_to_sorted_list({'a': {'o':{'d': 1, 'c': 2}, 'e': ['u']}, 't': (5, {'k': 8, 'i': 9})}) ['a', 'e', 'u', 'o', 'c', '2', 'd', '1', 't', '5', 'i', '9', 'k', '8'] >>> dict_to_sorted_list({'a': {'o':{'d': 1, 'c': 2}, 'e': ['u']}, 't': (5, {'k': 8, 'i': (9, 29)})}) ['a', 'e', 'u', 'o', 'c', '2', 'd', '1', 't', '5', 'i', '9', 29, 'k', '8'] """ result_list = [] if isinstance(adict, dict): for k, v in sorted(adict.iteritems()): result_list.extend(dict_to_sorted_list(k)) result_list.extend(dict_to_sorted_list(v)) elif isinstance(adict, list) or isinstance(adict, tuple): for k in adict: result_list.extend(dict_to_sorted_list(k)) else: result_list.append(utils.returnunicode(adict).encode('utf-8')) return result_list
def get_data_and_make_plot(self): self.create_parameter_selection() self.get_selected_observations() if self.ms.settingsdict['piper_markers'] == 'type': self.get_selected_obstypes( ) #gets unique plt.plottypes and a plt.plot type dictionary self.create_markers() elif self.ms.settingsdict[ 'piper_markers'] == 'obsid' or self.ms.settingsdict[ 'piper_markers'] == 'obsid but no legend': self.create_markers() elif self.ms.settingsdict['piper_markers'] == 'date_time': self.get_selected_datetimes() self.create_markers() self.get_piper_data() #here is a simple printout (to python console) to let the user see the piper plt.plot data try: print( """obsid, date_time, type, Cl_meqPl, HCO3_meqPl, SO4_meqPl, Na+K_meqPl, Ca_meqPl, Mg_meqPl""" ) except: pass for row in self.obsrecarray: #print ','.join([unicode(col).encode('utf-8') for col in row]) try: print ','.join([utils.returnunicode(col) for col in row]) except: try: print "failed printing piper data..." except: pass self.make_the_plot()
def test_stratigraphy(self, mock_skippopup, mock_messagebar): """ :param mock_skippopup: :param mock_messagebar: :return: """ db_utils.sql_alter_db('''INSERT INTO obs_points (obsid, h_gs, geometry) VALUES ('1', 5, ST_GeomFromText('POINT(633466 711659)', 3006))''') db_utils.sql_alter_db('''INSERT INTO obs_points (obsid, h_gs, geometry) VALUES ('2', 10, ST_GeomFromText('POINT(6720727 016568)', 3006))''') db_utils.sql_alter_db('''INSERT INTO obs_points (obsid, h_gs, geometry) VALUES ('3', 20, ST_GeomFromText('POINT(6720728 016569)', 3006))''') db_utils.sql_alter_db('''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('1', 1, 0, 1, 'sand', 'sand', '3', 'j')''') db_utils.sql_alter_db('''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('1', 2, 1, 4.5, 'morän', 'morän', '3', 'j')''') self.create_and_select_vlayer() #print(str(self.vlayer.isValid())) #print(str(db_utils.sql_load_fr_db('select * from obs_points'))) #print(str(db_utils.sql_load_fr_db('select * from stratigraphy'))) dlg = Stratigraphy(self.iface, self.vlayer, self.ms.settingsdict) #print(str(mock_messagebar.mock_calls)) #print(str(mock_skippopup.mock_calls)) dlg.showSurvey() test = utils.anything_to_string_representation(dlg.data) test_survey = utils.anything_to_string_representation(repr(dlg.data['1'])) test_strata = utils.anything_to_string_representation(utils.returnunicode(dlg.data['1'].strata, keep_containers=True)) assert len(mock_skippopup.mock_calls) == 0 print(str(mock_messagebar.mock_calls)) assert len(mock_messagebar.mock_calls) == 0 assert test == """{"1": SURVEY('1', 5.000000, '<QgsPointXY: POINT(633466 711659)>')}""" assert test_survey == '''"SURVEY('1', 5.000000, '<QgsPointXY: POINT(633466 711659)>')"''' print("test_strata: " + test_strata) assert test_strata == '''["strata(1, '3', 'sand', 'sand', 0.000000-1.000000)", "strata(2, '3', 'morän', 'moran', 1.000000-4.500000)"]'''
def sql_load_fr_db(sql, dbconnection=None): try: if not isinstance(dbconnection, DbConnectionManager): dbconnection = DbConnectionManager() result = dbconnection.execute_and_fetchall(sql) except Exception as e: textstring = utils.returnunicode( QCoreApplication.translate( u'sql_load_fr_db', u"""DB error!\n SQL causing this error:%s\nMsg:\n%s""")) % ( utils.returnunicode(sql), utils.returnunicode(str(e))) utils.MessagebarAndLog.warning(bar_msg=utils.sql_failed_msg(), log_msg=textstring, duration=4) return False, [] else: return True, result
def get_last_used_quality_instruments(): """ Returns quality instrumentids :return: A tuple with instrument ids from w_qual_field """ sql = "select parameter, unit, instrument, staff, max(date_time) from w_qual_field group by parameter, unit, instrument, staff" connection_ok, result_dict = utils.get_sql_result_as_dict(sql) return returnunicode(result_dict, True)
def check_db_is_locked(self): if self.dbtype == u'spatialite': if os.path.exists(u'%s-journal' % self.dbpath): raise DatabaseLockedError( utils.returnunicode( QCoreApplication.translate( u'DbConnectionManager', u"Error, The database is already in use (a journal-file was found)" )))
def rpt_lower_left(self, GeneralData): rpt = r"""<p style="font-family:'arial'; font-size:10pt; font-weight:400; font-style:normal;">""" if utils.returnunicode(GeneralData[0][24]) not in ['','NULL'] and utils.returnunicode(GeneralData[0][25]) not in ['','NULL']: rpt += utils.returnunicode(GeneralData[0][24]) rpt += utils.returnunicode(GeneralData[0][25]) elif utils.returnunicode(GeneralData[0][24]) not in ['','NULL']: rpt += utils.returnunicode(GeneralData[0][24]) elif utils.returnunicode(GeneralData[0][25]) not in ['','NULL']: rpt += utils.returnunicode(GeneralData[0][25]) rpt += r"""</p>""" return rpt
def get_postgis_connections(): qs = QSettings() postgresql_connections = {} for k in sorted(qs.allKeys()): k = utils.returnunicode(k) if k.startswith(u'PostgreSQL'): cols = k.split(u'/') conn_name = cols[2] try: setting = cols[3] except IndexError: #utils.MessagebarAndLog.info(log_msg=u'Postgresql connection info: Setting ' + str(k) + u" couldn't be read") continue value = qs.value(k) postgresql_connections.setdefault(conn_name, {})[setting] = value postgresql_connections = utils.returnunicode(postgresql_connections, keep_containers=True) return postgresql_connections
def rpt_lower_right(self, statistics,meas_or_level_masl): if meas_or_level_masl == 'meas': unit = u' m below toc<br>' else: unit = u' m above sea level<br>' rpt = r"""<p style="font-family:'arial'; font-size:10pt; font-weight:400; font-style:normal;">""" if utils.returnunicode(statistics[2]) != '' and utils.returnunicode(statistics[2]) != '0': rpt += u'Number of water level measurements: ' + utils.returnunicode(statistics[2]) + u'<br>' if utils.returnunicode(statistics[0]) != '': rpt += u'Highest measured water level: ' + utils.returnunicode(statistics[0]) + unit if utils.returnunicode(statistics[1]) != '': rpt += u'Median water level: ' + utils.returnunicode(statistics[1]) + unit if utils.returnunicode(statistics[3]) != '': rpt += u'Lowest measured water level: ' + utils.returnunicode(statistics[3]) + unit rpt += r"""</p>""" return rpt
def rpt_lower_right_sv(self, statistics,meas_or_level_masl): if meas_or_level_masl == 'meas': unit = u' m u rök<br>' else: unit = u' m ö h<br>' rpt = r"""<p style="font-family:'arial'; font-size:10pt; font-weight:400; font-style:normal;">""" if utils.returnunicode(statistics[2]) != '' and utils.returnunicode(statistics[2]) != '0': rpt += u'Antal nivåmätningar: ' + utils.returnunicode(statistics[2]) + u'<br>' if utils.returnunicode(statistics[0]) != '': rpt += u'Högsta uppmätta nivå: ' + utils.returnunicode(statistics[0]) + unit if utils.returnunicode(statistics[1]) != '': rpt += u'Medianvärde för nivå: ' + utils.returnunicode(statistics[1]) + unit if utils.returnunicode(statistics[3]) != '': rpt += u'Lägsta uppmätta nivå: ' + utils.returnunicode(statistics[3]) + unit rpt += r"""</p>""" return rpt
def sql_alter_db(sql, dbconnection=None, all_args=None): if not isinstance(dbconnection, DbConnectionManager): dbconnection = DbConnectionManager() if dbconnection.dbtype == u'spatialite': try: dbconnection.execute(u'PRAGMA foreign_keys = ON') except: pass try: dbconnection.execute_and_commit(sql, all_args=all_args) except Exception as e: textstring = utils.returnunicode( QCoreApplication.translate( u'sql_alter_db', u"""DB error!\n SQL causing this error:%s\nMsg:\n%s""")) % ( utils.returnunicode(sql), utils.returnunicode(str(e))) utils.MessagebarAndLog.warning(bar_msg=utils.returnunicode( QCoreApplication.translate( u'sql_alter_db', u'Some sql failure, see log for additional info.')), log_msg=textstring, duration=4)
def find_date_format(datestring): """ Parses a string and returns the found working dateformat string :param datestring: A string representing a date, ex: '2015-01-01 12:00' :return: The dateformat of the string, ex: '%Y-%m-%d %H:%M' Can only parse a list of preconfigured datestrings. See the code. >>> find_date_format(u'2015-01-01 01:01:01') u'%Y-%m-%d %H:%M:%S' >>> find_date_format(u'01-01-2015 01:01:01') u'%d-%m-%Y %H:%M:%S' >>> find_date_format(u'01:01:01') u'%H:%M:%S' >>> find_date_format(u'2015-01-01') u'%Y-%m-%d' >>> print(find_date_format(u'abc')) None """ datestring = str(datestring) date_formats_to_try = [ u'%Y/%m/%d %H:%M:%S', u'%Y-%m-%d %H:%M:%S', u'%Y%m%d %H:%M:%S', u'%Y-%m-%d %H:%M', u'%Y%m%d', u'%Y-%m-%d', u'%d-%m-%Y', u'%H:%M:%S', u'%d-%m-%Y %H:%M:%S', u'%d-%m-%Y %H:%M', u'%d-%m-%Y %H', u'%Y/%m/%d %H:%M', u'%Y/%m/%d %H', u'%Y%m%d %H%M%S', u'%Y%m%d %H%M', u'%Y%m%d %H' ] found_format = None for dateformat in date_formats_to_try: try: datetime.datetime.strptime(datestring, dateformat) except ValueError: continue else: found_format = dateformat break if found_format is None: utils.MessagebarAndLog.critical( bar_msg=QCoreApplication.translate( u'find_date_format', u'Date parsing failed, see log message panel'), log_msg=ru( QCoreApplication.translate( u'find_date_format', u'Could not find the date format for string "%s"\nSupported date formats:\n%s' )) % (utils.returnunicode(datestring), u'\n'.join(date_formats_to_try))) return found_format
def paste_data(self, paste_list=None): if paste_list is None: paste_list = PyQt4.QtGui.QApplication.clipboard().text().split(u'\n') #Use lists to keep the data ordering (the reason set() is not used old_text = self.get_all_data() new_items = [] for alist in [old_text, paste_list]: for x in alist: if x not in new_items: new_items.append(returnunicode(x)) self.clear() if self.keep_sorted: self.addItems(list(sorted(new_items))) else: self.addItems(list(new_items))
def test_stratigraphy(self, mock_skippopup, mock_messagebar): """ :param mock_skippopup: :param mock_messagebar: :return: """ db_utils.sql_alter_db( '''INSERT INTO obs_points (obsid, h_gs, geometry) VALUES ('1', 5, ST_GeomFromText('POINT(633466 711659)', 3006))''' ) db_utils.sql_alter_db( '''INSERT INTO obs_points (obsid, h_gs, geometry) VALUES ('2', 10, ST_GeomFromText('POINT(6720727 016568)', 3006))''' ) db_utils.sql_alter_db( '''INSERT INTO obs_points (obsid, h_gs, geometry) VALUES ('3', 20, ST_GeomFromText('POINT(6720728 016569)', 3006))''' ) db_utils.sql_alter_db( '''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('1', 1, 0, 1, 'sand', 'sand', '3', 'j')''' ) db_utils.sql_alter_db( '''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('1', 2, 1, 4.5, 'morän', 'morän', '3', 'j')''' ) self.create_and_select_vlayer() #print(str(self.vlayer.isValid())) #print(str(db_utils.sql_load_fr_db('select * from obs_points'))) #print(str(db_utils.sql_load_fr_db('select * from stratigraphy'))) dlg = Stratigraphy(self.iface, self.vlayer, self.midvatten.ms.settingsdict) #print(str(mock_messagebar.mock_calls)) #print(str(mock_skippopup.mock_calls)) dlg.showSurvey() test = utils.anything_to_string_representation(dlg.data) test_survey = utils.anything_to_string_representation( repr(dlg.data['1'])) test_strata = utils.anything_to_string_representation( utils.returnunicode(dlg.data['1'].strata, keep_containers=True)) assert len(mock_skippopup.mock_calls) == 0 print(str(mock_messagebar.mock_calls)) assert len(mock_messagebar.mock_calls) == 0 assert test == """{"1": SURVEY('1', 5.000000, '<QgsPointXY: POINT(633466 711659)>')}""" assert test_survey == '''"SURVEY('1', 5.000000, '<QgsPointXY: POINT(633466 711659)>')"''' print("test_strata: " + test_strata) assert test_strata == '''["strata(1, '3', 'sand', 'sand', 0.000000-1.000000)", "strata(2, '3', 'morän', 'moran', 1.000000-4.500000)"]'''
def delete_srids(execute_able_object, keep_epsg_code): if isinstance(execute_able_object, DbConnectionManager): if not execute_able_object.dbtype == u'spatialite': return None delete_srid_sql_aux = r"""delete from spatial_ref_sys_aux where srid NOT IN ('%s', '4326');""" % keep_epsg_code try: execute_able_object.execute(delete_srid_sql_aux) except: pass delete_srid_sql = r"""delete from spatial_ref_sys where srid NOT IN ('%s', '4326');""" % keep_epsg_code try: execute_able_object.execute(delete_srid_sql) except: utils.MessagebarAndLog.info(log_msg=utils.returnunicode( QCoreApplication.translate(u'delete_srids', u'Removing srids failed using: %s')) % str(delete_srid_sql))
def rpt_upper_right(self, StratData): rpt = r"""<p style="font-family:'arial'; font-size:10pt; font-weight:400; font-style:normal;">""" if len(StratData) > 0: rpt += r"""<TR VALIGN=TOP><TD WIDTH=15%><P><u>""" + u'level (m b gs)</P></u></TD>' rpt += r"""<TD WIDTH=27%><P><u>""" + u'geology, full text' + '</P></u></TD>' rpt += r"""<TD WIDTH=17%><P><u>""" + u'geology, short' + '</P></u></TD>' rpt += r"""<TD WIDTH=9%><P><u>""" + u'capacity' + '</P></u></TD>' rpt += r"""<TD WIDTH=13%><P><u>""" + u'development' + '</P></u></TD>' rpt += r"""<TD WIDTH=21%><P><u>""" + u'comment' + '</P></u></TD></TR>' for row in StratData: rpt += r"""<TR VALIGN=TOP><TD WIDTH=15%><P>""" rpt += utils.returnunicode(row[2]) + ' - ' + utils.returnunicode(row[3]) + '</P></TD>' rpt += r"""<TD WIDTH=27%><P>""" + utils.returnunicode(row[4]) + '</P></TD>' rpt += r"""<TD WIDTH=17%><P>""" + utils.returnunicode(row[5]) + '</P></TD>' rpt += r"""<TD WIDTH=9%><P>""" + utils.returnunicode(row[6]) + '</P></TD>' rpt += r"""<TD WIDTH=13%><P>""" + utils.returnunicode(row[7]) + '</P></TD>' rpt += r"""<TD WIDTH=21%><P>""" + utils.returnunicode(row[8]) + '</P></TD></TR>' rpt += r"""</p>""" return rpt
def select_file(self): """ Open a dialog to locate the sqlite file and some more...""" dbpath = QFileDialog.getOpenFileName(None, str("Select database:"), "*.sqlite") if dbpath: # Only get new db name if not cancelling the FileDialog self.dbpath = dbpath self.midvsettingsdialogdock.ms.settingsdict[ 'database'] = utils.anything_to_string_representation( {u'spatialite': { u'dbpath': dbpath }}) self.midvsettingsdialogdock.ms.save_settings('database') self.midvsettingsdialogdock.load_plot_settings() #self.midvsettingsdialogdock.LoadAndSelectLastSettings() else: # debug utils.MessagebarAndLog.info(log_msg=ru( QCoreApplication.translate( u'SpatialiteSettings', u"DB selection cancelled and still using database path %s") ) % utils.returnunicode( self.midvsettingsdialogdock.ms.settingsdict['database']))
def rpt_upper_right_sv(self, StratData): rpt = r"""<p style="font-family:'arial'; font-size:10pt; font-weight:400; font-style:normal;">""" if len(StratData) > 0: rpt += r"""<TR VALIGN=TOP><TD WIDTH=17%><P><u>""" + u'nivå (mumy)</P></u></TD>' rpt += r"""<TD WIDTH=27%><P><u>""" + u'jordart, fullst beskrivn' + '</P></u></TD>' rpt += r"""<TD WIDTH=17%><P><u>""" + u'huvudfraktion' + '</P></u></TD>' rpt += r"""<TD WIDTH=5%><P><u>""" + u'vg' + '</P></u></TD>' rpt += r"""<TD WIDTH=9%><P><u>""" + u'stänger?' + '</P></u></TD>' rpt += r"""<TD WIDTH=27%><P><u>""" + u'kommentar' + '</P></u></TD></TR>' for row in StratData: rpt += r"""<TR VALIGN=TOP><TD WIDTH=17%><P>""" rpt += utils.returnunicode(row[2]) + ' - ' + utils.returnunicode(row[3]) + '</P></TD>' rpt += r"""<TD WIDTH=27%><P>""" + utils.returnunicode(row[4]) + '</P></TD>' rpt += r"""<TD WIDTH=17%><P>""" + utils.returnunicode(row[5]) + '</P></TD>' rpt += r"""<TD WIDTH=5%><P>""" + utils.returnunicode(row[6]) + '</P></TD>' rpt += r"""<TD WIDTH=9%><P>""" + utils.returnunicode(row[7]) + '</P></TD>' rpt += r"""<TD WIDTH=27%><P>""" + utils.returnunicode(row[8]) + '</P></TD></TR>' rpt += r"""</p>""" return rpt
def __init__(self,msettings,activelayer): self.ms = msettings self.activelayer = activelayer self.create_parameter_selection() self.get_selected_observations() if self.ms.settingsdict['piper_markers']=='type': self.get_selected_obstypes()#gets unique plt.plottypes and a plt.plot type dictionary self.create_markers() elif self.ms.settingsdict['piper_markers']=='obsid' or self.ms.settingsdict['piper_markers']=='obsid but no legend': self.create_markers() elif self.ms.settingsdict['piper_markers']=='date_time': self.get_selected_datetimes() self.create_markers() self.get_piper_data() #here is a simple printout (to python console) to let the user see the piper plt.plot data print """obsid, date_time, type, Cl_meqPl, HCO3_meqPl, SO4_meqPl, Na+K_meqPl, Ca_meqPl, Mg_meqPl""" for row in self.obsrecarray: #print ','.join([unicode(col).encode('utf-8') for col in row]) try: print ','.join([utils.returnunicode(col) for col in row]) except: print "failed printing piper data..." self.make_the_plot()
def get_table_info(tablename, dbconnection=None): if not isinstance(dbconnection, DbConnectionManager): dbconnection = DbConnectionManager() if dbconnection.dbtype == u'spatialite': columns_sql = """PRAGMA table_info (%s)""" % tablename try: columns = dbconnection.execute_and_fetchall(columns_sql) except Exception as e: if dbconnection.dbtype == u'spatialite': columns_sql = """PRAGMA table_info ("%s")""" % tablename try: columns = dbconnection.execute_and_fetchall(columns_sql) except Exception as e: utils.MessagebarAndLog.warning( bar_msg=utils.sql_failed_msg(), log_msg=utils.returnunicode( QCoreApplication.translate( u'get_table_info', u'Sql failed: %s\msg:%s')) % (columns_sql, str(e))) return None else: columns_sql = u"SELECT ordinal_position, column_name, data_type, CASE WHEN is_nullable = 'NO' THEN 1 ELSE 0 END AS notnull, column_default, 0 AS primary_key FROM information_schema.columns WHERE table_schema = '%s' AND table_name = '%s'" % ( dbconnection.schemas(), tablename) columns = [ list(x) for x in dbconnection.execute_and_fetchall(columns_sql) ] primary_keys = [ x[0] for x in dbconnection.execute_and_fetchall( u"SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type FROM pg_index i JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) WHERE i.indrelid = '%s'::regclass AND i.indisprimary;" % tablename) ] for column in columns: if column[1] in primary_keys: column[5] = 1 columns = [tuple(column) for column in columns] return columns
def test_stratigraphy_missing_h_gs(self, mock_skippopup, mock_messagebar): """ :param mock_skippopup: :param mock_messagebar: :return: """ db_utils.sql_alter_db('''INSERT INTO obs_points (obsid, h_gs, geometry) VALUES ('1', 5, ST_GeomFromText('POINT(633466 711659)', 3006))''') db_utils.sql_alter_db('''INSERT INTO obs_points (obsid, h_gs, h_toc, geometry) VALUES ('2', NULL, 10, ST_GeomFromText('POINT(6720727 016568)', 3006))''') db_utils.sql_alter_db('''INSERT INTO obs_points (obsid, geometry) VALUES ('3', ST_GeomFromText('POINT(6720728 016569)', 3006))''') db_utils.sql_alter_db('''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('1', 1, 0, 1, 'sand', 'sand', '3', 'j')''') db_utils.sql_alter_db('''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('1', 2, 1, 4.5, 'morän', 'morän', '3', 'j')''') db_utils.sql_alter_db('''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('2', 1, 0, 1, 'sand', 'sand', '3', 'j')''') db_utils.sql_alter_db('''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('2', 2, 1, 4.5, 'morän', 'morän', '3', 'j')''') db_utils.sql_alter_db('''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('3', 1, 0, 2, 'sand', 'sand', '3', 'j')''') db_utils.sql_alter_db('''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('3', 2, 2, 6, 'morän', 'morän', '3', 'j')''') self.create_and_select_vlayer() dlg = Stratigraphy(self.iface, self.vlayer, self.ms.settingsdict) dlg.showSurvey() test = utils.anything_to_string_representation(dlg.data) test_survey = utils.anything_to_string_representation(repr(dlg.data['1'])) test_strata = utils.anything_to_string_representation(utils.returnunicode(dlg.data['1'].strata, keep_containers=True)) assert len(mock_skippopup.mock_calls) == 1 print(str(mock_skippopup.mock_calls)) print(str(mock_messagebar.mock_calls)) assert mock_skippopup.mock_calls == [mock.call('Warning, h_gs is missing. See messagebar.')] assert mock_messagebar.mock_calls == [mock.call.warning(bar_msg="Obsid 2: using h_gs '' failed, using 'h_toc' instead.", duration=90, log_msg='False'), mock.call.warning(bar_msg="Obsid 3: using h_gs '' failed, using '-1' instead.", duration=90, log_msg='False')] print("test: " + test) assert test == """{"1": SURVEY('1', 5.000000, '<QgsPointXY: POINT(633466 711659)>'), "2": SURVEY('2', 10.000000, '<QgsPointXY: POINT(6720727 16568)>'), "3": SURVEY('3', -1.000000, '<QgsPointXY: POINT(6720728 16569)>')}""" print("test_survey " + test_survey) assert test_survey == '''"SURVEY('1', 5.000000, '<QgsPointXY: POINT(633466 711659)>')"''' print("Test strata " + test_strata) assert test_strata == '''["strata(1, '3', 'sand', 'sand', 0.000000-1.000000)", "strata(2, '3', 'morän', 'moran', 1.000000-4.500000)"]'''
def dbpath(self, value): self._dbpath.setText(utils.returnunicode(value))
def dbpath(self): return utils.returnunicode(self._dbpath.text())
def dbtype_combobox(self, value): index = self._dbtype_combobox.findText(utils.returnunicode(value)) if index != -1: self._dbtype_combobox.setCurrentIndex(index)
def dbtype_combobox(self): return utils.returnunicode(self._dbtype_combobox.currentText())
def execute(self, sql, all_args=None): """ :param sql: :param all_args: A list of lists of equal lenght to sql (if sql is a list) containing arguments for ? in the corresponding sql. :return: """ if isinstance(sql, basestring): sql = [sql] elif not isinstance(sql, (list, tuple)): raise TypeError( utils.returnunicode( QCoreApplication.translate( u'DbConnectionManager', u'DbConnectionManager.execute: sql must be type string or a list/tuple of strings. Was %s' )) % utils.returnunicode(type(sql))) for idx, line in enumerate(sql): if all_args is None: try: self.cursor.execute(line) except Exception as e: textstring = utils.returnunicode( QCoreApplication.translate( u'sql_load_fr_db', u"""DB error!\n SQL causing this error:%s\nMsg:\n%s""" )) % (utils.returnunicode(line), utils.returnunicode(str(e))) utils.MessagebarAndLog.warning( bar_msg=utils.sql_failed_msg(), log_msg=textstring) raise elif isinstance(all_args, (list, tuple)): args = all_args[idx] try: self.cursor.execute(line, args) except Exception as e: textstring = utils.returnunicode( QCoreApplication.translate( u'sql_load_fr_db', u"""DB error!\n SQL causing this error:%s\nusing args %s\nMsg:\n%s""" )) % (utils.returnunicode(line), utils.returnunicode(args), utils.returnunicode(str(e))) utils.MessagebarAndLog.warning( bar_msg=utils.sql_failed_msg(), log_msg=textstring) raise else: raise TypeError( utils.returnunicode( QCoreApplication.translate( u'DbConnectionManager', u'DbConnectionManager.execute: all_args must be a list/tuple. Was %s' )) % utils.returnunicode(type(all_args)))
def create_export_printlist(export_objects): """ Creates a result list with FieldLogger format from selected obsids and parameters :return: a list with result lines to export to file """ latlons = utils.get_latlon_for_all_obsids() parameters_inputtypes_hints = OrderedDict() sublocations_locations = OrderedDict() sublocations_lat_lon = OrderedDict() sublocations_parameters = OrderedDict() for export_object in export_objects: parameter = export_object.final_parameter_name if not parameter: utils.MessagebarAndLog.critical( bar_msg=u"Critical: Parameter " + parameter + u' error. See log message panel', log_msg=u'Parameter name not given.') continue input_type = export_object.input_type if not input_type: utils.MessagebarAndLog.critical( bar_msg=u"Critical: Parameter " + parameter + u' error. See log message panel', log_msg=u'Input type not given.') continue if parameter in parameters_inputtypes_hints: utils.MessagebarAndLog.warning(bar_msg=u"Warning: Parameter " + parameter + u' error. See log message panel', log_msg=u'The parameter ' + parameter + u' already exists. Only the first occurence one will be written to file.') continue parameters_inputtypes_hints[parameter] = (input_type, export_object.hint) for location, sublocation, obsid in export_object.locations_sublocations_obsids: location_exists = sublocations_locations.get(sublocation, None) if location != location_exists and location_exists is not None: utils.MessagebarAndLog.warning(bar_msg=u'Warning: sublocation ' + sublocation + u' error, see log message panel', log_msg=u'sublocation ' + sublocation + u' already existed for location ' + location_exists + u' and is duplicated by location ' + location + u'. It will be skipped.') continue if sublocation not in sublocations_lat_lon: lat, lon = latlons.get(obsid, [None, None]) if any([lat is None, not lat, lon is None, not lon]): utils.MessagebarAndLog.critical(bar_msg=u'Critical: Obsid ' + u' did not have lat-lon coordinates. Check obs_points table') continue sublocations_lat_lon[sublocation] = (returnunicode(lat), returnunicode(lon)) sublocations_locations[sublocation] = location sublocations_parameters.setdefault(sublocation, []).append(parameter) comments = [par for par in parameters_inputtypes_hints.keys() if u'comment' in par] if not comments: utils.MessagebarAndLog.warning(bar_msg=u'Warning: No comment parameter found. Is it forgotten?') #Make a flat set of used parameters #used_parameters = [item for v in sublocations_parameters.values() for item in v] #Remove unused parameters #parameters_inputtypes_hints = OrderedDict([(k, v) for k, v in parameters_inputtypes_hints.iteritems() if k in used_parameters]) printlist = [] printlist.append(u"FileVersion 1;" + str(len(parameters_inputtypes_hints))) printlist.append(u"NAME;INPUTTYPE;HINT") printlist.extend([u';'.join([returnunicode(par), returnunicode(i_h[0]) if i_h[0] else u'', returnunicode(i_h[1]) if i_h[1] else u'']) for par, i_h in parameters_inputtypes_hints.iteritems()]) printlist.append(u'NAME;sublocation;LAT;LON;INPUTFIELD') printlist.extend([u';'.join([returnunicode(location), returnunicode(sublocation), returnunicode(sublocations_lat_lon[sublocation][0]), returnunicode(sublocations_lat_lon[sublocation][1]), u'|'.join(returnunicode(sublocations_parameters[sublocation], keep_containers=True))]) for sublocation, location in sorted(sublocations_locations.iteritems())]) return printlist
def connection(self, value): index = self._connection.findText(utils.returnunicode(value)) if index != -1: self._connection.setCurrentIndex(index)
def test_stratigraphy_missing_h_gs(self, mock_skippopup, mock_messagebar): """ :param mock_skippopup: :param mock_messagebar: :return: """ db_utils.sql_alter_db( '''INSERT INTO obs_points (obsid, h_gs, geometry) VALUES ('1', 5, ST_GeomFromText('POINT(633466 711659)', 3006))''' ) db_utils.sql_alter_db( '''INSERT INTO obs_points (obsid, h_gs, h_toc, geometry) VALUES ('2', NULL, 10, ST_GeomFromText('POINT(6720727 016568)', 3006))''' ) db_utils.sql_alter_db( '''INSERT INTO obs_points (obsid, geometry) VALUES ('3', ST_GeomFromText('POINT(6720728 016569)', 3006))''' ) db_utils.sql_alter_db( '''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('1', 1, 0, 1, 'sand', 'sand', '3', 'j')''' ) db_utils.sql_alter_db( '''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('1', 2, 1, 4.5, 'morän', 'morän', '3', 'j')''' ) db_utils.sql_alter_db( '''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('2', 1, 0, 1, 'sand', 'sand', '3', 'j')''' ) db_utils.sql_alter_db( '''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('2', 2, 1, 4.5, 'morän', 'morän', '3', 'j')''' ) db_utils.sql_alter_db( '''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('3', 1, 0, 2, 'sand', 'sand', '3', 'j')''' ) db_utils.sql_alter_db( '''INSERT INTO stratigraphy (obsid, stratid, depthtop, depthbot, geology, geoshort, capacity, development) VALUES ('3', 2, 2, 6, 'morän', 'morän', '3', 'j')''' ) self.create_and_select_vlayer() dlg = Stratigraphy(self.iface, self.vlayer, self.ms.settingsdict) dlg.showSurvey() test = utils.anything_to_string_representation(dlg.data) test_survey = utils.anything_to_string_representation( repr(dlg.data['1'])) test_strata = utils.anything_to_string_representation( utils.returnunicode(dlg.data['1'].strata, keep_containers=True)) assert len(mock_skippopup.mock_calls) == 1 print(str(mock_skippopup.mock_calls)) print(str(mock_messagebar.mock_calls)) assert mock_skippopup.mock_calls == [ mock.call('Warning, h_gs is missing. See messagebar.') ] assert mock_messagebar.mock_calls == [ mock.call.warning( bar_msg="Obsid 2: using h_gs '' failed, using 'h_toc' instead.", duration=90, log_msg='False'), mock.call.warning( bar_msg="Obsid 3: using h_gs '' failed, using '-1' instead.", duration=90, log_msg='False') ] print("test: " + test) assert test == """{"1": SURVEY('1', 5.000000, '<QgsPointXY: POINT(633466 711659)>'), "2": SURVEY('2', 10.000000, '<QgsPointXY: POINT(6720727 16568)>'), "3": SURVEY('3', -1.000000, '<QgsPointXY: POINT(6720728 16569)>')}""" print("test_survey " + test_survey) assert test_survey == '''"SURVEY('1', 5.000000, '<QgsPointXY: POINT(633466 711659)>')"''' print("Test strata " + test_strata) assert test_strata == '''["strata(1, '3', 'sand', 'sand', 0.000000-1.000000)", "strata(2, '3', 'morän', 'moran', 1.000000-4.500000)"]'''
def rpt_upper_left_sv(self, GeneralData, CRS='', CRSname=''): rpt = r"""<p style="font-family:'arial'; font-size:8pt; font-weight:400; font-style:normal;">""" if utils.returnunicode(GeneralData[0][1]) != '' and utils.returnunicode(GeneralData[0][1]) != 'NULL' and utils.returnunicode(GeneralData[0][1]) != utils.returnunicode(GeneralData[0][0]): rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'originalbenämning' + r"""</TD><TD WIDTH=67%>""" + utils.returnunicode(GeneralData[0][1]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][3]) != '' and utils.returnunicode(GeneralData[0][3]) != 'NULL': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'obstyp' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][3]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][4]) != '' and utils.returnunicode(GeneralData[0][4]) != 'NULL': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'djup (m fr my t botten)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][4]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][17]) != '' and utils.returnunicode(GeneralData[0][17]) != 'NULL': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'röröverkant (möh)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][17]) if utils.returnunicode(GeneralData[0][21]) != '': rpt += u' (' + utils.returnunicode(GeneralData[0][21]) + ')' rpt += '</TD></TR>' if utils.returnunicode(GeneralData[0][18]) != '' and utils.returnunicode(GeneralData[0][18]) != 'NULL' and utils.returnunicode(GeneralData[0][18]) != '0' and utils.returnunicode(GeneralData[0][18]) != '0.0': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'rörövermått (m ö my)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][18]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][19]) != '' and utils.returnunicode(GeneralData[0][19]) != 'NULL': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'markytans nivå, my (möh)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][19]) if utils.returnunicode(GeneralData[0][21]) != '': rpt += u' (' + utils.returnunicode(GeneralData[0][21]) + ')' rpt += '</TD></TR>' if utils.returnunicode(GeneralData[0][20]) != '' and utils.returnunicode(GeneralData[0][20]) != 'NULL': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'onoggrannhet i höjd, avser rök (m)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][20]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][13]) != '' and utils.returnunicode(GeneralData[0][13]) != 'NULL': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'östlig koordinat' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][13]) + ' (' + CRSname + ', EPSG:' + CRS + ')</TD></TR>' if utils.returnunicode(GeneralData[0][14]) != '' and utils.returnunicode(GeneralData[0][14]) != 'NULL': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'nordlig koordinat' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][14]) + ' (' + CRSname + ', EPSG:' + CRS + ')</TD></TR>' if utils.returnunicode(GeneralData[0][13]) != '' and utils.returnunicode(GeneralData[0][13]) != 'NULL' and utils.returnunicode(GeneralData[0][14]) != '' and utils.returnunicode(GeneralData[0][15]) != '': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'lägesonoggrannhet' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][15]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][7]) != '' and utils.returnunicode(GeneralData[0][7]) != 'NULL': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'material' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][7]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][6]) != '' and utils.returnunicode(GeneralData[0][6]) != 'NULL': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'innerdiameter (mm)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][6]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][5]) != '' and utils.returnunicode(GeneralData[0][5]) != 'NULL': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'borrningens avslut' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][5]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][8]) != '' and utils.returnunicode(GeneralData[0][8]) != 'NULL': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'filter/spets' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][8]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][10]) != '' and utils.returnunicode(GeneralData[0][10]) != 'NULL': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'borrningen avslutades' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][10]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][9]) != '' and utils.returnunicode(GeneralData[0][9]) != 'NULL': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'kapacitet/vg på spetsnivå' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][9]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][2]) != '' and utils.returnunicode(GeneralData[0][2]) != 'NULL': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'fastighet/plats' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][2]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][23]) != '' and utils.returnunicode(GeneralData[0][23]) != 'NULL': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'referens' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][23]) + '</TD></TR>' rpt += r"""</p>""" if utils.returnunicode(GeneralData[0][16]) != '' and utils.returnunicode(GeneralData[0][16]) != 'NULL': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'lägesangivelsens ursprung' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][16]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][22]) != '' and utils.returnunicode(GeneralData[0][22]) != 'NULL': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'höjdangivelsens ursprung' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][22]) + '</TD></TR>' return rpt
def calculate_median_value(table, column, obsid, dbconnection=None): """ Code from https://stackoverflow.com/questions/15763965/how-can-i-calculate-the-median-of-values-in-sqlite :param table: :param column: :param obsid: :param dbconnection: :return: """ if not isinstance(dbconnection, DbConnectionManager): dbconnection = DbConnectionManager() if dbconnection.dbtype == u'spatialite': data = {u'column': column, u'table': table, u'obsid': obsid} sql = u'''SELECT AVG({column}) FROM (SELECT {column} FROM {table} WHERE obsid = '{obsid}' ORDER BY {column} LIMIT 2 - (SELECT COUNT(*) FROM {table} WHERE obsid = '{obsid}') % 2 OFFSET (SELECT (COUNT(*) - 1) / 2 FROM {table} WHERE obsid = '{obsid}'))'''.format( **data).replace(u'\n', u' ') # median value old variant #sql = r"""SELECT x.obsid, x.""" + column + r""" as median from (select obsid, """ + column + r""" FROM %s WHERE obsid = '"""%table #sql += obsid #sql += r"""' and (typeof(""" + column + r""")=typeof(0.01) or typeof(""" + column + r""")=typeof(1))) as x, (select obsid, """ + column + r""" FROM %s WHERE obsid = '"""%table #sql += obsid #sql += r"""' and (typeof(""" + column + r""")=typeof(0.01) or typeof(""" + column + r""")=typeof(1))) as y GROUP BY x.""" + column + r""" HAVING SUM(CASE WHEN y.""" + column + r""" <= x.""" + column + r""" THEN 1 ELSE 0 END)>=(COUNT(*)+1)/2 AND SUM(CASE WHEN y.""" + column + r""" >= x.""" + column + r""" THEN 1 ELSE 0 END)>=(COUNT(*)/2)+1""" ConnectionOK, median_value = sql_load_fr_db(sql, dbconnection) try: median_value = median_value[0][0] except IndexError: utils.MessagebarAndLog.warning( bar_msg=utils.returnunicode( QCoreApplication.translate( u'calculate_median_value', u'Median calculation error, see log message panel')), log_msg=utils.returnunicode( QCoreApplication.translate(u'calculate_median_value', u'Sql failed: %s')) % sql) median_value = None else: sql = u"""SELECT median(u.%s) AS median_value FROM (SELECT %s FROM %s WHERE obsid = '%s') AS u;""" % ( column, column, table, obsid) ConnectionOK, median_value = sql_load_fr_db(sql, dbconnection) try: median_value = median_value[0][0] except IndexError: utils.MessagebarAndLog.warning( bar_msg=utils.returnunicode( QCoreApplication.translate( u'calculate_median_value', u'Median calculation error, see log message panel')), log_msg=utils.returnunicode( QCoreApplication.translate(u'calculate_median_value', u'Sql failed: %s')) % sql) median_value = None return median_value
def locations_sublocations_obsids(self): locations_sublocations_obsids = [(u'.'.join([returnunicode(obsid), returnunicode(self.location_suffix)]), u'.'.join([returnunicode(obsid), returnunicode(self.sublocation_suffix)]), returnunicode(obsid)) for obsid in self.obsid_list.get_all_data()] return locations_sublocations_obsids
def select_file(self): """ Open a dialog to locate the sqlite file and some more...""" dbpath, __ = QFileDialog.getOpenFileName(None, str("Select database:"), "*.sqlite") if dbpath: # Only get new db name if not cancelling the FileDialog self.dbpath = dbpath self.midvsettingsdialogdock.ms.settingsdict['database'] = utils.anything_to_string_representation({'spatialite': {'dbpath': dbpath}}) self.midvsettingsdialogdock.ms.save_settings('database') self.midvsettingsdialogdock.load_plot_settings() warn_about_old_database() else: # debug utils.MessagebarAndLog.info(log_msg=ru(QCoreApplication.translate('SpatialiteSettings', "DB selection cancelled and still using database path %s"))%utils.returnunicode(self.midvsettingsdialogdock.ms.settingsdict['database']))
def connection(self): return utils.returnunicode(self._connection.currentText())
def __init__(self, obsid='', settingsdict = {}): #open connection to report file reportfolder = os.path.join(QDir.tempPath(), 'midvatten_reports') if not os.path.exists(reportfolder): os.makedirs(reportfolder) reportpath = os.path.join(reportfolder, "drill_report.html") logopath = os.path.join(os.sep,os.path.dirname(__file__),"..","about","midvatten_logga.png") imgpath = os.path.join(os.sep,os.path.dirname(__file__),"..","reports") f = codecs.open(reportpath, "wb", "utf-8") #write some initiating html, header and also rpt = r"""<meta http-equiv="content-type" content="text/html; charset=utf-8" />""" rpt += r"""<head><title>%s General report from Midvatten plugin for QGIS</title></head>"""%obsid rpt += r"""<html><TABLE WIDTH=100% BORDER=0 CELLPADDING=1 CELLSPACING=1><TR VALIGN=TOP><TD WIDTH=15%><h3 style="font-family:'arial';font-size:18pt; font-weight:600">""" rpt += obsid if utils.getcurrentlocale() == 'sv_SE': rpt += ''.join([r'''</h3><img src="''', os.path.join(imgpath, 'for_general_report_sv.png'), r'''" /><br><img src=''', r"""'"""]) #rpt += r"""</h3><img src="for_general_report_sv.png" /><br><img src='""" else: rpt += ''.join([r'''</h3><img src="''', os.path.join(imgpath, 'for_general_report.png'), r'''" /><br><img src=''', r"""'"""]) #rpt += r"""</h3><img src="for_general_report.png" /><br><img src='""" rpt += logopath rpt +="""' /></TD><TD WIDTH=85%><TABLE WIDTH=100% BORDER=1 CELLPADDING=4 CELLSPACING=3><TR VALIGN=TOP><TD WIDTH=50%><P><U><B>""" if utils.getcurrentlocale() == 'sv_SE': rpt += u'Allmän information' else: rpt += u'General information' rpt += r"""</B></U></P><TABLE style="font-family:'arial'; font-size:10pt; font-weight:400; font-style:normal;" WIDTH=100% BORDER=0 CELLPADDING=0 CELLSPACING=1><COL WIDTH=43*><COL WIDTH=43*>""" f.write(rpt) # GENERAL DATA UPPER LEFT QUADRANT ConnectionOK, GeneralData = self.GetData(obsid, 'obs_points', 'n')#MacOSX fix1 #utils.pop_up_info(str(ConnectionOK))#debug if ConnectionOK==True: result2 = (utils.sql_load_fr_db(r"""SELECT srid FROM geometry_columns where f_table_name = 'obs_points'""")[1])[0][0] CRS = utils.returnunicode(result2) #1st we need crs result3 = (utils.sql_load_fr_db(r"""SELECT ref_sys_name FROM spatial_ref_sys where srid =""" + CRS)[1])[0][0] CRSname = utils.returnunicode(result3) # and crs name if utils.getcurrentlocale() == 'sv_SE': reportdata_1 = self.rpt_upper_left_sv(GeneralData, CRS, CRSname) else: reportdata_1 = self.rpt_upper_left(GeneralData, CRS, CRSname) f.write(reportdata_1) rpt = r"""</TABLE></TD><TD WIDTH=50%><P><U><B>""" if utils.getcurrentlocale() == 'sv_SE': rpt += u'Lagerföljd' else: rpt += u'Stratigraphy' rpt += r"""</B></U></P><TABLE style="font-family:'arial'; font-size:10pt; font-weight:400; font-style:normal;" WIDTH=100% BORDER=0 CELLPADDING=0 CELLSPACING=1><COL WIDTH=43*><COL WIDTH=43*><COL WIDTH=43*><COL WIDTH=43*><COL WIDTH=43*><COL WIDTH=43*>""" f.write(rpt) # STRATIGRAPHY DATA UPPER RIGHT QUADRANT StratData = self.GetData(obsid, 'stratigraphy', 'n')[1] #MacOSX fix1 if utils.getcurrentlocale() == 'sv_SE': reportdata_2 = self.rpt_upper_right_sv(StratData) else: reportdata_2 = self.rpt_upper_right(StratData) f.write(reportdata_2) rpt = r"""</TABLE></TD></TR><TR VALIGN=TOP><TD WIDTH=50%><P><U><B>""" if utils.getcurrentlocale() == 'sv_SE': rpt += u'Kommentarer' else: rpt += u'Comments' rpt += r"""</B></U></P>""" f.write(rpt) # COMMENTS LOWER LEFT QUADRANT reportdata_3 = self.rpt_lower_left(GeneralData) f.write(reportdata_3) rpt = r"""</TD><TD WIDTH=50%><P><U><B>""" if utils.getcurrentlocale() == 'sv_SE': rpt += u'Vattennivåer' else: rpt += u'Water levels' rpt += r"""</B></U></P>""" f.write(rpt) # WATER LEVEL STATISTICS LOWER RIGHT QUADRANT meas_or_level_masl, statistics = GetStatistics(obsid)#MacOSX fix1 if utils.getcurrentlocale() == 'sv_SE': reportdata_4 = self.rpt_lower_right_sv(statistics,meas_or_level_masl) else: reportdata_4 = self.rpt_lower_right(statistics,meas_or_level_masl) f.write(reportdata_4) f.write(r"""</TD></TR></TABLE></TD></TR></TABLE>""") f.write("\n</p></body></html>") f.close() #print reportpath#debug QDesktopServices.openUrl(QUrl.fromLocalFile(reportpath))
def rpt_upper_left(self, GeneralData, CRS='', CRSname=''): rpt = r"""<p style="font-family:'arial'; font-size:8pt; font-weight:400; font-style:normal;">""" if utils.returnunicode(GeneralData[0][1]) != '' and utils.returnunicode(GeneralData[0][1]) != 'NULL' and utils.returnunicode(GeneralData[0][1]) != utils.returnunicode(GeneralData[0][0]): rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'original name' + r"""</TD><TD WIDTH=67%>""" + utils.returnunicode(GeneralData[0][1]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][3]) not in ['','NULL']: rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'obs type' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][3]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][4]) not in ['','NULL']: rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'depth (m fr gs to bottom)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][4]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][17]) not in ['','NULL']: rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'top of casing, toc (masl)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][17]) if utils.returnunicode(GeneralData[0][21]) != '': rpt += u' (' + utils.returnunicode(GeneralData[0][21]) + ')' rpt += '</TD></TR>' if utils.returnunicode(GeneralData[0][18]) not in ['','NULL'] and utils.returnunicode(GeneralData[0][18]) != '0' and utils.returnunicode(GeneralData[0][18]) != '0.0': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'distance toc-gs, tocags (mags)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][18]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][19]) not in ['','NULL']: rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'ground surface level, gs (masl)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][19]) if utils.returnunicode(GeneralData[0][21]) != '': rpt += u' (' + utils.returnunicode(GeneralData[0][21]) + ')' rpt += '</TD></TR>' if utils.returnunicode(GeneralData[0][20]) not in ['','NULL']: rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'elevation accuracy (m)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][20]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][13]) not in ['','NULL']: rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'eastern coordinate' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][13]) + ' (' + CRSname + ', EPSG:' + CRS + ')</TD></TR>' if utils.returnunicode(GeneralData[0][14]) not in ['','NULL']: rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'northern coordinate' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][14]) + ' (' + CRSname + ', EPSG:' + CRS + ')</TD></TR>' if utils.returnunicode(GeneralData[0][13]) not in ['','NULL'] and utils.returnunicode(GeneralData[0][14]) != '' and utils.returnunicode(GeneralData[0][15]) != '': rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'position accuracy' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][15]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][7]) not in ['','NULL']: rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'material' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][7]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][6]) not in ['','NULL']: rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'inner diameter (mm)' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][6]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][5]) not in ['','NULL']: rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'drill stop' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][5]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][8]) not in ['','NULL']: rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'screen type' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][8]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][10]) not in ['','NULL']: rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'drill date' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][10]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][9]) not in ['','NULL']: rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'capacity' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][9]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][2]) not in ['','NULL']: rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'place' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][2]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][23]) not in ['','NULL']: rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'reference' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][23]) + '</TD></TR>' rpt += r"""</p>""" if utils.returnunicode(GeneralData[0][16]) not in ['','NULL']: rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'source for position' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][16]) + '</TD></TR>' if utils.returnunicode(GeneralData[0][22]) not in ['','NULL']: rpt += r"""<TR VALIGN=TOP><TD WIDTH=33%>""" + u'source for elevation' + r"""</TD><TD WIDTH=50%>""" + utils.returnunicode(GeneralData[0][22]) + '</TD></TR>' return rpt
def format_obsids(self, obsids): formatted_obsids = u''.join([u'(', u', '.join([u"'{}'".format(k) for k in utils.returnunicode(obsids, True)]), u')']).encode('utf-8') return formatted_obsids
def GetData(self, dbPath='', obsid = ''): # GetData method that returns a table with water quality data #conn = sqlite.connect(dbPath,detect_types=sqlite.PARSE_DECLTYPES|sqlite.PARSE_COLNAMES) myconnection = utils.dbconnection() myconnection.connect2db() # skapa en cursor curs = myconnection.conn.cursor() # Load all water quality parameters stored in two result columns: parameter, unit if not(unicode(self.settingsdict['wqual_unitcolumn']) ==''): #If there is a a given column for unit sql =r"""select distinct parameter, """ sql += self.settingsdict['wqual_unitcolumn'] sql +=r""" from """ else: # IF no specific column exist for unit sql =r"""select distinct parameter, parameter from """ # The twice selection of parameter is a dummy to keep same structure (2 cols) of sql-answer as if a unit column exists sql += self.settingsdict['wqualtable'] sql += r""" where obsid = '""" sql += obsid sql += r"""' ORDER BY parameter""" parameters_cursor = curs.execute(sql) #Send SQL-syntax to cursor print(sql)#debug parameters = parameters_cursor.fetchall() if not parameters: qgis.utils.iface.messageBar().pushMessage("Debug","Something is wrong, no parameters are found in table w_qual_lab for "+ obsid, 0 ,duration=10)#DEBUG return False print('parameters for ' + obsid + ' is loaded at time: ' + str(time.time()))#debug # Load all date_times, stored in two result columns: reportnr, date_time if not (self.settingsdict['wqual_sortingcolumn'] == ''): #If there is a a specific sorting column sql =r"""select distinct """ sql += self.settingsdict['wqual_sortingcolumn'] sql += r""", date_time from """ #including parameters else: # IF no specific column exist for sorting sql =r"""select distinct date_time, date_time from """ # The twice selection of date_time is a dummy to keep same structure (2 cols) of sql-answer as if reportnr exists sql += self.settingsdict['wqualtable'] sql += """ where obsid = '""" sql += obsid sql += """' ORDER BY date_time""" #sql2 = unicode(sql) #To get back to unicode-string date_times_cursor = curs.execute(sql) #Send SQL-syntax to cursor, date_times = date_times_cursor.fetchall() print (sql)#debug print('loaded distinct date_time for the parameters for ' + obsid + ' at time: ' + str(time.time()))#debug if not date_times: qgis.utils.iface.messageBar().pushMessage("Debug","Something is wrong, no parameters are found in table w_qual_lab for "+ obsid, 0 ,duration=10)#DEBUG return ReportTable = ['']*(len(parameters)+2) # Define size of ReportTable for i in range(len(parameters)+2): # Fill the table with '' ReportTable[i] = [''] * (len(date_times)+1) #Populate First 'column' w parameters parametercounter = 2 #First two rows are for obsid and date_time for p, u in parameters: if not(self.settingsdict['wqual_unitcolumn']==''): if u: #ReportTable[parametercounter][0] = p.encode(locale.getdefaultlocale()[1]) + ", " + u.encode(locale.getdefaultlocale()[1]) ReportTable[parametercounter][0] = p + ", " + u else: #ReportTable[parametercounter][0] = p.encode(locale.getdefaultlocale()[1]) ReportTable[parametercounter][0] = p else: #ReportTable[parametercounter][0] = p.encode(locale.getdefaultlocale()[1]) ReportTable[parametercounter][0] = p parametercounter = parametercounter + 1 print('now go for each parameter value for ' + obsid + ', at time: ' + str(time.time()))#debug #Populate First 'row' w obsid datecounter = 1 #First col is 'parametercolumn' for r, d in date_times: #date_times includes both report and date_time (or possibly date_time and date_time if there is no reportnr) ReportTable[0][datecounter]=obsid datecounter += 1 datecounter=1 # first 'column' is for parameter names for k, v in date_times: # Loop through all report parametercounter = 1 # first row is for obsid ReportTable[parametercounter][datecounter] = v # v is date_time for p, u in parameters: parametercounter = parametercounter + 1 # one 'row' down after date was stored sql =r"""SELECT """ sql += self.settingsdict['wqual_valuecolumn'] sql += r""" from """ sql += self.settingsdict['wqualtable'] sql += """ where obsid = '""" sql += obsid sql += """' and date_time = '""" sql += v if not(self.settingsdict['wqual_unitcolumn'] == '') and u: sql += """' and parameter = '""" sql += p sql += """' and """ sql += self.settingsdict['wqual_unitcolumn'] sql += """ = '""" sql += u sql += """'""" else: sql += """' and parameter = '""" sql += p sql += """'""" rs = curs.execute(sql) #Send SQL-syntax to cursor, NOTE: here we send sql which was utf-8 already from interpreting it #print (sql)#debug #print('time: ' + str(time.time()))#debug recs = rs.fetchall() # All data are stored in recs #each value must be in unicode or string to be written as html report if recs: try: ReportTable[parametercounter][datecounter] = utils.returnunicode(recs[0][0]) except: ReportTable[parametercounter][datecounter]='' qgis.utils.iface.messageBar().pushMessage("Note!","The value for %s [%s] at %s, %s was not readable. Check your data!"%(p,u,k,v),0,duration=15) else: ReportTable[parametercounter][datecounter] =' ' datecounter = datecounter + 1 self.htmlcols = datecounter + 1 # to be able to set a relevant width to the table parameters_cursor.close() date_times_cursor.close() rs.close() #conn.close() myconnection.closedb() return ReportTable
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()