A complete tutorial on how to operate oracle using python, and a pythonoracle tutorial

A complete tutorial on how to operate oracle using python, and a pythonoracle tutorial

1. connection object

Before operating the database, you must first establish a database connection.

There are several methods to connect.

>>>import cx_Oracle>>>db = cx_Oracle.connect('hr', 'hrpwd', 'localhost:1521/XE')>>>db1 = cx_Oracle.connect('hr/hrpwd@localhost:1521/XE')>>>dsn_tns = cx_Oracle.makedsn('localhost', 1521, 'XE')>>>print dsn_tns >>>print db.version10.2.0.1.0>>> versioning = db.version.split('.')>>> print versioning['10', '2', '0', '1', '0']>>> if versioning[0]=='10':... print "Running 10g"... elif versioning[0]=='9':... print "Running 9i"...Running 10g>>> print db.dsnlocalhost:1521/XE

2. cursor object

Using the database connection object's cursor () method, you can define any number of cursor objects. A simple program may use a cursor and reuse it, however, large projects use multiple different cursor.

>>>cursor= db.cursor()

Application logic usually needs to clearly differentiate each stage of data processing operations. This will help you better understand performance bottlenecks and code optimization.

These steps are as follows:

parse（optional）

You do not need to call this method because it is automatically executed in the execution phase to check whether the SQL statement is correct. If there is an error, a DatabaseError exception and the corresponding error message are thrown. For example, ''ora-00900: invalid SQL statement. ".

Executecx_Oracle.Cursor.execute(statement,[parameters], **keyword_parameters)

This method can receive a single parameter SQL, operate the database directly, or execute dynamic SQL by binding variables. parames or keyworparameters can be dictionary, sequence, or a set of keyword parameters.

cx_Oracle.Cursor.executemany(statement,parameters)

It is particularly useful for batch insertion to avoid inserting only one entry at a time;

Fetch(optional)

Only used for query, because DDL and DCL statements do not return results. If the cursor does not execute the query, an InterfaceError error is thrown.

cx_Oracle.Cursor.fetchall() 

Obtains all result sets and returns the ancestor list. If no valid row exists, an empty list is returned.

cx_Oracle.Cursor.fetchmany([rows_no]) 

Fetch the next rows_no data from the database

cx_Oracle.Cursor.fetchone() 

Retrieve a single ancestor from the database. If no valid data is returned, none is returned.

3. bind variables

Variable binding can improve the efficiency and avoid unnecessary compilation. parameters can be name parameters or location parameters. Try to bind names as much as possible.

>>>named_params = {'dept_id':50, 'sal':1000}>>>query1 = cursor.execute('SELECT * FROM employees WHERE department_id=:dept_idAND salary>:sal', named_params)>>> query2 = cursor.execute('SELECT * FROM employees WHERE department_id=:dept_idAND salary>:sal', dept_id=50, sal=1000)Whenusing named bind variables you can check the currently assigned ones using thebindnames() method of the cursor: >>> printcursor.bindnames() ['DEPT_ID', 'SAL']

4. Batch insert

A large number of insert operations, you can use python's batch insert function, you do not need to call insert multiple times separately, this can improve performance. See the following sample code.

5. Sample Code

'''Created on July 7, 2016 @ author: Tommy ''' import cx_Oracleclass Oracle (object): "" oracle db operator "def _ init _ (self, userName, password, host, instance): self. _ conn = cx_Oracle.connect ("% s/% s @ % s/% s" % (userName, password, host, instance) self. cursor = self. _ conn. cursor () def queryTitle (self, SQL, nameParams ={}): if len (nameParams)> 0: self.cursor.exe cute (SQL, nameParams) else: self.cursor.exe cute (SQL) colNames = [] for I in range (0, len (self. cursor. description): colNames. append (self. cursor. description [I] [0]) return colNames # query methods def queryAll (self, SQL): self.cursor.exe cute (SQL) return self. cursor. fetchall () def queryOne (self, SQL): self.cursor.exe cute (SQL) return self. cursor. fetchone () def queryBy (self, SQL, nameParams ={}): if len (nameParams)> 0: self.cursor.exe cute (SQL, nameParams) else: self.cursor.exe cute (SQL) return self. cursor. fetchall () def insertBatch (self, SQL, nameParams = []): "" batch insert much rows one time, use location parameter "self. cursor. prepare (SQL) self.cursor.exe cute.pdf (None, nameParams) self. commit () def commit (self): self. _ conn. commit () def _ del _ (self): if hasattr (self, 'cursor '): self. cursor. close () if hasattr (self, '_ conn'): self. _ conn. close () def test1 (): # SQL = "select user_name, user_real_name, to_char (create_date, 'yyyy-mm-dd ') create_date from sys_user where id = '000000' "" SQL = "select user_name, user_real_name, to_char (create_date, 'yyyy-mm-dd ') create_date from sys_user where id =: id "oraDb = Oracle ('test', 'java', '2017. 168.0.192 ', 'orcl') fields = oraDb. queryTitle (SQL, {'id': '000000'}) print (fields) print (oraDb. queryBy (SQL, {'id': '000000'}) def test2 (): oraDb = Oracle ('test', 'java', '123. 168.0.192 ', 'orcl') cursor = oraDb. cursor create_table = "create table python_modules (module_name VARCHAR2 (50) not null, file_path VARCHAR2 (300) not null)" from sys import modules cursor.exe cute (create_table) M = [] for m_name, m_info in modules. items (): try: M. append (m_name, m_info. _ file _) t AttributeError: pass SQL = "INSERT INTO python_modules (module_name, file_path) VALUES (: 1,: 2)" oraDb. insertBatch (SQL, M) cursor.exe cute ("SELECT COUNT (*) FROM python_modules") print (cursor. fetchone () print ('insert batch OK. ') cursor.exe cute ("drop table python_modules PURGE") test2 ()

The complete tutorial on oracle operations in this article is the full content shared by the editor. I hope to give you a reference and support for more.