Python操作Oracle数据库

#-*- coding:GBK -*-
#time module
import cx_Oracle

conn=cx_Oracle.connect("scott","Scott1314","127.0.0.1:1521/XE")#"scott/Scott1314@127.0.0.1:1521/XE"

print "DB's  version :",conn.version
print "DB's username :",conn.username
print "DB's password :",conn.password
print conn.dsn
cursor=conn.cursor()
param=["SALESMAN"]

#Oracle采用:PARAM匹配SQL语句，MySQL采用%s匹配SQL语句
sql="SELECT * FROM EMP where JOB=:JOB "
cursor.execute(sql,param);
rows=cursor.fetchall()
for row in rows :
    print row
cursor.close()
conn.close()

print "*"*30

conn=cx_Oracle.connect("scott","Scott1314","127.0.0.1:1521/XE")#"scott/Scott1314@127.0.0.1:1521/XE"
cursor=conn.cursor()
sql="SELECT * FROM EMP where JOB=:JOB AND ENAME LIKE :LIKENAME "
#cursor.execute(sql,JOB="SALESMAN",LIKENAME="%N%");
#cursor.execute(sql,("SALESMAN","%N%"));
#cursor.execute(sql,["SALESMAN","%N%"]);
cursor.execute(sql,{"JOB":"SALESMAN","LIKENAME":"%N%"});
#按位置传递时，变量名是任意的，变量名在这里只是起占位符的作用，所以要谨慎命名。
"""
sql="SELECT * FROM EMP where JOB=:JOB AND ENAME LIKE '%:ENAME%'"
cursor.execute(sql,JOB="SALESMAN",ENAME="N");
报错：DatabaseError: ORA-01036: 非法的变量名/编号
"""
rows=cursor.fetchall()
for row in rows :
    print row
cursor.close()
conn.close()

#绑定变量模式是数据库开发的核心原则，它们不仅使程序运行更快，更主要能够防止SQL非法注入攻击。
print "*******将模块名以及对应的文件路径信息保存到新创建的表中***********"
from sys import modules
import pprint
allModulesInfos = []
for module_info in modules.items() :
    #module是一个tuple：('sys', <module 'sys' (built-in)>)
    #('copy', <module 'copy' from 'C:\Python27\lib\copy.pyc'>)
    module=(module_info[1])
    try :
        type(module)
        allModulesInfos.append( (module_info[0],module.__file__) )
    except :
        pass
pprint.pprint(len(allModulesInfos))

conn = cx_Oracle.connect("scott","Scott1314","127.0.0.1:1521/XE")
cursor=conn.cursor()

deleteTable="DROP TABLE T_MODULE_INFO"

createTable="""CREATE TABLE T_MODULE_INFO
(
MODULE_NAME varchar2(50) ,
MODULE_FILE varchar2(200)
)
"""

queryTable="SELECT * FROM T_MODULE_INFO"
#删除表T_MODULE_INFO
try:
    cursor.execute(deleteTable)
    conn.commit()
except :
    pass
#创建表T_MODULE_INFO
cursor.execute(createTable)
conn.commit()
#插入数据到表T_MODULE_INFO
insertModuleInfo="INSERT INTO t_module_info VALUES(:MODULE_NAME,:MODULE_FILE)"
cursor.executemany(insertModuleInfo,allModulesInfos)
conn.commit()
#从表T_MODULE_INFO查询数据
rows=cursor.execute(queryTable);
for row in rows :
    print "The Result -- ",row

cursor.close()
conn.close()

print "************综合运用************"
import cx_Oracle
import sys

class EmpDept :
    def getConnection(self) :
        self.conn=cx_Oracle.connect("scott","Scott1314","127.0.0.1:1521/XE")
        self.cursor=self.conn.cursor()
        return self
   
    def closeQuitely(self) :
        print "Close the cursor !"
        self.cursor.close()
        print "Close the connnection !"
        self.conn.close()

    def swapDept(self , empno1 , empno2) :

        swapsql="SELECT EMPNO,DEPTNO FROM EMP WHERE EMPNO IN(:EMPNO1 ,:EMPNO2)"
        self.cursor.execute(swapsql,(empno1,empno2))
        self.edict=dict(self.cursor.fetchall())
        self.conn.begin()
        try :
            updatedept="UPDATE EMP SET DEPTNO=:DEPTNO WHERE EMPNO=:EMPNO"
            self.cursor.execute(updatedept,(self.edict[empno2],empno1))
            self.cursor.execute(updatedept,(self.edict[empno1],empno2))
            self.conn.commit()
        except :
            self.conn.rollback()
 

    def raiseSalary(self , empno , percent) :
        try :
            self.conn.begin()
            for oneEmp in empno :   
                sql="UPDATE EMP SET SAL=SAL*(1+:PER) WHERE EMPNO=:EMPNO"
                self.cursor.execute(sql,(percent,oneEmp))
                self.conn.commit()
            self.conn.commit()
        except :
            print "Cannot update many emp's salary ! Rollback it ! "
            self.conn.rollback()
            for info in sys.exc_info() :
                print info

    def queryAllEmps(self) :
        self.cursor.execute("SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP")
        emps = self.cursor.fetchall()
        return emps

empDept = EmpDept().getConnection()

empDept.swapDept(7369,7499)

empDept.raiseSalary([7369,7499],0.2)

allemps = empDept.queryAllEmps()
for oneemp in allemps :
    print oneemp

empDept.closeQuitely()

 

 附帮助信息：

"""
*********dir(cx_Oracle) begin************
['ATTR_PURITY_DEFAULT', 'ATTR_PURITY_NEW', 'ATTR_PURITY_SELF', 'BFILE','BINARY', 'BLOB', 'Binary', 'CLOB', 'CURSOR', 'Connection', 'Cursor','DATETIME', 'DBSHUTDOWN_ABORT', 'DBSHUTDOWN_FINAL', 'DBSHUTDOWN_IMMEDIATE','DBSHUTDOWN_TRANSACTIONAL', 'DBSHUTDOWN_TRANSACTIONAL_LOCAL', 'DataError',
'DatabaseError', 'Date', 'DateFromTicks', 'EVENT_DEREG', 'EVENT_NONE','EVENT_OBJCHANGE', 'EVENT_SHUTDOWN', 'EVENT_SHUTDOWN_ANY', 'EVENT_STARTUP','Error', 'FIXED_CHAR', 'FIXED_UNICODE', 'FNCODE_BINDBYNAME', 'FNCODE_BINDBYPOS','FNCODE_DEFINEBYPOS', 'FNCODE_STMTEXECUTE', 'FNCODE_STMTFETCH','FNCODE_STMTPREPARE', 'INTERVAL', 'IntegrityError', 'InterfaceError','InternalError', 'LOB', 'LONG_BINARY', 'LONG_STRING', 'LONG_UNICODE','NATIVE_FLOAT', 'NCLOB', 'NUMBER', 'NotSupportedError', 'OBJECT','OPCODE_ALLOPS', 'OPCODE_ALLROWS', 'OPCODE_ALTER', 'OPCODE_DELETE','OPCODE_DROP', 'OPCODE_INSERT', 'OPCODE_UPDATE', 'OperationalError','PRELIM_AUTH', 'ProgrammingError', 'ROWID', 'SPOOL_ATTRVAL_FORCEGET','SPOOL_ATTRVAL_NOWAIT', 'SPOOL_ATTRVAL_WAIT', 'STRING', 'SUBSCR_NAMESPACE_DBCHANGE', 'SUBSCR_PROTO_HTTP', 'SUBSCR_PROTO_MAIL', 'SUBSCR_PROTO_OCI','SUBSCR_PROTO_SERVER', 'SYSDBA', 'SYSOPER', 'SessionPool', 'TIMESTAMP','Time', 'TimeFromTicks', 'Timestamp', 'TimestampFromTicks', 'UCBTYPE_ENTRY','UCBTYPE_EXIT', 'UCBTYPE_REPLACE', 'UNICODE', 'Warning', '_Error', '__doc__','__file__', '__name__', '__package__', 'apilevel', 'buildtime', 'clientversion','connect', 'makedsn', 'paramstyle', 'threadsafety', 'version']
*********dir(cx_Oracle)  end ************
*********dir(connect) begin**************
['__class__', '__delattr__', '__doc__', '__enter__', '__exit__', '__format__','__getattribute__', '__hash__', '__init__', '__new__', '__reduce__','__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__','__subclasshook__', 'action', 'autocommit', 'begin', 'cancel', 'changepassword','client_identifier', 'clientinfo', 'close', 'commit', 'current_schema','cursor', 'dsn', 'encoding', 'inputtypehandler', 'maxBytesPerCharacter','module', 'nencoding', 'outputtypehandler', 'password', 'ping', 'prepare','register', 'rollback', 'shutdown', 'startup', 'stmtcachesize', 'subscribe','tnsentry', 'unregister', 'username', 'version']
*********dir(connect)  end ********************
*********dir(cursor) begin************
['__class__', '__delattr__', '__doc__', '__format__', '__getattribute__','__hash__', '__init__', '__iter__', '__new__', '__reduce__', '__reduce_ex__','__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__','arraysize', 'arrayvar', 'bindarraysize', 'bindnames', 'bindvars', 'callfunc','callproc', 'close', 'connection', 'description', 'execute', 'executemany','executemanyprepared', 'fetchall', 'fetchmany', 'fetchone', 'fetchraw','fetchvars', 'inputtypehandler', 'next', 'numbersAsStrings', 'outputtypehandler','parse', 'prepare', 'rowcount', 'rowfactory', 'setinputsizes', 'setoutputsize','statement', 'var']
*********dir(cursor)  end ************
"""