How can I access Oracle from Python? I have downloaded a cx_Oracle msi installer, but Python can't import the library.
I get the following error:
import cx_Oracle
Traceback (most recent call last):
File "<pyshell#1>", line 1, in <module>
import cx_Oracle
ImportError: DLL load failed: The specified module could not be found.
I will be grateful for any help.
This question is related to
python
oracle
database-connection
cx-oracle
If you are using virtualenv, it is not as trivial to get the driver using the installer. What you can do then: install it as described by Devon. Then copy over cx_Oracle.pyd and the cx_Oracle-XXX.egg-info folder from Python\Lib\site-packages into the Lib\site-packages from your virtual env. Of course, also here, architecture and version are important.
Here is how my code looks like. It also shows an example of how to use query parameters using a dictionary. It works on using Python 3.6:
import cx_Oracle
CONN_INFO = {
'host': 'xxx.xx.xxx.x',
'port': 12345,
'user': 'SOME_SCHEMA',
'psw': 'SECRETE',
'service': 'service.server.com'
}
CONN_STR = '{user}/{psw}@{host}:{port}/{service}'.format(**CONN_INFO)
QUERY = '''
SELECT
*
FROM
USER
WHERE
NAME = :name
'''
class DB:
def __init__(self):
self.conn = cx_Oracle.connect(CONN_STR)
def query(self, query, params=None):
cursor = self.conn.cursor()
result = cursor.execute(query, params).fetchall()
cursor.close()
return result
db = DB()
result = db.query(QUERY, {'name': 'happy'})
In addition to cx_Oracle, you need to have the Oracle client library installed and the paths set correctly in order for cx_Oracle to find it - try opening the cx_Oracle DLL in "Dependency Walker" (http://www.dependencywalker.com/) to see what the missing DLL is.
In addition to the Oracle instant client, you may also need to install the Oracle ODAC components and put the path to them into your system path. cx_Oracle seems to need access to the oci.dll file that is installed with them.
Also check that you get the correct version (32bit or 64bit) of them that matches your: python, cx_Oracle, and instant client versions.
You can use any of the following way based on Service Name or SID whatever you have.
With SID:
import cx_Oracle
dsn_tns = cx_Oracle.makedsn('server', 'port', 'sid')
conn = cx_Oracle.connect(user='username', password='password', dsn=dsn_tns)
c = conn.cursor()
c.execute('select count(*) from TABLE_NAME')
for row in c:
print(row)
conn.close()
OR
With Service Name:
import cx_Oracle
dsn_tns = cx_Oracle.makedsn('server', 'port', service_name='service_name')
conn = cx_Oracle.connect(user='username', password='password', dsn=dsn_tns)
c = conn.cursor()
c.execute('select count(*) from TABLE_NAME')
for row in c:
print(row)
conn.close()
import cx_Oracle
dsn_tns = cx_Oracle.makedsn('host', 'port', service_name='give service name')
conn = cx_Oracle.connect(user='username', password='password', dsn=dsn_tns)
c = conn.cursor()
c.execute('select count(*) from schema.table_name')
for row in c:
print row
conn.close()
Note :
In (dsn_tns) if needed, place an 'r' before any parameter in order to address any special character such as '\'.
In (conn) if needed, place an 'r' before any parameter in order to address any special character such as '\'. For example, if your user name contains '\', you'll need to place 'r' before the user name: user=r'User Name' or password=r'password'
use triple quotes if you want to spread your query across multiple lines.
Note if you are using pandas you can access it in following way:
import pandas as pd
import cx_Oracle
conn= cx_Oracle.connect('username/pwd@host:port/service_name')
try:
query = '''
SELECT * from dual
'''
df = pd.read_sql(con = conn, sql = query)
finally:
conn.close()
df.head()
Here's what worked for me. My Python and Oracle versions are slightly different from yours, but the same approach should apply. Just make sure the cx_Oracle binary installer version matches your Oracle client and Python versions.
My versions:
Steps:
Source: oracle_python.bat
@echo off
set ORACLE_HOME=C:\your\path\to\instantclient_11_2
set PATH=%ORACLE_HOME%;%PATH%
Source: oracle_test.py
import cx_Oracle
conn_str = u'user/password@host:port/service'
conn = cx_Oracle.connect(conn_str)
c = conn.cursor()
c.execute(u'select your_col_1, your_col_2 from your_table')
for row in c:
print row[0], "-", row[1]
conn.close()
Possible Issues:
Ensure these two and it should work:-
Fixes this issue on windows like a charm.
Source: Stackoverflow.com