python cx_Oracle 连接 oracle

一、安装与使用

1.安装

pip3 install cx_Oracle

2.下载 oracle_client

3.使用

# -*- coding: utf-8 -*-
# !/usr/bin/env python
# @Time    : 2021/11/10 11:38 
# @Author  : 
# @Desc    : ***
# @File    : sync_oracle.py
# @Software: PyCharm
import cx_Oracle


class oracle_db(object):

    def __init__(self, ip="ip", user="user", password="password", port="1521", sid="sid"):
        ip = ip
        user = user
        pwd = password
        port = port
        sid = sid
        self.conn = cx_Oracle.connect(user + "/" + pwd + "@" + ip + ":" + port + "/" + sid)  # 连接数据库
        self.cur = self.conn.cursor()   # 连接游标

    def query_sql(self, v_sql):
        """执行查询sql"""
        try:
            self.cur.prepare(v_sql)
        except cx_Oracle.Error as e:
            print(e)
        self.cur.execute(v_sql)
        return self.cur.fetchall()

    def insert_sql(self, v_sql, date):
        try:
            self.cur.prepare(v_sql)
        except cx_Oracle.DatabaseError as e:
            print(e)
        self.cur.execute(v_sql,date)
        self.conn.commit()

    def delete_sql(self,v_sql):
        try:
            self.cur.parse(v_sql)
        except cx_Oracle.DatabaseError as e:
            print(e)
        self.cur.execute(v_sql)
        self.conn.commit()

    def close_db(self):
        """关闭数据库连接"""
        self.cur.close()
        self.conn.close()


if __name__ == '__main__':
    oracle_db = oracle_db()
    sql = "select * from  e.table"
    for i in oracle_db.query_sql(sql):
        print(i)

二、问题

1.dpi-1047

cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "The specified module could not be found". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help

下载oracle_client 把 oci.dll 放到python 目录下得 Lib–site-packages

2.dpi-1072

把oracle_client下的oci.dll oraocci*.dll ocrociei*.dll复制到lib-site-packages