def open_psql_db(db_host, db_name, db_port, db_admin, db_admin_pwd):
"""
Open PostGIS db connection taking care that the connection exists only once
:param db_host: the db host
:param db_name: the db name
:param db_port: the db port
:param db_admin: the db administrator username
:param db_admin_pwd: the db administrator username password
:return: a db object
:rtype: QSqlDatabase
"""
connection_name = "%s@%s:%s/%s" % (db_admin, db_host, db_port, db_name)
try:
if connection_name in QSqlDatabase.connectionNames():
db = QSqlDatabase.database(connection_name)
else:
db = QSqlDatabase.addDatabase("QPSQL", connection_name)
db.setHostName(db_host)
db.setPort(int(db_port))
db.setDatabaseName(db_name)
db.setUserName(db_admin)
db.setPassword(db_admin_pwd)
if not db.open():
raise Exception()
except Exception as e:
message = "Could not open psql database: %s" % connection_name
message = tr(message)
raise VerisoError(message, e, db.lastError().text())
return db
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 __evaluate(self, query):
"""
:type query: str
:return: bool
"""
t = QTime()
t.start()
# QgsMessageLog.logMessage("(VFK) SQL: {}\n".format(query))
self.setQuery(query, QSqlDatabase.database(self.__mConnectionName))
while self.canFetchMore():
self.fetchMore()
# if t.elapsed() > 500:
# QgsMessageLog.logMessage("(VFK) Time elapsed: {} ms\n".format(t.elapsed()))
if self.lastError().isValid():
iface.messageBar().pushWarning(
'ERROR', 'SQL ({}): {}'.format(query,
self.lastError().text()))
return False
return True
def connect(cls, **connection):
dbtype = connection.get('type', "QODBC")
connectionname = connection.get("connectionname",
hash(tuple(connection.items())))
db = QSqlDatabase.database(connectionname)
if not db.isValid():
db = QSqlDatabase.addDatabase(dbtype, connectionname)
if dbtype == "QSQLITE":
db.setDatabaseName(connection['database'])
else:
if "constring" in connection:
db.setDatabaseName(connection['constring'])
else:
constring = "driver={driver};server={host};database={database};uid={user};pwd={password}"
connection["driver"] = "{SQL Server}"
constring = constring.format(**connection)
db.setHostName(connection['host'])
db.setDatabaseName(constring)
db.setUserName(connection['user'])
db.setPassword(connection['password'])
if not db.open():
raise DatabaseException(db.lastError().text())
return Database(db)
def dveRadyCislovani(self):
"""
:return: bool
"""
query = "SELECT 1 FROM doci WHERE druh_cislovani_par = 1"
self.setQuery(query, QSqlDatabase.database(self.__mConnectionName))
if self.rowCount() > 0:
return True
else:
return False
def open_sqlite_db(file_path, connection_name):
"""
Opens SQLite db connection taking care that the connection exists only once
:param file_path: the oprional path of the SQLite file
:return: a db object
:rtype: QSqlDatabase
"""
try:
if connection_name in QSqlDatabase.connectionNames():
db = QSqlDatabase.database(connection_name)
else:
db = QSqlDatabase.addDatabase("QSQLITE", connection_name)
db.setDatabaseName(file_path)
if not db.open():
raise Exception()
except Exception as e:
message = "Could not open sqlite database: %s" % connection_name
message = tr(message)
raise VerisoError(message, e, db.lastError().text())
return db
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 open_psql_db(db_host, db_name, db_port, db_admin, db_admin_pwd):
"""
Open PostGIS db connection taking care that the connection exists only once
:param db_host: the db host
:param db_name: the db name
:param db_port: the db port
:param db_admin: the db administrator username
:param db_admin_pwd: the db administrator username password
:return: a db object
:rtype: QSqlDatabase
"""
connection_name = "%s@%s:%s/%s" % (db_admin, db_host, db_port, db_name)
try:
if connection_name in QSqlDatabase.connectionNames():
db = QSqlDatabase.database(connection_name)
else:
if not QSqlDatabase.isDriverAvailable("QPSQL"):
raise VerisoError('Please install the PSQL Qt driver\n'
'(libqt4-sql-psql in ubuntu).\n')
db = QSqlDatabase.addDatabase("QPSQL", connection_name)
db.setHostName(db_host)
db.setPort(int(db_port))
db.setDatabaseName(db_name)
db.setUserName(db_admin)
db.setPassword(db_admin_pwd)
if not db.open():
raise Exception()
except VerisoError:
raise
except Exception as e:
message = "Could not open psql database: %s see log for more details"\
% connection_name
message = tr(message)
#
raise VerisoError(message, e, db.lastError().text())
return db