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.
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:
ImportError: DLL load failed: The specified procedure could not be found. while I am trying to import the cx_Oracle library. –
Spawn 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'})
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()
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.
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.
Ensure these two and it should work:-
Fixes this issue on windows like a charm.
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.
import cx_Oracle
from sshtunnel import SSHTunnelForwarder
# remote server variables
remote_ip_address = "<PUBLIC_IP_ADDRESS_OF_DB_SERVER>"
remote_os_username = "<OS_USERNAME>"
ssh_private_key = "<PATH_TO_PRIVATE_KEY>"
# Oracle database variables
database_username = "<DATABASE_USER>"
database_password = "<DATABASE_USER_PASSWORD>"
database_server_sid = "<ORACLE_SID>"
def server_connection():
server = SSHTunnelForwarder(
remote_ip_address,
ssh_username=remote_os_username,
ssh_password=ssh_private_key,
remote_bind_address=('localhost', 1521) # default Oracle DB port
)
return server
def database_connection():
data_source_name = cx_Oracle.makedsn("localhost",
server.local_bind_port,
service_name=database_server_sid)
connection = cx_Oracle.connect(database_username,
database_password,
data_source_name,
mode=cx_Oracle.SYSDBA) # If logging in with SYSDBA privs,
# leave out if not.
return connection
def database_execute():
connection = database_connection()
cursor = connection.cursor()
for row in cursor.execute("SELECT * FROM HELLO_WORLD_TABLE"):
print(row)
if __name__ == '__main__':
server = server_connection()
server.start() # start remote server connection
connection = database_connection() # create Oracle database connection
database_execute() # execute query
connection.close() # close Oracle database connection
server.stop() # close remote server connection
If you're accessing the Oracle database via a bastion tunnel, you just need to modify this piece of code:
def server_connection():
server = SSHTunnelForwarder(
remote_ip_address, # public IP of bastion server
ssh_username=remote_os_username,
ssh_password=ssh_private_key,
remote_bind_address=('localhost', 1521),
local_bind_address=('0.0.0.0', 3333) # Suppose local bind is '3333'
)
return server
© 2022 - 2024 — McMap. All rights reserved.