How to solve the problem of Python connecting to Oracle

Technical Framework

Development language: Python, database: oracle, third-party library: cx_Oracle (for connection between python and oracle), prettytable (used Display data in tabular output)

Development steps

1. Install cx_Oracle

pip install cx_Oracle

2. Write database operation class

Directly use the chatgpt provided In the code, because I only used the query method, I only checked without adding, deleting or modifying. In addition, considering that I needed to query multiple data at the same time, I modified it myself to implement a connection pool function.

import cx_Oracle
import queue

class OracleDatabase:
    # 构造函数，传入数据库连接参数
    def __init__(self, user, pwd, dsn, size):
        self.user = user
        self.pwd = pwd
        self.dsn = dsn
        ## 定义连接池
        self.size = size
        self.conn_queue = queue.Queue(maxsize=self.size)
        for i in range(self.size):
            self.conn_queue.put(self._create_connection())

    # 创建数据库连接
    def _create_connection(self):
        return cx_Oracle.connect(self.user, self.pwd, self.dsn)
  
    # 从连接池里面获取连接
    def _get_conn(self):
        conn = self.conn_queue.get()
        if conn is None:
            self._create_connection()
        return conn

    # 将连接put到连接池中
    def _put_conn(self, conn):
        self.conn_queue.put(conn)

    # 关闭所有连接
    def _close_conn(self):
        try:
            while True:
                conn = self.conn_queue.get_nowait()
                if conn:
                    conn.close()
        except queue.Empty:
            print(">>>>数据库连接全部关闭<<<<")
            pass 

    # 执行查询语句
    def query(self, sql, params=None):
        res = []
        conn = self._get_conn()
        cursor = conn.cursor()
        try:
            if params:
                cursor.execute(sql, params)
            else:
                cursor.execute(sql)
            rows = cursor.fetchall()
            for row in rows:
                res.append(row)
        except Exception as e:
            print(str(e))
        finally:
            cursor.close()
            self._put_conn(conn)
        return res

3. Enter the order number and execute the query

if __name__ == '__main__':
    user = "user_dba"
    pwd = "user_password"
    dsn = cx_Oracle.makedsn('0.0.0.0', '1521', service_name='s_demo_db')
    db = OracleDatabase(user, pwd, dsn, 2)
    cl_code = input("输入订单号: ").strip()
    
    print("数据信息展示:")
    sql_1 = """select *
		  from table_demo c
		  where c.cl_code = :cl_code"""

    results_1 = db.query(sql_1, [cl_code])
	print(results_1)
	# ......

4. Format printing

Install prettytable

pip install PrettyTable

Sample code

from prettytable import PrettyTable

## 接着第三部分的代码
tb_1 = PrettyTable(['**号', '**时间', '当前状态', '单号', '机构'])
for rs_1 in results_1:
	tb_1.add_row([rs_1[0], rs_1[1], rs_1[2], rs_1[3], rs_1[4]])
print(tb_1)

5. Printing effect

The usage effect is as follows: Paste the order number and press Enter to directly return the required information data (test data) below:

Problem Record

The first problem is an error when installing cx_Oracle:

ERROR: Could not build wheels for cx_Oracle, which is required to install pyproject.toml-based projects

Solution: Install Microsoft C Build Tool, Microsoft C Build Tool - Visual Studio, change the installation directory, and install according to the default options.

Error message

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

Solution: Copy oci, oraocci11 in the directory of the oracle client (see question 3 for client download), Paste the three DLLs of oraociei11 into the Lib/site-packages folder of your Paython directory.

Error message

cx_Oracle.DatabaseError: DPI-1072: the Oracle Client library version is unsupported

Download the oracle client, unzip and install it. Download address: oracle.github.io/odpi/doc/installation This problem occurred to me because my machine was originally installed with version 19.18 and replaced it with version 11.2 of the client. Follow the instructions in question 2 to install the three dll files. Copy it again to solve the problem.

Post-Optimization

• Put the sql statements into the configuration file and configure the table header to achieve free expansion of multiple queries .

• Call and execute through bat script to truly realize one-click query.

The above is the detailed content of How to solve the problem of Python connecting to Oracle. For more information, please follow other related articles on the PHP Chinese website!