Detailed explanation of Python using cx_Oracle module to operate Oracle database

This article mainly introduces Python's use of the cx_Oracle module to operate the Oracle database. It analyzes in detail the downloading and installation of the cx_Oracle module and the connection to the Oracle database, executing SQL statements, stored procedures and other related operating techniques in the form of examples. Friends in need can refer to the following

The example in this article describes how Python uses the cx_Oracle module to operate the Oracle database. Share it with everyone for your reference, the details are as follows:

ORACLE_SID parameter, this parameter is used in the operating system, it describes the database instance we want to connect to by default, for a situation where there are multiple instances on a machine Next, you need to modify it before you can connect through conn/as sysdba, because the default instance name is used here.

In short, for example, your name is Xiao Ming, but you have many nicknames. Your parents call you Xiao Ming, but your friends all call you by your nickname.

Here your parents are the oracle instance, Xiao Ming is the sid, and service name is your nickname.

sid is used to distinguish each database from an instance, and service name is used for external links. They may be different, so pay attention to which name you get and use it appropriately, otherwise errors may occur when connecting to other databases remotely.

Preface

There are many modules that come with Python that manipulate files. We can read the data from the file and write the data to the file after processing. But for data management and analysis, databases are still more professional. If Python can be combined with a database, it can combine the advantages of both and improve efficiency.

The Oracle database is used at work. Python has a module cx_Oracle that can be connected to Oracle. To use cx_Oracle, you must download it first.

1. Download cx_Oracle

Python is an official website PyPI, which has a wealth of modules. cx_Oracle can be downloaded from PyPI. Open the PyPI URL https://pypi.python.org/pypi and search for cx_Oracle to find the module. Its download address is http://cx-oracle.sourceforge.net/. Of course, you can also download it through other ways.

You can use it after downloading.

2. Usage process

The simple usage process is as follows:

①.Reference module cx_Oracle
②.Connect to the database
③ .Get cursor
④.Use cursor for various operations
⑤.Close cursor
⑥.Close connection

The following is a simple example:

cx_Oracle.connect("Username/Password@Oracle Server IP/Oracle's SERVICE_NAME")

Get Oracle's SERVICE_NAME:

su - oracle #Switch to oracle user

env | grep ORACLE #Query ORACLE environment variables

ORACLE_SID=benguo # benguo is SERVICE_NAME

import cx_Oracle                     #引用模块cx_Oracle
conn=cx_Oracle.connect('load/123456@localhost/ora11g')  #连接数据库
c=conn.cursor()                      #获取cursor
x=c.execute('select sysdate from dual')          #使用cursor进行各种操作
x.fetchone()
c.close()                         #关闭cursor
conn.close()                       #关闭连接

Example:

#coding:utf-8
import cx_Oracle
def main():
  conn = cx_Oracle.connect("zebra/zebra@192.168.0.113/benguo")
  cur =conn.cursor()
  r= cur.execute("select * from userinfo")
  print
  print r.fetchone()
if __name__ == '__main__':
  main()

3. Several usages

Python's operations on the database mainly include two aspects: one is to write data, and the other is to read data. The implementation of these two aspects can be achieved through SQL statements or stored procedures. Therefore, the main uses of cx_Oracle are:

①. Execute SQL statements

②. Call stored procedures and functions.

4. Execute SQL statements

Executing SQL statements is very simple, as can be seen from the above example. Use cursor.execute to execute. Use fetchone or fetchall to read out the execution results.

The following example is an Insert statement using variable binding.

import cx_Oracle
conn=cx_Oracle.connect('load/123456@loaclhost/ora11g')
c=conn.cursor()
x=c.execute('insert into demo(v) values(:1)',['nice'])
conn.commit();
c.close()
conn.close()

Variable binding is the same as Oracle's dynamic SQL. It uses a colon as the placeholder, which is: 1 in the code. Copying the variable is to pass Enter a List, which is ['nice'] in the code. How many variables there are, how many values ​​should correspond to the list, and the numbers must be consistent, otherwise an error will be reported.

After execution, you can use one of the connection methods connect.commit() to commit the transaction.

5. Call stored procedures and methods

Directly enter the code:

--存储过程代码：
CREATE OR REPLACE PROCEDURE P_DEMO(V1 IN VARCHAR2, V2 OUT VARCHAR2) IS
BEGIN
  V2 := V1;
END;

#Python代码：
import cx_Oracle
conn=cx_Oracle.connect('load/123456@localhost/ora11g')
c=conn.cursor()
str1='nice'
str2='  '#需要有值，即len(str2)>=len(str1)
x=c.callproc('p_demo',[str1,str2])
print(str2)
c.close()
conn.close()

The cursor.callproc method is used to call the stored procedure. In the above stored procedure, the value of a variable is of type OUT. In Python, when assigning a value to a variable of type OUT, the length of the variable cannot be less than that in the stored procedure.

--函数代码：
CREATE OR REPLACE function F_DEMO(V1 VARCHAR2) RETURN VARCHAR2 IS
BEGIN
  RETURN V1;
END;

#Python代码：
import cx_Oracle
conn=cx_Oracle.connect('load/123456@localhost/ora11g')
c=conn.cursor()
str1='nice'
str2=c.callfunc('f_demo',cx_Oracle.STRING,[str1])
print(str2)
c.close()
conn.close()

The method used to call the function is cursor.callfunc. The difference between this and calling a stored procedure is that it requires specifying the type of transmission parameters.

End

Through cx_Oracle, Python and Oracle database can communicate with each other, so that the two can complement each other's strengths.

For example, Python can be used as a data collection tool. It can obtain data from the Web and files, and then save the data to the Oracle database for further analysis of the data on the Oracle database.

related suggestion:

Python uses the openpyxl library to modify the excel table data method

Python uses the email module to encode and decode emails

The above is the detailed content of Detailed explanation of Python using cx_Oracle module to operate Oracle database. For more information, please follow other related articles on the PHP Chinese website!