python连接oracle

环境:
OS:windows
Python:3.6.8

1.下载cx-Oracle
https://pypi.org/project/cx-Oracle/6.4.1/#files

2.安装
pip.exe install E:\python_oracle\cx_Oracle-6.4.1-cp36-cp36m-win_amd64.whl

 

若是安装报错误,这里需要注意python是64位的还是32位的，找对应的安装包下载

C:\Users\Administrator>python
Python 3.6.8 (tags/v3.6.8:3c6b436a57, Dec 23 2018, 23:31:17) [MSC v.1916 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>>

 

 

ERROR: cx_Oracle-6.4.1-cp36-cp36m-win_amd64.whl is not a supported wheel on this platform.
可以使用如下命令安装
pip install cx_Oracle

3.测试
cmd命令行模式
import cx_Oracle
conn=cx_Oracle.connect('xxx/xxxx@192.168.1.7:1521/orcl')
curs=conn.cursor()
sql='select * from dual'
curs.execute(sql)
row=curs.fetchone()
print(row[0])

4.在JetBrains PyCharm工具上安装cx_oracle
file->settings...->Project:你的项目->点击+ ->输入cx-Oracle,选择后点击Install Package进行安装

5.写数据例子

#!/usr/bin/env python
#coding=utf-8
import os, json, urllib, datetime, shutil
import time
import cx_Oracle

gl_mysql_server="192.168.1.118"
gl_user_name="hxl"
gl_password= "mysql"
gl_db_name="db_test"

##create table tb_test
##(
##id number not null primary key,
##name1 varchar(32),
##name2 varchar(32),
##name3 varchar(32),
##name4 varchar(32),
##name5 varchar(32),
##name6 varchar(32),
##createtime date default sysdate,
##modifytime date default sysdate
##);

def insert_data():
    db =  cx_Oracle.connect('hxl','oracle','192.168.1.100:1521/slnngk',encoding="UTF-8")
    # 得到一个可以执行SQL语句的光标对象
    cursor = db.cursor()

    db.autocommit=False ## 关闭字自动提交

    for i in range(1, 11):
        str_i = str(i)
        insert_sql = "insert into tb_test(id,name1,name2,name3,name4,name5,name6) values ('%s','%s','%s','%s','%s','%s','%s')" %(str_i,"nameA"+str_i, "nameB"+str_i, "nameC"+str_i,"nameD"+str_i,"nameE"+str_i,"nameF"+str_i)
        update_sql="update tb_test set name1='%s' where id=%s" %("update"+str_i,i)
        delete_sql="delete from tb_test where id=%s" %(i)
        try:
            # 执行sql语句
            cursor.execute(insert_sql)
            # 提交到数据库执行
            if (i % 10000) == 0:
                db.commit()
        except Exception as err:
            # Rollback in case there is any error
            print("sql语句执行错误", err)
            db.rollback()
    db.commit()
    cursor.close()
    db.close()
    return 0

if __name__ == '__main__':
    print("开始时间:"+time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))
    l_flag = insert_data()
    print("结束时间:"+time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))

 

 

-- The End --