python操作oracle的完整教程分享

>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.version
10.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.dsn
localhost:1521/XE

>cursor= db.cursor()

parse（optional）

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

cx_Oracle.Cursor.executemany(statement,parameters)

Fetch(optional)

cx_Oracle.Cursor.fetchall() 

cx_Oracle.Cursor.fetchmany([rows_no]) 

cx_Oracle.Cursor.fetchone() 

>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']

'''
Created on 2016年7月7日
@author: Tommy
'''
import cx_Oracle

class 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.execute(sql,nameParams)
 else:
  self.cursor.execute(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.execute(sql)
 return self.cursor.fetchall()
 
 def queryOne(self,sql):
 self.cursor.execute(sql)
 return self.cursor.fetchone()
 
 def queryBy(self,sql,nameParams={}):
 if len(nameParams) > 0 :
  self.cursor.execute(sql,nameParams)
 else:
  self.cursor.execute(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.executemany(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 = '10000' """
 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','192.168.0.192','orcl')
 
 fields = oraDb.queryTitle(sql, {'id':'10000'})
 print(fields)
 
 print(oraDb.queryBy(sql, {'id':'10000'}))

def test2():
 oraDb = Oracle('test','java','192.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.execute(create_table)
 M = []
 for m_name, m_info in modules.items():
 try:
  M.append((m_name, m_info.__file__))
 except AttributeError:
  pass
 
 sql = "INSERT INTO python_modules(module_name, file_path) VALUES (:1, :2)"
 oraDb.insertBatch(sql,M)
 
 cursor.execute("SELECT COUNT(*) FROM python_modules")
 print(cursor.fetchone())
 print('insert batch ok.')
 cursor.execute("DROP TABLE python_modules PURGE")
test2()