Shulou(Shulou.com)06/02 Report--

Editor to share with you how to use the CX_ORACLE library in ORACLE, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

1. Install the cx_Oracle package:

Pip install cx_Oracle

Current version 7.0

2. Three ways to create a database connection:

Method 1: write user name, password and monitor separately

Import cx_Oracle

Db=cx_Oracle.connect ('username/password@host/orcl')

Db.close ()

Method 2: write the user name, password and monitor together

Import cx_Oracle

Db=cx_Oracle.connect ('username','password','host/orcl')

Db.close ()

Method 3: configure monitoring and connection

Import cx_Oracle

Tns=cx_Oracle.makedsn ('host',1521,'orcl')

Db=cx_Oracle.connect ('username','password',tns)

Db.close ()

3. Establish cursor and execute SQL statements: query, update, insert, delete

Create a database connection, create a cursor cursor, and then execute the sql statement. When the execution is complete, close the cursor and close the database connection

After creating the connection, establish the cursor and execute the SQL statement

# encoding=utf-8

# exampl

Import cx_Oracle

Conn = cx_Oracle.connect ('system/oracle@192.0.2.7/orcl')

Cursor = conn.cursor ()

# two ways to execute statements, preprocessing and direct execution

# prepare the statement first, and then execute the None with the changed parameters. According to the principle that it is sufficient to prepare a statement when binding a variable, Oracle will handle it as in the previous example. Prepared statements can be executed any number of times.

Sql = "select * from dba_users where user_id =: dbv"

Cursor.prepare (sql)

Rs = cursor.execute (None, {'dbv':63})

Rsa = cursor.fetchall ()

Print (rsa)

# Direct execution

Cursor.execute ("select * from dba_users where user_id=62")

Row = cursor.fetchone ()

Print (row [0])

Cursor.close ()

Conn.close ()

Import cx_Oracle

# after creating a connection, establish cursor and execute SQL statement

Db=cx_Oracle.connect ('system','oracle','10.98.156.148/oral')

# db.close ()

Cr = db.cursor () # create cursor

Sql = 'select * from vested version'

Cr.execute (sql) # execute sql statement

# return all result sets fetchall at once

Rs = cr.fetchall ()

Print ("print all: (% s)"% rs)

For x in rs:

Print (x)

# return one row of fetchone at a time

Print ("fetchone")

Cr.execute (sql)

While (1):

Rs = cr.fetchone ()

If rs = = None:

Break

Print (rs)

# query using parameters

Pr = {'dbv':'61'}

Cr.execute ('select * from dba_users where user_id =: dbv',pr)

# here the parameters are treated as a dictionary

Rs = cr.fetchall ()

Print ("parameter print all: (% s)"% rs)

Cr.execute ('select * from dba_users where user_id =: dbv',dbv =' 61')

# write parameters directly here

Rs = cr.fetchall ()

Print ("parameter print all: (% s)"% rs)

Cr.close ()

Db.close ()

# submit commit after insert, update and delete operation

# query include:select

Def sqlSelect (sql,db):

Cr=db.cursor ()

Cr.execute (sql)

Rs=cr.fetchall ()

Cr.close ()

Return rs

# submit include:insert,update,delete after insert, update and delete operation

Def sqlDML (sql,db):

Cr=db.cursor ()

Cr.execute (sql)

Cr.close ()

Db.commit ()

# execute dml with parameters

Def sqlDML2 (sql,params,db):

Cr=db.cursor ()

Cr.execute (sql,params)

Cr.close ()

Db.commit ()

# examples of additions, deletions, modifications and queries

# 1. Single insert:

Sql = "INSERT INTO T_AUTOMONITOR_TMP (point_id) VALUES (: pointId)"

CursorObj.prepare (sql)

Rown = cursorObj.execute (None, {'pointId': pointId})

ConnectObj.commit ()

# 2. Multiple inserts:

Sql = "INSERT INTO T_AUTOMONITOR_TMP (point_id) VALUES (: pointId)"

CursorObj.prepare (sql)

Rown = cursorObj.executemany (None, recordList)

ConnectObj.commit ()

# Delete

Sql = "DELETE FROM T_AUTOMONITOR_TMP t WHERE t.point_id =: pointId"

CursorObj.prepare (sql)

Rown = cursorObj.execute (None, {'pointId': pointId})

ConnectObj.commit ()

# change

Sql = "UPDATE t_automonitor_other t\"

SET t.active ='2'\

WHERE t.active ='1'\

AND t.point_id =: pointId\

"

CursorObj.prepare (sql)

CursorObj.execute (None, {'pointId': pointId})

ConnectObj.commit ()

# check

Sql = "SELECT t.describ FROM t_automonitor_tmp t WHERE t.point_id =: pointId"

CursorObj.prepare (sql)

CursorObj.execute (None, {'pointId': pointId})

When binding, you can first prepare the statement and then execute None with the changed parameters. According to the principle that it is sufficient to prepare a statement when binding a variable, Oracle will handle it as in the previous example. Prepared statements can be executed any number of times.

R1 = cursor.execute ('SELECT * FROM locations WHERE country_id=:1 AND city=:2', (' US', 'Seattle'))

# simple tools for database operation

Class baseUtilsX ():

"" baseUtils "

Def _ init__ (self):

Self.connectObj = ""

Self.connCnt = 0

Self.cursorCnt = 0

Def initOracleConnect (self):

Oracle_tns = cx_Oracle.makedsn ('XXX.XXX.XXX.XXX', 1521 Magneto XX')

If self.connCnt = = 0:

Self.connectObj = cx_Oracle.connect ('oracleUserName',' password', oracle_tns)

Self.connCnt + = 1

Def getOracleConnect (self):

Self.initOracleConnect ()

Return self.connectObj

Def closeOracleConnect (self, connectObj):

ConnectObj.close ()

Self.connCnt-= 1

Def getOracleCursor (self):

Self.initOracleConnect ()

Self.cursorCnt + = 1

Return self.connectObj.cursor ()

Def closeOracleCursor (self, cursorObj):

CursorObj.close ()

Self.cursorCnt-= 1

If self.cursorCnt = = 0:

Print "will close conn"

Self.closeOracleConnect (self.connectObj)

Def selectFromDbTable (self, sql, argsDict):

# change the query result from tuple to list

QueryAnsList = []

SelectCursor = self.getOracleCursor ()

SelectCursor.prepare (sql)

QueryAns = selectCursor.execute (None, argsDict)

For ansItem in queryAns:

QueryAnsList.append (list (ansItem))

Self.closeOracleCursor (selectCursor)

Return queryAnsList

# excerpt oracle official document https://www.oracle.com/technetwork/cn/articles/dsl/mastering-oracle-python-1391323-zhs.html

# oracle date function

# use EXTRACT statement to extract the fields of year, month and day through SQL query

SELECT EXTRACT (YEAR FROM hire_date) FROM employees ORDER BY 1

Use Python's datetime.date, datetime.time, and datetime.datetime objects as binding variables to query the date

If you need to parse an existing string into a date (time) object, you can use the strptime () method of the datetime object.

> from datetime import datetime

> datetime.strptime ("2007-12-31 23:59:59", "Y-%m-%d H:%M:%S")

Datetime.datetime (2007, 12, 31, 23, 59, 59)

> import datetime

> > d = datetime.datetime.now ()

> print d

2007-03-03 16 purse 48,27.734000

> > print type (d)

> > print d.hour, d.minute, d.second

(16, 48, 27)

The EXTRACT () function is used to extract individual parts of the date / time, such as year, month, day, hour, minute, and so on.

Select sysdate

Extract (year from sysdate)

Extract (month from sysdate)

Extract (day from sysdate)

From dual s

SELECT EXTRACT (HOUR FROM TIMESTAMP '2010-01-10 11V 12V 13')

EXTRACT (minute FROM TIMESTAMP '2010-01-10 11V 12V 13')

EXTRACT (second FROM TIMESTAMP '2010-01-10 11V 12V 13')

FROM DUAL

# proficient in Oracle+Python, part 3: data parsing

Https://www.oracle.com/technetwork/cn/articles/dsl/prez-python-dataparsing-087750-zhs.html

The best advice for using regular expressions is to avoid using them as much as possible. Before embedding them in the code, make sure that no string method can do the same thing, because the string method is faster and does not incur the extra overhead of import and regular expression processing. Use dir () on the string object to see what is available.

The following example shows how regular expressions are viewed in a dynamic language like Python. Parse the tnsnames.ora file to create a simple connection string for each network alias (point the file () function to the location of your tnsnames.ora file):

> import re

> tnsnames = file (rasctnsnames.ora') .read ()

> easy_connects = {}

> tns_re = "^ (\ wayside?)\ s?=.*?HOST\ sstories =\ s? (. +?)\). *? PORT\ sboxes =\ s? (\ dflowers?)\). *? SERVICE_NAME\ sboxes =\ s? (. +?)\)"

> for match in re.finditer (tns_re, tnsnames, re.M+re.S):

... T = match.groups ()

... Easy_connects [t [0]] = "% s:%s/%s"% t [1:]

> print easy_connects

The output of this program on the Oracle Database XE default tnsnames.ora file is:

{'XE':' localhost:1521/XE'}

The above is all the contents of the article "how to use the CX_ORACLE Library in ORACLE". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.