cx_Oracle - How do I access Oracle from Python?

Question

How do I get started?

Answer 1

cx_Oracle is a Python extension module that allows access to Oracle databases and conforms to the Python database API specification.

More information is available here. Here is a sample showing how to connect and process a query.

#!/usr/bin/python

import cx_Oracle
connstr='scott/tiger'
conn = cx_Oracle.connect(connstr)
curs = conn.cursor()

curs.execute('select * from emp')
print curs.description
for row in curs:
    print row
conn.close()

Answer 2

Extra information:

• The complete connstr for a non-local database should could be something like 'scott/tiger@DATABASE_SID'
• You'll the oracle client installed (Instant Client might be a good choice) and your tnsnames.ora configured

Answer 3

Also, you can bypass TNS by using this as a connection string: scott/tiger@myhost:1521/DATABASE_SID

Answer 4

The Oracle Technology Network website has a lot of Python examples that would be worth reviewing.

http://www.oracle.com/technology/pub/articles/devlin-python-oracle.html

Querying best practices: http://www.oracle.com/technology/pub/articles/prez-python-queries.html

Data Parsing: http://www.oracle.com/technology/pub/articles/prez-python-dataparsing.html

Working with Times and Dates: http://www.oracle.com/technology/pub/articles/prez-python-timesanddates.html

Answer 5

I just set up cx_Oracle on windows. It was very tricky. Ultimately I needed to tweak my path variable: I needed to set ORACLE_HOME and ORACLE_BASE to C:\oracle\ora9 and C:\oracle respectively.

Also, I first downloaded cx_Oracle for Oracle 10 because I new that was the DB. But we use Oracle 9 libraries. I had to reinstall the right cx_Oracle version.

If "SQL Plus" does not work, you have environment issues to sort out.