Python midvatten_utils.sql_load_fr_db函数代码示例

    def calcall(self):
        fr_d_t = self.FromDateTime.dateTime().toPyDateTime()
        to_d_t = self.ToDateTime.dateTime().toPyDateTime()
        
#        sanity1 = utils.sql_load_fr_db("""SELECT obs_points.h_toc FROM obs_points LEFT JOIN w_levels WHERE w_levels.obsid = obs_points.obsid AND obs_points.h_toc""")[1]
        sanity1 = utils.sql_load_fr_db("""SELECT obs_points.h_toc FROM obs_points LEFT JOIN w_levels WHERE w_levels.obsid = obs_points.obsid""")[1]
        sanity2 = utils.sql_load_fr_db("""SELECT obs_points.h_toc FROM obs_points LEFT JOIN w_levels WHERE w_levels.obsid = obs_points.obsid AND obs_points.h_toc NOT NULL""")[1]
        
        if len(sanity1) == len(sanity2): #only if h_toc exists for all objects!!
            sql1 = """UPDATE OR IGNORE w_levels SET h_toc = (SELECT obs_points.h_toc FROM obs_points WHERE w_levels.obsid = obs_points.obsid) WHERE """
            sql1 += """date_time >= '"""
            sql1 += str(fr_d_t)
            sql1 += """' AND date_time <= '"""
            sql1 += str(to_d_t)
            sql1 += """' """
            utils.sql_alter_db(sql1)
            sql2 = """UPDATE OR IGNORE w_levels SET level_masl = h_toc - meas WHERE """
            sql2 += """date_time >= '"""
            sql2 += str(fr_d_t)
            sql2 += """' AND date_time <= '"""
            sql2 += str(to_d_t)
            sql2 += """' """        
            utils.sql_alter_db(sql2)
            self.close()
        else:
            utils.pop_up_info('Calculation aborted! There seems to be NULL values in your table obs_points, column h_toc.','Error')
            self.close()

    def calcselected(self):
        obsar = utils.getselectedobjectnames(self.layer)
        observations = obsar
        i=0
        for obs in obsar:
                observations[i] = obs.encode('utf-8') #turn into a list of python byte strings
                i += 1        
        fr_d_t = self.FromDateTime.dateTime().toPyDateTime()
        to_d_t = self.ToDateTime.dateTime().toPyDateTime()

        sanity1 = utils.sql_load_fr_db("""SELECT obs_points.h_toc FROM obs_points LEFT JOIN w_levels WHERE w_levels.obsid = obs_points.obsid AND obs_points.obsid IN """ + (str(observations)).encode('utf-8').replace('[','(').replace(']',')'))[1]
        sanity2 = utils.sql_load_fr_db("""SELECT obs_points.h_toc FROM obs_points LEFT JOIN w_levels WHERE w_levels.obsid = obs_points.obsid AND obs_points.h_toc NOT NULL  AND obs_points.obsid IN """ + (str(observations)).encode('utf-8').replace('[','(').replace(']',')'))[1]

        if len(sanity1) == len(sanity2): #only if h_toc exists for all objects
            sql1 = """UPDATE OR IGNORE w_levels SET h_toc = (SELECT obs_points.h_toc FROM obs_points WHERE w_levels.obsid = obs_points.obsid) WHERE obsid IN """
            sql1 += str(observations)
            sql1 += """ AND date_time >= '"""
            sql1 += str(fr_d_t)
            sql1 += """' AND date_time <= '"""
            sql1 += str(to_d_t)
            sql1 += """' """   
            utils.sql_alter_db(sql1.replace("[","(").replace("]",")"))
            sql2 = """UPDATE OR IGNORE w_levels SET level_masl = h_toc - meas WHERE obsid IN """
            sql2 += str(observations)
            sql2 += """ AND date_time >= '"""
            sql2 += str(fr_d_t)
            sql2 += """' AND date_time <= '"""
            sql2 += str(to_d_t)
            sql2 += """' """        
            utils.sql_alter_db(sql2.replace("[","(").replace("]",")"))
            self.close()        
        else:
            utils.pop_up_info('Calculation aborted! There seems to be NULL values in your table obs_points, column h_toc.','Error')
            self.close()

 def calibrateandplot(self):
     obsid = unicode(self.combobox_obsid.currentText())
     if not obsid=='':
         sanity1sql = """select count(obsid) from w_levels_logger where obsid = '""" +  obsid[0] + """'"""
         sanity2sql = """select count(obsid) from w_levels_logger where head_cm not null and head_cm !='' and obsid = '""" +  obsid[0] + """'"""
         if utils.sql_load_fr_db(sanity1sql)[1] == utils.sql_load_fr_db(sanity2sql)[1]: # This must only be done if head_cm exists for all data
             fr_d_t = self.FromDateTime.dateTime().toPyDateTime()
             to_d_t = self.ToDateTime.dateTime().toPyDateTime()
             newzref = self.LoggerPos.text()
             if len(newzref)>0:
                 sql =r"""UPDATE w_levels_logger SET level_masl = """
                 sql += str(newzref)
                 sql += """ + head_cm / 100 WHERE obsid = '"""
                 sql += obsid   
                 sql += """' AND date_time >= '"""
                 sql += str(fr_d_t)
                 sql += """' AND date_time <= '"""
                 sql += str(to_d_t)
                 sql += """' """
                 dummy = utils.sql_alter_db(sql)
             self.CalibrationPlot(obsid)
             self.getlastcalibration()
         else:
             utils.pop_up_info("Calibration aborted!!\nThere must not be empty cells or\nnull values in the 'head_cm' column!")
     else:
         self.INFO.setText("Select the observation point with logger data to be calibrated.")

def GetStatistics(obsid = ''):
    Statistics_list = [0]*4

    columns = ['meas', 'level_masl']
    meas_or_level_masl= 'meas'#default value

    #number of values, also decide wehter to use meas or level_masl in report
    for column in columns:
        sql = r"""select Count(""" + column + r""") from w_levels where obsid = '"""
        sql += obsid
        sql += r"""'"""
        ConnectionOK, number_of_values = utils.sql_load_fr_db(sql)
        if number_of_values and number_of_values[0][0] > Statistics_list[2]:#this will select meas if meas >= level_masl
            meas_or_level_masl = column
            Statistics_list[2] = number_of_values[0][0]

    #min value
    if meas_or_level_masl=='meas':
        sql = r"""select min(meas) from w_levels where obsid = '"""
    else:
        sql = r"""select max(level_masl) from w_levels where obsid = '"""
    sql += obsid
    sql += r"""'"""
    ConnectionOK, min_value = utils.sql_load_fr_db(sql)
    if min_value:
        Statistics_list[0] = min_value[0][0]

    #median value
    sql = r"""SELECT x.obsid, x.""" + meas_or_level_masl + r""" as median from (select obsid, """ + meas_or_level_masl + r""" FROM w_levels WHERE obsid = '"""
    sql += obsid
    sql += r"""' and (typeof(""" + meas_or_level_masl + r""")=typeof(0.01) or typeof(""" + meas_or_level_masl + r""")=typeof(1))) as x, (select obsid, """ + meas_or_level_masl + r""" FROM w_levels WHERE obsid = '"""
    sql += obsid
    sql += r"""' and (typeof(""" + meas_or_level_masl + r""")=typeof(0.01) or typeof(""" + meas_or_level_masl + r""")=typeof(1))) as y GROUP BY x.""" + meas_or_level_masl + r""" HAVING SUM(CASE WHEN y.""" + meas_or_level_masl + r""" <= x.""" + meas_or_level_masl + r""" THEN 1 ELSE 0 END)>=(COUNT(*)+1)/2 AND SUM(CASE WHEN y.""" + meas_or_level_masl + r""" >= x.""" + meas_or_level_masl + r""" THEN 1 ELSE 0 END)>=(COUNT(*)/2)+1"""
    ConnectionOK, median_value = utils.sql_load_fr_db(sql)
    if median_value:
        Statistics_list[1] = median_value[0][1]

    #max value
    if meas_or_level_masl=='meas':
        sql = r"""select max(meas) from w_levels where obsid = '"""
    else:
        sql = r"""select min(level_masl) from w_levels where obsid = '"""
    sql += obsid
    sql += r"""'"""
    ConnectionOK, max_value = utils.sql_load_fr_db(sql)
    if max_value:
        Statistics_list[3] = max_value[0][0]

    return meas_or_level_masl, Statistics_list

 def sql_into_recarray(self, sql):
     """ Converts and runs an sql-string and turns the answer into an np.recarray and returns it""" 
     my_format = [('date_time', datetime.datetime), ('values', float)] #Define (with help from function datetime) a good format for numpy array     
     recs = utils.sql_load_fr_db(sql)[1]
     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 
     return table2        

    def test_create_db_locale_se(self, mock_locale):
        mock_locale.return_value = [u'se_SV']

        self.midvatten.new_db(u'sv_SE')
        test_string = utils_for_tests.create_test_string(utils.sql_load_fr_db(u'select * from zz_strat'))
        reference_string = ur"""(True, [(okänt, white, , white, NoBrush, not in ('berg','b','rock','ro','grovgrus','grg','coarse gravel','cgr','grus','gr','gravel','mellangrus','grm','medium gravel','mgr','fingrus','grf','fine gravel','fgr','grovsand','sag','coarse sand','csa','sand','sa','mellansand','sam','medium sand','msa','finsand','saf','fine sand','fsa','silt','si','lera','ler','le','clay','cl','morän','moran','mn','till','ti','torv','t','peat','pt','fyll','fyllning','f','made ground','mg','land fill')), (berg, red, x, red, DiagCrossPattern, in ('berg','b','rock','ro')), (grovgrus, DarkGreen, O, darkGreen, Dense7Pattern, in ('grovgrus','grg','coarse gravel','cgr')), (grus, DarkGreen, O, darkGreen, Dense7Pattern, in ('grus','gr','gravel')), (mellangrus, DarkGreen, o, darkGreen, Dense6Pattern, in ('mellangrus','grm','medium gravel','mgr')), (fingrus, DarkGreen, o, darkGreen, Dense6Pattern, in ('fingrus','grf','fine gravel','fgr')), (grovsand, green, *, green, Dense5Pattern, in ('grovsand','sag','coarse sand','csa')), (sand, green, *, green, Dense5Pattern, in ('sand','sa')), (mellansand, green, ., green, Dense4Pattern, in ('mellansand','sam','medium sand','msa')), (finsand, DarkOrange, ., orange, Dense5Pattern, in ('finsand','saf','fine sand','fsa')), (silt, yellow, \\, yellow, BDiagPattern, in ('silt','si')), (lera, yellow, -, yellow, HorPattern, in ('lera','ler','le','clay','cl')), (morän, cyan, /, yellow, CrossPattern, in ('morän','moran','mn','till','ti')), (torv, DarkGray, +, darkGray, NoBrush, in ('torv','t','peat','pt')), (fyll, white, +, white, DiagCrossPattern, in ('fyll','fyllning','f','made ground','mg','land fill'))])"""
        assert test_string == reference_string

 def test_add_triggers_not_change_existing(self):
     """ Adding triggers should not automatically change the db """
     utils.sql_alter_db(u'''INSERT INTO obs_points ("obsid", "east", "north") VALUES ('rb1', 1, 1)''')
     test_string = utils_for_tests.create_test_string(utils.sql_load_fr_db(u'select obsid, east, north, AsText(geometry) from obs_points'))
     utils.add_triggers_to_obs_points()
     reference_string = u'(True, [(rb1, 1.0, 1.0, None)])'
     assert test_string == reference_string

 def get_piper_data(self):
     #These observations are supposed to be in mg/l and must be stored in a Midvatten database, table w_qual_lab
     sql = self.big_sql()
     print(sql)#debug
     # get data into a list: obsid, date_time, type, Cl_meqPl, HCO3_meqPl, SO4_meqPl, Na+K_meqPl, Ca_meqPl, Mg_meqPl
     obsimport = utils.sql_load_fr_db(sql)[1]
     #convert to numpy ndarray W/O format specified
     self.obsnp_nospecformat = np.array(obsimport)
     #define format
     """ some problems with string fields
     np.str_
     My_format = [('obsid', str), 
     My_format = [('obsid', unicode), 
     My_format = [('obsid', np.dtype('a35')), 
     My_format = [('obsid', np.dtype(np.str_)),
     My_format = [('obsid', np.str_),
     My_format = [('obsid', object),
     none is working besides from 'a35' which limits string length to 35 characters 
     least bad is the "object" type, then everything is loaded, but all strings as unicode strings which _should_ be ok
     """
     My_format = [('obsid', object), ('date_time', datetime.datetime),('obstype', object),('Cl_meqPl', float),('HCO3_meqPl', float),('SO4_meqPl', float),('NaK_meqPl', float),('Ca_meqPl', float),('Mg_meqPl', float)]
     #convert to numpy ndarray W format specified - i.e. a structured array
     self.obsnp_specified_format = np.array(obsimport, dtype=My_format)
     #convert to np recarray - takes the structured array and makes the columns into callable objects, i.e. write table2.Cl_meqPl
     self.obsrecarray=self.obsnp_specified_format.view(np.recarray)

    def test_add_east_north_from_geometry(self):
        """ Test that adding triggers and adding obsid with geometry also adds east, north
        :return:
        """
        utils.add_triggers_to_obs_points()
        utils.sql_alter_db(u"""INSERT INTO obs_points (obsid, geometry) VALUES ('rb1', GeomFromText('POINT(1.0 1.0)', 3006))""")

        test_string = utils_for_tests.create_test_string(utils.sql_load_fr_db(u'select obsid, east, north, AsText(geometry) from obs_points'))
        reference_string = u'(True, [(rb1, 1.0, 1.0, POINT(1 1))])'
        assert test_string == reference_string

    def test_add_geometry_from_east_north(self):
        """ Test that adding triggers and adding obsid with east, north also adds geometry
        :return:
        """
        utils.add_triggers_to_obs_points()
        utils.sql_alter_db(u'''INSERT INTO obs_points ("obsid", "east", "north") VALUES ('rb1', 1, 1)''')

        test_string = utils_for_tests.create_test_string(utils.sql_load_fr_db(u'select obsid, east, north, AsText(geometry) from obs_points'))
        reference_string = u'(True, [(rb1, 1.0, 1.0, POINT(1 1))])'
        assert test_string == reference_string

    def test_add_trigger_add_obsid_without_anything(self):
        """ Test that adding triggers and updating obsid from east, north don't set null values for previous obsid.
        :return:
        """
        utils.add_triggers_to_obs_points()
        utils.sql_alter_db(u"""INSERT INTO obs_points (obsid) VALUES ('rb1')""")
        utils.sql_alter_db(u"""INSERT INTO obs_points (obsid) VALUES ('rb2')""")

        test_string = utils_for_tests.create_test_string(utils.sql_load_fr_db(u'select obsid, east, north, AsText(geometry) from obs_points'))
        reference_string = u'(True, [(rb1, None, None, None), (rb2, None, None, None)])'
        assert test_string == reference_string

 def do_it(self):
     """Update positions for all observations in self.observations"""
     # First find EPSG-ID for the CRS
     sql = r"""SELECT srid FROM geometry_columns where f_table_name = 'obs_points'"""
     ConnectionOK, result = utils.sql_load_fr_db(sql)
     EPSGID= str(result[0][0])
     #Then do the operation
     sql = r"""Update or ignore 'obs_points' SET Geometry=MakePoint(east, north, """
     sql += EPSGID
     sql += """) WHERE obsid IN """ + self.sqlpart2
     utils.sql_alter_db(sql)

    def test_add_trigger_add_east_north_not_nulling_east_north(self):
        """ Test that adding triggers and adding obsid from east, north don't set null values for previous obsid.
        :return:
        """
        utils.sql_alter_db(u"""INSERT INTO obs_points (obsid, east, north) VALUES ('rb1', 1, 1)""")

        utils.add_triggers_to_obs_points()
        utils.sql_alter_db(u"""INSERT INTO obs_points (obsid, east, north) VALUES ('rb2', 2, 2)""")

        test_string = utils_for_tests.create_test_string(utils.sql_load_fr_db(u'select obsid, east, north, AsText(geometry) from obs_points'))
        reference_string = u'(True, [(rb1, 1.0, 1.0, None), (rb2, 2.0, 2.0, POINT(2 2))])'
        assert test_string == reference_string

 def GetData(self, obsid = '', tablename='', debug = 'n'):            # GetData method that returns a table with water quality data
     # Load all data in obs_points table
     sql = r"""select * from """
     sql += tablename
     sql += r""" where obsid = '"""
     sql += obsid   
     sql += r"""'"""
     if tablename == 'stratigraphy':
         sql += r""" order by stratid"""
     if debug == 'y':
         utils.pop_up_info(sql)
     ConnectionOK, data = utils.sql_load_fr_db(sql)
     return ConnectionOK, data

    def test_add_trigger_add_geometry_not_nulling_geometry(self):
        """ Test that adding triggers and adding obsid don't set null values for previous obsid.
        :return:
        """
        utils.sql_alter_db(u"""INSERT INTO obs_points (obsid, geometry) VALUES ('rb1', GeomFromText('POINT(1.0 1.0)', 3006))""")
        #After the first: u'(True, [(rb1, None, None, POINT(1 1))])

        utils.add_triggers_to_obs_points()
        utils.sql_alter_db(u"""INSERT INTO obs_points (obsid, geometry) VALUES ('rb2', GeomFromText('POINT(2.0 2.0)', 3006))""")
        #After the second: u'(True, [(rb1, 1.0, 1.0, POINT(1 1)), (rb2, 2.0, 2.0, POINT(2 2))])

        test_string = utils_for_tests.create_test_string(utils.sql_load_fr_db(u'select obsid, east, north, AsText(geometry) from obs_points'))
        reference_string = u'(True, [(rb1, None, None, POINT(1 1)), (rb2, 2.0, 2.0, POINT(2 2))])'
        assert test_string == reference_string