Python utils.spatialite_connect函数代码示例

    def testCreateAttributeIndex(self):
        vl = QgsVectorLayer("dbname=%s table='test_defaults' key='id'" % self.dbname, "test_defaults", "spatialite")
        self.assertTrue(vl.dataProvider().capabilities() & QgsVectorDataProvider.CreateAttributeIndex)
        self.assertFalse(vl.dataProvider().createAttributeIndex(-1))
        self.assertFalse(vl.dataProvider().createAttributeIndex(100))
        self.assertTrue(vl.dataProvider().createAttributeIndex(1))

        con = spatialite_connect(self.dbname, isolation_level=None)
        cur = con.cursor()
        rs = cur.execute("SELECT * FROM sqlite_master WHERE type='index' AND tbl_name='test_defaults'")
        res = [row for row in rs]
        self.assertEqual(len(res), 1)
        index_name = res[0][1]
        rs = cur.execute("PRAGMA index_info({})".format(index_name))
        res = [row for row in rs]
        self.assertEqual(len(res), 1)
        self.assertEqual(res[0][2], 'name')

        # second index
        self.assertTrue(vl.dataProvider().createAttributeIndex(2))
        rs = cur.execute("SELECT * FROM sqlite_master WHERE type='index' AND tbl_name='test_defaults'")
        res = [row for row in rs]
        self.assertEqual(len(res), 2)
        indexed_columns = []
        for row in res:
            index_name = row[1]
            rs = cur.execute("PRAGMA index_info({})".format(index_name))
            res = [row for row in rs]
            self.assertEqual(len(res), 1)
            indexed_columns.append(res[0][2])

        self.assertEqual(set(indexed_columns), set(['name', 'number']))
        con.close()

    def init_spatialite(self):
        # Get SpatiaLite version
        c = self.con.cursor()
        try:
            self._exec_sql(c, u'SELECT spatialite_version()')
            rep = c.fetchall()
            v = [int(x) if x.isdigit() else x for x in re.findall("\d+|[a-zA-Z]+", rep[0][0])]

            # Add SpatiaLite support
            if v >= [4, 1, 0]:
                # 4.1 and above
                sql = "SELECT initspatialmetadata(1)"
            else:
                # Under 4.1
                sql = "SELECT initspatialmetadata()"
            self._exec_sql_and_commit(sql)
        except:
            return False
        finally:
            self.con.close()

        try:
            self.con = spatialite_connect(self.con_info())

        except (sqlite.InterfaceError, sqlite.OperationalError) as e:
            raise DbError(str(e))

        return self.check_spatialite()

    def _opendb(self):

        self.gdal_ds = None
        if hasattr(gdal, 'OpenEx'):
            # GDAL >= 2
            self.gdal_ds = gdal.OpenEx(self.dbname, gdal.OF_UPDATE)
            if self.gdal_ds is None:
                self.gdal_ds = gdal.OpenEx(self.dbname)
            if self.gdal_ds is None or self.gdal_ds.GetDriver().ShortName != 'GPKG':
                raise ConnectionError(QApplication.translate("DBManagerPlugin", '"{0}" not found').format(self.dbname))
            self.has_raster = self.gdal_ds.RasterCount != 0 or self.gdal_ds.GetMetadata('SUBDATASETS') is not None
            self.connection = None
            self.gdal2 = True
        else:
            # GDAL 1.X compat. To be removed at some point
            self.gdal_ds = ogr.Open(self.dbname, update=1)
            if self.gdal_ds is None:
                self.gdal_ds = ogr.Open(self.dbname)
            if self.gdal_ds is None or self.gdal_ds.GetDriver().GetName() != 'GPKG':
                raise ConnectionError(QApplication.translate("DBManagerPlugin", '"{0}" not found').format(self.dbname))
            # For GDAL 1.X, we cannot issue direct SQL SELECT to the OGR datasource
            # so we need a direct sqlite connection
            try:
                self.connection = spatialite_connect(str(self.dbname))
            except self.connection_error_types() as e:
                raise ConnectionError(e)
            self.gdal2 = False

    def export_2_splite(self,source_db,target_db, EPSG_code):
        """
        Exports a datagbase to a new spatialite database file
        :param target_db: The name of the new database file
        :param source_db: The name of the source database file
        :param EPSG_code:
        :return:

        """
        conn = spatialite_connect(target_db,detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
        self.curs = conn.cursor()
        self.curs.execute("PRAGMA foreign_keys = ON")
        self.curs.execute(r"""ATTACH DATABASE '%s' AS a"""%source_db)
        conn.commit()#commit sql statements so far

        # first transfer data from data domains (beginning with zz_ in the database)
        dd_tables = self.curs.execute("select name from sqlite_master where name like 'zz_%'")
        d_domain_tables = [str(dd_table[0]) for dd_table in dd_tables]
        for tablename in d_domain_tables:
            self.to_sql(tablename)
        conn.commit()

        #ordered dictionary of layers with (some) data domains
        layers_dict = defs.default_layers()
        for tablename in list(layers_dict.keys()):
            self.to_sql(tablename)
        conn.commit()

        self.curs.execute(r"""DETACH DATABASE a""")
        self.curs.execute('vacuum')

        MessagebarAndLog.info("Export done! Layers from the new database will be loaded to your qgis project")

        conn.commit()
        conn.close()

    def __init__(self, dbpath):
        self.dbpath = dbpath
        # creating/connecting the test_db
        self.conn = spatialite_connect(self.dbpath)
        # creating a Cursor
        self.cur = self.conn.cursor()
        self.cur.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.
        
        #add layer styles
        self.add_layer_styles_2_db()

        #load style from file and set it as value into the layer styles table
        self.style_from_file_into_db('gvmag', 'gvmag.qml','gvmag.sld')
        self.style_from_file_into_db('gvdel', 'gvdel.qml','gvdel.sld')
        self.style_from_file_into_db('gvflode', 'gvflode.qml','gvflode.sld')
        try:
            self.style_from_file_into_db('tillromr', 'tillromr.qml','tillromr.sld')
        except:
            pass
        try:
            self.style_from_file_into_db('sprickzon', 'sprickzon.qml','sprickzon.sld')
        except:
            pass
        try:
            self.style_from_file_into_db('strukturlinje', 'strukturlinje.qml','strukturlinje.sld')
        except:
            pass
        self.cur.execute("PRAGMA foreign_keys = OFF")
        #FINISHED WORKING WITH THE DATABASE, CLOSE CONNECTIONS
        self.rs.close()
        self.conn.close()

    def testTextPks(self):
        """Test regression when retrieving features from tables with text PKs, see #21176"""

        # create test db
        dbname = os.path.join(tempfile.gettempdir(), "test_text_pks.sqlite")
        if os.path.exists(dbname):
            os.remove(dbname)
        con = spatialite_connect(dbname, isolation_level=None)
        cur = con.cursor()
        cur.execute("BEGIN")
        sql = "SELECT InitSpatialMetadata()"
        cur.execute(sql)

        # simple table with primary key
        sql = "CREATE TABLE test_pg (id TEXT NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
        cur.execute(sql)

        sql = "SELECT AddGeometryColumn('test_pg', 'geometry', 4326, 'POLYGON', 'XY')"
        cur.execute(sql)

        sql = "INSERT INTO test_pg (id, name, geometry) "
        sql += "VALUES ('one', 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
        cur.execute(sql)

        sql = "INSERT INTO test_pg (id, name, geometry) "
        sql += "VALUES ('two', 'bogo', GeomFromText('POLYGON((0 0,2 0,2 2,0 2,0 0))', 4326))"
        cur.execute(sql)

        cur.execute("COMMIT")
        con.close()

        def _test_db(testPath):
            vl = QgsVectorLayer(testPath, 'test', 'spatialite')
            self.assertTrue(vl.isValid())

            f = next(vl.getFeatures())
            self.assertTrue(f.isValid())
            fid = f.id()
            self.assertTrue(fid > 0)
            self.assertTrue(vl.getFeature(fid).isValid())
            f2 = next(vl.getFeatures(QgsFeatureRequest().setFilterFid(fid)))
            self.assertTrue(f2.isValid())
            self.assertEqual(f2.id(), f.id())
            self.assertEqual(f2.geometry().asWkt(), f.geometry().asWkt())

            for f in vl.getFeatures():
                self.assertTrue(f.isValid())
                self.assertTrue(vl.getFeature(f.id()).isValid())
                self.assertEqual(vl.getFeature(f.id()).id(), f.id())

        testPath = "dbname=%s table='test_pg' (geometry) key='id'" % dbname
        _test_db(testPath)
        testPath = "dbname=%s table='test_pg' (geometry)" % dbname
        _test_db(testPath)
        testPath = "dbname=%s table='test_pg' key='id'" % dbname
        _test_db(testPath)
        testPath = "dbname=%s table='test_pg'" % dbname
        _test_db(testPath)

    def testPkLessQuery(self):
        """Test if features in queries with/without pk can be retrieved by id"""
        # create test db
        dbname = os.path.join(tempfile.gettempdir(), "test_pkless.sqlite")
        if os.path.exists(dbname):
            os.remove(dbname)
        con = spatialite_connect(dbname, isolation_level=None)
        cur = con.cursor()
        cur.execute("BEGIN")
        sql = "SELECT InitSpatialMetadata()"
        cur.execute(sql)

        # simple table with primary key
        sql = "CREATE TABLE test_pk (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
        cur.execute(sql)

        sql = "SELECT AddGeometryColumn('test_pk', 'geometry', 4326, 'POINT', 'XY')"
        cur.execute(sql)

        for i in range(11, 21):
            sql = "INSERT INTO test_pk (id, name, geometry) "
            sql += "VALUES ({id}, 'name {id}', GeomFromText('POINT({id} {id})', 4326))".format(id=i)
            cur.execute(sql)

        # simple table without primary key
        sql = "CREATE TABLE test_no_pk (name TEXT NOT NULL)"
        cur.execute(sql)

        sql = "SELECT AddGeometryColumn('test_no_pk', 'geometry', 4326, 'POINT', 'XY')"
        cur.execute(sql)

        for i in range(11, 21):
            sql = "INSERT INTO test_no_pk (name, geometry) "
            sql += "VALUES ('name {id}', GeomFromText('POINT({id} {id})', 4326))".format(id=i)
            cur.execute(sql)

        cur.execute("COMMIT")
        con.close()

        def _check_features(vl, offset):
            self.assertEqual(vl.featureCount(), 10)
            i = 11
            for f in vl.getFeatures():
                self.assertTrue(f.isValid())
                self.assertTrue(vl.getFeature(i - offset).isValid())
                self.assertEqual(vl.getFeature(i - offset)['name'], 'name {id}'.format(id=i))
                self.assertEqual(f.id(), i - offset)
                self.assertEqual(f['name'], 'name {id}'.format(id=i))
                self.assertEqual(f.geometry().asWkt(), 'Point ({id} {id})'.format(id=i))
                i += 1

        vl_pk = QgsVectorLayer('dbname=\'%s\' table="(select * from test_pk)" (geometry) sql=' % dbname, 'pk', 'spatialite')
        self.assertTrue(vl_pk.isValid())
        _check_features(vl_pk, 0)

        vl_no_pk = QgsVectorLayer('dbname=\'%s\' table="(select * from test_no_pk)" (geometry) sql=' % dbname, 'pk', 'spatialite')
        self.assertTrue(vl_no_pk.isValid())
        _check_features(vl_no_pk, 10)

    def _execute(self, cursor, sql):

        if self.gdal2 and self.connection is None:
            # Needed when evaluating a SQL query
            try:
                self.connection = spatialite_connect(str(self.dbname))
            except self.connection_error_types() as e:
                raise ConnectionError(e)

        return DBConnector._execute(self, cursor, sql)

    def is_point_table_still_exist(self):
        conn = spatialite_connect(self.db_clone)
        cur = conn.cursor()
        sql = "select * from point"
        point_table_exist = True
        try:
            cur.execute(sql)
        except:
            point_table_exist = False
        conn.close()

        return point_table_exist

 def create_test_db(dbfile):
     if os.path.exists(dbfile):
         os.remove(dbfile)
     con = spatialite_connect(dbfile)
     cur = con.cursor()
     cur.execute("SELECT InitSpatialMetadata(1)")
     cur.execute("CREATE TABLE test (id INTEGER, name TEXT)")
     cur.execute("SELECT AddGeometryColumn('test', 'geometry', 4326, 'POINT', 'XY')")
     sql = "INSERT INTO test (id, name, geometry) "
     sql += "VALUES (1, 'toto',GeomFromText('POINT(0 0)',4326))"
     cur.execute(sql)
     con.close()

    def __init__(self, uri=None):
        self.uri = uri
        self.dbname = uri.database()

        try:
            self.con = spatialite_connect(self.con_info())

        except (sqlite.InterfaceError, sqlite.OperationalError) as e:
            raise DbError(str(e))

        self.has_spatialite = self.check_spatialite()
        if not self.has_spatialite:
            self.has_spatialite = self.init_spatialite()

    def connection(self):
        """Creates and returns a spatialite connection, if
        the existing connection was created in another thread
        invalidates it and create a new one.
        """

        if self._connection is None or self._current_thread != int(QThread.currentThreadId()):
            self._current_thread = int(QThread.currentThreadId())
            try:
                self._connection = spatialite_connect(str(self.dbname))
            except self.connection_error_types() as e:
                raise ConnectionError(e)
        return self._connection

 def connect2db(self):
     if os.path.exists(self.dbpath):
         try:#verify this is an existing sqlite database
             self.conn = spatialite_connect(self.dbpath,detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
             self.conn.cursor().execute("select count(*) from sqlite_master") 
             ConnectionOK = True
         except:
             pop_up_info("Could not connect to %s"%str(self.dbpath))
             ConnectionOK = False
     else:
         pop_up_info("The file %s do not exist!"%str(self.dbpath))
         ConnectionOK = False
     return ConnectionOK

    def testPKNotInt(self):
        """ Check when primary key is not an integer """
        # create test db
        dbname = os.path.join(tempfile.mkdtemp(), "test_pknotint.sqlite")
        con = spatialite_connect(dbname, isolation_level=None)
        cur = con.cursor()

        # try the two different types of index creation
        for index_creation_method in ['CreateSpatialIndex', 'CreateMbrCache']:

            table_name = "pk_is_string_{}".format(index_creation_method)

            cur.execute("BEGIN")
            sql = "SELECT InitSpatialMetadata()"
            cur.execute(sql)

            # create table with spatial index and pk is string
            sql = "CREATE TABLE {}(id VARCHAR PRIMARY KEY NOT NULL, name TEXT NOT NULL);"
            cur.execute(sql.format(table_name))

            sql = "SELECT AddGeometryColumn('{}', 'geometry',  4326, 'POINT', 'XY')"
            cur.execute(sql.format(table_name))

            sql = "SELECT {}('{}', 'geometry')"
            cur.execute(sql.format(index_creation_method, table_name))

            sql = "insert into {} ('id', 'name', 'geometry') values( 'test_id', 'test_name', st_geomfromtext('POINT(1 2)', 4326))"
            cur.execute(sql.format(table_name))

            cur.execute("COMMIT")

            testPath = "dbname={} table='{}' (geometry)".format(dbname, table_name)
            vl = QgsVectorLayer(testPath, 'test', 'spatialite')
            self.assertTrue(vl.isValid())
            self.assertEqual(vl.featureCount(), 1)

            # make spatial request to force the index use
            request = QgsFeatureRequest(QgsRectangle(0, 0, 2, 3))
            feature = next(vl.getFeatures(request), None)
            self.assertTrue(feature)

            self.assertEqual(feature.id(), 1)
            point = feature.geometry().asPoint()
            self.assertTrue(point)
            self.assertEqual(point.x(), 1)
            self.assertEqual(point.y(), 2)

        con.close()

        basepath, filename = os.path.split(dbname)
        shutil.rmtree(basepath)

    def __init__(self, uri):
        DBConnector.__init__(self, uri)

        self.dbname = uri.database()
        if not QFile.exists(self.dbname):
            raise ConnectionError(QApplication.translate("DBManagerPlugin", '"{0}" not found').format(self.dbname))

        try:
            self.connection = spatialite_connect(self._connectionInfo())

        except self.connection_error_types() as e:
            raise ConnectionError(e)

        self._checkSpatial()
        self._checkRaster()