python----运用Oracle(cx_Oracle)

# coding:utf-8
importcx_Oracle
'''
#db=cx_Oracle.connect('vms/abc#2012aug@VMS.dbsit.sfdc.com.cn/vmssit')
username = "vms"
passward = "abc#2012aug"
dave = "VMS.dbsit.sfdc.com.cn/vmssit"
db = cx_Oracle.connect(username,passward,dave)
print db.version
db.close()
'''
defconDb(dbname='xiaodb',username='vms',pwd='abc#2012aug'):
ifdbname =='xiaodb':
username ='vms'
pwd ='abc#2012aug'
pave ='VMS.dbsit.sfdc.com.cn/vmssit'
db = cx_Oracle.connect(username,pwd,pave)
returndb

defselectDb(db,sql):
cursor = db.cursor()
cursor.execute(sql)
result = cursor.fetchall()
cursor.close()
returnresult

defdmlDb_N(db,sql):
cursor = db.cursor()
cursor.execute(sql)
cursor.close()
db.commit()

defdmlDb_P(db,sql,para):
cursor = db.cursor()
cursor.execute(sql,para)
cursor.close()
db.commit()

defddlDb(db,sql):
cursor = db.cursor()
cursor.execute(sql)
cursor.close()

defprintResult(rs):
forrowinrs:
printrow

print"=======connect database======="
db = conDb()
'''
print "=======create table======"
ddl='create table pytab1(id number,val varchar2(20))'
ddlDb(db,sql)
print "===============向pytb插入数据==============="
inst="insert into pytab1 values(0,'abcefg')"
dmlDb_N(db,inst)

sel='select * from pytab1'
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 pytab1 values(:id,:val)'
for bulk in dt:
dmlDb_P(db,inst,bulk)
sel='select * from pytab1'
rs=selectDb(db,sel)
printResult(rs)

print "===============向pytb增加一列==============="
sql= "alter table pytab1 add telephone NUMBER (20)"

print "============向pytab1增加多列============="
sql ="alter table pytab1 add(address VARCHAR2(15),email VARCHAR2(20),company VARCHAR2(20))"
dmlDb_N(db,sql)

print "=========修改一列=========="
sql = "alter table pytab1 modify ADDRESS VARCHAR2(15)"
dmlDb_N(db, sql)

print"=========修改多列=========="
sql = "alter table pytab1 modify(ADDRESS VARCHAR2(15),telephone NUMBER(11))"
dmlDb_N(db, sql)

print "========删除一列=========="
sql = "alter table pytab1 drop column ADDRESS"
ddlDb(db, sql)

print "=======删除多列=========="
sql = "alter table pytab1 drop(email,company)"
ddlDb(db, sql)

print "=======更新多个字段======"
sql = "update pytab1 set val = 'qilin' ,address ='cxq',telephone =13538383838 where id =1"
dmlDb_N(db,sql)

print "========删除一行数据======="
sql ="delete from pytab1 where id = 0"
dmlDb_N(db,sql)
'''
print"=======删除多行数据========="
sql ="delete from pytab1 where id in (2,4,6)"
dmlDb_N(db,sql)