search
HomeBackend DevelopmentPython TutorialDetailed 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!

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
What data types can be stored in a Python array?What data types can be stored in a Python array?Apr 27, 2025 am 12:11 AM

Pythonlistscanstoreanydatatype,arraymodulearraysstoreonetype,andNumPyarraysarefornumericalcomputations.1)Listsareversatilebutlessmemory-efficient.2)Arraymodulearraysarememory-efficientforhomogeneousdata.3)NumPyarraysareoptimizedforperformanceinscient

What happens if you try to store a value of the wrong data type in a Python array?What happens if you try to store a value of the wrong data type in a Python array?Apr 27, 2025 am 12:10 AM

WhenyouattempttostoreavalueofthewrongdatatypeinaPythonarray,you'llencounteraTypeError.Thisisduetothearraymodule'sstricttypeenforcement,whichrequiresallelementstobeofthesametypeasspecifiedbythetypecode.Forperformancereasons,arraysaremoreefficientthanl

Which is part of the Python standard library: lists or arrays?Which is part of the Python standard library: lists or arrays?Apr 27, 2025 am 12:03 AM

Pythonlistsarepartofthestandardlibrary,whilearraysarenot.Listsarebuilt-in,versatile,andusedforstoringcollections,whereasarraysareprovidedbythearraymoduleandlesscommonlyusedduetolimitedfunctionality.

What should you check if the script executes with the wrong Python version?What should you check if the script executes with the wrong Python version?Apr 27, 2025 am 12:01 AM

ThescriptisrunningwiththewrongPythonversionduetoincorrectdefaultinterpretersettings.Tofixthis:1)CheckthedefaultPythonversionusingpython--versionorpython3--version.2)Usevirtualenvironmentsbycreatingonewithpython3.9-mvenvmyenv,activatingit,andverifying

What are some common operations that can be performed on Python arrays?What are some common operations that can be performed on Python arrays?Apr 26, 2025 am 12:22 AM

Pythonarrayssupportvariousoperations:1)Slicingextractssubsets,2)Appending/Extendingaddselements,3)Insertingplaceselementsatspecificpositions,4)Removingdeleteselements,5)Sorting/Reversingchangesorder,and6)Listcomprehensionscreatenewlistsbasedonexistin

In what types of applications are NumPy arrays commonly used?In what types of applications are NumPy arrays commonly used?Apr 26, 2025 am 12:13 AM

NumPyarraysareessentialforapplicationsrequiringefficientnumericalcomputationsanddatamanipulation.Theyarecrucialindatascience,machinelearning,physics,engineering,andfinanceduetotheirabilitytohandlelarge-scaledataefficiently.Forexample,infinancialanaly

When would you choose to use an array over a list in Python?When would you choose to use an array over a list in Python?Apr 26, 2025 am 12:12 AM

Useanarray.arrayoveralistinPythonwhendealingwithhomogeneousdata,performance-criticalcode,orinterfacingwithCcode.1)HomogeneousData:Arrayssavememorywithtypedelements.2)Performance-CriticalCode:Arraysofferbetterperformancefornumericaloperations.3)Interf

Are all list operations supported by arrays, and vice versa? Why or why not?Are all list operations supported by arrays, and vice versa? Why or why not?Apr 26, 2025 am 12:05 AM

No,notalllistoperationsaresupportedbyarrays,andviceversa.1)Arraysdonotsupportdynamicoperationslikeappendorinsertwithoutresizing,whichimpactsperformance.2)Listsdonotguaranteeconstanttimecomplexityfordirectaccesslikearraysdo.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment