如何从 Python 访问 Oracle？

How can I access Oracle from Python?

提问by user425194

How can I access Oracle from Python? I have downloaded a cx_Oracle msi installer, but Python can't import the library.

如何从 Python 访问 Oracle？我已经下载了 cx_Oracle msi 安装程序，但 Python 无法导入该库。

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.

我将不胜感激任何帮助。

回答by TML

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.

除了 cx_Oracle，您还需要安装 Oracle 客户端库并正确设置路径，以便 cx_Oracle 找到它 - 尝试在“Dependency Walker”（http://www.dependencywalker.com/）中打开 cx_Oracle DLL以看看丢失的 DLL 是什么。

回答by Devon Biere

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.

这对我有用。我的 Python 和 Oracle 版本与您的略有不同，但应该采用相同的方法。只需确保 cx_Oracle 二进制安装程序版本与您的 Oracle 客户端和 Python 版本匹配。

My versions:

我的版本：

• Python 2.7
• Oracle Instant Client 11G R2
• cx_Oracle 5.0.4 (Unicode, Python 2.7, Oracle 11G)
• Windows XP SP3

• 蟒蛇 2.7
• Oracle 即时客户端 11G R2
• cx_Oracle 5.0.4（Unicode、Python 2.7、Oracle 11G）
• 视窗 XP SP3

Steps:

脚步：

1. Download the Oracle Instant Client package. I used instantclient-basic-win32-11.2.0.1.0.zip. Unzip it to C:\your\path\to\instantclient_11_2
2. Download and run the cx_Oracle binary installer. I used cx_Oracle-5.0.4-11g-unicode.win32-py2.7.msi. I installed it for all users and pointed it to the Python 2.7 location it found in the registry.
3. Set the ORACLE_HOME and PATH environment variables via a batch script or whatever mechanism makes sense in your app context, so that they point to the Oracle Instant Client directory. See oracle_python.bat source below. I'm sure there must be a more elegant solution for this, but I wanted to limit my system-wide changes as much as possible. Make sure you put the targeted Oracle Instant Client directory at the beginning of the PATH (or at least ahead of any other Oracle client directories). Right now, I'm only doing command-line stuff so I just run oracle_python.bat in the shell before running any programs that require cx_Oracle.
4. Run regedit and check to see if there's an NLS_LANG key set at \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE. If so, rename the key (I changed it to NLS_LANG_OLD) or unset it. This key should only be used as the default NLS_LANG value for Oracle 7 client, so it's safe to remove it unless you happen to be using Oracle 7 client somewhere else. As always, be sure to backup your registry before making changes.
5. Now, you should be able to import cx_Oracle in your Python program. See the oracle_test.py source below. Note that I had to set the connection and SQL strings to Unicode for my version of cx_Oracle.

1. 下载 Oracle Instant Client 包。我使用了 Instantclient-basic-win32-11.2.0.1.0.zip。解压到 C:\your\path\to\instantclient_11_2
2. 下载并运行 cx_Oracle 二进制安装程序。我使用了 cx_Oracle-5.0.4-11g-unicode.win32-py2.7.msi。我为所有用户安装了它，并将它指向它在注册表中找到的 Python 2.7 位置。
3. 通过批处理脚本或任何在您的应用程序上下文中有意义的机制设置 ORACLE_HOME 和 PATH 环境变量，以便它们指向 Oracle Instant Client 目录。请参阅下面的 oracle_python.bat 源代码。我确信必须有一个更优雅的解决方案，但我想尽可能地限制我的系统范围的更改。确保将目标 Oracle Instant Client 目录放在 PATH 的开头（或至少放在任何其他 Oracle 客户端目录之前）。现在，我只执行命令行操作，所以在运行任何需要 cx_Oracle 的程序之前，我只在 shell 中运行 oracle_python.bat。
4. 运行 regedit 并检查在 \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE 中是否设置了 NLS_LANG 密钥。如果是这样，请重命名密钥（我将其更改为 NLS_LANG_OLD）或取消设置。此键应仅用作 Oracle 7 客户端的默认 NLS_LANG 值，因此删除它是安全的，除非您碰巧在其他地方使用 Oracle 7 客户端。与往常一样，请务必在进行更改之前备份您的注册表。
5. 现在，您应该能够在 Python 程序中导入 cx_Oracle。请参阅下面的 oracle_test.py 源。请注意，对于我的 cx_Oracle 版本，我必须将连接和 SQL 字符串设置为 Unicode。

Source: oracle_python.bat

来源：oracle_python.bat

@echo off
set ORACLE_HOME=C:\your\path\to\instantclient_11_2
set PATH=%ORACLE_HOME%;%PATH%

Source: oracle_test.py

来源：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:

可能的问题：

• "ORA-12705: Cannot access NLS data files or invalid environment specified" - I ran into this before I made the NLS_LANG registry change.
• "TypeError: argument 1 must be unicode, not str" - if you need to set the connection string to Unicode.
• "TypeError: expecting None or a string" - if you need to set the SQL string to Unicode.
• "ImportError: DLL load failed: The specified procedure could not be found." - may indicate that cx_Oracle can't find the appropriate Oracle client DLL.

• “ORA-12705：无法访问 NLS 数据文件或指定的环境无效” - 我在更改 NLS_LANG 注册表之前遇到了这个问题。
• “TypeError: argument 1 must be unicode, not str” - 如果您需要将连接字符串设置为 Unicode。
• “TypeError: expecting None or a string” - 如果您需要将 SQL 字符串设置为 Unicode。
• “导入错误：DLL 加载失败：找不到指定的过程。” - 可能表示 cx_Oracle 找不到合适的 Oracle 客户端 DLL。

回答by Mike

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.

除了 Oracle 即时客户端，您可能还需要安装 Oracle ODAC 组件并将它们的路径放入您的系统路径中。cx_Oracle 似乎需要访问与它们一起安装的 oci.dll 文件。

Also check that you get the correct version (32bit or 64bit) of them that matches your: python, cx_Oracle, and instant client versions.

还要检查您是否获得了与您的：python、cx_Oracle 和即时客户端版本相匹配的正确版本（32 位或 64 位）。

回答by Venu Murthy

Ensure these two and it should work:-

确保这两个，它应该工作： -

1. Python, Oracle instantclient and cx_Oracle are 32 bit.
2. Set the environment variables.

1. Python、Oracle Instantclient 和 cx_Oracle 是 32 位的。
2. 设置环境变量。

Fixes this issue on windows like a charm.

在 Windows 上修复了这个问题，就像一个魅力。

回答by Ward

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.

如果您使用的是 virtualenv，则使用安装程序获取驱动程序并非易事。然后你可以做什么：按照 Devon 的描述安装它。然后将 cx_Oracle.pyd 和 cx_Oracle-XXX.egg-info 文件夹从 Python\Lib\site-packages 复制到虚拟环境中的 Lib\site-packages 中。当然，在这里，架构和版本也很重要。

回答by Vlad Bezden

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:

这是我的代码的样子。它还展示了如何使用字典使用查询参数的示例。它适用于使用 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'})

回答by sushmit

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()

回答by Siraj

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 :

笔记 ：

1. In (dsn_tns) if needed, place an 'r' before any parameter in order to address any special character such as '\'.

2. 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'

3. use triple quotes if you want to spread your query across multiple lines.

1. 如果需要，在 (dsn_tns) 中，在任何参数之前放置一个 'r' 以寻址任何特殊字符，例如 '\'。

2. 如果需要，在 (conn) 中，在任何参数之前放置一个 'r' 以寻址任何特殊字符，例如 '\'。例如，如果您的用户名包含 '\'，则需要在用户名之前放置 'r'：user=r'User Name' 或 password=r'password'

3. 如果您想将查询扩展到多行，请使用三引号。

回答by Sahil Chhabra

You can use any of the following way based on Service Nameor SIDwhatever you have.

您可以根据Service Name或SID您拥有的任何方式使用以下任何一种方式。

With SID:

使用 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()