Connecting directly to an Oracle Database using PyQGIS

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?

Answer

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()