python3连接Oracle数据库

1、下载cx_Oracle，下载地址为：https://pypi.org/project/cx-Oracle/#files

下载时注意选择python版本以及对应的操作系统型号

 

2、安装cx_Oracle：

cmd到cx_Oracle路径

执行pip install xxx.whl，执行报如下报错，原因：对应cx_Oracle下载版本有误，重新下载一个版本

cx_Oracle-8.2.1-cp36-cp36m-win32.whl is not a supported wheel on this platform.

重新执行：pip install cx_Oracle-8.2.1-cp36-cp36m-win_amd64.whl，安装成功，提示“Successfully installed cx-Oracle-8.2.1”，见下图

3、使用cx_Oracle与Oracle数据库交互

用例1（查询）

import cx_Oracle
#连接数据库，下面括号里内容根据自己实际情况填写
conn = cx_Oracle.connect('tjzsyl/tjzsyl@192.168.129.221:1521/ora11g')
# 使用cursor()方法获取操作游标
cursor = conn.cursor()
#使用execute方法执行SQL语句
result=cursor.execute('Select * from ic89')
#使用fetchone()方法获取一条数据
#data=cursor.fetchone()
 
#获取所有数据
all_data=cursor.fetchall()
 
#获取部分数据，8条
#many_data=cursor.fetchmany(8)
 
print (all_data)
cursor.close()
conn.close()

用例2（插入数据）

def try_to_connect_and_insert():
    username="tjzsyl"
    userpwd="tjzsyl"
    host="192.168.129.221"
    port=1521
    dbname="ora11g"
    insertCount = 0
    totalCount = 1
    dsn=cx_Oracle.makedsn(host,port,dbname)
    try:
        connection=cx_Oracle.connect(username,userpwd,dsn)
        connection.autocommit=True
        sql="insert into bec7(BAZ498,BAZ002,BAA502,BAA504) VALUES(20210622001,20210622001,'PYTHON测试','PYTHON测试oracle插入数据')"
        cursor=connection.cursor()
        for i in range(totalCount-insertCount):
            insertCount=i
            cursor.execute(sql)
        print(totalCount)
        cursor.close()
        connection.close()
    except Exception as e:
        print(e)
        print(totalCount)
        
if __name__ == '__main__':
    #try_to_connect_and_select()
    try_to_connect_and_insert()

用例3(update)

def try_to_connect_and_update():
    username="tjzsyl"
    userpwd="tjzsyl"
    host="192.168.129.221"
    port=1521
    dbname="ora11g"
    insertCount = 0
    totalCount = 1
    dsn=cx_Oracle.makedsn(host,port,dbname)
    try:
        connection=cx_Oracle.connect(username,userpwd,dsn)
        connection.autocommit=True
        sql="update bec7 set baa502='python测试二版' where baz498=20210622001"
        cursor=connection.cursor()
        for i in range(totalCount-insertCount):
            insertCount=i
            cursor.execute(sql)
        print(totalCount)
        cursor.close()
        connection.close()
    except Exception as e:
        print(e)
        print(totalCount)