Merge pull request #3650 from nirvn/processing_spatialite_sql

[processing] add a "Spatialite execute SQL" algorithm
qgis · Oct 21, 2016 · bde0f6e · bde0f6e
2 parents 2b9f475 + d83e650
commit bde0f6e
Show file tree

Hide file tree

Showing 4 changed files with 71 additions and 2 deletions.
diff --git a/python/plugins/processing/algs/help/qgis.yaml b/python/plugins/processing/algs/help/qgis.yaml
@@ -333,6 +333,9 @@ qgis:polygonize:  >
 qgis:polygonstolines: >
   This algorithm takes a polygon layer and creates a line layer, with lines representing the rings of the polygons in the input layer.
 
+qgis:spatialiteexecutesql: >
+  This algorithm performs a SQL database query on a Spatialite database.
+
 qgis:postgisexecutesql: >
   This algorithm performs a SQL database query on a PostGIS database connected to QGIS.
 

diff --git a/python/plugins/processing/algs/qgis/QGISAlgorithmProvider.py b/python/plugins/processing/algs/qgis/QGISAlgorithmProvider.py
@@ -131,6 +131,7 @@
 from .RandomPointsPolygonsVariable import RandomPointsPolygonsVariable
 from .RandomPointsAlongLines import RandomPointsAlongLines
 from .PointsToPaths import PointsToPaths
+from .SpatialiteExecuteSQL import SpatialiteExecuteSQL
 from .PostGISExecuteSQL import PostGISExecuteSQL
 from .ImportIntoPostGIS import ImportIntoPostGIS
 from .SetVectorStyle import SetVectorStyle
@@ -213,6 +214,7 @@ def __init__(self):
                         RandomPointsLayer(), RandomPointsPolygonsFixed(),
                         RandomPointsPolygonsVariable(),
                         RandomPointsAlongLines(), PointsToPaths(),
+                        SpatialiteExecuteSQL(),
                         PostGISExecuteSQL(), ImportIntoPostGIS(),
                         SetVectorStyle(), SetRasterStyle(),
                         SelectByExpression(), HypsometricCurves(),

diff --git a/python/plugins/processing/algs/qgis/SpatialiteExecuteSQL.py b/python/plugins/processing/algs/qgis/SpatialiteExecuteSQL.py
@@ -0,0 +1,62 @@
+# -*- coding: utf-8 -*-
+
+"""
+***************************************************************************
+    SpatialiteExecuteSQL.py
+    ---------------------
+    Date                 : October 2016
+    Copyright            : (C) 2016 by Mathieu Pellerin
+    Email                : nirvn dot asia at gmail dot com
+***************************************************************************
+*                                                                         *
+*   This program is free software; you can redistribute it and/or modify  *
+*   it under the terms of the GNU General Public License as published by  *
+*   the Free Software Foundation; either version 2 of the License, or     *
+*   (at your option) any later version.                                   *
+*                                                                         *
+***************************************************************************
+"""
+from builtins import str
+
+__author__ = 'Mathieu Pellerin'
+__date__ = 'October 2016'
+__copyright__ = '(C) 2016, Mathieu Pellerin'
+
+# This will get replaced with a git SHA1 when you do a git archive
+
+__revision__ = '$Format:%H$'
+
+from processing.core.GeoAlgorithm import GeoAlgorithm
+from processing.core.GeoAlgorithmExecutionException import GeoAlgorithmExecutionException
+from processing.core.parameters import ParameterVector
+from processing.core.parameters import ParameterString
+from processing.tools import spatialite
+
+from qgis.core import QgsDataSourceUri, QgsMessageLog
+
+
+class SpatialiteExecuteSQL(GeoAlgorithm):
+
+    DATABASE = 'DATABASE'
+    SQL = 'SQL'
+
+    def defineCharacteristics(self):
+        self.name, self.i18n_name = self.trAlgorithm('Spatialite execute SQL')
+        self.group, self.i18n_group = self.trAlgorithm('Database')
+        self.addParameter(ParameterVector(self.DATABASE, self.tr('File Database'), False, False))
+        self.addParameter(ParameterString(self.SQL, self.tr('SQL query'), '', True))
+
+    def processAlgorithm(self, progress):
+        database = self.getParameterValue(self.DATABASE)
+        uri = QgsDataSourceUri(database)
+        if uri.database() is '':
+            if '|layerid' in database:
+                database = database[:database.find('|layerid')]
+            uri = QgsDataSourceUri('dbname=\'%s\'' % (database))
+        self.db = spatialite.GeoDB(uri)
+        sql = self.getParameterValue(self.SQL).replace('\n', ' ')
+        try:
+            self.db._exec_sql_and_commit(str(sql))
+        except spatialite.DbError as e:
+            raise GeoAlgorithmExecutionException(
+                self.tr('Error executing SQL:\n%s') % str(e))
diff --git a/python/plugins/processing/tools/spatialite.py b/python/plugins/processing/tools/spatialite.py
@@ -29,13 +29,15 @@
 
 from qgis.utils import spatialite_connect
 
+import sqlite3 as sqlite
+
 
 class DbError(Exception):
 
     def __init__(self, message, query=None):
         # Save error. funny that the variables are in utf-8
-        self.message = str(message, 'utf-8')
-        self.query = (str(query, 'utf-8') if query is not None else None)
+        self.message = str(message)
+        self.query = (str(query) if query is not None else None)
 
     def __str__(self):
         return 'MESSAGE: %s\nQUERY: %s' % (self.message, self.query)