python操作Oracle数据库

参考文章：python操作Oracle数据库

python操作oracle数据库

官方教程：Introduction to cx_Oracle — cx_Oracle 8.3.0-dev documentation

pycharm连接oracle cx_Oracle.DatabaseError: ORA-12154: TNS:could not resolve the connect identifier....

一、安装

1.pip安装

pip install cx_Oracle

2.安装Oracle客户端

1. 以下链接下载 Basic or Basic Light 类型的客户端 64-bit or 32-bit
2. 注意：
   1. Oracle Instant Client 19 will connect to Oracle Database 11.2 or later.（19版本的可以连接11.2及以后版本的Oracle）
   2. Windows 7 users: Note that Oracle 19c is not supported on Windows 7.（19版本不支持win7，所以win7用户建议使用18.5版本的客户端）

3.如提示缺少依赖，请根据以下安装对应依赖：

• 对于 Instant Client 19 安装VS 2017。
• 对于 Instant Client 18 或 12.2 安装VS 2013
• 对于 Instant Client 12.1 安装VS 2010
• 对于 Instant Client 11.2 安装VS 2005 64 位或VS 2005 32 位

二、配置客户端

1. 方式一：代码中使用Oracle客户端路径（推荐）
2. 将Oracle客户端放入系统环境变量 PATH 路径中

# 方式一：
import cx_Oracle
# 填入下载下来的Oracle客户端解压后的路径
cx_Oracle.init_oracle_client(lib_dir=r"C:\oracle\instantclient_19_11")

 三、连接示例

下面的 cx_Oracle.connect可能会报错，将代码替换为下面的方式即可，其中‘<given service name>’是填写oracle的实例名(数据库实例名,v$instance)。

connection = cx_Oracle.connect('user', 'pwd', cx_Oracle.makedsn( '<ip>', '1521',None,'<given service name>')) 

1.query.py

# query.py

import cx_Oracle

# Establish the database connection
connection = cx_Oracle.connect(user="username", password="password",
                               dsn="localhost:1521/orclpdb1")

# Obtain a cursor
cursor = connection.cursor()

# Data for binding
manager_id = 145
first_name = "Peter"

# Execute the query
sql = """SELECT first_name, last_name
         FROM employees
         WHERE manager_id = :mid AND first_name = :fn"""
cursor.execute(sql, mid=manager_id, fn=first_name)

# Loop over the result set
for row in cursor:
    print(row)
    
connection.close()

2.with 

with cx_Oracle.connect(user=user, password=password,
                       dsn="dbhost.example.com/orclpdb1",
                       encoding="UTF-8") as connection:
    cursor = connection.cursor()
    cursor.execute("insert into SomeTable values (:1, :2)",
                   (1, "Some string"))
    connection.commit()

3.连接池

# Create the session pool
pool = cx_Oracle.SessionPool(user="hr", password=userpwd,
                             dsn="dbhost.example.com/orclpdb1", min=2,
                             max=5, increment=1, encoding="UTF-8")

# Acquire a connection from the pool
connection = pool.acquire()

# Use the pooled connection
cursor = connection.cursor()
for result in cursor.execute("select * from mytab"):
    print(result)

# Release the connection to the pool
pool.release(connection)

# Close the pool
pool.close()
pool = cx_Oracle.SessionPool(user="hr", password=userpwd,
                             dsn="dbhost.example.com/orclpdb1", min=2,
                             max=5, increment=1,
                             getmode=cx_Oracle.SPOOL_ATTRVAL_WAIT,
                             encoding="UTF-8")

三、查询

查询使用 Cursor.execute() 方法。使用 迭代行、Cursor.fetchone(), Cursor.fetchmany() or Cursor.fetchall() 可以获取查询结果。

不推荐使用这种方式，影响性能且有安全风险：cur.execute("SELECT * FROM mytab WHERE mycol = '" + myvar + "'")，推荐使用如下方式： cur.execute("SELECT * FROM mytab WHERE mycol = :mybv", mybv=myvar).

cur = connection.cursor()
for row in cur.execute("select * from MyTable"):
    print(row)
cur = connection.cursor()
cur.execute("select * from MyTable")
while True:
    row = cur.fetchone()
    if row is None:
        break
    print(row)
cur = connection.cursor()
cur.execute("select * from MyTable")
num_rows = 10
while True:
    rows = cur.fetchmany(num_rows)
    if not rows:
        break
    for row in rows:
        print(row)
cur = connection.cursor()
cur.execute("select * from MyTable")
rows = cur.fetchall()
for row in rows:
    print(row)

四、示例

import cx_Oracle

# 填入下载下来的Oracle客户端解压后的路径
cx_Oracle.init_oracle_client(lib_dir="C:\programs\oracle\instantclient-basic-windows\instantclient_19_12")


# 普通方式
def normal_model():
    # Establish the database connection
    # connection = cx_Oracle.connect(user="YSAPOS", password="TWUSHAJSH",
    #                                dsn="jdbc:oracle:thin:@toosd-sss:1521/tasa",
    #                                encoding="UTF-8")

    # given service name: 数据库实例名,v$instance
    # connection = cx_Oracle.connect('user', 'pwd', cx_Oracle.makedsn( '<ip>', '1521',None,'<given service name>'))
    connection = cx_Oracle.connect('YSAPOS', 'TWUSHAJSH',
                                   cx_Oracle.makedsn('toosd-sss', '1521', None, 'tasa'))
    # Obtain a cursor
    cursor = connection.cursor()

    # Data for binding
    # manager_id = 145
    # first_name = "Peter"

    # Execute the query
    # sql = """SELECT first_name, last_name
    #          FROM employees
    #          WHERE manager_id = :mid AND first_name = :fn"""
    # cursor.execute(sql, mid=manager_id, fn=first_name)
    sql = """SELECT count(*) FROM NAEA_TABLE"""
    cursor.execute(sql)

    # # Loop over the result set
    for row in cursor:
        print(row)

    connection.close()


# 连接池方式
def pool_model():
    # pool = cx_Oracle.SessionPool(user="hr", password=userpwd,
    #                              dsn="dbhost.example.com/orclpdb1", min=2,
    #                              max=5, increment=1,
    #                              getmode=cx_Oracle.SPOOL_ATTRVAL_WAIT,
    #                              encoding="UTF-8")
    # Create the session pool
    pool = cx_Oracle.SessionPool(user="koop", password='snUsd',
                                 dsn="dsiud-sdjsk:1521/tupa", min=2,
                                 max=5, increment=1, encoding="UTF-8")

    # Acquire a connection from the pool
    connection = pool.acquire()

    # Use the pooled connection
    cursor = connection.cursor()
    for result in cursor.execute("SELECT count(*) FROM USYUA_ASY_TABLE"):
        print(result)

    # Release the connection to the pool
    pool.release(connection)

    # Close the pool
    pool.close()


if __name__ == '__main__':
    print('PyCharm')
    pool_model()