class QvLog(Singleton): def __init__(self, family='QVISTA', logname='DESKTOP', dbLog=_DB_QVISTA): if hasattr(self, 'db'): # Solo se inicializa una vez return self.family = family.upper() self.logname = logname.upper() self.usuari = getpass.getuser().upper() self.db = self.connexio(dbLog) self.sessio = str(uuid.uuid1()) self.query = QSqlQuery() def connexio(self, dbLog): db = QSqlDatabase.addDatabase(dbLog['Database']) if db.isValid(): db.setHostName(dbLog['HostName']) db.setPort(dbLog['Port']) db.setDatabaseName(dbLog['DatabaseName']) db.setUserName(dbLog['UserName']) db.setPassword(dbLog['Password']) if db.open(): return db return None def desconnexio(self): if self.db is None: return conName = self.db.connectionName() self.db.close() self.db = None QSqlDatabase.removeDatabase(conName) def registre(self, topic, params=None): if self.db is None or self.query is None: return False self.query.prepare( "CALL QV_LOG_WRITE(:IDUSER, :IDSESSION, :FAMILY, :LOGNAME, :TOPIC, :PARAMS)" ) self.query.bindValue(':IDUSER', self.usuari) self.query.bindValue(':IDSESSION', self.sessio) self.query.bindValue(':FAMILY', self.family) self.query.bindValue(':LOGNAME', self.logname) self.query.bindValue(':TOPIC', topic) self.query.bindValue(':PARAMS', params) ok = self.query.exec_() return ok def inici(self): ok = self.registre('LOG_INICI') return ok def fi(self): ok = self.registre('LOG_FI') self.desconnexio() return ok def error(self): if self.db is None or self.query is None: return None else: return self.query.lastError().text()
def execSQLCommand(self, sql, ignore_errors=False): self.assertTrue(self.conn) query = QSqlQuery(self.conn) res = query.exec_(sql) if not ignore_errors: self.assertTrue(res, sql + ': ' + query.lastError().text()) query.finish()
def getStructureDict(self): ''' Gets database structure according to the edgv version ''' self.checkAndOpenDb() classDict = dict() sql = self.gen.getStructure(self.getDatabaseVersion()) query = QSqlQuery(sql, self.db) if not query.isActive(): self.db.close() raise Exception( self.tr("Problem getting database structure: ") + query.lastError().text()) while query.next(): className = str(query.value(0)) classSql = str(query.value(1)) if className.split('_')[0] == 'complexos' or className.split( '_')[-1] in ['p', 'l', 'a']: if className not in list(classDict.keys()): classDict[className] = dict() classSql = classSql.split(className)[1] sqlList = classSql.replace('(', '').replace(')', '').replace( '\"', '').replace('\'', '').split(',') for s in sqlList: fieldName = str(s.strip().split(' ')[0]) classDict[className][fieldName] = fieldName if 'GEOMETRY' in list(classDict[className].keys()): classDict[className]['GEOMETRY'] = 'geom' if 'geometry' in list(classDict[className].keys()): classDict[className]['geometry'] = 'geom' if 'OGC_FID' in list(classDict[className].keys()): classDict[className]['OGC_FID'] = 'id' return classDict
def check_geom(self, layer, pk, wkt, wkt_ref=None, check_valid=True): """ Add geom to the layer and check everything is OK """ table = layer.dataProvider().uri().table() # insert geom in layer self.assertTrue(layer.startEditing()) feature = QgsFeature(layer.fields()) geom = QgsGeometry.fromWkt(wkt) feature.setAttributes([pk]) feature.setGeometry(geom) self.assertTrue(layer.addFeature(feature)) self.assertTrue(layer.commitChanges()) if check_valid: self.assertTrue(self.conn) query = QSqlQuery(self.conn) sql = """select p.GEOM.st_isvalid() from QGIS.{} p where "pk" = {}""".format( table, pk) res = query.exec_(sql) self.assertTrue(res, sql + ': ' + query.lastError().text()) query.next() valid = query.value(0) self.assertTrue( valid, "geometry '{}' inserted in database is not valid".format(wkt)) query.finish() expected_wkt = wkt if wkt_ref is None else wkt_ref res_wkt = layer.getFeature(pk).geometry().asWkt() self.assertTrue( compareWkt(res_wkt, expected_wkt, 0.00001), "\nactual = {}\nexpected = {}".format(res_wkt, expected_wkt))
def getDbsFromServer(self, name): """ Gets server databases name: server name """ gen = self.factory.createSqlGenerator(driver=DsgEnums.DriverPostGIS) (host, port, user, password) = self.getServerConfiguration(name) database = 'postgres' postgisDb = self.dbFactory.createDbFactory(DsgEnums.DriverPostGIS) postgisDb.connectDatabaseWithParameters(host, port, database, user, password) if not postgisDb.db.open(): QgsMessageLog.logMessage(db.lastError().text(), "DSGTools Plugin", Qgis.Critical) QMessageBox.critical( self.iface.mainWindow(), self.tr('Critical'), self.tr('A problem occurred! Check log for details.')) query = QSqlQuery(gen.getDatabasesFromServer(), postgisDb.db) if not query.isActive(): QMessageBox.critical( self.iface.mainWindow(), self.tr('Critical'), self.tr("Problem executing query: ") + query.lastError().text()) dbList = [] while query.next(): dbList.append(query.value(0)) postgisDb.closeDatabase() return self.browseServer(dbList, host, port, user, password)
def delete_data_in_database(self): """Deletes the project (= database schema) in the database. Returns: False: If something went wrong with deleting in the database. Otherwise True. """ try: db = open_psql_db(self.db_host, self.db_name, self.db_port, self.db_admin, self.db_admin_pwd) sql = "BEGIN;" sql += "DROP SCHEMA IF EXISTS %s CASCADE;" % self.db_schema sql += "DELETE FROM geometry_columns WHERE f_table_schema='%s' ;" % self.db_schema sql += "COMMIT;" query = db.exec_(sql) if not query.isActive(): message = "Error occured while deleting project in database." raise VerisoError( message, long_message=QSqlQuery.lastError(query).text()) db.close() del db return True except Exception as e: message = "Something went wrong while deleting the project." raise VerisoError(message, e)
def getGeomColumnTupleList(self, showViews=False): """ list in the format [(table_schema, table_name, geometryColumn, geometryType, tableType)] centroids are hidden by default """ self.checkAndOpenDb() edgvVersion = self.getDatabaseVersion() sql = self.gen.getGeomColumnTupleList(edgvVersion) query = QSqlQuery(sql, self.db) if not query.isActive(): raise Exception( self.tr("Problem getting geom tuple list: ") + query.lastError().text()) geomList = [] while query.next(): if edgvVersion in ['2.1.3', 'FTer_2a_Ed']: geomList.append((query.value(0).split('_')[0], '_'.join(query.value(0).split('_')[1::]), query.value(1), query.value(2), 'BASE TABLE')) else: geomList.append( (query.value(0).split('_')[0], '_'.join(query.value(0).split('_')[1::]), query.value(1), self.getResolvedGeomType(int(query.value(2))), 'BASE TABLE')) return geomList
def deleteUser(self): sql = "DELETE FROM postnas_search_access_control WHERE lower(username) = :username" self.__openDB() queryDeleteUser = QSqlQuery(self.db) queryDeleteUser.prepare(sql) queryDeleteUser.bindValue(":username", self.getUsername()) queryDeleteUser.exec_() if (queryDeleteUser.lastError().number() == -1): return True else: QgsMessageLog.logMessage( "Datenbankfehler beim Löschen: " + queryDeleteUser.lastError().text(), 'PostNAS-Suche', Qgis.Critical) return False
def update_project_database_sqlite(self): """Deletes the deleted project from the sqlite project database. Returns: False: If something went wrong. Otherwise True. """ try: db = get_projects_db() sql = "DELETE FROM projects WHERE dbschema = '%s';" % ( self.db_schema) query = db.exec_(sql) if not query.isActive(): message = "Error while reading from projects database." raise VerisoError( message, long_message=QSqlQuery.lastError(query).text()) db.close() del db return True except Exception as e: message = "Something went wrong while updating projects database." raise VerisoError(message, e)
def delete_data_in_database(self): """Deletes the project (= database schema) in the database. Returns: False: If something went wrong with deleting in the database. Otherwise True. """ try: if self.settings.value("options/general/use_pg_projects_database", False, type=bool): db = get_default_db() else: db = open_psql_db(self.db_host, self.db_name, self.db_port, self.db_admin, self.db_admin_pwd) sql = "BEGIN;" sql += "DROP SCHEMA IF EXISTS %s CASCADE;" % self.db_schema sql += "COMMIT;" query = db.exec_(sql) if not query.isActive(): message = "Error occured while deleting project in database." raise VerisoError( message, long_message=QSqlQuery.lastError(query).text()) db.close() del db return True except Exception as e: message = "Something went wrong while deleting the project." raise VerisoError(message, e)
def update_projects_database_sqlite(self): """Updates the sqlite projects database. Returns: False: When there an error occured. Otherswise True. """ error_message = ("Something went wrong while updating projects " "database. You need to delete the database schema " "manually.") try: # Create a new projects database if there is none (copy one from # the templates). if self.projects_database == "": template = get_absolute_path("templates/template_projects.db") self.projects_database = QDir.toNativeSeparators( QDir.cleanPath(self.projects_root_directory + "/projects.db")) shutil.copyfile(template, self.projects_database) self.settings.setValue("options/general/projects_database", self.projects_database) db = get_projects_db() project_root_directory = QDir.toNativeSeparators( QDir.cleanPath(self.projects_root_directory + "/" + str(self.db_schema))) values = (self.db_schema, self.db_schema, self.db_host, self.db_name, self.db_port, self.db_schema, self.db_user, self.db_pwd, self.db_admin, self.db_admin_pwd, self.epsg, self.ili, self.app_module, self.app_module_name, self.projects_root_directory, project_root_directory, self.data_date, self.notes, self.itf, self.max_scale) values = """VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', 'postgres', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')""" % values sql = "INSERT INTO projects (id, displayname, dbhost, dbname, " \ "dbport, dbschema, dbuser, dbpwd, dbadmin, dbadminpwd, " \ "provider, epsg, ilimodelname, appmodule, appmodulename, " \ "projectrootdir, projectdir, datadate, notes, itf, " \ "max_scale)" + values query = db.exec_(sql) if not query.isActive(): message = "Error while updating projects database." raise VerisoError( message, long_message=QSqlQuery.lastError(query).text()) db.close() self.projectsDatabaseHasChanged.emit() return True except Exception as e: raise VerisoError(error_message, e)
def execute_query(self, sql): query = QSqlQuery(self.db) res = query.exec_(sql) if res is False: QApplication.restoreOverrideCursor() raise VerisoErrorWithBar(self.iface.messageBar(), "Error " + (query.lastError().text()))
def getTableSchemaFromDb(self, table): self.checkAndOpenDb() sql = self.gen.getFullTablesName(table) query = QSqlQuery(sql, self.db) if not query.isActive(): raise Exception( self.tr("Problem getting full table name: ") + query.lastError().text()) while query.next(): return query.value(0).split('_')[0]
def updateUser(self, username_old): if (self.getUsername() != None): self.__openDB() sql = "UPDATE postnas_search_access_control SET username = :username, name = :name, access = :access WHERE username = :username_old" query = QSqlQuery(self.db) query.prepare(sql) query.bindValue(":username", self.getUsername().lower()) query.bindValue(":username_old", username_old) query.bindValue(":name", self.name) query.bindValue(":access", self.access) query.exec_() if (query.lastError().number() == -1): return True else: QgsMessageLog.logMessage( "Datenbankfehler beim Update: " + query.lastError().text(), 'PostNAS-Suche', Qgis.Critical) return False else: return False
def executeQuery(self, db, sql, feat=None): try: fparams = self.preparesql(feat) if self.debug: self.info.log("sql:", sql) self.info.log("fparams:", fparams) sqlexe = sql % fparams if self.debug: self.info.log("SQL execute:", sqlexe) # if db is not specified, or is invalid, the application's default database is used. # If query is not an empty string, it will be executed query = QSqlQuery(db) # query = db.exec_(sql)#obsolete query.prepare(sqlexe) self.result = query.exec_() if not self.result: self.info.log(query.lastError().databaseText()) self.info.gtoWarning("Database Error:" + query.lastError().databaseText()) except Exception as e: self.info.err(e)
def __createLoggingTable(self): file_path = os.path.dirname(os.path.realpath(__file__)) + "/create_loggingtable/create_logging_table.sql" sql = open(file_path).read() self.__openDB() query = QSqlQuery(self.db) query.exec_(sql) if(query.lastError().number() == -1): return True else: return False
def findEPSG(self, parameters=dict()): """ Finds the database EPSG """ self.checkAndOpenDb() sql = self.gen.getSrid(parameters=parameters) query = QSqlQuery(sql, self.db) if not query.isActive(): raise Exception( self.tr("Problem finding EPSG: ") + query.lastError().text()) srid = -1 while query.next(): srid = query.value(0) break return srid
def checkUserExists(self): sql = "SELECT lower(username) as username FROM postnas_search_access_control WHERE lower(username) = :username" self.__openDB() queryCheckUserExists = QSqlQuery(self.db) queryCheckUserExists.prepare(sql) queryCheckUserExists.bindValue(":username", self.getUsername()) queryCheckUserExists.exec_() if (queryCheckUserExists.lastError().number() == -1): if (queryCheckUserExists.size() > 0): return True else: return False else: return False
def disassociateComplexFromComplex(self, aggregated_class, link_column, id): ''' Disassociates a complex from another complex aggregated_class: aggregated class that will be disassociated link_column: link column between complex and its aggregated class id: complex id (uid) to be disassociated ''' sql = self.gen.disassociateComplexFromComplex( 'complexos_' + aggregated_class, link_column, id) query = QSqlQuery(self.db) if not query.exec_(sql): self.db.close() raise Exception( self.tr('Problem disassociating complex from complex: ') + '\n' + query.lastError().text())
def insertUser(self): if (self.getUsername() != None): self.__openDB() sql = "INSERT INTO postnas_search_access_control (username,name,access) VALUES (:username,:name,:access)" query = QSqlQuery(self.db) query.prepare(sql) query.bindValue(":username", self.getUsername().lower()) query.bindValue(":name", self.name) query.bindValue(":access", self.access) query.exec_() if (query.lastError().number() == -1): return True else: return False else: return False
def getAggregationAttributes(self): """ Gets complex link columns """ self.checkAndOpenDb() columns = [] sql = self.gen.getAggregationColumn() query = QSqlQuery(sql, self.db) if not query.isActive(): raise Exception( self.tr("Problem getting aggregation attributes: ") + query.lastError().text()) while query.next(): value = query.value(0) columns.append(value) return columns
def __insertLogEntry(self,requestType,search,result): self.__openDB() sql = "INSERT INTO postnas_search_logging (datum,username,requestType,search,result) VALUES (:datum,:username,:requestType,:search,:result)" query = QSqlQuery(self.db) query.prepare(sql) query.bindValue(":datum",datetime.datetime.now().isoformat()) query.bindValue(":username",self.username) query.bindValue(":requestType",requestType) query.bindValue(":search",search) query.bindValue(":result",str(result).replace("u'","'").replace("\'","\"").replace("[","{").replace("]","}")) query.exec_() if(query.lastError().number() == -1): return True else: return False
def checkUserHasEigentuemerAccess(self): if (self.getUsername() != None): self.__openDB() sql = "SELECT lower(username) as username FROM postnas_search_access_control WHERE access IN (0,1) AND lower(username) = :username" queryEigentuemerAccess = QSqlQuery(self.db) queryEigentuemerAccess.prepare(sql) queryEigentuemerAccess.bindValue(":username", self.getUsername()) queryEigentuemerAccess.exec_() if (queryEigentuemerAccess.lastError().number() == -1): if (queryEigentuemerAccess.size() > 0): return True else: return False else: return False else: return False
def listClassesFromDatabase(self): """ Gets a list with all classes from database. :return: (str) list of all classes in the database. """ self.checkAndOpenDb() classList = [] sql = self.gen.getTablesFromDatabase() query = QSqlQuery(sql, self.db) if not query.isActive(): self.db.close() raise Exception( self.tr("Problem listing geom classes: ") + query.lastError().text()) while query.next(): classList.append(str(query.value(0))) return classList
def getQmlRecordDict(self, inputLayer): self.checkAndOpenDb() if isinstance(inputLayer, list): sql = self.gen.getQmlRecords(inputLayer) else: sql = self.gen.getQmlRecords([inputLayer]) query = QSqlQuery(sql, self.db) if not query.isActive(): raise Exception( self.tr("Problem getting qmlRecordDict: ") + query.lastError().text()) qmlDict = dict() while query.next(): if isinstance(inputLayer, list): qmlDict[query.value(0)] = query.value(1) else: return query.value(1) return qmlDict
def isComplexClass(self, className): ''' Checks if a class is a complex class className: class name to be checked ''' self.checkAndOpenDb() #getting all complex tables query = QSqlQuery(self.gen.getComplexTablesFromDatabase(), self.db) if not query.isActive(): self.db.close() raise Exception( self.tr("Problem executing query: ") + query.lastError().text()) while query.next(): if query.value(0) == 'complexos_' + className: return True return False
def getTablesFromDatabase(self): ''' Gets all tables from database ''' self.checkAndOpenDb() ret = [] sql = self.gen.getTablesFromDatabase() query = QSqlQuery(sql, self.db) if not query.isActive(): self.db.close() raise Exception( self.tr("Problem getting tables from database: ") + query.lastError().text()) while query.next(): #table name ret.append(query.value(0)) return ret
def listGeomClassesFromDatabase(self, primitiveFilter=[]): ''' Gets a list with geometry classes from database ''' self.checkAndOpenDb() classList = [] sql = self.gen.getTablesFromDatabase() query = QSqlQuery(sql, self.db) if not query.isActive(): self.db.close() raise Exception( self.tr("Problem listing geom classes: ") + query.lastError().text()) while query.next(): tableName = str(query.value(0)) layerName = tableName if tableName[-2:].lower() in ["_p", "_l", "_a"]: classList.append(layerName) return classList
def getGeomColumnDict(self): ''' Dict in the form 'geomName':[-list of table names-] ''' self.checkAndOpenDb() sql = self.gen.getGeomColumnDict() query = QSqlQuery(sql, self.db) if not query.isActive(): raise Exception( self.tr("Problem getting geom column dict: ") + query.lastError().text()) geomDict = dict() while query.next(): geomColumn = query.value(0) tableName = query.value(1) lyrName = '_'.join(tableName.split('_')[1::]) if geomColumn not in list(geomDict.keys()): geomDict[geomColumn] = [] geomDict[geomColumn].append(lyrName) return geomDict
def listComplexClassesFromDatabase(self): ''' Gets a list with complex classes from database ''' self.checkAndOpenDb() classList = [] sql = self.gen.getTablesFromDatabase() query = QSqlQuery(sql, self.db) if not query.isActive(): self.db.close() raise Exception( self.tr("Problem listing complex classes: ") + query.lastError().text()) while query.next(): tableName = str(query.value(0)) layerName = tableName tableSchema = layerName.split('_')[0] if tableSchema == 'complexos': classList.append(layerName) return classList
def fetchall(self, db, sql): rows = [] qry = QSqlQuery(db) if qry.exec_(sql): rec = qry.record() while qry.next(): row = {} for i in range(0, rec.count()): v = "%s" % qry.value(i) if v == "NULL": v = '' row[rec.fieldName(i)] = v.strip() rows.append(row) else: qDebug("Exec failed: " + qry.lastError().text()) return rows
class QtSqlDBCursor(object): def __init__(self, conn): self.qry = QSqlQuery(conn) self.description = None self.rowcount = -1 self.arraysize = 1 def close(self): self.qry.finish() def execute(self, operation, parameters=[]): if len(parameters) == 0: if not self.qry.exec_(operation): raise ExecError(self.qry.lastError().databaseText()) else: if not self.qry.prepare(operation): raise ExecError(self.qry.lastError().databaseText()) for i in range(len(parameters)): self.qry.bindValue(i, parameters[i]) if not self.qry.exec_(): raise ExecError(self.qry.lastError().databaseText()) self.rowcount = self.qry.size() self.description = [] for c in range(self.qry.record().count()): f = self.qry.record().field(c) if f.type() == QVariant.Date: t = Date elif f.type() == QVariant.Time: t = Time elif f.type() == QVariant.DateTime: t = Timestamp elif f.type() == QVariant.Double: t = float elif f.type() == QVariant.Int: t = int elif f.type() == QVariant.String: t = str elif f.type() == QVariant.ByteArray: t = str else: continue self.description.append([ f.name(), # name t, # type_code f.length(), # display_size f.length(), # internal_size f.precision(), # precision None, # scale f.requiredStatus() != QSqlField.Required # null_ok ]) def executemany(self, operation, seq_of_parameters): if len(seq_of_parameters) == 0: return if not self.qry.prepare(operation): raise ExecError(self.qry.lastError().databaseText()) for r in seq_of_parameters: for i in range(len(r)): self.qry.bindValue(i, r[i]) if not self.qry.exec_(): raise ExecError(self.qry.lastError().databaseText()) def scroll(self, row): return self.qry.seek(row) def fetchone(self): if not next(self.qry): return None row = [] for i in range(len(self.description)): value = self.qry.value(i) if (isinstance(value, QDate) or isinstance(value, QTime) or isinstance(value, QDateTime)): value = value.toString() elif isinstance(value, QByteArray): value = u"GEOMETRY" # value = value.toHex() row.append(value) return row def fetchmany(self, size=10): rows = [] while len(rows) < size: row = self.fetchone() if row is None: break rows.append(row) return rows def fetchall(self): rows = [] while True: row = self.fetchone() if row is None: break rows.append(row) return rows def setinputsize(self, sizes): raise ExecError("nyi") def setoutputsize(self, size, column=None): raise ExecError("nyi")
def execSQLCommand(self, sql, ignore_errors=False): self.assertTrue(self.conn) query = QSqlQuery(self.conn) self.assertTrue(query.exec_(sql), sql + ': ' + query.lastError().text()) query.finish()