def get_bcgw_column_key(db_table,user_name,user_pass): # estimate a unique id column for an oracle table if OBJECTID does not exist owner,table = db_table.split('.') driver ="QOCISPATIAL" conn_name = "bcgw_conn" if not QSqlDatabase.contains(conn_name): db = QSqlDatabase.addDatabase(driver,conn_name) else: db = QSqlDatabase.database(conn_name) db.setDatabaseName('bcgw.bcgov' + "/" + 'idwprod1.bcgov') db.setUserName(user_name) db.setPassword(user_pass) db.open() if not db.open(): print ("Failed Connection from find_bcgw_the_geom") q = QSqlQuery(db) sql = f"SELECT cols.column_name \ FROM all_tab_cols cols where cols.table_name = '{table}' and cols.COLUMN_NAME like \'OBJECTID\'" q.exec(sql) if q.first(): key_c = q.value(0) else: sql = f"SELECT COLUMN_NAME FROM all_tab_cols where table_name = '{table}' \ order by COLUMN_ID FETCH FIRST 1 ROWS ONLY" q.exec(sql) if q.first(): key_c = q.value(0) db.close() return key_c
def getFavorits(self, usuari=getpass.getuser().upper()): '''Comentari explicant''' if not self.__CONNECTA_BASE_DADES__(usuari): return [] query = QSqlQuery(self.db) query.prepare( "select nom_mapa from QV_MAPES_FAVORITS where iduser=:IDUSER") query.bindValue(':IDUSER', usuari) query.exec() res = [] while query.next(): res.append(query.value(0)) #Consulta self.__DESCONNECTA_BASE_DADES__(usuari, False) return res
def get_bcgw_geomcolumn(db_table,user_name,user_pass): # get the name of the geometry column for oracle table owner,table = db_table.split('.') driver ="QOCISPATIAL" conn_name = "bcgw_conn" if not QSqlDatabase.contains(conn_name): db = QSqlDatabase.addDatabase(driver,conn_name) else: db = QSqlDatabase.database(conn_name) db.setDatabaseName('bcgw.bcgov' + "/" + 'idwprod1.bcgov') db.setUserName(user_name) db.setPassword(user_pass) db.open() if not db.open(): print ("Failed Connection from find_bcgw_the_geom") q = QSqlQuery(db) query ="SELECT COLUMN_NAME from all_tab_columns where OWNER = '{}' AND TABLE_NAME = '{}' AND DATA_TYPE = 'SDO_GEOMETRY'".format(owner,table) q.exec(query) q.first() geom_c = q.value(0) db.close() return geom_c
def get_bcgw_table_geomtype(db_table,geom_column_name,user_name,user_pass): # get geometry type from oracle table - oracle stores multiple types so # this returns the maximum type ie multiline, multipolygon, multipoint if # present in geometry owner,table = db_table.split('.') driver ="QOCISPATIAL" conn_name = "bcgw_conn" if not QSqlDatabase.contains(conn_name): db = QSqlDatabase.addDatabase(driver,conn_name) else: db = QSqlDatabase.database(conn_name) db.setDatabaseName('bcgw.bcgov' + "/" + 'idwprod1.bcgov') db.setUserName(user_name) db.setPassword(user_pass) db.open() if not db.open(): print ("Failed Connection from find_bcgw_the_geom") q = QSqlQuery(db) query = f"SELECT MAX(t.{geom_column_name}.GET_GTYPE()) AS geometry_type from {owner}.{table} t" q.exec(query) q.first() type_num = q.value(0) if type_num == 1: geom_t = 'Point' elif type_num == 2: geom_t = 'LineString' elif type_num == 3: geom_t = 'Polygon' elif type_num == 7: geom_t = 'MultiPolygon' elif type_num ==5: geom_t = 'MulitPoint' elif type_num ==6: geom_t = 'MultiLineString' else: db.close() raise TypeError db.close() return geom_t
def eliminaFavorit(self, mapa, usuari=getpass.getuser().upper()): if not self.__CONNECTA_BASE_DADES__(usuari): QMessageBox.critical( "Atenció", "No s'ha pogut eliminar el mapa de favorits. Intenteu-ho més tard, si us plau" ) return False query = QSqlQuery(self.db) query.prepare( "delete from QV_MAPES_FAVORITS where iduser=:IDUSER and nom_mapa=:NOM_MAPA" ) query.bindValue(':IDUSER', usuari) query.bindValue(':NOM_MAPA', mapa) if not query.exec(): QMessageBox.critical( "Atenció", "No s'ha pogut eliminar el mapa de favorits. Intenteu-ho més tard, si us plau" ) self.__DESCONNECTA_BASE_DADES__(usuari) return True
def afegeixFavorit(self, mapa, usuari=getpass.getuser().upper()): if not self.__CONNECTA_BASE_DADES__(usuari): QMessageBox.critical( None, "Atenció", "No s'ha pogut afegir el mapa a favorits. Intenteu-ho més tard, si us plau" ) return False query = QSqlQuery(self.db) query.prepare( "insert into QV_MAPES_FAVORITS (iduser, nom_mapa) values (:IDUSER,:NOM_MAPA)" ) query.bindValue(':IDUSER', usuari) query.bindValue(':NOM_MAPA', mapa) if not query.exec(): QMessageBox.critical( None, "Atenció", "No s'ha pogut afegir el mapa a favorits. Intenteu-ho més tard, si us plau" ) self.__DESCONNECTA_BASE_DADES__(usuari) return True