Python simple methods for manipulating Oracle databases and encapsulating class instances

This article mainly describes the python operation of the Oracle database of simple methods and packaging classes, in conjunction with the instance form analysis of Python simple connection, query, close the basic operation of Oracle database, and give a python for the various operations of Oracle encapsulation class, A friend you need can refer to the following

The examples in this article describe the simple methods and encapsulation classes that Python operates with Oracle databases. Share to everyone for your reference, as follows:

It's a lot more convenient to have a job with Oracle recently and find a lot of places to do it with Python scripts, so you'll want to learn the basics of how Python works with Oracle.

Given the existence of a OracleClient netconfig for Oracle's use, I think it's not a simple matter to connect.

Sure enough, the Internet to find a few connection methods, and then according to the gourd but painted a half-day, but also not a scoop.

Method 1: User name, password, and listen as parameters respectively

Conn=cx_oracle.connect (' username ', ' password ', ' Database address: Database Port/sid ')

Based on a few articles I read and hints that I wrote a code error, I found that the configuration entry for the Python connection database should be related to the configuration file Tnsnames.ora for the Oracle client. But my configuration item doesn't have SID entries, and at first I didn't know what Sid was, and I wrote what I wrote on the Internet, so this method failed. Later figured out to add a SID to the configuration item, and then think about this thing configured my system is not to restart AH. So, let's look at other ways ....

Method 2: User name, password and listen together as a parameter

Conn=cx_oracle.connect (' username/password @ database address: Database Port/sid ')

This method is basically the same as the method one, the same ...

Method 3: Use TNS configuration information

Conn=cx_oracle.connect (' username ', ' password ', TNS)

The online code capture TNS is done with a function that still uses the SID, but. I already can use the Config item and no SID Ah, so use

TNS=CX_ORACLE.MAKEDSN (' Database address ', ' Database Port ', ' SID ')

Still not, but look at this TNS generation method is similar to the above two methods. But I found that the data generated when I entered a SID randomly was like this.

(Description= (address_list= (address= (protocol=tcp) (host=127.0.0.1) (port=1521))) (Connect_data= (SID=XE)))

However, the configuration items for my client are probably like this,

(Description= (address_list= (address= (protocol=tcp) (host=127.0.0.1) (port=1521)) (Connect_data= (SERVICE_NAME= KGDB)))

I rub, as if it looks the same, the type is also a string type, try to directly assign the configuration item in my file to TNS to try.

TNS = ' (description= (address_list= (address= (protocol=tcp) (host=127.0.0.1) (port=1521))) (Connect_data= (SERVICE_ NAME=KGDB)) ' conn = Cx_oracle.connect (' Nicker ', ' 123456 ', TNS)

Well. Success ~

Finally, paste a basic use method full code

#coding: Utf-8import cx_oracle# Create DATABASE Connection # Cx_oracle.connect (' username ', ' pwd ', ' ora TNS info ') # Oracle database TNS information, Locate the Plsql available configuration item from Tnsnames.ora and copy the configuration item directly to Ora_tns = ' (description= (address_list= (address= (protocol=tcp) (host= 127.0.0.1) (port=1521)) (Connect_data= (SERVICE_NAME=KGDB))) ' conn = Cx_oracle.connect (' Nicker ', ' 123456 ', Ora_tns) # Action cursor cursor = conn.cursor () # Execute Query cursor.execute ("SELECT * from Inst_info") # Get return information rs = Cursor.fetchall () # Output information for V in rs:< C1/>print v# close connection, release resource Cursor.close () Conn.close ()

It is important to observe the findings and to understand

Attach a class that encapsulates Oracle itself

#coding: Utf-8import cx_oracle# Package Class Cxoracle: The value of the "TNS" value Tnsnames.ora corresponding configuration item, such as: TNS = ' (description= (address_list = (address= (protocol=tcp) (host=10.16.18.23) (port=1521)) (Connect_data= (Service_name=mydb)) "Def __init__ (self , uname, Upwd,tns): self. _uname = uname self. _upwd = upwd self. _tns = TNS self. _conn = None self. _reco Nnect () def _reconnect (self): if isn't self._conn:self. _conn = Cx_oracle.connect (self. _uname, self. _upwd, SE Lf._tns) Else:pass def __del__ (self): if self. _conn:self. _conn. Close () self. _conn = None def-_newcursor (self): cur = self.      _conn.cursor () If Cur:return cur else:print "#Error # Get New cursor Failed." Return None def _delcursor (self, cur): if cur:cur. Close () # Check if SQL statements are allowed to execute def _permitedupdatesql (self, SQL) : RT = True lrsql = sql.  Lower () Sql_elems = [Lrsql.strip (). Split ()] # Update and delete have a minimum of four word entries if Len (Sql_elems) < 4:    RT = False # Update DELETE statement, determine first word, SQL without where statement not executed Elif sql_elems[0] in [' Update ', ' delete ']: If ' where ' isn't in s QL_ELEMS:RT = False return RT # Export result to file Def export (self, SQL, file_name, COLFG = ' | | '): RT = self. Query (SQL) if Rt:with open (file_name, ' a ') as Fd:for row in rt:ln_info = "for col In Row:ln_info + = str (col) + colfg ln_info + = ' \ n ' fd. Write (ln_info) # query def query (SE LF, SQL, nstart=0, nnum=-1): RT = [] # gets cursor cur = self. _newcursor () if not Cur:return RT # query to List cur. Execute (SQL) if (nstart==0) and (nnum==1): Rt. AP Pend (Cur.fetchone ()) Else:rs = cur. Fetchall () if nnum==-1:rt. Extend (Rs[nstart:]) else:rt. Extend (Rs[nstart:nstart +nnum]) # Release the cursor self. _delcursor (cur) return RT # update Def Exec (self, SQL): # gets cursor RT = None cur = self. _newcursor () if not Cur:returN RT # Determines whether SQL allows it to execute if not _permitedupdatesql (SQL): Return RT # Executes the statement RT = cur. Execute (SQL) # releases the cursor self. _delcursor (cur) return rt# class using the example TNS = ' (description= (address_list= (address= (Protoc OL=TCP) (host=10.16.17.46) (port=1521))) (Connect_data= (Service_name=mydb))) ' Ora = cxoracle (' Nicker ', ' 123456 ', TNS) # Export the result to a file rs = ora. Export ("SELECT * from org", ' 1.txt ') # Query results to list rs = Ora. Query ("SELECT * from org") Print rs# update data ora. Exec ("Update org set org_name= ' newnameforupdate ' where org_id=123456;")