Python midvatten_utils.sql_alter_db函数代码示例

 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 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 delete_selected_range(self, table_name):
        """ Deletes the current selected range from the database from w_levels_logger
        :return: De
        """
        current_loaded_obsid = self.obsid
        selected_obsid = self.load_obsid_and_init()
        if current_loaded_obsid != selected_obsid:
            utils.pop_up_info("Error!\n The obsid selection has been changed but the plot has not been updated. No deletion done.\nUpdating plot.")
            self.update_plot()
            return

        fr_d_t = str((self.FromDateTime.dateTime().toPyDateTime() - datetime.datetime(1970,1,1)).total_seconds())
        to_d_t = str((self.ToDateTime.dateTime().toPyDateTime() - datetime.datetime(1970,1,1)).total_seconds())

        sql_list = []
        sql_list.append(r"""delete from "%s" """%table_name)
        sql_list.append(r"""where obsid = '%s' """%selected_obsid)
        sql_list.append(r"""AND CAST(strftime('%s', date_time) AS NUMERIC) """)
        sql_list.append(r""" >= '%s' """%fr_d_t)
        sql_list.append(r"""AND CAST(strftime('%s', date_time) AS NUMERIC) """)
        sql_list.append(r""" <= '%s' """%to_d_t)
        sql = ''.join(sql_list)

        really_delete = utils.askuser("YesNo", "Do you want to delete the period " +
                                      str(self.FromDateTime.dateTime().toPyDateTime()) + " to " +
                                      str(self.ToDateTime.dateTime().toPyDateTime()) +
                                      " for obsid " + selected_obsid + " from table " + table_name + "?").result
        if really_delete:
            utils.sql_alter_db(sql)
            self.update_plot()

 def do_it(self):
     """Update coordinates for all observations in self.observations"""
     
     sql = r"""UPDATE OR IGNORE obs_points SET east=X(Geometry) WHERE obsid IN """ + self.sqlpart2
     utils.sql_alter_db(sql)
     sql = r"""UPDATE OR IGNORE obs_points SET north=Y(Geometry) WHERE obsid IN """ + self.sqlpart2
     utils.sql_alter_db(sql)

    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 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 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 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

 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 setUp(self):
     self.iface = DummyInterface()
     self.midvatten = midvatten.midvatten(self.iface)
     try:
         os.remove(TestObsPointsTriggers.temp_db_path)
     except OSError:
         pass
     self.midvatten.new_db()
     self.importinstance = midv_data_importer()
     utils.sql_alter_db(u"""DROP TRIGGER IF EXISTS after_insert_obs_points_geom_fr_coords""")
     utils.sql_alter_db(u"""DROP TRIGGER IF EXISTS after_update_obs_points_geom_fr_coords""")
     utils.sql_alter_db(u"""DROP TRIGGER IF EXISTS after_insert_obs_points_coords_fr_geom""")
     utils.sql_alter_db(u"""DROP TRIGGER IF EXISTS after_update_obs_points_coords_fr_geom""")

 def update_level_masl_from_head(self, obsid, fr_d_t, to_d_t, newzref):
     """ Updates the level masl using newzref
     :param obsid: (str) The obsid
     :param fr_d_t: (datetime) start of calibration
     :param to_d_t: (datetime) end of calibration
     :param newzref: (int/float/str [m]) The correction that should be made against the head [m]
     :return: None
     """
     sql =r"""UPDATE w_levels_logger SET level_masl = """
     sql += str(newzref)
     sql += """ + head_cm / 100 WHERE obsid = '"""
     sql += obsid
     # Sqlite seems to have problems with date comparison date_time >= a_date, so they have to be converted into total seconds first.
     sql += """' AND CAST(strftime('%s', date_time) AS NUMERIC) >= """
     sql += str((fr_d_t - datetime.datetime(1970,1,1)).total_seconds())
     sql += """ AND CAST(strftime('%s', date_time) AS NUMERIC) <= """
     sql += str((to_d_t - datetime.datetime(1970,1,1)).total_seconds())
     sql += """ """
     dummy = utils.sql_alter_db(sql)

    def test_update_trigger_add_east_north_not_nulling_east_north(self):
        """ Test that adding triggers and updating 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, geometry) VALUES ('rb1', 1, 1, GeomFromText('POINT(1.0 1.0)', 3006))""")
        utils.sql_alter_db(u"""INSERT INTO obs_points (obsid, east, north, geometry) VALUES ('rb2', 2, 2, GeomFromText('POINT(2.0 2.0)', 3006))""")

        utils.add_triggers_to_obs_points()

        utils.sql_alter_db(u'''UPDATE obs_points SET east = 3, north = 3 WHERE (obsid = 'rb1')''')

        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, 3.0, 3.0, POINT(3 3)), (rb2, 2.0, 2.0, POINT(2 2))])'
        assert test_string == reference_string

    def test_export_spatialite(self, mock_iface, mock_find_layer, mock_newdbpath, mock_verify):

        mock_find_layer.return_value.crs.return_value.authid.return_value = u'EPSG:3006'

        mock_newdbpath.return_value = EXPORT_DB_PATH
        mock_verify.return_value = 0

        utils.sql_alter_db(u'''insert into obs_points (obsid, geometry) values ("P1", GeomFromText('POINT(633466, 711659)', 3006))''')
        utils.sql_alter_db(u'''insert into zz_staff (staff) values ('s1')''')
        utils.sql_alter_db(u'''insert into comments (obsid, date_time, staff, comment) values ('P1', '2015-01-01 00:00:00', 's1', 'comment1')''')
        utils.sql_alter_db(u'''insert into w_qual_lab (obsid, parameter, report, staff) values ('P1', 'labpar1', 'report1', 's1')''')
        utils.sql_alter_db(u'''insert into zz_w_qual_field_parameters (parameter, unit, shortname) values ('par1', 'unit1', 'parshortname1')''')
        utils.sql_alter_db(u'''insert into w_qual_field (obsid, parameter, staff, date_time, unit) values ('P1', 'par1', 's1', '2015-01-01 01:00:00', 'unit1')''')
        utils.sql_alter_db(u'''insert into w_flow (obsid, instrumentid, flowtype, date_time, unit) values ('P1', 'inst1', 'Momflow', '2015-04-13 00:00:00', 'l/s')''')
        utils.sql_alter_db(u'''insert into w_levels (obsid, date_time, meas) values ('P1', '2015-01-02 00:00:01', '2')''')
        utils.sql_alter_db(u'''insert into stratigraphy (obsid, stratid) values ('P1', 'strat1')''')
        utils.sql_alter_db(u'''insert into obs_lines (obsid) values ('L1')''')
        utils.sql_alter_db(u'''insert into seismic_data (obsid, length) values ('L1', '5')''')
        utils.sql_alter_db(u'''insert into meteo (obsid, instrumentid, parameter, date_time) values ('P1', 'meteoinst', 'precip', '2017-01-01 00:19:00')''')

        self.midvatten.export_spatialite()

        sql_list = [u'''select obsid from obs_points''',
                    u'''select staff from zz_staff''',
                    u'''select obsid, date_time, staff, comment from comments''',
                    u'''select obsid, parameter, report, staff from w_qual_lab''',
                    u'''select parameter, unit, shortname from zz_w_qual_field_parameters''',
                    u'''select obsid, parameter, staff, date_time, comment from w_qual_field''',
                    u'''select obsid, instrumentid, flowtype, date_time, unit from w_flow''',
                    u'''select obsid, date_time, meas from w_levels''',
                    u'''select obsid, stratid from stratigraphy''',
                    u'''select obsid from obs_lines''',
                    u'''select obsid, length from seismic_data''',
                    u'''select obsid, instrumentid, parameter, date_time from meteo''']


        conn = sqlite.connect(EXPORT_DB_PATH, detect_types=sqlite.PARSE_DECLTYPES|sqlite.PARSE_COLNAMES)
        curs = conn.cursor()

        test_list = []
        for sql in sql_list:
            test_list.append('\n' + sql + '\n')
            test_list.append(curs.execute(sql).fetchall())

        conn.commit()
        conn.close()

        test_string = utils_for_tests.create_test_string(test_list)
        reference_string = [u'''[''',
                            u'''select obsid from obs_points''',
                            u''', [(P1)], ''',
                            u'''select staff from zz_staff''',
                            u''', [(s1)], ''',
                            u'''select obsid, date_time, staff, comment from comments''',
                            u''', [(P1, 2015-01-01 00:00:00, s1, comment1)], ''',
                            u'''select obsid, parameter, report, staff from w_qual_lab''',
                            u''', [(P1, labpar1, report1, s1)], ''',
                            u'''select parameter, unit, shortname from zz_w_qual_field_parameters''',
                            u''', [(DO, %, DO), (DO, mg/L, DO), (conductivity, µS/cm, cond), (pH, None, pH), (reduction potential, mV, redox), (temperature, grC, temp), (turbidity, FNU, turb), (par1, unit1, None)], ''',
                            u'''select obsid, parameter, staff, date_time, comment from w_qual_field''',
                            u''', [(P1, par1, s1, 2015-01-01 01:00:00, None)], ''',
                            u'''select obsid, instrumentid, flowtype, date_time, unit from w_flow''',
                            u''', [(P1, inst1, Momflow, 2015-04-13 00:00:00, l/s)], ''',
                            u'''select obsid, date_time, meas from w_levels''',
                            u''', [(P1, 2015-01-02 00:00:01, 2.0)], ''',
                            u'''select obsid, stratid from stratigraphy''',
                            u''', [(P1, strat1)], ''',
                            u'''select obsid from obs_lines''',
                            u''', [(L1)], ''',
                            u'''select obsid, length from seismic_data''',
                            u''', [(L1, 5.0)], ''',
                            u'''select obsid, instrumentid, parameter, date_time from meteo''',
                            u''', [(P1, meteoinst, precip, 2017-01-01 00:19:00)]]''']
        reference_string = u'\n'.join(reference_string)
        assert test_string == reference_string