Win10下使用Python连接Oracle

前言

本来以为写篇这个就够了 Python离线安装第三方库,后来发现还有好多坑,还是全记一下吧。使用python连接oracle有很多种方式,本文使用的是cx-Oracle。此外,本文完全是在不能连外网的情况下操作的,能连外网没有这么麻烦。

正文

1 cannot import name ‘SourceDistribution’

本来在linux上配置好了所有环境,把python包全拷到windows下后发现不能直接用,还得重新安装cx-Oracle,如 Python离线安装第三方库写的那样,我先用能连外网的笔记本在https://pypi.org/project/cx-Oracle/7.3.0/#files下载了whl包,然后传到了不能连外网的机器上,而安装时报了如下错误:
pip install ImportError: cannot import name ‘SourceDistribution’

pip install d:\xx\xx.whl

查了下说是pip版本的问题,需要升级。
那么第一个问题来了,再使用这种方式通过有问题的pip离线安装升级pip肯定是不行的,怎么办呢?
然后我使用了Python离线安装第三方库中写的第三种方式,源码安装升级pip,然后再安装cx-Oracle。

2 DPI-1047: Cannot locate a 64-bit Oracle Client library

cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "D:\xx\oci.dll is not the correct architecture
查了下说是Oracle Client版本不对,要和python一致,python是64位,Oracle Client也需要是64位,小问题,去官网下载:https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html
然而坑又来了,下载需要注册oracle账户,注册完后登录,oracle官网又出问题了,下载不了。
在这里插入图片描述
找了半天终于在csdn上找到一个64位的,下载然后传到不能联网的机器上。然而,又出问题了,见下。

3 make sure you have the 32 bits oracle client installed

用64位替换了32位的oracle客户端后,cx-Oracle没问题了,而PLSQL Developer并不支持Oracle 64位客户端连接,冲突了,这不是为难我胖虎吗?
还好机智的我想起来可以在plsql里设置oci库,我把它改成了32位的,环境变量里配置64位的,这样就完美共存了。
在这里插入图片描述

4 ORA-12154: TNS

cx_Oracle.DatabaseError: ORA-12154: TNS

这个问题也有点坑,在linux下ora_addr需写成"ip:port/orcl",而windows下需写成实例名,就是tnsnames.ora里配置的。
在这里插入图片描述
例子:

# -*- coding: utf-8 -*-
# @Time    : 2020/12/12 16:14
# @Author  : drguo
# @FileName: test.py
# @Software: PyCharm
# @blog :drguo.blog.csdn.net
import cx_Oracle

ora_name = ""
ora_pasw = ""
# linux
# ora_addr = "ip:port/orcl"
# windows 写实例名
ora_addr = "xx-log"

# 查询oracle
def ora_query(sql_log):
    print(sql_log)

    conn = cx_Oracle.connect(
        '{ora_name}/{ora_pasw}@{ora_addr}'.format(ora_name=ora_name, ora_pasw=ora_pasw, ora_addr=ora_addr))
    curs = conn.cursor()
    res = curs.execute(sql_log)
    row = res.fetchone()
    curs.close()
    conn.close()
    return row


if __name__ == '__main__':
    res1 = ora_query("select * from ETL_JOB_STATUS t where t.etl_job='audit_fraud_outbound_d_s'")
    print res1

光于前裕于后 CSDN认证博客专家 数据分析 神经网络 图像处理
本人有多年大数据与机器学习开发经验,并乐于总结与分享,如有侵权或写的不对的地方可以私信我,有问题也可以问我哟,free~
已标记关键词 清除标记
相关推荐
<div><p>Hello, I get the following error when running my node.js code on my Mac (OS version 10.14.6):</p> <pre><code> DPI-1047: Cannot locate a 64-bit Oracle Client library: "dlopen(libclntsh.dylib, 1): image not found". See https://oracle.github.io/odpi/doc/installation.html#macos for help Node-oracledb installation instructions: https://oracle.github.io/node-oracledb/INSTALL.html You must have the 64-bit Oracle Instant Client Basic or Basic Light package in ~/lib or /usr/local/lib They can be downloaded from http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html </code></pre> <p>I have the library in ~/lib</p> <pre><code> atael-mac:Autonomous atael$ ls -l ~/lib total 0 lrwxr-xr-x 1 atael staff 34 Oct 24 08:35 libclntsh.dylib -> instantclient_18_1/libclntsh.dylib </code></pre> <pre><code> atael-mac:Autonomous atael$ which python /usr/local/opt/python/libexec/bin/python atael-mac:Autonomous atael$ </code></pre> <p>The code looks like this:</p> <pre><code> var oracledb = require('oracledb'); var dbConfig = require('./dbconfig.js'); oracledb.getConnection ( { user: dbConfig.dbuser, password: dbConfig.dbpassword, connectString: dbConfig.connectString }, function(err, connection) { if (err) { console.error(err.message); return; } connection.execute ( 'SELECT CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME FROM sh.customers WHERE CUST_ID = 5993', {}, { outFormat: oracledb.OBJECT }, function(err, result) { if (err) { console.error(err.message); doRelease(connection); return; } console.log('We are specifically looking for customer ID 5992'); console.log(result.rows); doRelease(connection); } ); } ); </code></pre> <p>Any idea what I might be doing wrong?</p> <p>Thanks Andy</p><p>该提问来源于开源项目:oracle/node-oracledb</p></div>
<div><p><em>Delete unnecessary parts of this template.</em></p> <h3>For security issues:</h3> <p>See https://www.oracle.com/support/assurance/vulnerability-remediation/reporting-security-vulnerabilities.html for how to report security issues.</p> <h3>For general questions:</h3> <p>Describe exactly what you did and what you want to happen. Use the questions at the bottom of this template as a guide.</p> <p>Use Markdown syntax, particularly for code blocks: see https://help.github.com/articles/basic-writing-and-formatting-syntax/#quoting-code</p> <h3>For installation issues:</h3> <p>Use a gist for screen output and logs: see https://gist.github.com/ <strong>Do not paste long output into this issue</strong></p> <p>Review your output and logs. <strong>Google any errors</strong> Try to install in a different way. Try some potential solutions.</p> <p>Review the install instructions at http://cx-oracle.readthedocs.io/en/latest/installation.html</p> <h4>Answer the following questions:</h4> <ol><li> <p>What is your version of Python? Is it 32-bit or 64-bit? --> Python 3.7.3 (tags/v3.7.3:ef4ec6ed12, Mar 25 2019, 22:05:12) [MSC v.1916 64 bit (AMD64)] on win32</p> </li><li> <p>What is your cx_Oracle version? --> cx-oracle-7.1.3</p> </li><li> <p>What <em>exact</em> command caused the problem (e.g. what command did you try to install with)? Who were you logged in as? -->con = cx_Oracle.connect('USER/pass:port/SID')</p> </li><li> <p>What error(s) you are seeing?</p> </li><li> <p>What OS (and version) is Python executing on?</p> </li><li> <p>What is your version of the Oracle client (e.g. Instant Client)? How was it installed? Where is it installed? --> cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "The specified module could not be found". See https://oracle.github.io/odpi/doc/installation.html#windows for help</p> </li><li> <p>What is your Oracle Database version? --> Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production</p> </li><li> <p>What is the <code>PATH</code> environment variable (on Windows) or <code>LD_LIBRARY_PATH</code> (on Linux) set to? On macOS, what is in <code>~/lib</code>? -> C:\Oracle\instantclient_18_5\;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Windows\System32\OpenSSH\;C:\Users\A123846\AppData\Local\Microsoft\WindowsApps;;C:\Users\A123846\AppData\Local\Programs\Microsoft VS Code\bin</p> </li><li> <p>What Oracle environment variables did you set? How <em>exactly</em> did you set them? oracle_home --> C:\Oracle\instantclient_18_5\</p> </li><li> <p>Do you have a small, single Python script that immediately runs to show us the problem?</p> </li></ol> <p>import cx_Oracle import os import sys</p> <p>print(sys.version)</p> <h1>print(os.environ['ORACLE_HOME'])</h1> <p>print(os.environ['path'])</p> <p>con = cx_Oracle.connect('USER/pass:port/SID') print (con.version)</p> <p>con.close()</p><p>该提问来源于开源项目:oracle/python-cx_Oracle</p></div>
©️2020 CSDN 皮肤主题: 博客之星2020 设计师:CY__ 返回首页
实付 19.90元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值