def create_db_views(self): SQLFile = os.path.join(os.sep,os.path.dirname(__file__),"..","definitions","add_spatial_views_for_gis2threejs.sql") myconnection = utils.dbconnection() myconnection.connect2db() curs = myconnection.conn.cursor() curs.execute("PRAGMA foreign_keys = ON") #Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection separately. sqliteline = r"""create view strat_obs_p_for_qgsi2threejs as select distinct "a"."rowid" as "rowid", "a"."obsid" as "obsid", "a"."geometry" as "geometry" from "obs_points" as "a" JOIN "stratigraphy" as "b" using ("obsid") where (typeof("a"."h_toc") in ('integer', 'real') or typeof("a"."h_gs") in ('integer', 'real'))""" curs.execute(sqliteline) sqliteline = r"""insert into views_geometry_columns (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only) values ('strat_obs_p_for_qgsi2threejs', 'geometry', 'rowid', 'obs_points', 'geometry',1);""" curs.execute(sqliteline) for key in self.strat_layers_dict: f = open(SQLFile, 'r') linecounter = 1 for line in f: if linecounter > 1: # first line is encoding info.... sqliteline = line.replace('CHANGETOVIEWNAME',key).replace('CHANGETOPLOTTYPESDICTVALUE',self.strat_layers_dict[key]) #print(sqliteline)#debug curs.execute(sqliteline) linecounter += 1 curs.execute("PRAGMA foreign_keys = OFF") myconnection.closedb()
def zip_db(self): force_another_db = False dbpath=None if self.db: use_current_db = utils.Askuser("YesNo",'Vill du göra backup av %s?'%self.db,'Which database?') if use_current_db.result == 1: dbpath = self.db force_another_db = False elif use_current_db.result == 0: force_another_db = True elif use_current_db.result == '': return if not self.db or force_another_db: dbpath = QFileDialog.getOpenFileName(None, 'Ange db som du vill skapa backup utav','',"Spatialite (*.sqlite)")[0] if dbpath: QApplication.setOverrideCursor(Qt.WaitCursor) connection = utils.dbconnection(dbpath) connection.connect2db() connection.conn.cursor().execute("begin immediate") file_path = os.path.realpath(dbpath) dir_path = os.path.dirname(file_path) current_dir = dir_path.split(os.sep)[-1] bkupname = dbpath + datetime.datetime.now().strftime('%Y%m%dT%H%M') + '.zip' zf = zipfile.ZipFile(bkupname, mode='w') zf.write(dbpath,os.path.basename(dbpath), compress_type=compression) #compression will depend on if zlib is found or not zf.close() connection.conn.rollback() connection.closedb() self.iface.messageBar().pushMessage("Information","Database backup was written to " + bkupname, 1,duration=15) QApplication.restoreOverrideCursor()
def LoadColumnsFromTable(self, table=''): """ This method returns a list with all the columns in the table""" if len(table)>0 and len(self.ms.settingsdict['database'])>0: # Should not be needed since the function never should be called without existing table... myconnection = utils.dbconnection(self.ms.settingsdict['database']) if myconnection.connect2db() == True: curs = myconnection.conn.cursor() sql = r"""SELECT * FROM '""" sql += str(table) sql += """'""" rs = curs.execute(sql) #Send the SQL statement to get the columns in the table columns = {} columns = [tuple[0] for tuple in curs.description] rs.close() myconnection.closedb()# then close the database else: columns = {} return columns # This method returns a list with all the columns in the table
def zip_db(self): force_another_db = False dbpath = None if self.db: use_current_db = utils.Askuser( "YesNo", 'Vill du göra backup av %s?' % self.db, 'Which database?') if use_current_db.result == 1: dbpath = self.db force_another_db = False elif use_current_db.result == 0: force_another_db = True elif use_current_db.result == '': return if not self.db or force_another_db: dbpath = QFileDialog.getOpenFileName( None, 'Ange db som du vill skapa backup utav', '', "Spatialite (*.sqlite)")[0] if dbpath: QApplication.setOverrideCursor(Qt.WaitCursor) connection = utils.dbconnection(dbpath) connection.connect2db() connection.conn.cursor().execute("begin immediate") file_path = os.path.realpath(dbpath) dir_path = os.path.dirname(file_path) current_dir = dir_path.split(os.sep)[-1] bkupname = dbpath + datetime.datetime.now().strftime( '%Y%m%dT%H%M') + '.zip' zf = zipfile.ZipFile(bkupname, mode='w') zf.write(dbpath, os.path.basename(dbpath), compress_type=compression ) #compression will depend on if zlib is found or not zf.close() connection.conn.rollback() connection.closedb() self.iface.messageBar().pushMessage( "Information", "Database backup was written to " + bkupname, 1, duration=15) QApplication.restoreOverrideCursor()
def loadTablesFromDB(self,db): # This method populates all table-comboboxes with the tables inside the database # Execute a query in SQLite to return all available tables (sql syntax excludes some of the predefined tables) # start with cleaning comboboxes before filling with new entries myconnection = utils.dbconnection(db)#self.ms.settingsdict['database']) if myconnection.connect2db() == True: cursor = myconnection.conn.cursor() rs=cursor.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.ListOfTables.addItem('') self.ListOfTables_2.addItem('') self.ListOfTables_3.addItem('') self.ListOfTables_WQUAL.addItem('') for row in cursor: self.ListOfTables.addItem(row[0]) self.ListOfTables_2.addItem(row[0]) self.ListOfTables_3.addItem(row[0]) self.ListOfTables_WQUAL.addItem(row[0]) rs.close() myconnection.closedb()# then close the database
def LoadDistinctPiperParams(self,db): self.ClearPiperParams() myconnection = utils.dbconnection(db)#self.ms.settingsdict['database']) if myconnection.connect2db() == True: cursor = myconnection.conn.cursor() rs=cursor.execute(r"""SELECT DISTINCT parameter FROM w_qual_lab ORDER BY parameter""") #SQL statement to get all unique parameter names self.paramCl.addItem('') self.paramHCO3.addItem('') self.paramSO4.addItem('') self.paramNa.addItem('') self.paramK.addItem('') self.paramCa.addItem('') self.paramMg.addItem('') for row in cursor: self.paramCl.addItem(row[0]) self.paramHCO3.addItem(row[0]) self.paramSO4.addItem(row[0]) self.paramNa.addItem(row[0]) self.paramK.addItem(row[0]) self.paramCa.addItem(row[0]) self.paramMg.addItem(row[0]) rs.close() myconnection.closedb()# then close the database
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) if len(self.y3col): nY = 3 elif len(self.y2col): nY = 2 else: nY = 1 p=[None]*nF*nY # List for plot objects plabel=[None]*nF*nY # List for label strings i=0 j=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) attributes = ob[i] obsid = attributes[kolumnindex] # Copy value in column obsid in the attribute list # Load all observations (full time series) for the object [i] (i.e. selected observation point no i) sql =r"""SELECT """ sql += unicode(self.xcol) #MacOSX fix1 sql += r""" as 'x'""" if len(self.y1col): sql += r""", """ sql += unicode(self.y1col) #MacOSX fix1 sql += r""" as 'y1'""" if len(self.y2col): sql += r""", """ sql += unicode(self.y2col) #MacOSX fix1 sql += r""" as 'y2'""" if len(self.y3col): sql += r""", """ sql += unicode(self.y3col) #MacOSX fix1 sql += r""" as 'y3'""" sql += """ FROM """ sql += unicode(self.table) #MacOSX fix1 sql += r""" WHERE obsid = '""" sql += obsid sql += """' ORDER BY """ sql += unicode(self.xcol) #MacOSX fix1 rs = curs.execute(sql) #Send SQL-syntax to cursor recs = rs.fetchall() # All data are stored in recs """Transform data to a numpy.recarray""" if len(self.y1col): My_format = [('x', float), ('y1', float)] if len(self.y2col): My_format.append(('y2', float)) if len(self.y3col): My_format.append(('y3', float)) 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 if self.markers==2: # If the checkbox is checked - markers will be plotted, just a line #MacOSX fix1 p[j], = ax.plot(table2.x, table2.y1, marker = 'o', linestyle = '-', label=obsid) # PLOT!! else: p[j], = ax.plot(table2.x, table2.y1, marker = 'None', linestyle = '-', label=obsid) # PLOT!! plabel[j] = obsid + unicode(self.y1col) #+ str(j)# Label for the plot #MacOSX fix1 if len(self.y2col): j = j + 1 if self.markers==2:# If the checkbox is checked - markers will be plotted, just a line #MacOSX fix1 p[j], = ax.plot(table2.x, table2.y2, marker = 'o', linestyle = '-', label=obsid) # PLOT!! else: p[j], = ax.plot(table2.x, table2.y2, marker = 'None', linestyle = '-', label=obsid) # PLOT!! plabel[j] = obsid + unicode(self.y2col) #+ str(j)# Label for the plot #MacOSX fix1 if len(self.y3col): j = j + 1 if self.markers==2: # If the checkbox is checked - markers will be plotted, just a line #MacOSX fix1 p[j], = ax.plot(table2.x, table2.y3, marker = 'o', linestyle = '-', label=obsid) # PLOT!! else: p[j], = ax.plot(table2.x, table2.y3, marker = 'None', linestyle = '-', label=obsid) # PLOT!! plabel[j] = obsid + unicode(self.y3col) #+ str(j)# Label for the plot #MacOSX fix1 j = j + 1 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)) ax.set_xlabel(self.xcol) #MacOSX fix1 ylabel = unicode(self.y1col) + ", \n" + unicode(self.y2col) + ", \n" + unicode(self.y3col) #MacOSX fix1 ax.set_ylabel(ylabel) ax.set_title(self.settingsdict['xytable']) #MacOSX fix1 leg = fig.legend(p, plabel, loc=0) 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.show() # causes conflict with plugins "statist" and "chartmaker" else: utils.pop_up_info("Please select at least one point with xy data") else: utils.pop_up_info("Please select a layer containing observations with xy data")