Python Installation cx_Oracle and Operation Data Test Summary

Posted by jmarais on Sun, 13 Oct 2019 07:41:59 +0200

Here is a brief summary of Python Oracle database operation related knowledge. Just sort out the previous experiments and notes. The test server here is CentOS Linux release 7.5. A machine for personal experimentation, testing and data acquisition.

1: Install cx_Oracle

Here's a brief introduction to cx_Oracle. The following paragraph is excerpted from a blog post by the author of cx_Oracle (Anthony Tuininga), for more information. https://www.oracle.com/technetwork/cn/topics/tuininga-cx-oracle-086962-zhs.html

Cx_Oracle is a Python extension module, which implements query and update of Oracle database by using database API common to all database access modules. To use some features designed specifically for Oracle, extensions to several general database APIs have been added. The development of cx_Oracle has lasted for more than a decade, covering the needs of most customers who need to access Oracle in Python. In December 2008, a new major version addressed many of the limitations of earlier versions and added support for Python 3.0 and some of Oracle's new features.

Official document of cx_Oracle: https://cx-oracle.readthedocs.io/en/latest/ Official document introduces cx_Oracle from the aspects of architecture and features. A brief excerpt is as follows:

Architecture

Python programs call cx_Oracle functions. Internally cx_Oracle dynamically loads Oracle Client libraries to access Oracle Database.

Fig. 1 cx_Oracle Architecture
cx_Oracle is typically installed from PyPI using pip. The Oracle Client libraries need to be installed separately. The libraries can be obtained from an installation of Oracle Instant Client, from a full Oracle Client installation, or even from an Oracle Database installation (if Python is running on the same machine as the database).

Features

The cx_Oracle feature highlights are:

  • Easily installed from PyPI
  • Support for Python 2 and 3, and for multiple Oracle Database versions
  • Execution of SQL and PL/SQL statements
  • Extensive Oracle data type support, including large objects (CLOB and BLOB) and binding of SQL objects
  • Connection management, including connection pooling
  • Oracle Database High Availability features
  • Full use of Oracle Network Service infrastructure, including encrypted network traffic and security features
    A complete list of supported features can be seen here.

Here we introduce how to install cx_Oracle. There are many ways to install cx_Oracle components. I really can't think of the reason why you don't need pip installation, because it's so simple and convenient!

pip install cx_Oracle

[root@db-server ~]# pip install cx_Oracle
Collecting cx_Oracle
  Downloading https://files.pythonhosted.org/packages/ff/95/6c50d13da95de3e438e7d333dbd7b0946a0981c778e8153c73061b018a33/cx_Oracle-7.2.3-cp36-cp36m-manylinux1_x86_64.whl (731kB)
     |████████████████████████████████| 737kB 295kB/s 
Installing collected packages: cx-Oracle
Successfully installed cx-Oracle-7.2.3

After successful installation, test verification

[root@db-server ~]# python
Python 3.6.6 (default, Sep 20 2019, 08:20:38) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> 

2: Install Oracle Client

Here we plan to install Oracle Instant Client, you can go to the official website below to select the correct version. In this experiment, I chose instantclient-basic-linux.x64-11.2.0.4.0.zip

https://www.oracle.com/database/technologies/instant-client/downloads.html
https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

The Oracle Instant Client Zip downloaded here is simple to install as follows:

1: Unzip the installation package file to the specified directory

 mkdir -p /opt/oracle
 cd /opt/oracle/
 mv instantclient-basic-linux.x64-11.2.0.4.0.zip  /opt/oracle/
 unzip instantclient-basic-linux.x64-11.2.0.4.0.zip 

2: Install the libaio package on the operating system, otherwise you may encounter an error prompt "ImportError: libaio.so.1: cannot open shared object file: No such file or directory" when running the python script.

yum install libaio

Note: This is not necessary. In some cases, this error will be encountered.

3: Permanently add Instant Client to the runtime link path

 sudo sh -c "echo /opt/oracle/instantclient_11_2  > /etc/ld.so.conf.d/oracle-instantclient.conf"
 sudo  ldconfig #

Or set the environment variable LD_LIBRARY_PATH to the corresponding directory of the Instant Client version.

# echo $LD_LIBRARY_PATH
/usr/local/hadoop/lib/native

stay/etc/profile Join in
export LD_LIBRARY_PATH=/opt/oracle/instantclient_11_2:$LD_LIBRARY_PATH

# source /etc/profile
# echo $LD_LIBRARY_PATH
/opt/oracle/instantclient_11_2:/usr/local/hadoop/lib/native

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

Installation of other platforms (or RPM installation), as detailed in the official document https://oracle.github.io/odpi/doc/installation.html#linux, will not add to the list. RPM installation is also simple, as follows

#rpm -ivh oracle-instantclient19.3-basic-19.3.0.0.0-1.x86_64.rpm 
Preparing...                ########################################### [100%]
   1:oracle-instantclient19.########################################### [100%]

It should be noted that the installation must be version-consistent: cx_Oracle, Oracle Client, Python are best consistent, otherwise there will be many problems, such as the following (errors encountered in learning and testing)

- 1: cx_Oracle Report errors: cx_Oracle.DatabaseError: DPI-1050: Oracle Client library must be at version 11.2
- 2: cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "/lib64/libc.so.6: version `GLIBC_2.14' not found (required by /usr/lib/oracle/19.3/client64/lib/libclntsh.so)". See https://oracle.github.io/odpi/doc/installation.html#linux for help

Connection mode of ORACLE

There are many ways for cx_Oracle to connect to ORACLE database Connection Strings. Here's a brief overview:

First, we can look at the parameters of cx_Oracle.connect as follows:

cx_Oracle.connect(user=None, password=None, dsn=None, mode=cx_Oracle.DEFAULT_AUTH, handle=0, pool=None, threaded=False, events=False, cclass=None, purity=cx_Oracle.ATTR_PURITY_DEFAULT, newpassword=None, encoding=None, nencoding=None, edition=None, appcontext=[], tag=None, matchanytag=None, shardingkey=[], supershardingkey=[])

The parameters look quite dazzling. But it doesn't matter. In fact, you don't use much. Many parameters can be ignored selectively. Basically, the following are commonly used:

user username
Password password
dsn dsn
encoding

Easy Connect Syntax for Connection Strings

dbhost.example.com is the machine name, or the IP address of the database server. orclpdb1 is the service names. Note that this method does not use the early SID.

import cx_Oracle

# Obtain password string from a user prompt or environment variable
userpwd = ". . ." 

connection = cx_Oracle.connect(username, userpwd, "dbhost.example.com/orclpdb1", encoding="UTF-8")

If the database is not the default port number, you need to specify the port number in the connection string as follows:

connection = cx_Oracle.connect(username, userpwd, "dbhost.example.com:1984/orclpdb1",
        encoding="UTF-8")

Oracle Net Connect Descriptor Strings

Before introducing this connection string approach, we must first understand the function makedns, whose function is to create a dns object, as follows:

cx_Oracle.makedns
( host
, port
, sid=None
, service_name=None
, region=None
, sharding_key=None
, super_sharding_key=None)

dsn = cx_Oracle.makedsn("dbhost.example.com", 1521, service_name="orclpdb1")
connection = cx_Oracle.connect(username, userpwd, dsn, encoding="UTF-8")
In addition, you can create similar connection descriptor strings as follows:
dsn = """(DESCRIPTION=
             (FAILOVER=on)
             (ADDRESS_LIST=
               (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521))
               (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521)))
             (CONNECT_DATA=(SERVICE_NAME=sales.example.com)))"""

connection = cx_Oracle.connect(username, userpwd, dsn, encoding="UTF-8")

Net Service Names for Connection Strings

This way of connecting strings need not be introduced much. Basically, when you learn the introduction of ORACLE, you already know the knowledge of this aspect.

MyDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclpdb1)
    )
  )

connection = cx_Oracle.connect(username, userpwd, "MyDB", encoding="UTF-8")

JDBC and Oracle SQL Developer Connection Strings

The connection string syntax of cx_Oracle is different from that of Java JDBC and general Oracle SQL Developer. The connection string of general JDBC is as follows:

jdbc:oracle:thin:@hostname:port/service_name,

cx_Oracle is as follows:

connection = cx_Oracle.connect("hr", userpwd, "dbhost.example.com:1521/orclpdb1", encoding="UTF-8")

User name/password@Oracle server IP/Oracle SERVICE_NAME")
db_conn=cx_Oracle.connect('test/test123456@10.20.57.24/GSP')

Basic operation of ORACLE

Here are some simple examples of using cx_Oracle to manipulate databases

ORACLE Queries

Simple query
import cx_Oracle

db_conn=cx_Oracle.connect('test/test123456@10.20.57.24/gsp.localdomain')
db_cursor=db_conn.cursor()

sql_cmd='SELECT * FROM TEST.TEST'

db_cursor.execute(sql_cmd)


for row in  db_cursor:
    print(row)

db_cursor.close()
db_conn.close()

Note: The semicolon (;) should not be added to the SQL statement, otherwise ORA-00911: invalid character error will be reported:

If a semicolon is added to the above SQL script, the following error will occur:

sql_cmd='SELECT * FROM TEST.TEST;'



[root@MyDB python]# python orace_select.py 
Traceback (most recent call last):
  File "orace_select.py", line 8, in <module>
    db_cursor.execute(sql_cmd)
cx_Oracle.DatabaseError: ORA-00911: invalid character

Query with parameters

import cx_Oracle

db_conn=cx_Oracle.connect("test", "test123456", "10.20.57.24/gsp.localdomain",encoding="UTF-8")
db_cursor=db_conn.cursor()

sql_cmd='SELECT * FROM TEST WHERE ID=:ID'
sql_p_id={'ID':100}

db_cursor.execute(sql_cmd,sql_p_id)


for row in  db_cursor:
    print(row)

db_cursor.close()
db_conn.close()

If you want to get multiple rows of records, you can use the fetchall function. No use

import cx_Oracle

db_conn=cx_Oracle.connect("test", "test123456", "10.20.57.24/gsp.localdomain",encoding="UTF-8")
db_cursor=db_conn.cursor()

sql_cmd='SELECT * FROM TEST WHERE ID=:ID'
sql_p_id={'ID':100}

db_cursor.execute(sql_cmd,sql_p_id)

db_records= db_cursor.fetchall()
print(db_records)


db_cursor.close()
db_conn.close()

DML of ORACLE

In fact, the operation of INSERT, DELETE and UPDATE is basically not much worse. The following is a simple example of UPDATE, not from the details and classification (UPDATE, INSERT, DELETE).

#-*- coding: utf-8 -*-
import cx_Oracle

db_conn=cx_Oracle.connect("test", "test123456", "10.20.57.24/gsp.localdomain",encoding="UTF-8")
db_cursor = db_conn.cursor()

sql_cmd = 'INSERT INTO TEST.TEST(OWNER,OBJECT_ID,OBJECT_NAME)' \
          'VALUES(:OWNER, :OBJECT_ID, :OBJECT_NAME)'


db_cursor.execute(sql_cmd, ('TEST', 1, 'KERRY1'))
db_cursor.execute(sql_cmd, ('TEST', 2, 'KERRY2'))

db_conn.commit()

db_cursor.close()
db_conn.close()

Note that if you insert multiple rows of records, you will report the following error using execute:

cx_Oracle.NotSupportedError: Python value of type tuple not supported.

#-*- coding: utf-8 -*-
import cx_Oracle

db_conn=cx_Oracle.connect("test", "test123456", "10.20.57.24/gsp.localdomain",encoding="UTF-8")
db_cursor = db_conn.cursor()

sql_cmd = 'INSERT INTO TEST.TEST(OWNER,OBJECT_ID,OBJECT_NAME)' \
          'VALUES(:1, :2, :3)'

insert_record=[('TEST', 1, 'KERRY1'),('TEST', 2, 'KERRY2'),('TEST', 3, 'KERRY3')]
db_cursor.bindarraysize =3
db_cursor.setinputsizes(30,int, 30)
#db_cursor.execute(sql_cmd, ('TEST', 1, 'KERRY1'))
#db_cursor.execute(sql_cmd, ('TEST', 2, 'KERRY2'))
db_cursor.execute(sql_cmd, insert_record)

db_conn.commit()

db_cursor.close()
db_conn.close()

The correct approach is to use executemany:

#-*- coding: utf-8 -*-
import cx_Oracle

db_conn=cx_Oracle.connect("test", "test123456", "10.20.57.24/gsp.localdomain",encoding="UTF-8")
db_cursor = db_conn.cursor()

sql_cmd = 'INSERT INTO TEST.TEST(OWNER,OBJECT_ID,OBJECT_NAME)' \
          'VALUES(:1, :2, :3)'

insert_record=[('TEST', 1, 'KERRY1'),('TEST', 2, 'KERRY2'),('TEST', 3, 'KERRY3')]
db_cursor.bindarraysize =3
db_cursor.setinputsizes(30,int, 30)
db_cursor.executemany(sql_cmd, insert_record)

db_conn.commit()


db_cursor.close()
db_conn.close()

When I summarize, I find official documents. https://cx-oracle.readthedocs.io/en/latest/user_guide/sql_execution.html The examples are so detailed that there is no need to add anything to them.

Reference material:

https://cx-oracle.readthedocs.io/en/latest/index.html
https://github.com/oracle/python-cx_Oracle/tree/master/samples
https://blogs.oracle.com/oraclemagazine/perform-basic-crud-operations-using-cx-oracle-part-1
https://www.oracle.com/technetwork/articles/dsl/prez-python-queries-101587.html

Topics: Python Oracle Database encoding

Hot Topics

©2024 Programmer Think