Cx_oracle Operations Oracle Database

Oracle_sid parameter, this parameter is used in the operating system, it is a description of the database we want the default connection instance, for a machine with more than one instance of the case, to be modified before the Conn/as SYSDBA connection, because the default instance name.
In short, for example, your name is xiaoming, but you have a lot of nicknames. Your parents call you xiaoming, but friends call you a nickname.

Here your parents are examples of Oracle, Xiaoming is sid,service name is your nickname.

SIDs are used to differentiate between individual databases and service name for external links. They may be different, pay attention to which name you get, use it properly, or you may have an error with a remote connection to another database.
Preface

Python's self-contained modules have a lot of manipulating files. We can read the data from the file and process it and write it to the file. But for data management and analysis, the database is still professional. If Python can be combined with a database, it can combine two advantages to improve efficiency.

The Oracle database is used in the work, and Python has a module cx_oracle that can be connected to Oracle. To use cx_oracle, you need to download it first. 1. Download Cx_oracle

Python an official website PyPI, which has a wealth of modules. Cx_oracle can be downloaded in PyPI. Open PyPI URL https://pypi.python.org/pypi, search cx_oracle inside, you can find the module, its download address for http://cx-oracle.sourceforge.net/. Of course, can also be downloaded by other means.

You can use it after downloading. 2. Use Process

The simple use process is as follows:

1. Reference Module Cx_oracle
2. Connecting to the database
3. Get cursor
4. Use cursor for various operations
5. Close cursor
6. Close the connection

The following is a simple example:

Cx_oracle.connect ("username/password @ Oracle server ip/oracle service_name")

Get Oracle's SERVICE_NAME:

Su-oracle #切换至oracle用户

env | grep ORACLE #查询ORACLE的环境变量

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 ()                                              #关闭连接

#例子:
#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 uses

Python operates on a database with 2 main aspects: one is writing data, the other is reading data. Implementations of these 2 aspects can be implemented through SQL statements, or through stored procedures. So the main uses of cx_oracle are:

1. Execute SQL statement

2. Call stored procedures and functions. 4. Execute SQL statement

Executing the SQL statement is simple, as you can see from the example above. can be performed using Cursor.execute. Use Fetchone or fetchall to read the execution results.

The following example is an INSERT statement that uses a 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 ()                                                 

Variable bindings, like Oracle's dynamic SQL, are colons that are placeholders, that is, in code: 1, copying a variable is passing in a list, which is [' Nice '] in the code. How many variables, the list should correspond to how many values, the number should be consistent, otherwise it will be an error.

After execution, you can use a method of the connection Connect.commit () to commit the transaction. 5. Calling stored procedures and methods

Directly on the code:

--Stored procedure 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 calling stored procedure uses the Cursor.callproc method. In the stored procedure above, the value of a variable is out type, in Python, the variable of the out type is assigned a value that cannot be less than the length of the stored procedure.

--Function code:
CREATE OR REPLACE function F_demo (V1 VARCHAR2) return VARCHAR2 are
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 invoke the function is Cursor.callfunc. This differs from calling a stored procedure in that it requires specifying the type of transport parameter. End

Through cx_oracle, Python can communicate with Oracle databases so that they can complement each other's strengths.

For example, Python can be used as a data collection tool that can retrieve data from the Web, from a file, and then save that data to an Oracle database for further analysis of the data on the Oracle database.