【Python Oracle】使用cx_Oracle 進行資料庫操作介紹

楊奇龍發表於2011-11-24
PythonOracle資料庫
介紹了基本使用，本文介紹一下使用python 對oracle 資料庫進行常見操作的介紹
oracle@rac3:/home/oracle/python>cat sqlops.py 
#!/usr/bin/python
# -*- coding: utf-8 -*- 
import cx_Oracle
import sys
import urllib
import os
# function #
def conndb(dbname='yangdb',username='yang',pwd='yang'):
##建立資料庫連線，設定預設值   
   if dbname == 'yangdb':
        username = "yang" 
        pwd = "yang" 
        dsn=cx_Oracle.makedsn('127.0.0.1','1523','yangdb')
        db=cx_Oracle.connect(username,pwd,dsn)
   return db

def SelectDB(db,sql):
##select 查詢
    cursor = db.cursor() 
    cursor.execute(sql)
    result=cursor.fetchall()
    cursor.close()
    return result
    
def DMLDB_N(db,sql):
##插入，更新，刪除
   cursor = db.cursor()
   cursor.execute(sql)
   cursor.close()
   db.commit()

def DMLDB_P(db,sql,para):
##插入，更新，刪除
   cursor = db.cursor()
   cursor.execute(sql,para)
   cursor.close()
   db.commit()

def DDLDB(db,sql):
#DDL 語句
   cursor=db.cursor()
   cursor.execute(sql)
   cursor.close()

def printResult(rs):
   for row in rs: 
        print row 

print "============== 連線資料庫 =================="
db=conndb()
print "===============建立表 pytb=================="
ddl='create table pytab(id number,val varchar2(20))'
DDLDB(db,ddl)

sel='select * from pytab'
rs=SelectDB(db,sel)

printResult(rs)
print "===============向pytb插入資料==============="
inst='insert into pytab values(0,\'dba\')'
DMLDB_N(db,inst)
sel='select * from pytab'
rs=SelectDB(db,sel)
printResult(rs)
print "=======使用引數,向pytb插入資料============="
dt=[{'id':1,'val':'qilong'},
    {'id':2,'val':'xxq'},   
    {'id':3,'val':'aliyun'},    
    {'id':4,'val':'aliyundba'},   
    {'id':5,'val':'aliyunsa'},
    {'id':6,'val':'aliyunidc'},
    {'id':7,'val':'aliyunnework'},
    {'id':8,'val':'alibaba'},
    {'id':9,'val':'taobao'},    
    {'id':10,'val':'alipay'},
    {'id':11,'val':'tech'},
    {'id':12,'val':'oracle'},
    {'id':13,'val':'IBM'}    
   ]
inst='insert into pytab values(:id,:val)'
for bulk in dt:
   DMLDB_P(db,inst,bulk)
sel='select * from pytab'
rs=SelectDB(db,sel)
printResult(rs)
print "===============刪除表 pytb 資料=============="
delt='delete from pytab where id=1'
DMLDB_N(db,delt)
print "===============查詢表 pytb 資料=============="
sel='select * from pytab'
rs=SelectDB(db,sel)

printResult(rs)
結果顯示：
oracle@rac3:/home/oracle/python>python sqlops.py 
============== 連線資料庫 ==================
===============建立表 pytb==================
===============向pytb插入資料===============
(0, 'dba')
=======使用引數，向pytb插入資料=============
(0, 'dba')
(1, 'qilong')
(2, 'xxq')
(3, 'aliyun')
(4, 'aliyundba')
(5, 'aliyunsa')
(6, 'aliyunidc')
(7, 'aliyunnework')
(8, 'alibaba')
(9, 'taobao')
(10, 'alipay')
(11, 'tech')
(12, 'oracle')
(13, 'IBM')
===============刪除表 pytb 資料==============
===============查詢表 pytb 資料==============
(0, 'dba')
(2, 'xxq')
(3, 'aliyun')
(4, 'aliyundba')
(5, 'aliyunsa')
(6, 'aliyunidc')
(7, 'aliyunnework')
(8, 'alibaba')
(9, 'taobao')
(10, 'alipay')
(11, 'tech')
(12, 'oracle')
(13, 'IBM')

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-711919/，如需轉載，請註明出處，否則將追究法律責任。

相關文章