1、下载格式为whl的cx_Oracle文件
文件名:cx_Oracle‑7.3.0‑cp37‑cp37m‑win_amd64.whl 注意对应cp版本(python版本)
下载地址:https://www.lfd.uci.edu/~gohlke/pythonlibs/#cx_oracle 下载到 D:\software
安装步骤:
1)打开anaconda下的Anaconda3 Prompt;
2)更改路径为刚下载的whl文件的文件夹地址:
3)开始安装:命令,pip install cx_Oracle-5.3+oci12c-cp36-cp36m-win_amd64.whl(conda install会提示无法找到)
附pip和conda 安装本地包命令
pip 安装本地包
pip install ~/Dowloads/torch-0.2.0.post3-cp35-cp35m-manylinux1_x86_64.whl
conda 安装本地包
conda install --use-local pytorch-0.4.0-py35_cuda8.0.61_cudnn7.1.2_1.tar.bz2
之后我们list命令执行一下
发现cx-oracle 已在learn环境下安装完成。回到pycharm写入连接oracle代码,报错:
cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "The specified module could not be found".
出现这个问题首先检查下你的客户端是否为64位,如果正常,则可能是缺少dll文件(如下图)。将上一篇下载的Oracle Instant Client文件夹下所有.dll 文件到D:\software\anaconda和D:\software\anaconda\Lib\site-packages文件夹下(你的anaconda 目录)
这时回到pycharm运行下面代码:
import cx_Oracle#connect参数 用户名、密码、host地址:端口、服务名
conn = cx_Oracle.connect('MOA/moa@17.37.63.87:1521/orcl')
print("连接成功")
curs = conn.cursor()
sql = 'select * from JJFX_SSGQ_KPI'
curs.execute(sql)
for result in curs:print(result)
curs.close()
conn.close()
成功连接并输出查询结果。
常见的增删改查代码示例:
import cx_Oracleconn = cx_Oracle.connect('MOA/moa@17.87.23.37:1521/orcl')
#或conn = cx_Oracle.connect('MOA','moa','17.87.23.37:1521/orcl')
#使用sysdba角色连接
# conn = cx_Oracle.connect('MOA','moa','17.87.23.37:1521/orcl',mode=cx_Oracle.SYSDBA)
curs = conn.cursor()
print("连接成功")
#1 查询
curs.execute('select * from JJFX_SSGQ_GQKPI')
for result in curs:print(result)
#2 查询全部fetchall
curs.execute('select * from JJFX_SSGQ_GQKPI')
print(curs.fetchall())
#3 查询单个
curs.execute('select * from JJFX_SSGQ_GQKPI')
while 1:res = curs.fetchone()if res == None:breakprint(res)
#4 查询多个
# 10个10个输出
curs.execute('select * from JJFX_SSGQ_GQKPI')
resCount=0
while 1:res = curs.fetchmany(10)if res == []:breakprint(res)resCount += 10
#5 参数查询
curs.execute('select * from JJFX_SSGQ_GQKPI where QYNAME = :1',('华数传媒',))
curs.execute('select * from JJFX_SSGQ_GQKPI where QYNAME = :qyname',qyname='华数传媒')
print(curs.fetchall())#6 插入
curs.execute('insert into JJFX_SSGQ_GQKPI values(2019,12,:1,:2,0,0,0,0,0,0)',('000156','华数传媒')) #sql中使用参数
conn.commit()#7 修改
curs.prepare('update JJFX_SSGQ_GQKPI set MONTH=:month where qycode=:qycode')
curs.execute(None,{'month':12,'qycode':'000156'})
#使用了prepare函数,在execute里面可以不传入sql语句,直接传入参数。
# 注意:这里的第一个参数必须为None
conn.commit()#8 执行多条语句
list1 = [('000411','英特集团'),('000607','华媒控股')]
# list1 = [{'qycode':'000411','qyname':'英特集团'},{'qycode':'000607','qyname':'华媒控股'}]
# curs.prepare('insert into JJFX_SSGQ_GQKPI values(2019,12,:qycode,:qyname,0,0,0,0,0,0)')
curs.prepare('insert into JJFX_SSGQ_GQKPI values(2019,12,:1,:2,:3,0,0,0,0,0,0)')
curs.executemany(None,list1)#第一个参数必须为None
conn.commit()#9 删除
sql = "DELETE FROM JJFX_SSGQ_GQKPI t WHERE t.qycode = :qycode "
curs.prepare(sql)
rown = curs.execute(None, {'qycode' : '000607'})
conn.commit()curs.close()
conn.close()