python操作Oracle数据库

pip install cx_Oracle
​

• SQL statements should not contain a trailing semicolon (“;”) or forward slash (“/”). This will fail:

官方教程：[https://cx-oracle.readthedocs.io/en/latest/user_guide/introduction.html](https://cx-oracle.readthedocs.io/en/latest/user_guide/introduction.html)

安装

1. 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 位
4. 配置客户端
   1. 方式一：代码中使用Oracle客户端路径（推荐）
   2. 将Oracle客户端放入系统环境变量 PATH 路径中

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

连接示例

# 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()

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()

连接池

# 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)