basic usage of Python with Oracle DB, in Debian

What if you wanted to access Oracle using Python? Once you are set up, it's real easy. We use cx_Oracle, the most popular Python library to read Oracle DBs.

Let's start by simply reading the DB:

import cx_Oracle
conn = cx_Oracle.connect("usr", "pwd", "tns")
cursor = conn.cursor()
cursor.execute("SELECT * TABLE_NAME")
for item in cursor.fetchall()[0]:
    print(item)
conn.close()

The cx_Oracle explain exactly what each of these means.

If we wanted instead to modify stuff in there, we only add one extra line, which is cursor.commit, like this:

import cx_Oracle
conn = cx_Oracle.connect("usr", "pwd", "tns")
cursor = conn.cursor()
cursor.execute("SELECT * TABLE_NAME")
cursor.commit()
conn.close()

For real-world code, you are of course going to add exception handling for all this, something like this:

import cx_Oracle
try:
    conn = cx_Oracle.connect("usr", "pwd", "tns")
    cursor = conn.cursor()
    cursor.execute("SELECT * TABLE_NAME")
    for item in cursor.fetchall()[0]:
        print(item)
    conn.close()
except Exception as e:
    print(e)

That exception handling is rather oversimplified though, but is good enough for illustrative purposes. Also, note that there is no timeout set in case of problematic/slow network access, so my code will just sit there waiting to connect.