代码改变世界

python连接oracle数据库

2019-01-09 10:52  改改~_~  阅读(737)  评论(0)  编辑  收藏  举报

遇到的问题如下，这个问题倒腾了好几天

以下是oracle服务器的版本信息,，所以相应的客户端也要用以下版本的

 

 1、下载文件如下两个文件

https://www.oracle.com/technetwork/topics/intel-macsoft-096467.html

（163邮箱-123456Aa）

 

2、创建目录如下图，并把以上下载的的两个文件都解压到图中的文件夹内

 

 

3、继续创建lib文件夹，见下图，并放三个文件进去

就这一步卡了好久，一直没有放3个文件到lib中所以一直提示开始时的错误wuwu

参考https://oracle.github.io/odpi/doc/installation.html#macos：

macOS

ODPI-C requires Oracle Client libraries, which are found in Oracle Instant Client for macOS.

On macOS, ODPI-C first searches for a library called “libclntsh.dylib” using the standard library search order. If this is not found, it will then search for “libclntsh.dylib.18.1”, “libclntsh.dylib.12.1” and then for “libclntsh.dylib.11.1” before returning an error.

Oracle Instant Client Zip

To run ODPI-C applications with Oracle Instant Client zip files:

  1. Download the 18, 12, or 11.2 “Basic” or “Basic Light” zip file from here. Choose either a 64-bit or 32-bit package, matching your application architecture. Most applications use 64-bit.

  2. Unzip the package into a single directory that is accessible to your application. For example:

    mkdir -p /opt/oracle
unzip instantclient-basic-macos.x64-12.2.0.1.0.zip

  3. Add links to $HOME/lib or /usr/local/lib to enable applications to find the library. For example:

    mkdir ~/lib
ln -s /opt/oracle/instantclient_12_2/libclntsh.dylib.12.1 ~/lib/

    Alternatively, copy the required OCI libraries. For example:

    mkdir ~/lib
cp /opt/oracle/instantclient_12_2/{libclntsh.dylib.12.1,libclntshcore.dylib.12.1,libons.dylib,libnnz12.dylib,libociei.dylib} ~/lib/

    For Instant Client 11.2, the OCI libraries must be copied. For example:

    mkdir ~/lib
cp /opt/oracle/instantclient_11_2/{libclntsh.dylib.11.1,libnnz11.dylib,libociei.dylib} ~/lib/

  4. If you intend to co-locate optional Oracle configuration files such as tnsnames.ora, sqlnet.ora or oraaccess.xml with Instant Client, then create a network/admin subdirectory, if it does not exist. For example:

    mkdir -p /opt/oracle/instantclient_12_2/network/admin

    This is the default Oracle configuration directory for applications linked with this Instant Client.

    Alternatively, Oracle configuration files can be put in another, accessible directory. Then set the environment variable TNS_ADMIN to that directory name.

Other Platforms

To run ODPI-C applications on other platforms (such as Solaris and AIX), follow the same general directions as for Linux Instant Client zip files or Local Database. Add the Oracle libraries to the appropriate library path variable, such as LD_LIBRARY_PATH on Solaris, or LIBPATH on AIX.

4、配置环境变量

 

- [x] localhost:~ ligaijiang$ open -e .bash_profile

export PATH=$PATH;


### oracle client

#export DYLD_LIBRARY_PATH=/Users/ligaijiang/Documents/instantclient:$PATH

#export ORACLE_HOME=/Users/ligaijiang/Documents/instantclient

#export SQLPATH=$ORACLE_HOME

#export PATH=$DYLD_LIBRARY_PATH:$ORACLE_HOME:SQLPATH:$PATH

export ORACLE_HOME=/Users/ligaijiang/oracle/instantclient_11_2
export DYLD_LIBRARY_PATH=$ORACLE_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME
export PATH=~/instantclient_11_2:$PATH
export PATH=$DYLD_LIBRARY_PATH:$ORACLE_HOME:SQLPATH:$PATH

#export ORACLE_HOME=/Users/ligaijiang/oracle/instantclient_11_2
#export DYLD_LIBRARY_PATH=$ORACLE_HOME
#export LD_LIBRARY_PATH=$ORACLE_HOME
export NLS_LANG=AMERICAN_AMERICA.UTF8
#export TNS_ADMIN=$HOME/etc
#export PATH=$PATH:$ORACLE_HOME
- [x] localhost:~ ligaijiang$ source .bash_profile

 至此oracle客户端基本安装完成，验证是否安装成功：

验证oracle客户端是否安装成功————————————————————————————
- [x] localhost:~ ligaijiang$  echo $ORACLE_HOME
echo /Users/ligaijiang/oracle/instantclient_11_2

验证sqlplus是否安装成功————————————————————————————
- [x]  localhost:~ ligaijiang$ sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 11 10:35:06 2019
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Enter user-name: finse

localhost:~ ligaijiang$ sqlplus finser/finser2015X@//10.139.39.43:1524/sjw2

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 14 11:29:15 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select t.* from CLEA_BANK_BIND_INFO t where phone_no = '18200717087';

5、安装cx_Oracle

https://pypi.org/project/cx_Oracle/

#files

解压缩后放后放python安装路径下的这里

 

卸载安装cx_Oracle模————————————————————————————
- [x] localhost:~ ligaijiang$ pip uninstall cx_Oracle
Uninstalling cx-Oracle-7.0.0:
  Would remove:
    /Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/cx_Oracle-7.0.0-py3.7-macosx-10.9-x86_64.egg
Proceed (y/n)? y
  Successfully uninstalled cx-Oracle-7.0.0
- [x] localhost:~ ligaijiang$ cd /Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/cx_Oracle-7.0.0
- [x] localhost:cx_Oracle-7.0.0 ligaijiang$ python setup.py build
- [x] localhost:cx_Oracle-7.0.0 ligaijiang$ python setup.py install 

 

6、解决乱码的问题

#代码前添加这两行
import os
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'