def export_2_csv(self,exportfolder): database = utils.dbconnection() database.connect2db() #establish connection to the current midv db curs = database.conn.cursor()#get a cursor #--------First export selected obs_points and corresponding data--------------------------- if len(self.ID_obs_points)>0:#only if there are any obs_points selected at all ptabs = ['obs_points', 'w_levels', 'w_levels_logger', 'w_flow', 'w_qual_lab', 'w_qual_field', 'stratigraphy', 'meteo'] for tname in ptabs: # problems with string replacement and tuples (no success with parameter substitution) # and therefore different sql_clause depending on number of obs if len(self.ID_obs_points)==1: sql_clause = r"""select count(obsid) from %s where obsid in ('%s')""" %(tname, self.ID_obs_points[0]) elif len(self.ID_obs_points)>1: sql_clause = r"select count(obsid) from %s where obsid in %s" %(tname, self.ID_obs_points) no_of_obs_cursor = curs.execute(sql_clause) no_of_obs = no_of_obs_cursor.fetchall() if no_of_obs[0][0] > 0:#only go on if there are any observations for this obsid some_data = 0 if len(self.ID_obs_points)==1:#problems to combine parameter substitution and string replacement in same sql clause, should be fixed sql_clause = r"""select * from %s where obsid in ('%s')""" %(tname, self.ID_obs_points[0]) some_data = 1 elif len(self.ID_obs_points)>1: sql_clause = r"select * from %s where obsid in %s" %(tname, self.ID_obs_points) some_data = 1 if some_data !=0: output = UnicodeWriter(file(os.path.join(exportfolder,tname + ".csv"), 'w')) curs.execute(sql_clause) output.writerow([col[0] for col in curs.description]) filter(None, (output.writerow(row) for row in curs)) #--------Then export selected obs_lines with corresponding data--------------------------- if len(self.ID_obs_lines)>0:#only if there are any obs_points selected at all ptabs = ['obs_lines', 'vlf_data', 'seismic_data'] for tname in ptabs: # problems with string replacement and tuples (no success with parameter substitution) # and therefore different sql_clause depending on number of obs if len(self.ID_obs_lines)==1: sql_clause = r"""select count(obsid) from %s where obsid in ('%s')""" %(tname, self.ID_obs_lines[0]) elif len(self.ID_obs_lines)>1: sql_clause = r"select count(obsid) from %s where obsid in %s" %(tname, self.ID_obs_lines) no_of_obs_cursor = curs.execute(sql_clause) no_of_obs = no_of_obs_cursor.fetchall() if no_of_obs[0][0] > 0:#only go on if there are any observations for this obsid some_data = 0 if len(self.ID_obs_lines)==1:#problems to combine parameter substitution and string replacement in same sql clause, should be fixed sql_clause = r"""select * from %s where obsid in ('%s')""" %(tname, self.ID_obs_lines[0]) some_data = 1 elif len(self.ID_obs_lines)>1: sql_clause = r"select * from %s where obsid in %s" %(tname, self.ID_obs_lines) some_data = 1 if some_data !=0: output = UnicodeWriter(file(os.path.join(exportfolder,tname + ".csv"), 'w')) curs.execute(sql_clause) output.writerow([col[0] for col in curs.description]) filter(None, (output.writerow(row) for row in curs)) database.closedb()
def _getDataStep2(self, surveys): """ STEP 2: get strata information for every point """ myconnection = utils.dbconnection() if myconnection.connect2db() == True: # create a cursor curs = myconnection.conn.cursor() for (obsid, survey) in surveys.iteritems(): sql =r"""SELECT stratid, depthtop, depthbot, geology, geoshort, capacity, comment, development FROM """ sql += self.stratitable #MacOSX fix1 sql += r""" WHERE obsid = '""" sql += str(obsid) # THIS IS WHERE THE KEY IS GIVEN TO LOAD STRATIGRAPHY FOR CHOOSEN obsid sql += """' ORDER BY stratid""" rs = curs.execute(sql) #Send SQL-syntax to cursor recs = rs.fetchall() # All data are stored in recs # parse attributes for record in recs: if utils.isinteger(record[0]) and utils.isfloat(record[1]) and utils.isfloat(record[2]): stratigaphy_id = record[0] # Stratigraphy layer no depthtotop = record[1] # depth to top of stratrigraphy layer depthtobot = record[2] # depth to bottom of stratrigraphy layer else: raise DataSanityError(str(obsid), "Something bad with stratid, depthtop or depthbot!") stratigaphy_id = 1 # when something went wrong, put it into first layer depthtotop = 0 depthtobot = 999#default value when something went wrong if record[3]: # Must check since it is not possible to print null values as text in qt widget geology = record[3] # Geology full text else: geology = " " geo_short_txt = record[4] # geo_short might contain national special characters if geo_short_txt: # Must not try to encode an empty field geo_short = unicodedata.normalize('NFKD', geo_short_txt).encode('ascii','ignore') # geo_short normalized for symbols and color else: # If the field is empty, then store an empty string geo_short = '' hydro = record[5] # waterloss (hydrogeo parameter) for color if record[6]: # Must check since it is not possible to print null values as text in qt widget comment = record[6] # else: comment = " " if record[7]: # Must check since it is not possible to print null values as text in qt widget development = record[7] # else: development = " " st = StrataInfo(stratigaphy_id, depthtotop, depthtobot, geology, geo_short, hydro, comment, development) # add strata information (in right order) insertAt = 0 for a in survey.strata: if a.stratid > stratigaphy_id: break insertAt += 1 survey.strata.insert(insertAt, st) """ Close SQLite-connections """ rs.close() # First close the table myconnection.closedb()# then close the database DataLoadingStatus = True return DataLoadingStatus, surveys else: return False, surveys
def export_2_csv(self,exportfolder): database = utils.dbconnection() database.connect2db() #establish connection to the current midv db self.curs = database.conn.cursor()#get a cursor self.exportfolder = exportfolder self.write_data(self.to_csv, self.ID_obs_points, defs.get_subset_of_tables_fr_db(category='obs_points'), utils.verify_table_exists) self.write_data(self.to_csv, self.ID_obs_lines, defs.get_subset_of_tables_fr_db(category='obs_lines'), utils.verify_table_exists) self.write_data(self.zz_to_csv, u'no_obsids', defs.get_subset_of_tables_fr_db(category='data_domains'), utils.verify_table_exists) database.closedb()
def export_2_csv(self,exportfolder): database = utils.dbconnection() database.connect2db() #establish connection to the current midv db self.curs = database.conn.cursor()#get a cursor self.exportfolder = exportfolder self.write_data(self.to_csv, self.ID_obs_points, ['obs_points', 'comments', 'w_levels', 'w_levels_logger', 'w_flow', 'w_qual_lab', 'w_qual_field', 'stratigraphy', 'meteo'], utils.verify_table_exists) self.write_data(self.to_csv, self.ID_obs_lines, ['obs_lines', 'vlf_data', 'seismic_data'], utils.verify_table_exists) self.write_data(self.zz_to_csv, u'no_obsids', ['zz_flowtype', 'zz_meteoparam', 'zz_staff', 'zz_strat', 'zz_capacity', 'zz_w_qual_field_parameters'], utils.verify_table_exists) database.closedb()
def load_obsid_from_db(self): self.combobox_obsid.clear() myconnection = utils.dbconnection() if myconnection.connect2db() == True: # skapa en cursor curs = myconnection.conn.cursor() rs=curs.execute("""select distinct obsid from w_levels_logger order by obsid""") self.combobox_obsid.addItem('') for row in curs: self.combobox_obsid.addItem(row[0]) rs.close() myconnection.closedb()
def LoadColumnsFromTable(self, table=''): """ This method returns a list with all the columns in the table""" if len(table)>0: # Should not be needed since the function never should be called without existing table... myconnection = utils.dbconnection() if myconnection.connect2db() == True: # skapa en cursor curs = myconnection.conn.cursor() sql = r"""SELECT * FROM '""" sql += unicode(table) sql += """'""" rs=curs.execute(sql) columns = {} columns = [tuple[0] for tuple in curs.description] rs.close() myconnection.closedb() else: #QMessageBox.information(None,"info","no table is loaded") # DEBUGGING columns = {} return columns # This method returns a list with all the columns in the table
def LoadTablesFromDB( self ): # Open the SpatiaLite file to extract info about tables self.table_ComboBox_1.clear() self.table_ComboBox_2.clear() self.table_ComboBox_3.clear() for i in range (1,3): self.clearthings(i) myconnection = utils.dbconnection() if myconnection.connect2db() == True: # skapa en cursor curs = myconnection.conn.cursor() rs=curs.execute(r"""SELECT tbl_name FROM sqlite_master WHERE (type='table' or type='view') and not (name in""" + midvatten_defs.SQLiteInternalTables() + r""") ORDER BY tbl_name""") #SQL statement to get the relevant tables in the spatialite database #self.dbTables = {} self.table_ComboBox_1.addItem('') self.table_ComboBox_2.addItem('') self.table_ComboBox_3.addItem('') for row in curs: self.table_ComboBox_1.addItem(row[0]) self.table_ComboBox_2.addItem(row[0]) self.table_ComboBox_3.addItem(row[0]) rs.close() myconnection.closedb()
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 drawPlot(self): QtGui.QApplication.setOverrideCursor(QtGui.QCursor(QtCore.Qt.WaitCursor))#show the user this may take a long time... self.axes.clear() My_format = [('date_time', datetime.datetime), ('values', float)] #Define (with help from function datetime) a good format for numpy array myconnection = utils.dbconnection() myconnection.connect2db() curs = myconnection.conn.cursor() i = 0 nop=0# nop=number of plots self.p=[] self.plabels=[] if not (self.table_ComboBox_1.currentText() == '' or self.table_ComboBox_1.currentText()==' ') and not (self.xcol_ComboBox_1.currentText()== '' or self.xcol_ComboBox_1.currentText()==' ') and not (self.ycol_ComboBox_1.currentText()== '' or self.ycol_ComboBox_1.currentText()==' '): #if anything is to be plotted from tab 1 self.ms.settingsdict['custplot_maxtstep'] = self.spnmaxtstep.value() # if user selected a time step bigger than zero than thre may be discontinuous plots plottable1='y' filter1 = unicode(self.Filter1_ComboBox_1.currentText()) filter1list = [] filter2list = [] filter1list = self.Filter1_QListWidget_1.selectedItems() filter2 = unicode(self.Filter2_ComboBox_1.currentText()) filter2list= self.Filter2_QListWidget_1.selectedItems() nop += max(len(filter1list),1)*max(len(filter2list),1) #self.p= [None]*nop#list for plot objects self.p.extend([None]*nop)#list for plot objects self.plabels.extend([None]*nop)# List for plot labels while i < len(self.p): if not (filter1 == '' or filter1==' ' or filter1list==[]) and not (filter2== '' or filter2==' ' or filter2list==[]): for item1 in filter1list: for item2 in filter2list: sql = r""" select """ + unicode(self.xcol_ComboBox_1.currentText()) + """, """ + unicode(self.ycol_ComboBox_1.currentText()) + """ from """ + unicode(self.table_ComboBox_1.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' and """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_1.currentText()) self.plabels[i] = unicode(item1.text()) + """, """ + unicode(item2.text()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_1.currentText()) i += 1 elif not (filter1 == '' or filter1==' ' or filter1list==[]): for item1 in filter1list: sql = r""" select """ + unicode(self.xcol_ComboBox_1.currentText()) + """, """ + unicode(self.ycol_ComboBox_1.currentText()) + """ from """ + unicode(self.table_ComboBox_1.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' order by """ + unicode(self.xcol_ComboBox_1.currentText()) self.plabels[i] = unicode(item1.text()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_1.currentText()) i += 1 elif not (filter2 == '' or filter2==' ' or filter2list==[]): for item2 in filter2list: sql = r""" select """ + unicode(self.xcol_ComboBox_1.currentText()) + """, """ + unicode(self.ycol_ComboBox_1.currentText()) + """ from """ + unicode(self.table_ComboBox_1.currentText()) + """ where """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_1.currentText()) self.plabels[i] = unicode(item2.text()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_1.currentText()) i += 1 else: sql = r""" select """ + unicode(self.xcol_ComboBox_1.currentText()) + """, """ + unicode(self.ycol_ComboBox_1.currentText()) + """ from """ + unicode(self.table_ComboBox_1.currentText()) + """ order by """ + unicode(self.xcol_ComboBox_1.currentText()) self.plabels[i] = unicode(self.ycol_ComboBox_1.currentText())+""", """+unicode(self.table_ComboBox_1.currentText()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_1.currentText()) i += 1 if not (self.table_ComboBox_2.currentText() == '' or self.table_ComboBox_2.currentText()==' ') and not (self.xcol_ComboBox_2.currentText()== '' or self.xcol_ComboBox_2.currentText()==' ') and not (self.ycol_ComboBox_2.currentText()== '' or self.ycol_ComboBox_2.currentText()==' '):#if anything is to be plotted from tab 2 self.ms.settingsdict['custplot_maxtstep'] = self.spnmaxtstep.value() # if user selected a time step bigger than zero than thre may be discontinuous plots plottable2='y' filter1 = unicode(self.Filter1_ComboBox_2.currentText()) filter1list = [] filter2list = [] filter1list = self.Filter1_QListWidget_2.selectedItems() filter2 = unicode(self.Filter2_ComboBox_2.currentText()) filter2list= self.Filter2_QListWidget_2.selectedItems() nop =+ max(len(filter1list),1)*max(len(filter2list),1) self.p.extend([None]*nop)#list for plot objects self.plabels.extend([None]*nop)# List for plot labels while i < len(self.p): if not (filter1 == '' or filter1==' ' or filter1list==[]) and not (filter2== '' or filter2==' ' or filter2list==[]): for item1 in filter1list: for item2 in filter2list: sql = r""" select """ + unicode(self.xcol_ComboBox_2.currentText()) + """, """ + unicode(self.ycol_ComboBox_2.currentText()) + """ from """ + unicode(self.table_ComboBox_2.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' and """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_2.currentText()) self.plabels[i] = unicode(item1.text()) + """, """ + unicode(item2.text()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_2.currentText()) i += 1 elif not (filter1 == '' or filter1==' ' or filter1list==[]): for item1 in filter1list: sql = r""" select """ + unicode(self.xcol_ComboBox_2.currentText()) + """, """ + unicode(self.ycol_ComboBox_2.currentText()) + """ from """ + unicode(self.table_ComboBox_2.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' order by """ + unicode(self.xcol_ComboBox_2.currentText()) self.plabels[i] = unicode(item1.text()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_2.currentText()) i += 1 elif not (filter2 == '' or filter2==' ' or filter2list==[]): for item2 in filter2list: sql = r""" select """ + unicode(self.xcol_ComboBox_2.currentText()) + """, """ + unicode(self.ycol_ComboBox_2.currentText()) + """ from """ + unicode(self.table_ComboBox_2.currentText()) + """ where """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_2.currentText()) self.plabels[i] = unicode(item2.text()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_2.currentText()) i += 1 else: sql = r""" select """ + unicode(self.xcol_ComboBox_2.currentText()) + """, """ + unicode(self.ycol_ComboBox_2.currentText()) + """ from """ + unicode(self.table_ComboBox_2.currentText()) + """ order by """ + unicode(self.xcol_ComboBox_2.currentText()) self.plabels[i] = unicode(self.ycol2)+""", """+unicode(self.table_ComboBox_2.currentText()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_2.currentText()) i += 1 if not (self.table_ComboBox_3.currentText() == '' or self.table_ComboBox_3.currentText()==' ') and not (self.xcol_ComboBox_3.currentText()== '' or self.xcol_ComboBox_3.currentText()==' ') and not (self.ycol_ComboBox_3.currentText()== '' or self.ycol_ComboBox_3.currentText()==' '):#if anything is to be plotted from tab 3 self.ms.settingsdict['custplot_maxtstep'] = self.spnmaxtstep.value() # if user selected a time step bigger than zero than thre may be discontinuous plots plottable3='y' filter1 = unicode(self.Filter1_ComboBox_3.currentText()) filter1list = [] filter2list = [] filter1list = self.Filter1_QListWidget_3.selectedItems() filter2 = unicode(self.Filter2_ComboBox_3.currentText()) filter2list= self.Filter2_QListWidget_3.selectedItems() nop =+ max(len(filter1list),1)*max(len(filter2list),1) self.p.extend([None]*nop)#list for plot objects self.plabels.extend([None]*nop)# List for plot labels while i < len(self.p): if not (filter1 == '' or filter1==' ' or filter1list==[]) and not (filter2== '' or filter2==' ' or filter2list==[]): for item1 in filter1list: for item2 in filter2list: sql = r""" select """ + unicode(self.xcol_ComboBox_3.currentText()) + """, """ + unicode(self.ycol_ComboBox_3.currentText()) + """ from """ + unicode(self.table_ComboBox_3.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' and """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_3.currentText()) self.plabels[i] = unicode(item1.text()) + """, """ + unicode(item2.text()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_3.currentText()) i += 1 elif not (filter1 == '' or filter1==' ' or filter1list==[]): for item1 in filter1list: sql = r""" select """ + unicode(self.xcol_ComboBox_3.currentText()) + """, """ + unicode(self.ycol_ComboBox_3.currentText()) + """ from """ + unicode(self.table_ComboBox_3.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' order by """ + unicode(self.xcol_ComboBox_3.currentText()) self.plabels[i] = unicode(item1.text()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_3.currentText()) i += 1 elif not (filter2 == '' or filter2==' ' or filter2list==[]): for item2 in filter2list: sql = r""" select """ + unicode(self.xcol_ComboBox_3.currentText()) + """, """ + unicode(self.ycol_ComboBox_3.currentText()) + """ from """ + unicode(self.table_ComboBox_3.currentText()) + """ where """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_3.currentText()) self.plabels[i] = unicode(item2.text()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_3.currentText()) i += 1 else: sql = r""" select """ + unicode(self.xcol_ComboBox_3.currentText()) + """, """ + unicode(self.ycol_ComboBox_3.currentText()) + """ from """ + unicode(self.table_ComboBox_3.currentText()) + """ order by """ + unicode(self.xcol_ComboBox_3.currentText()) self.plabels[i] = unicode(self.ycol_ComboBox_3.currentText())+""", """+unicode(self.table_ComboBox_3.currentText()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_3.currentText()) i += 1 #rs.close() # close the cursor myconnection.closedb() # close the database self.refreshPlot() QtGui.QApplication.restoreOverrideCursor()#now this long process is done and the cursor is back as normal
def drawPlot(self): QtGui.QApplication.setOverrideCursor(QtGui.QCursor(QtCore.Qt.WaitCursor))#show the user this may take a long time... self.axes.clear() self.axes.legend_ = None My_format = [('date_time', datetime.datetime), ('values', float)] #Define (with help from function datetime) a good format for numpy array myconnection = utils.dbconnection() myconnection.connect2db() curs = myconnection.conn.cursor() i = 0 nop=0# nop=number of plots self.p=[] self.plabels=[] if not (self.table_ComboBox_1.currentText() == '' or self.table_ComboBox_1.currentText()==' ') and not (self.xcol_ComboBox_1.currentText()== '' or self.xcol_ComboBox_1.currentText()==' ') and not (self.ycol_ComboBox_1.currentText()== '' or self.ycol_ComboBox_1.currentText()==' '): #if anything is to be plotted from tab 1 self.ms.settingsdict['custplot_maxtstep'] = self.spnmaxtstep.value() # if user selected a time step bigger than zero than thre may be discontinuous plots plottable1='y' filter1 = unicode(self.Filter1_ComboBox_1.currentText()) filter1list = [] filter2list = [] filter1list = self.Filter1_QListWidget_1.selectedItems() filter2 = unicode(self.Filter2_ComboBox_1.currentText()) filter2list= self.Filter2_QListWidget_1.selectedItems() nop += max(len(filter1list),1)*max(len(filter2list),1) #self.p= [None]*nop#list for plot objects self.p.extend([None]*nop)#list for plot objects self.plabels.extend([None]*nop)# List for plot labels try: factor1 = float(self.LineEditFactor1.text().replace(',','.')) except ValueError: factor1 = 1.0 try: offset1 = float(self.LineEditOffset1.text().replace(',','.')) except ValueError: offset1 = 0.0 remove_mean1 = self.checkBox_remove_mean1.isChecked() while i < len(self.p): if not (filter1 == '' or filter1==' ' or filter1list==[]) and not (filter2== '' or filter2==' ' or filter2list==[]): for item1 in filter1list: for item2 in filter2list: sql = r""" select """ + unicode(self.xcol_ComboBox_1.currentText()) + """, """ + unicode(self.ycol_ComboBox_1.currentText()) + """ from """ + unicode(self.table_ComboBox_1.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' and """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_1.currentText()) self.plabels[i] = unicode(item1.text()) + """, """ + unicode(item2.text()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_1.currentText(), factor1, offset1, remove_mean1) i += 1 elif not (filter1 == '' or filter1==' ' or filter1list==[]): for item1 in filter1list: sql = r""" select """ + unicode(self.xcol_ComboBox_1.currentText()) + """, """ + unicode(self.ycol_ComboBox_1.currentText()) + """ from """ + unicode(self.table_ComboBox_1.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' order by """ + unicode(self.xcol_ComboBox_1.currentText()) self.plabels[i] = unicode(item1.text()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_1.currentText(), factor1, offset1, remove_mean1) i += 1 elif not (filter2 == '' or filter2==' ' or filter2list==[]): for item2 in filter2list: sql = r""" select """ + unicode(self.xcol_ComboBox_1.currentText()) + """, """ + unicode(self.ycol_ComboBox_1.currentText()) + """ from """ + unicode(self.table_ComboBox_1.currentText()) + """ where """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_1.currentText()) self.plabels[i] = unicode(item2.text()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_1.currentText(), factor1, offset1, remove_mean1) i += 1 else: sql = r""" select """ + unicode(self.xcol_ComboBox_1.currentText()) + """, """ + unicode(self.ycol_ComboBox_1.currentText()) + """ from """ + unicode(self.table_ComboBox_1.currentText()) + """ order by """ + unicode(self.xcol_ComboBox_1.currentText()) self.plabels[i] = unicode(self.ycol_ComboBox_1.currentText())+""", """+unicode(self.table_ComboBox_1.currentText()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_1.currentText(), factor1, offset1, remove_mean1) i += 1 if not (self.table_ComboBox_2.currentText() == '' or self.table_ComboBox_2.currentText()==' ') and not (self.xcol_ComboBox_2.currentText()== '' or self.xcol_ComboBox_2.currentText()==' ') and not (self.ycol_ComboBox_2.currentText()== '' or self.ycol_ComboBox_2.currentText()==' '):#if anything is to be plotted from tab 2 self.ms.settingsdict['custplot_maxtstep'] = self.spnmaxtstep.value() # if user selected a time step bigger than zero than thre may be discontinuous plots plottable2='y' filter1 = unicode(self.Filter1_ComboBox_2.currentText()) filter1list = [] filter2list = [] filter1list = self.Filter1_QListWidget_2.selectedItems() filter2 = unicode(self.Filter2_ComboBox_2.currentText()) filter2list= self.Filter2_QListWidget_2.selectedItems() nop =+ max(len(filter1list),1)*max(len(filter2list),1) self.p.extend([None]*nop)#list for plot objects self.plabels.extend([None]*nop)# List for plot labels try: factor2 = float(self.LineEditFactor2.text().replace(',','.')) except ValueError: factor2 = 1.0 try: offset2 = float(self.LineEditOffset2.text().replace(',','.')) except ValueError: offset2 = 0.0 remove_mean2 = self.checkBox_remove_mean2.isChecked() while i < len(self.p): if not (filter1 == '' or filter1==' ' or filter1list==[]) and not (filter2== '' or filter2==' ' or filter2list==[]): for item1 in filter1list: for item2 in filter2list: sql = r""" select """ + unicode(self.xcol_ComboBox_2.currentText()) + """, """ + unicode(self.ycol_ComboBox_2.currentText()) + """ from """ + unicode(self.table_ComboBox_2.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' and """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_2.currentText()) self.plabels[i] = unicode(item1.text()) + """, """ + unicode(item2.text()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_2.currentText(), factor2, offset2, remove_mean2) i += 1 elif not (filter1 == '' or filter1==' ' or filter1list==[]): for item1 in filter1list: sql = r""" select """ + unicode(self.xcol_ComboBox_2.currentText()) + """, """ + unicode(self.ycol_ComboBox_2.currentText()) + """ from """ + unicode(self.table_ComboBox_2.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' order by """ + unicode(self.xcol_ComboBox_2.currentText()) self.plabels[i] = unicode(item1.text()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_2.currentText(), factor2, offset2, remove_mean2) i += 1 elif not (filter2 == '' or filter2==' ' or filter2list==[]): for item2 in filter2list: sql = r""" select """ + unicode(self.xcol_ComboBox_2.currentText()) + """, """ + unicode(self.ycol_ComboBox_2.currentText()) + """ from """ + unicode(self.table_ComboBox_2.currentText()) + """ where """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_2.currentText()) self.plabels[i] = unicode(item2.text()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_2.currentText(), factor2, offset2, remove_mean2) i += 1 else: sql = r""" select """ + unicode(self.xcol_ComboBox_2.currentText()) + """, """ + unicode(self.ycol_ComboBox_2.currentText()) + """ from """ + unicode(self.table_ComboBox_2.currentText()) + """ order by """ + unicode(self.xcol_ComboBox_2.currentText()) self.plabels[i] = unicode(self.ycol2)+""", """+unicode(self.table_ComboBox_2.currentText()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_2.currentText(), factor2, offset2, remove_mean2) i += 1 if not (self.table_ComboBox_3.currentText() == '' or self.table_ComboBox_3.currentText()==' ') and not (self.xcol_ComboBox_3.currentText()== '' or self.xcol_ComboBox_3.currentText()==' ') and not (self.ycol_ComboBox_3.currentText()== '' or self.ycol_ComboBox_3.currentText()==' '):#if anything is to be plotted from tab 3 self.ms.settingsdict['custplot_maxtstep'] = self.spnmaxtstep.value() # if user selected a time step bigger than zero than thre may be discontinuous plots plottable3='y' filter1 = unicode(self.Filter1_ComboBox_3.currentText()) filter1list = [] filter2list = [] filter1list = self.Filter1_QListWidget_3.selectedItems() filter2 = unicode(self.Filter2_ComboBox_3.currentText()) filter2list= self.Filter2_QListWidget_3.selectedItems() nop =+ max(len(filter1list),1)*max(len(filter2list),1) self.p.extend([None]*nop)#list for plot objects self.plabels.extend([None]*nop)# List for plot labels try: factor3 = float(self.LineEditFactor3.text().replace(',','.')) except ValueError: factor3 = 1.0 try: offset3 = float(self.LineEditOffset3.text().replace(',','.')) except ValueError: offset3 = 0.0 remove_mean3 = self.checkBox_remove_mean3.isChecked() while i < len(self.p): if not (filter1 == '' or filter1==' ' or filter1list==[]) and not (filter2== '' or filter2==' ' or filter2list==[]): for item1 in filter1list: for item2 in filter2list: sql = r""" select """ + unicode(self.xcol_ComboBox_3.currentText()) + """, """ + unicode(self.ycol_ComboBox_3.currentText()) + """ from """ + unicode(self.table_ComboBox_3.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' and """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_3.currentText()) self.plabels[i] = unicode(item1.text()) + """, """ + unicode(item2.text()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_3.currentText(), factor3, offset3, remove_mean3) i += 1 elif not (filter1 == '' or filter1==' ' or filter1list==[]): for item1 in filter1list: sql = r""" select """ + unicode(self.xcol_ComboBox_3.currentText()) + """, """ + unicode(self.ycol_ComboBox_3.currentText()) + """ from """ + unicode(self.table_ComboBox_3.currentText()) + """ where """ + filter1 + """='""" + unicode(item1.text())+ """' order by """ + unicode(self.xcol_ComboBox_3.currentText()) self.plabels[i] = unicode(item1.text()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_3.currentText(), factor3, offset3, remove_mean3) i += 1 elif not (filter2 == '' or filter2==' ' or filter2list==[]): for item2 in filter2list: sql = r""" select """ + unicode(self.xcol_ComboBox_3.currentText()) + """, """ + unicode(self.ycol_ComboBox_3.currentText()) + """ from """ + unicode(self.table_ComboBox_3.currentText()) + """ where """ + filter2 + """='""" + unicode(item2.text())+ """' order by """ + unicode(self.xcol_ComboBox_3.currentText()) self.plabels[i] = unicode(item2.text()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_3.currentText(), factor3, offset3, remove_mean3) i += 1 else: sql = r""" select """ + unicode(self.xcol_ComboBox_3.currentText()) + """, """ + unicode(self.ycol_ComboBox_3.currentText()) + """ from """ + unicode(self.table_ComboBox_3.currentText()) + """ order by """ + unicode(self.xcol_ComboBox_3.currentText()) self.plabels[i] = unicode(self.ycol_ComboBox_3.currentText())+""", """+unicode(self.table_ComboBox_3.currentText()) self.createsingleplotobject(sql,i,My_format,curs,self.PlotType_comboBox_3.currentText(), factor3, offset3, remove_mean3) i += 1 #rs.close() # close the cursor myconnection.closedb() # close the database self.xaxis_formatters = (self.axes.xaxis.get_major_formatter(), self.axes.xaxis.get_major_locator()) self.axes.set_title(self.ms.settingsdict['custplot_title']) self.axes.set_xlabel(self.ms.settingsdict['custplot_xtitle']) self.axes.set_ylabel(self.ms.settingsdict['custplot_ytitle']) self.drawn = True self.refreshPlot() QtGui.QApplication.restoreOverrideCursor()#now this long process is done and the cursor is back as normal
def showtheplot(self, layer): # PlotTS method that, at the moment, performs all the real work provider = layer.dataProvider() #Something with OGR kolumnindex = provider.fieldNameIndex('obsid') # To find the column named 'obsid' if kolumnindex == -1: kolumnindex = provider.fieldNameIndex('OBSID') # backwards compatibility if(layer): nF = layer.selectedFeatureCount() if (nF > 0): myconnection = utils.dbconnection() if myconnection.connect2db() == True: # skapa en cursor curs = myconnection.conn.cursor() # Load all selected observation points ob = layer.selectedFeatures() # Create a plot window with one single subplot fig = plt.figure() # causes conflict with plugins "statist" and "chartmaker" ax = fig.add_subplot(111) p=[None]*nF # List for plot objects plabel=[None]*nF # List for label strings i=0 for k in ob: # Loop through all selected objects, a plot is added for each one of the observation points (i.e. selected objects) obsid = unicode(ob[i][kolumnindex]) # Load all observations (full time series) for the object [i] (i.e. selected observation point no i) sql =r"""SELECT date_time as 'date [datetime]', """ sql += unicode(self.settingsdict['tscolumn']) #MacOSX fix1 sql += """ FROM """ sql += unicode(self.settingsdict['tstable']) #MacOSX fix1 sql += r""" WHERE obsid = '""" sql += obsid sql += """' ORDER BY date_time """ rs = curs.execute(sql) #Send SQL-syntax to cursor recs = rs.fetchall() # All data are stored in recs """Transform data to a numpy.recarray""" My_format = [('date_time', datetime.datetime), ('values', float)] #Define format with help from function datetime table = np.array(recs, dtype=My_format) #NDARRAY table2=table.view(np.recarray) # RECARRAY Makes the two columns inte callable objects, i.e. write table2.values """ Get help from function datestr2num to get date and time into float""" myTimestring = [] #LIST j = 0 for row in table2: myTimestring.append(table2.date_time[j]) j = j + 1 numtime=datestr2num(myTimestring) #conv list of strings to numpy.ndarray of floats if self.settingsdict['tsdotmarkers']==2: # If the checkbox is checked - markers will be plotted #MacOSX fix1 if self.settingsdict['tsstepplot']==2: # If the checkbox is checked - draw a step plot #MacOSX fix1 p[i], = ax.plot_date(numtime, table2.values, marker = 'o', linestyle = '-', drawstyle='steps-pre', label=obsid) # PLOT!! else: p[i], = ax.plot_date(numtime, table2.values, 'o-', label=obsid) else: # NO markers wil be plotted, , just a line if self.settingsdict['tsstepplot']==2: # If the checkbox is checked - draw a step plot #MacOSX fix1 p[i], = ax.plot_date(numtime, table2.values, marker = 'None', linestyle = '-', drawstyle='steps-pre', label=obsid) # PLOT!! else: p[i], = ax.plot_date(numtime, table2.values, '-', label=obsid) plabel[i] = obsid # Label for the plot i = i+1 """ Close SQLite-connections """ rs.close() # First close the table myconnection.closedb()# then close the database """ Finish plot """ ax.grid(True) ax.yaxis.set_major_formatter(tick.ScalarFormatter(useOffset=False, useMathText=False)) fig.autofmt_xdate() ax.set_ylabel(self.settingsdict['tscolumn']) #MacOSX fix1 ax.set_title(self.settingsdict['tstable'])#MacOSX fix1 leg = fig.legend(p, plabel, loc=0)#leg = fig.legend(p, plabel, 'right') leg.draggable(state=True) frame = leg.get_frame() # the matplotlib.patches.Rectangle instance surrounding the legend frame.set_facecolor('0.80') # set the frame face color to light gray frame.set_fill(False) # set the frame face color transparent for t in leg.get_texts(): t.set_fontsize(10) # the legend text fontsize for label in ax.xaxis.get_ticklabels(): label.set_fontsize(10) for label in ax.yaxis.get_ticklabels(): label.set_fontsize(10) #plt.ion()#force interactivity to prevent the plot window from blocking the qgis app plt.show() #plt.draw() else: utils.pop_up_info("Please select at least one point with time series data") else: utils.pop_up_info("Please select a layer with time series observation points")