def createQtMSDB(self):
"""
Returns a db Connection to a MSSQL (SQL Server database) using
QtSql. This is requiered in order to create views with SQL Server
"""
db = QSqlDatabase.addDatabase("QODBC")
if db.isValid():
db.setDatabaseName("DRIVER={SQL Server};SERVER=" +
self.uri.host() + ";DATABASE=" +
self.uri.database())
db.setUserName(self.uri.username())
db.setPassword(self.uri.password())
if db.open():
return db, True
else:
self.messageBar.pushCritical("Connection SQl Server",
db.lastError().text())
db.close()
db.removeDatabase(db.databaseName())
db = None
return db, False
else:
self.messageBar.pushCritical("Connection SQL Server",
'QODBC db is NOT valid')
def setUpClass(cls):
"""Run before all tests"""
# These are the connection details for the SQL Server instance running on Travis
cls.dbconn = "service='testsqlserver' user=sa password='******' "
if 'QGIS_MSSQLTEST_DB' in os.environ:
cls.dbconn = os.environ['QGIS_MSSQLTEST_DB']
# Create test layers
cls.vl = QgsVectorLayer(
cls.dbconn +
' sslmode=disable key=\'pk\' srid=4326 type=POINT table="qgis_test"."someData" (geom) sql=',
'test', 'mssql')
assert cls.vl.isValid(), cls.vl.dataProvider().error().message()
cls.source = cls.vl.dataProvider()
cls.poly_vl = QgsVectorLayer(
cls.dbconn +
' sslmode=disable key=\'pk\' srid=4326 type=POLYGON table="qgis_test"."some_poly_data" (geom) sql=',
'test', 'mssql')
assert cls.poly_vl.isValid(), cls.poly_vl.dataProvider().error(
).message()
cls.poly_provider = cls.poly_vl.dataProvider()
cls.conn = QSqlDatabase.addDatabase('QODBC')
cls.conn.setDatabaseName('testsqlserver')
if 'QGIS_MSSQLTEST_DB' in os.environ:
cls.conn.setDatabaseName('QGIS_MSSQLTEST_DB')
cls.conn.setUserName('SA')
cls.conn.setPassword('<YourStrong!Passw0rd>')
assert cls.conn.open(), cls.conn.lastError().text()
# Triggers a segfault in the sql server odbc driver on Travis - TODO test with more recent Ubuntu base image
if os.environ.get('TRAVIS', '') == 'true':
del cls.getEditableLayer
def __loadDB(self):
settings = QSettings("PostNAS", "PostNAS-Suche")
dbHost = settings.value("host", "")
dbDatabasename = settings.value("dbname", "")
dbPort = settings.value("port", "5432")
dbUsername = settings.value("user", "")
dbPassword = settings.value("password", "")
authcfg = settings.value( "authcfg", "" )
if authcfg != "" and hasattr(qgis.core,'QgsAuthManager'):
amc = qgis.core.QgsAuthMethodConfig()
qgis.core.QgsAuthManager.instance().loadAuthenticationConfig( authcfg, amc, True)
dbUsername = amc.config( "username", dbUsername )
dbPassword = amc.config( "password", dbPassword )
db = QSqlDatabase.addDatabase("QPSQL")
db.setHostName(dbHost)
db.setPort(int(dbPort))
db.setDatabaseName(dbDatabasename)
db.setUserName(dbUsername)
db.setPassword(dbPassword)
return db
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 setUpClass(cls):
"""Run before all tests"""
cls.dbconn = "host=localhost port=1521 user='******' password='******'"
if 'QGIS_ORACLETEST_DB' in os.environ:
cls.dbconn = os.environ['QGIS_ORACLETEST_DB']
# Create test layers
cls.vl = QgsVectorLayer(
cls.dbconn +
' sslmode=disable key=\'pk\' srid=4326 type=POINT table="QGIS"."SOME_DATA" (GEOM) sql=',
'test', 'oracle')
assert (cls.vl.isValid())
cls.source = cls.vl.dataProvider()
cls.poly_vl = QgsVectorLayer(
cls.dbconn +
' sslmode=disable key=\'pk\' srid=4326 type=POLYGON table="QGIS"."SOME_POLY_DATA" (GEOM) sql=',
'test', 'oracle')
assert (cls.poly_vl.isValid())
cls.poly_provider = cls.poly_vl.dataProvider()
cls.conn = QSqlDatabase.addDatabase('QOCISPATIAL', "oracletest")
cls.conn.setDatabaseName('10.0.0.2/orcl')
if 'QGIS_ORACLETEST_DBNAME' in os.environ:
cls.conn.setDatabaseName('QGIS_ORACLETEST_DBNAME')
cls.conn.setUserName('QGIS')
cls.conn.setPassword('qgis')
assert cls.conn.open()
def setUpClass(cls):
"""Run before all tests"""
# These are the connection details for the SQL Server instance running on Travis
cls.dbconn = "service='testsqlserver' user=sa password='******' "
if 'QGIS_MSSQLTEST_DB' in os.environ:
cls.dbconn = os.environ['QGIS_MSSQLTEST_DB']
# Create test layers
cls.vl = QgsVectorLayer(
cls.dbconn + ' sslmode=disable key=\'pk\' srid=4326 type=POINT table="qgis_test"."someData" (geom) sql=', 'test', 'mssql')
assert cls.vl.isValid(), cls.vl.dataProvider().error().message()
cls.source = cls.vl.dataProvider()
cls.poly_vl = QgsVectorLayer(
cls.dbconn + ' sslmode=disable key=\'pk\' srid=4326 type=POLYGON table="qgis_test"."some_poly_data" (geom) sql=', 'test', 'mssql')
assert cls.poly_vl.isValid(), cls.poly_vl.dataProvider().error().message()
cls.poly_provider = cls.poly_vl.dataProvider()
cls.conn = QSqlDatabase.addDatabase('QODBC')
cls.conn.setDatabaseName('testsqlserver')
if 'QGIS_MSSQLTEST_DB' in os.environ:
cls.conn.setDatabaseName('QGIS_MSSQLTEST_DB')
cls.conn.setUserName('SA')
cls.conn.setPassword('<YourStrong!Passw0rd>')
assert cls.conn.open(), cls.conn.lastError().text()
# Triggers a segfault in the sql server odbc driver on Travis - TODO test with more recent Ubuntu base image
if os.environ.get('TRAVIS', '') == 'true':
del cls.getEditableLayer
def getConnectionFromMetaData(self):
try:
db = None
sql = self.pocedure
if self.provider is not None:
db, sql = self.getDatabase(self.provider, self.pocedure)
if self.dbdriver is not None:
db = QSqlDatabase.addDatabase(self.dbdriver)
sql = self.sql
if self.service is not None:
db.setConnectOptions("service=" + self.service)
if self.host is not None: db.setHostName(self.host)
if self.database is not None: db.setDatabaseName(self.database)
if self.port is not None: db.setPort(int(self.port))
if self.user is not None: db.setUserName(self.user)
if self.password is not None: db.setPassword(self.password)
if self.debug:
dbinfo = 'Database connection:\n'
dbinfo = dbinfo + "driver: " + db.driverName() + "\nhost: " + db.hostName() + "\ndatabase: " + db.databaseName() + "\nport: " \
+ str(db.port()) + "\nuser: "******"\npassword: "******"\noptions: " + db.connectOptions()
self.info.log(dbinfo)
except Exception as e:
self.info.err(e)
finally:
return db, sql
def setUpClass(cls):
"""Run before all tests"""
super().setUpClass()
cls.dbconn = "host=localhost dbname=XEPDB1 port=1521 user='******' password='******'"
if 'QGIS_ORACLETEST_DB' in os.environ:
cls.dbconn = os.environ['QGIS_ORACLETEST_DB']
cls.ds_uri = QgsDataSourceUri(cls.dbconn)
# Create test layers
cls.vl = QgsVectorLayer(
cls.dbconn +
' sslmode=disable key=\'pk\' srid=4326 type=POINT table="QGIS"."SOME_DATA" (GEOM) sql=',
'test', 'oracle')
assert cls.vl.isValid()
cls.con = QSqlDatabase.addDatabase('QOCISPATIAL', "oracletest")
cls.con.setDatabaseName('localhost/XEPDB1')
if 'QGIS_ORACLETEST_DBNAME' in os.environ:
cls.con.setDatabaseName(os.environ['QGIS_ORACLETEST_DBNAME'])
cls.con.setUserName('QGIS')
cls.con.setPassword('qgis')
cls.schema = 'QGIS'
cls.provider = 'oracle'
cls.project_storage_type = 'oracle'
assert cls.con.open()
def __loadDB(self):
settings = QSettings("PostNAS", "PostNAS-Suche")
dbHost = settings.value("host", "")
dbDatabasename = settings.value("dbname", "")
dbPort = settings.value("port", "5432")
dbUsername = settings.value("user", "")
dbPassword = settings.value("password", "")
authcfg = settings.value("authcfg", "")
if authcfg != "" and hasattr(qgis.core, 'QgsAuthManager'):
amc = qgis.core.QgsAuthMethodConfig()
qgis.core.QgsAuthManager.instance().loadAuthenticationConfig(
authcfg, amc, True)
dbUsername = amc.config("username", dbUsername)
dbPassword = amc.config("password", dbPassword)
db = QSqlDatabase.addDatabase("QPSQL")
db.setHostName(dbHost)
db.setPort(int(dbPort))
db.setDatabaseName(dbDatabasename)
db.setUserName(dbUsername)
db.setPassword(dbPassword)
return db
def connectionsInit(self):
conf = self.conf
roleSelected = self.dlg.cmbConnection.currentText()
# Connection string to Postgis db
connectionInfo = "dbname='" + conf['pg']['dbname'] + "' "
connectionInfo += "host=" + conf['pg']['host'] + " "
connectionInfo += "port=" + conf['pg']['port'] + " "
connectionInfo += "sslmode=disable"
if roleSelected == 'Edition':
user = conf["pg"]["editor"]
pwd = self.dlg.txtPassword.text()
if pwd == '':
return
else:
s = QSettings()
s.setValue("SelvansGeo/writerCredentials", pwd)
# Setup QGIS credentials dialog
checkdb = QSqlDatabase.addDatabase("QPSQL")
checkdb.setHostName(conf['pg']['host'])
checkdb.setDatabaseName(conf['pg']['dbname'])
checkdb.setUserName(user)
checkdb.setPassword(pwd)
if checkdb.open():
self.credentialInstance.put(connectionInfo, user, pwd)
else:
self.messageBar.pushMessage("Erreur",
str(u"Mauvais mot de passe"),
level=QgsMessageBar.CRITICAL)
self.dlg.txtPassword.setText("")
return
elif roleSelected == 'Consultation':
self.credentialInstance.put(
connectionInfo,
conf['pg']['user'],
conf['pg']['password'],
)
self.switchUiMode(True)
if self.currentRole != roleSelected and self.currentRole != "init":
self.messageBar.pushMessage(
"Info",
str(u"Vous êtes connecté en" + "mode ") + roleSelected,
level=Qgis.Info)
self.openSelvansGeoProject()
else:
self.messageBar.pushMessage(
"Info",
str(u"Vous êtes connecté en" + "mode ") + roleSelected,
level=Qgis.Info)
self.openSelvansGeoProject()
# store the current role
self.currentRole = roleSelected
def connectionsInit(self):
conf = self.conf
roleSelected = self.dlg.cmbConnection.currentText()
# Connection string to Postgis db
connectionInfo = "dbname='" + conf['pg']['dbname'] + "' "
connectionInfo += "host=" + conf['pg']['host'] + " "
connectionInfo += "port=" + conf['pg']['port'] + " "
connectionInfo += "sslmode=disable"
if roleSelected == 'Edition':
user = conf["pg"]["editor"]
pwd = self.dlg.txtPassword.text()
if pwd == '':
return
else:
s = QSettings()
s.setValue("SelvansGeo/writerCredentials", pwd)
# Setup QGIS credentials dialog
checkdb = QSqlDatabase.addDatabase("QPSQL")
checkdb.setHostName(conf['pg']['host'])
checkdb.setDatabaseName(conf['pg']['dbname'])
checkdb.setPort(int(conf['pg']['port']))
checkdb.setUserName(user)
checkdb.setPassword(pwd)
if checkdb.open():
self.credentialInstance.put(connectionInfo, user, pwd)
else:
self.messageBar.pushCritical("Erreur",
str("Mauvais mot de passe"))
self.dlg.txtPassword.setText("")
return
elif roleSelected == 'Consultation':
self.credentialInstance.put(
connectionInfo,
conf['pg']['user'],
conf['pg']['password'],
)
self.switchUiMode(True)
self.messageBar.pushMessage(str(u"Vous êtes connecté en mode ") +
roleSelected,
level=Qgis.Info)
self.openSelvansGeoProject()
# store the current role
self.currentRole = roleSelected
# check that MS Connection is still valid. It seems that the credential manager
# somehow resets the connections when reconnecting to PG.
if self.qtmsdb.isValid() is False:
self.qtmsdb, isMSOpened = self.msdb.createQtMSDB()
def __init__(self, driver, dbname, user, passwd):
self.conn = QSqlDatabase.addDatabase(
driver, "qtsql_%d" % QtSqlDBConnection.connections)
QtSqlDBConnection.connections += 1
self.conn.setDatabaseName(dbname)
self.conn.setUserName(user)
self.conn.setPassword(passwd)
if not self.conn.open():
raise ConnectionError(self.conn.lastError().databaseText())
def getDataBaseLayerName(self):
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(self.getConnection().database())
db.open()
query = db.exec_("select * from geometry_columns;")
listOfNames = []
while query.next():
listOfNames.append(query.value(0))
db.close()
return listOfNames
def __init__(self, driver, dbname, user, passwd):
self.conn = QSqlDatabase.addDatabase(
driver, "qtsql_%d" % QtSqlDBConnection.connections)
QtSqlDBConnection.connections += 1
self.conn.setDatabaseName(dbname)
self.conn.setUserName(user)
self.conn.setPassword(passwd)
if not self.conn.open():
raise ConnectionError(self.conn.lastError().databaseText())
def get_db(self):
if QSqlDatabase.contains("midb"):
QSqlDatabase.removeDatabase("midb")
db = QSqlDatabase.addDatabase("QPSQL", "midb")
db.setHostName('localhost')
db.setPort(5432)
db.setDatabaseName('')
db.setUserName('')
db.setPassword('')
return db
def __init__(self):
if hasattr(self, 'db'):
return
self.dbQvista = _DB_QVISTA[QvApp().calcEntorn()]
self.db = QSqlDatabase.addDatabase(self.dbQvista['Database'], 'FAV')
if self.db.isValid():
self.db.setHostName(self.dbQvista['HostName'])
self.db.setPort(self.dbQvista['Port'])
self.db.setDatabaseName(self.dbQvista['DatabaseName'])
self.db.setUserName(self.dbQvista['UserName'])
self.db.setPassword(self.dbQvista['Password'])
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 connect_to_db():
settings = Settings()
db = QSqlDatabase.addDatabase("QPSQL", str(datetime.now()))
db.setHostName(settings.value("db_host"))
db.setPort(settings.value("db_port"))
db.setDatabaseName(settings.value("db_name"))
db.setUserName(settings.value("db_username"))
db.setPassword(settings.value("db_password"))
db.open()
return db
def createConnection():
con = QSqlDatabase.addDatabase("QSQLITE")
con.setDatabaseName("C:\\Users\\marie_000\\Documents\\MHTC\\VRM_Test.gpkg")
if not con.open():
QMessageBox.critical(
None, "Cannot open memory database",
"Unable to establish a database connection.\n\n"
"Click Cancel to exit.", QMessageBox.Cancel)
return False
#query = QtSql.QSqlQuery()
return True
def open_db(self):
try:
self.db = QSqlDatabase.addDatabase("QPSQL")
self.db.setHostName(self.db_host)
self.db.setPort(int(self.db_port))
self.db.setDatabaseName(self.db_name)
self.db.setUserName(self.db_user)
self.db.setPassword(self.db_pwd)
self.db.open()
except Exception as e:
raise VerisoErrorWithBar(self.iface.messageBar(),
"Error " + e.message)
def setUpClass(self):
self.settings = Settings()
self.layers = plugins['comptages'].layers
self.layers.load_layers()
self.comptages = plugins['comptages']
self.db = QSqlDatabase.addDatabase("QPSQL", "test_data_connection")
self.db.setHostName(self.settings.value("db_host"))
self.db.setPort(self.settings.value("db_port"))
self.db.setDatabaseName(self.settings.value("db_name"))
self.db.setUserName(self.settings.value("db_username"))
self.db.setPassword(self.settings.value("db_password"))
self.test_data_path = os.path.join(
os.path.dirname(os.path.realpath(__file__)), 'test_data/')
def get_db(self, cfg):
if QSqlDatabase.contains("midb"):
if self.db:
if self.db.isOpen():
self.db.close()
QSqlDatabase.removeDatabase("midb")
self.db = QSqlDatabase.addDatabase("QPSQL", "midb")
self.db.setHostName(cfg.get('host', 'localhost'))
self.db.setPort(int(cfg.get('port', 5432)))
self.db.setDatabaseName(cfg.get('dbname', ''))
self.db.setUserName(cfg.get('user', ''))
self.db.setPassword(cfg.get('pswd', ''))
self.query = QSqlQuery(self.db)
def dbLogConnexio(self):
if not self.intranet:
return
try:
if self.dbLog is None:
db = QSqlDatabase.addDatabase(self.dbQvista['Database'], 'LOG')
if db.isValid():
db.setHostName(self.dbQvista['HostName'])
db.setPort(self.dbQvista['Port'])
db.setDatabaseName(self.dbQvista['DatabaseName'])
db.setUserName(self.dbQvista['UserName'])
db.setPassword(self.dbQvista['Password'])
if db.open():
self.dbLog = db
except Exception:
self.dbLog = None
def __openDatabase(self, dbPath):
"""
:type dbPath: str
:return:
"""
# QgsMessageLog.logMessage("(VFK) Open DB: {}".format(dbPath))
if not QSqlDatabase.isDriverAvailable('QSQLITE'):
raise VFKError(u'Databázový ovladač QSQLITE není dostupný.')
connectionName = QUuid.createUuid().toString()
db = QSqlDatabase.addDatabase("QSQLITE", connectionName)
db.setDatabaseName(dbPath)
if not db.open():
raise VFKError(u'Nepodařilo se otevřít databázi. ')
self.setProperty("connectionName", connectionName)
def connect_to_database_service(service, sslmode=None):
""" Connect to database trough selected service
This service must exist in file pg_service.conf """
conn_string = f"service={service}"
if sslmode:
conn_string += f" sslmode={sslmode}"
# Get credentials from .pg_service.conf
credentials = tools_os.manage_pg_service(service)
if credentials:
status = connect_to_database(credentials['host'], credentials['port'],
credentials['dbname'],
credentials['user'],
credentials['password'],
credentials['sslmode'])
else:
# Try to connect using name defined in service file
# QSqlDatabase connection
global_vars.qgis_db_credentials = QSqlDatabase.addDatabase(
"QPSQL", global_vars.plugin_name)
global_vars.qgis_db_credentials.setConnectOptions(conn_string)
status = global_vars.qgis_db_credentials.open()
if not status:
msg = "Service database connection error (QSqlDatabase). Please open plugin log file to get more details"
global_vars.session_vars['last_error'] = tools_qt.tr(msg)
details = global_vars.qgis_db_credentials.lastError().databaseText(
)
tools_log.log_warning(str(details))
return False
# psycopg2 connection
global_vars.dao = tools_pgdao.GwPgDao()
global_vars.dao.set_conn_string(conn_string)
status = global_vars.dao.init_db()
tools_log.log_info(f"PostgreSQL PID: {global_vars.dao.pid}")
if not status:
msg = "Service database connection error (psycopg2). Please open plugin log file to get more details"
global_vars.session_vars['last_error'] = tools_qt.tr(msg)
tools_log.log_warning(str(global_vars.dao.last_error))
return False
return status
def dbConnect(name='NOVAGEO'):
if not QvNovageo.app.intranet:
return False
try:
dbParams = _DB_NOVAGEO[QvNovageo.app.entorn]
db = QSqlDatabase.addDatabase(dbParams['Database'], name)
if db.isValid():
db.setHostName(dbParams['HostName'])
db.setPort(dbParams['Port'])
db.setDatabaseName(dbParams['DatabaseName'])
db.setUserName(dbParams['UserName'])
db.setPassword(dbParams['Password'])
if db.open():
QvNovageo.db = db
return True
return False
except Exception:
QvNovageo.db = None
return False
def setUpClass(cls):
"""Run before all tests"""
TestPyQgsProviderConnectionBase.setUpClass()
# These are the connection details for the Docker Oracle instance running on Travis
cls.dbconn = "host=localhost/XEPDB1 port=1521 user='******' password='******'"
if 'QGIS_ORACLETEST_DB' in os.environ:
cls.dbconn = os.environ['QGIS_ORACLETEST_DB']
cls.uri = cls.dbconn
cls.conn = QSqlDatabase.addDatabase('QOCISPATIAL', "oracletest")
cls.conn.setDatabaseName('localhost/XEPDB1')
if 'QGIS_ORACLETEST_DBNAME' in os.environ:
cls.conn.setDatabaseName(os.environ['QGIS_ORACLETEST_DBNAME'])
cls.conn.setUserName('QGIS')
cls.conn.setPassword('qgis')
assert cls.conn.open()
def connect_to_database(host, port, db, user, pwd, sslmode):
""" Connect to database with selected parameters """
# Check if selected parameters is correct
if None in (host, port, db, user, pwd):
message = "Database connection error. Please check your connection parameters."
global_vars.session_vars['last_error'] = tools_qt.tr(message)
return False
# Update current user
global_vars.current_user = user
# QSqlDatabase connection for Table Views
global_vars.qgis_db_credentials = QSqlDatabase.addDatabase(
"QPSQL", global_vars.plugin_name)
global_vars.qgis_db_credentials.setHostName(host)
if port != '':
global_vars.qgis_db_credentials.setPort(int(port))
global_vars.qgis_db_credentials.setDatabaseName(db)
global_vars.qgis_db_credentials.setUserName(user)
global_vars.qgis_db_credentials.setPassword(pwd)
status = global_vars.qgis_db_credentials.open()
if not status:
msg = "Database connection error (QSqlDatabase). Please open plugin log file to get more details"
global_vars.session_vars['last_error'] = tools_qt.tr(msg)
details = global_vars.qgis_db_credentials.lastError().databaseText()
tools_log.log_warning(str(details))
return False
# psycopg2 connection
global_vars.dao = tools_pgdao.GwPgDao()
global_vars.dao.set_params(host, port, db, user, pwd, sslmode)
status = global_vars.dao.init_db()
tools_log.log_info(f"PostgreSQL PID: {global_vars.dao.pid}")
if not status:
msg = "Database connection error (psycopg2). Please open plugin log file to get more details"
global_vars.session_vars['last_error'] = tools_qt.tr(msg)
tools_log.log_warning(str(global_vars.dao.last_error))
return False
return status
def dbGeoConnexio(self):
try:
if self.db is None:
if not os.path.exists(self.dbFile):
return None
db = QSqlDatabase.addDatabase('QSQLITE', 'GEO')
db.setDatabaseName(self.dbFile)
db.setConnectOptions("QSQLITE_OPEN_READONLY")
if db.isValid() and db.open():
self.db = db
self.query = QSqlQuery(self.db)
# ok = self.query.exec_("PRAGMA cache_size = 32768")
# ok = self.query.exec_("PRAGMA temp_store = MEMORY")
else:
self.db = None
self.query = None
return db
except Exception:
self.db = None
self.query = None
return None
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 __init__(self,parent=None):
super(QDialog,self).__init__(parent)
self.setupUi(self)
#self.setModal(True)#block other stuff
self.connections_box.currentIndexChanged.connect(self.get_connection_info)
self.connect_button.clicked.connect(self.connect)
self.ok_button.clicked.connect(self.accept)
self.set_connected(False)
self.db = QSqlDatabase.addDatabase('QPSQL')
self.con=None
self.cur=None
self.connected=False
self.task=None#QgsTask task. only want to run 1 task at a time.
self.progress=QProgressDialog(parent=self.parent())#set parent to dockwidget
self.progress.setWindowModality(Qt.WindowModal)#make modal to prevent multiple tasks at once
self.progress.canceled.connect(self.task_canceled)
self.task_canceled()
self.refresh_connections()
self.refresh_button.clicked.connect(self.refresh_connections)
def setUpClass(cls):
"""Run before all tests"""
cls.dbconn = "host=localhost port=1521 user='******' password='******'"
if 'QGIS_ORACLETEST_DB' in os.environ:
cls.dbconn = os.environ['QGIS_ORACLETEST_DB']
# Create test layers
cls.vl = QgsVectorLayer(
cls.dbconn + ' sslmode=disable key=\'pk\' srid=4326 type=POINT table="QGIS"."SOME_DATA" (GEOM) sql=', 'test', 'oracle')
assert(cls.vl.isValid())
cls.source = cls.vl.dataProvider()
cls.poly_vl = QgsVectorLayer(
cls.dbconn + ' sslmode=disable key=\'pk\' srid=4326 type=POLYGON table="QGIS"."SOME_POLY_DATA" (GEOM) sql=', 'test', 'oracle')
assert(cls.poly_vl.isValid())
cls.poly_provider = cls.poly_vl.dataProvider()
cls.conn = QSqlDatabase.addDatabase('QOCISPATIAL', "oracletest")
cls.conn.setDatabaseName('10.0.0.2/orcl')
if 'QGIS_ORACLETEST_DBNAME' in os.environ:
cls.conn.setDatabaseName('QGIS_ORACLETEST_DBNAME')
cls.conn.setUserName('QGIS')
cls.conn.setPassword('qgis')
assert cls.conn.open()
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
