资讯专栏INFORMATION COLUMN

Oracle数据库Python连接方法

Big_fat_cat / 1046人阅读

Connecting Python to Oracle Cloud Database

There are three flavors of Oracle Databases hosted on Oracle Cloud Infrastructure:

Bare Mental, VM, and Exadata

Autonomous Data Warehouse (ADWC)

Autonomous Transaction Processing

Here we are refering "Oracle database" to Autonomous Data Warehouse, while all three should be similar in connectivity interface as they are Oracle-like.

For those of you who don"t have an Oracle cloud account, be reminded that you can apply for a free-trial account with 30-day trial period and $3000 HKD usable balance.
Before you begin

Before we discuss how to access Oracle database from Python, you should have already provisioned an Autonomous Data Warehouse instance. Refer to official doc for how to provision. Basically, you need to first construct a VCN (Virtual Cloud Network) under Networking tag, as later you"ll be prompted to bind your ADWC to one of the VCNs. In the startup dialog of provisioning the ADWC, choose storage and other parameters, set up your ADMIN account, etc.

Wait for the ADWC instance (in my case, it is called demo) to turn from Provising is in Available state, enter the details page, and enter DB Connection page. From the Download button, download the client credentials (a.k.a. your wallet**). The wallet is very important and you will need it to make client connections via any method (SQLDeveloper, Language driver, etc).

Install Oracle Client and Python driver

To make connections to Oracle database, you need an Oracle client. Oracle client can a full one that takes a lot of space, or a light-weight one, Instant Client. Because a full client is not available on macOS, I opt for the Instant Client. Download the zip file of proper DB version and OS, and place it in your workspace

cd ~/Downloads
unzip instantclient-basic-macos.x64-18.1.0.0.0.zip
cd instantclient_18_1

We will also need the Python driver cx_Oracle. I recommend using PyPI

python -m pip install cx_Oracle --upgrade

instantclient_18_1 directory has the dynamic libraries required by cx_Oracle, they must be on the Library Search Path in macOS dynamic linking process, available for the dlpen call from cx_Oracle.

…, the dynamic loader searches for the library in several locations until it finds it, in the following order:

$LD_LIBRARY_PATH

$DYLD_LIBRARY_PATH

The process’s working directory

$DYLD_FALLBACK_LIBRARY_PATH

Hence, we have several options, such as work in the instantclient_18_1 directory. Or, create a symbol link in DYLD_FALLBACK_LIBRARY_PATH which has a default value $HOME/lib;/usr/local/lib;/usr/lib. The second method is more flexible since we don"t have to stay in the client directory forever

ln -s ~/Downloads/instantclient_18_1/libclntsh.dylib ~/lib/
Configure Oracle database TNS

TNS stands for Transparent Network Substrate, an Oracle computer-networking technology mainly designed for connection to Oracle databases. We need to tell the Instant Client the TNS information of the database we would like to access.

cd ~/Downloads/instantclient_18_1/network/admin

If there is not network/admin inside, mkdir the subdirectories.

Unzip the wallet files to network/admin

unzip ~/Downloads/Wallet_demo.zip .
ls
# README           ewallet.p12      ojdbc.properties tnsnames.ora
# cwallet.sso      keystore.jks     sqlnet.ora       truststore.jks

There are a bunch of files in the wallet, but only three of them is required to make the connection (though you can always put all files inside network/admin). tnsnames.ora defines the namespace of where to find the databases (host, portnumber, service name) when cx_Oracle is asked to make a connection.

cat tnsnames.ora
demo_high = (description= (address=(protocol=tcps)(port=1522)(host=adb.ap-tokyo-1.oraclecloud.com))(connect_data=(service_name=lsd2p1z0t6mcrz2_demo_high.adwc.oraclecloud.com))(security=(ssl_server_cert_dn=
        "CN=adb.ap-tokyo-1.oraclecloud.com,OU=Oracle ADB TOKYO,O=Oracle Corporation,L=Redwood City,ST=California,C=US"))   )
# ...

There are other TNS entry points, like demo_medium and demo_low, they refer to different volumes of data transfer between the client and the database.

Get to the Python part

We are all set, lets create a python script with the following content:

import cx_Oracle

connection = cx_Oracle.connect("", "", "demo_high")
print("Database version:", connection.version)
connection.close()

If the script terminates with any error, you will see output

(base) ➜  Desktop python connection.py
Database version: 18.4.0.0.0

Then we successfully connect to the database.

Note: the third parameter of cx_Oracle.connect should one of the names defined in tnsnames.ora like demo_high, demo_HIGH (TNS name is case-insensitive), NOT the long connection string (something starting with adb.ap-tokyo-1.oraclecloud.com). If you mistakenly use the connection string as the third parameter, you are likely to end up with error TNS: connection closed.

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/18047.html

相关文章

  • python cx_Oracle基础使用方法

    摘要:使用的方法需要对格式进行控制,通过流获取这几个字段值不简洁个人观点。优点是能够使用的方法直接访问文件,不需要考虑打开关闭连接,并且通过流向文件中写入还挺好用的。要进行多个查询,个人建议使用完后将结果保留再关闭,多次查询重复该操作。 问题 使用python操作oracle数据库,获取表的某几个字段作为变量值使用。 使用Popen+sqlplus的方法需要对格式进行控制,通过流获取这几个字...

    edgardeng 评论0 收藏0
  • python cx_Oracle基础使用方法

    摘要:使用的方法需要对格式进行控制,通过流获取这几个字段值不简洁个人观点。优点是能够使用的方法直接访问文件,不需要考虑打开关闭连接,并且通过流向文件中写入还挺好用的。要进行多个查询,个人建议使用完后将结果保留再关闭,多次查询重复该操作。 问题 使用python操作oracle数据库,获取表的某几个字段作为变量值使用。 使用Popen+sqlplus的方法需要对格式进行控制,通过流获取这几个字...

    dack 评论0 收藏0
  • Oracle据库Python连接方法

    Connecting Python to Oracle Cloud Database There are three flavors of Oracle Databases hosted on Oracle Cloud Infrastructure: Bare Mental, VM, and Exadata Autonomous Data Warehouse (ADWC) Autonomous ...

    anonymoussf 评论0 收藏0
  • Oracle据库Python连接方法

    Connecting Python to Oracle Cloud Database There are three flavors of Oracle Databases hosted on Oracle Cloud Infrastructure: Bare Mental, VM, and Exadata Autonomous Data Warehouse (ADWC) Autonomous ...

    Jacendfeng 评论0 收藏0
  • Oracle据库Python连接方法

    Connecting Python to Oracle Cloud Database There are three flavors of Oracle Databases hosted on Oracle Cloud Infrastructure: Bare Mental, VM, and Exadata Autonomous Data Warehouse (ADWC) Autonomous ...

    Worktile 评论0 收藏0

发表评论

0条评论

Big_fat_cat

|高级讲师

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<