1

I have an Oracle database that I would like to do INSERTS and UPDATES on.

I would like to do this using python inside a standard QGIS installation without having to install additional packages or clients.

Is this possible and in that case, how can it be done?

1

Looking at the DB Manager plugin in QGIS I found that it is possible to use the QT framework to connect to an Oracle database and perform SELECTS, UPDATES and INSERTS.

The following shows a quick and dirty example of how it can be done:

from qgis.PyQt.QtSql import QSqlDatabase, QSqlQuery

# Check if the driver is available.
if not QSqlDatabase.isDriverAvailable('QOCI'):
    print 'Oracle driver not available'

db = QSqlDatabase.addDatabase("QOCI")

# Do NOT set the host name directly. 
#You'll get a ORA-12505: TNS:listener does not currently know of SID given in connect descriptor.
#db.setHostName(HOST)

db.setDatabaseName(HOST + '/' + DATABASE)

# You should probably not the port number either.
#db.setPort(1521)
# Instead insert the port number like:
db.setDatabaseName(HOST + ':' + str(PORT) + '/' + DATABASE)

db.setUserName(USER)
db.setPassword(PASSWORD)

if not db.open():
    print 'Could not open connection.'

q = QSqlQuery(db)
q.exec_('SELECT count(*) FROM my_table')
while(q.first()):
    print(str(q.value(0)))

db.close()
0

Another possibility is via "Ocispatial":

from qgis.PyQt.QtWidgets import *
from PyQt5.QtCore import *
from PyQt5.QtGui import *
from qgis.PyQt.QtSql import QSqlDatabase, QSqlQuery
from PyQt5.QtWidgets import QTableView, QApplication
import sys



#qgis.utils.iface.messageBar().pushMessage("Autosave", u"blba : "+str(QSqlDatabase.drivers()))


# Check if the driver is available.
if not QSqlDatabase.isDriverAvailable('QOCISPATIAL'):
    qgis.utils.iface.messageBar().pushMessage("Autosave", u"Driver not da")


db = QSqlDatabase.addDatabase("QOCISPATIAL")    

db.setPort(1521);
db.setHostName("ddddddddd");
db.setDatabaseName("BiBaBo");
db.setUserName("ruckucku");
db.setPassword("schalalal");

if not db.open():
    qgis.utils.iface.messageBar().pushMessage("x", u"DB closed")


#if db.open():
# assume you have a table called 'users'
query = db.exec_('SELECT OUTPUT_Z from blablabla where FID_AW_BIS='+str(myFeature["FID"])+' OR FID_AW_VON='+str(myFeature["FID"]))
while query.next(): 
record = query.record()
qgis.utils.iface.messageBar().pushMessage("x1", u"DB1: "+str(record.value(0)))

db.close()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.