【Python Oracle】使用cx_Oracle 进行数据库操作介绍

前面文章 《安装cx_Oracle 与使用》《cx_Oracle 连接oracle的简单介绍》

介绍了基本使用，本文介绍一下使用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')