Python connects to an oracle database instance

  • 2020-04-02 14:18:44
  • OfStack

This article is an example of a python connection to an oracle database. The specific steps are as follows:

First, download the driver :(cx_Oracle)

http://www.python.net/crew/atuining/cx_Oracle/
But watch out for versions, and choose according to your situation.

Ii. Installation:

First configure the oracle_home environment variable
Just execute the exe installer, which copies a cx_oracle.pyd to the libsite-packages directory.
If it's Linux, execute

python setup.py build
python setup.py install

Iii. Execute a test procedure:

import cx_Oracle
con = cx_Oracle.connect( "xjtu_test", "37343734","xjtu.world")
cursor = con.cursor()
cursor.close()
con.close()

The three parameters in connect from left to right are: user, pass, and TNS.
That TNS can be configured using the Net Configuration Assistant in the Oracle client tool.

Iv. Specific cx_Oracle API can be referred to:
http://www.python.net/crew/atuining/cx_Oracle/html/cx_Oracle.html

V. examples:

>>> import cx_Oracle
>>> conn=cx_Oracle.connect ('scott/tiger@oratest')
>>> curs=conn.cursor ()
>>> sql='select * from emp'
>>> rr=curs.execute (sql)
>>> row=curs.fetchone()
>>> row
(7369, 'SMITH', 'CLERK', 7902, datetime.datetime(1980, 12, 17, 0, 0), 800.0, None, 20)
>>> while row:
    (ID,NAME)=(row[0],row[1])
    row=curs.fetchone ()
    print ID,NAME    
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 daily
7934 MILLER

If you're using Windows, you're going to have a problem executing that test code, and you're going to have the following problems:

When importing cx_Oracle, oci. DLL cannot be found:
Go to a machine with Oracle installed and copy to the libsite-packages directory.

RuntimeError: Unable to acquire Oracle environment handle:
This is troublesome, follow the following steps to solve :(may not need all the steps, I did not confirm, but the following steps are executed, the problem is solved)
First, make sure you execute the python script from below the console. Rather than some ides, such as PyDev (which don't seem to load OS environment variables).
Actually, install Oracle locally (just install client tools).
Finally, add the following environment variables :(I will give you mine, change to your own path)

ORACLE_HOME = D:OracleOra81
PATH=D:OracleOra81bin;

I hope this article has helped you with your Python programming.


Related articles: