SqlAlchemy 2.0 中文文档（五十一）（1）

原文：docs.sqlalchemy.org/en/20/contents.html

Oracle

docs.sqlalchemy.org/en/20/dialects/oracle.html

支持 Oracle 数据库。

下表总结了数据库发布版本的当前支持级别。

支持的 Oracle 版本

DBAPI 支持

下列方言/DBAPI 选项可用。请参考各个 DBAPI 部分获取连接信息。

• cx-Oracle
  
• python-oracledb
  

自增行为

包含整数主键的 SQLAlchemy Table 对象通常被假定具有“自动递增”行为，这意味着它们可以在插入时生成自己的主键值。在 Oracle 中，有两种可用的选项，即使用 IDENTITY 列（仅限 Oracle 12 及以上版本）或将 SEQUENCE 与列关联。

指定 GENERATED AS IDENTITY（Oracle 12 及以上）

从版本 12 开始，Oracle 可以使用 Identity 来指定自增行为：

t = Table('mytable', metadata,
    Column('id', Integer, Identity(start=3), primary_key=True),
    Column(...), ...
)

上述 Table 对象的 CREATE TABLE 如下：

CREATE  TABLE  mytable  (
  id  INTEGER  GENERATED  BY  DEFAULT  AS  IDENTITY  (START  WITH  3),
  ...,
  PRIMARY  KEY  (id)
)

Identity 对象支持许多选项来控制列的“自动递增”行为，例如起始值、递增值等。除了标准选项外，Oracle 还支持将 Identity.always 设置为 None 以使用默认生成模式，在 DDL 中呈现 GENERATED AS IDENTITY。它还支持将 Identity.on_null 设置为 True，以指定在与“BY DEFAULT”标识列一起使用时的 ON NULL。

使用 SEQUENCE（所有 Oracle 版本）

旧版 Oracle 没有“自动递增”功能，SQLAlchemy 依赖序列来生成这些值。对于旧版 Oracle，必须始终明确指定序列以启用自动递增。这与大多数文档示例不一致，后者假定使用支持自动递增的数据库。要指定序列，请使用传递给 Column 构造函数的 sqlalchemy.schema.Sequence 对象：

t = Table('mytable', metadata,
      Column('id', Integer, Sequence('id_seq', start=1), primary_key=True),
      Column(...), ...
)

使用表反射时也需要此步骤，即 autoload_with=engine：

t = Table('mytable', metadata,
      Column('id', Integer, Sequence('id_seq', start=1), primary_key=True),
      autoload_with=engine
)

从版本 1.4 起更改：在 Column 中添加了 Identity 构造，用于指定自增列的选项。

事务隔离级别 / 自动提交

Oracle 数据库支持“READ COMMITTED”和“SERIALIZABLE”隔离模式。cx_Oracle 方言还支持 AUTOCOMMIT 隔离级别。

若要使用每个连接的执行选项进行设置：

connection = engine.connect()
connection = connection.execution_options(
    isolation_level="AUTOCOMMIT"
)

对于 READ COMMITTED 和 SERIALIZABLE，Oracle 方言使用 ALTER SESSION 在会话级别设置级别，在连接返回到连接池时会恢复到默认设置。

isolation_level 的有效值包括：

• READ COMMITTED
  
• AUTOCOMMIT
  
• SERIALIZABLE
  

注意

Oracle 方言实现的 Connection.get_isolation_level() 方法必要地使用 Oracle LOCAL_TRANSACTION_ID 函数启动事务；否则通常无法读取任何级别。

此外，如果由于权限或其他原因导致 v$transaction 视图不可用，Connection.get_isolation_level() 方法将引发异常，这在 Oracle 安装中是常见的。

当方言首次连接到数据库时，cx_Oracle 方言尝试调用 Connection.get_isolation_level() 方法以获取“默认”隔离级别。这个默认级别是必要的，以便在使用 Connection.execution_options() 方法临时修改连接后，可以将级别重置为连接。在常见事件中，Connection.get_isolation_level() 方法由于 v$transaction 不可读以及任何其他与数据库相关的故障而引发异常时，级别被假定为“READ COMMITTED”。对于这种初始首次连接条件，不会发出警告，因为预计这是 Oracle 数据库的常见限制。

版本 1.3.16 中新增了对 cx_oracle 方言的 AUTOCOMMIT 支持，以及默认隔离级别的概念

版本 1.3.21 中新增了对 SERIALIZABLE 的支持，以及隔离级别的实时读取。

版本 1.3.22 中的更改：在默认隔离级别由于 v$transaction 视图的权限而无法读取的情况下（这在 Oracle 安装中很常见），默认隔离级别被硬编码为“READ COMMITTED”，这是 1.3.21 之前的行为。

请参阅

设置事务隔离级别，包括 DBAPI 自动提交

标识符大小写

在 Oracle 中，数据字典使用大写文本表示所有不区分大小写的标识符名称。另一方面，SQLAlchemy  将所有小写标识符名称视为不区分大小写。Oracle 方言在模式级通信（如表和索引的反射）期间将所有不区分大小写的标识符转换为这两种格式之一。在  SQLAlchemy 方面使用大写名称表示区分大小写的标识符，SQLAlchemy 将引用该名称 - 这将导致与从 Oracle  收到的数据字典数据不匹配，因此除非标识符名称真正被创建为区分大小写（即使用带引号的名称），否则在 SQLAlchemy 方面应使用所有小写名称。

最大标识符长度

截至 Oracle Server 版本 12.2，Oracle 已更改了默认的最大标识符长度。在此版本之前，长度为 30，在 12.2  及更高版本中，现在为 128。这一变化影响了 SQLAlchemy 在生成的 SQL 标签名称以及约束名称的区域，特别是在使用描述在  配置约束命名约定 中的约束命名约定特性时。

为了辅助这一变化和其他变化，Oracle 包括了“兼容性”版本的概念，这是一个与实际服务器版本无关的版本号，用于帮助迁移 Oracle 数据库，并可以在 Oracle 服务器内部配置。这个兼容性版本是使用查询 SELECT value FROM v$parameter WHERE name = 'compatible'; 检索的。当 SQLAlchemy Oracle 方言被要求确定默认的最大标识符长度时，它将在第一次连接时尝试使用此查询，以确定服务器的有效兼容性版本，该版本确定了服务器允许的最大标识符长度。如果表不可用，则使用服务器版本信息。

从 SQLAlchemy 1.4 开始，Oracle 方言的默认最大标识符长度为 128 个字符。在第一次连接时，检测到兼容性版本，如果小于 Oracle 版本 12.2，则将最大标识符长度更改为 30 个字符。在所有情况下，设置 create_engine.max_identifier_length 参数将绕过此更改，并且将使用给定的值：

engine = create_engine(
    "oracle+cx_oracle://scott:tiger@oracle122",
    max_identifier_length=30)

最大标识符长度在生成 SELECT 语句中的匿名化 SQL 标签时起作用，但更重要的是在根据命名约定生成约束名称时起作用。正是这一领域促使 SQLAlchemy 谨慎地更改了这个默认值。例如，以下命名约定根据标识符长度产生了两个非常不同的约束名称：

from sqlalchemy import Column
from sqlalchemy import Index
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import Table
from sqlalchemy.dialects import oracle
from sqlalchemy.schema import CreateIndex
m = MetaData(naming_convention={"ix": "ix_%(column_0N_name)s"})
t = Table(
    "t",
    m,
    Column("some_column_name_1", Integer),
    Column("some_column_name_2", Integer),
    Column("some_column_name_3", Integer),
)
ix = Index(
    None,
    t.c.some_column_name_1,
    t.c.some_column_name_2,
    t.c.some_column_name_3,
)
oracle_dialect = oracle.dialect(max_identifier_length=30)
print(CreateIndex(ix).compile(dialect=oracle_dialect))

使用标识符长度为 30 时，上述 CREATE INDEX 看起来像：

CREATE INDEX ix_some_column_name_1s_70cd ON t
(some_column_name_1, some_column_name_2, some_column_name_3)

但是，当长度为 128 时，它变成：

CREATE INDEX ix_some_column_name_1some_column_name_2some_column_name_3 ON t
(some_column_name_1, some_column_name_2, some_column_name_3)

在 Oracle 服务器版本 12.2 或更高版本上运行 SQLAlchemy 之前版本的应用程序因此可能受到以下情景的影响：希望对以较短长度生成的名称进行“DROP CONSTRAINT”的数据库迁移。当更改标识符长度而不首先调整索引或约束的名称时，此迁移将失败。因此，强烈建议这些应用程序使用 create_engine.max_identifier_length 来控制生成截断名称，并在更改此值时完全审查和测试所有数据库迁移，以确保已减轻此更改的影响。

从版本 1.4 开始：Oracle 的默认 max_identifier_length 是 128 个字符，如果检测到旧版 Oracle 服务器（兼容性版本 < 12.2），则在首次连接时调整为 30。

LIMIT/OFFSET/FETCH 支持

像 Select.limit() 和 Select.offset() 这样的方法使用 FETCH FIRST N ROW / OFFSET N ROWS 语法，假设 Oracle 12c 或以上版本，并假设 SELECT 语句不嵌套在 UNION 这样的复合语句中。使用 Select.fetch() 方法也可以直接使用此语法。

从版本 2.0 开始：Oracle 方言现在对所有 Select.limit() 和 Select.offset() 的用法，包括 ORM 和旧版 Query，都使用 FETCH FIRST N ROW / OFFSET N ROWS。要强制使用窗口函数来保留旧版行为，请将 enable_offset_fetch=False 方言参数传递给 create_engine()。

通过在任何 Oracle 版本上传递 enable_offset_fetch=False 给 create_engine()，可以禁用 FETCH FIRST / OFFSET 的使用，这将强制使用使用窗口函数的“传统”模式。在使用 Oracle 12c 之前的版本时，也会自动选择此模式。

在使用传统模式或将带有限制/偏移的 Select 语句嵌入到复合语句中时，将使用基于窗口函数的 LIMIT / OFFSET 的模拟方法，涉及使用 ROW_NUMBER 创建子查询，这种方法容易出现性能问题以及对于复杂语句的 SQL 构建问题。但是，所有 Oracle 版本都支持此方法。请参阅下面的说明。

关于 LIMIT / OFFSET 模拟（当无法使用 fetch() 方法时）

如果在 Oracle 12c 之前的版本中使用 Select.limit() 和 Select.offset()，或使用 ORM 中的 Query.limit() 和 Query.offset() 方法，以下注意事项适用：

• SQLAlchemy 目前使用 ROWNUM 来实现 LIMIT/OFFSET；确切的方法取自 blogs.oracle.com/oraclemagazine/on-rownum-and-limiting-results。
  
• 默认情况下不使用“FIRST_ROWS()”优化关键字。要启用此优化指令的使用，请在 create_engine() 中指定 optimize_limits=True。
  1.4 版更改：Oracle 方言使用“编译后”方案呈现限制/偏移整数值，直接在传递语句给游标执行之前呈现整数。use_binds_for_limits 标志不再起作用。
  另请参阅
  Oracle 中用于 LIMIT/OFFSET 的新“编译后”绑定参数，以及 SQL Server。
  

RETURNING 支持

Oracle 数据库完全支持对使用单个绑定参数集合调用的 INSERT、UPDATE 和 DELETE 语句进行 RETURNING（即cursor.execute()风格语句；SQLAlchemy 通常不支持在 executemany 语句中使用 RETURNING）。也可以返回多行。

2.0 版更改：Oracle 后端具有与其他后端相同的 RETURNING 完全支持。

ON UPDATE CASCADE

Oracle 没有本地的 ON UPDATE CASCADE 功能。可以在 asktom.oracle.com/tkyte/update_cascade/index.html 上找到基于触发器的解决方案。

在使用 SQLAlchemy ORM 时，ORM 有限的能力可以手动发出级联更新 - 使用“deferrable=True,  initially=’deferred’”关键字参数指定 ForeignKey 对象，并在每个 relationship() 上指定  “passive_updates=False”。

Oracle 8 兼容性

警告

SQLAlchemy 2.0 的 Oracle 8 兼容性状态未知。

当检测到 Oracle 8 时，方言会自动配置为以下行为：

• 使用 use_ansi 标志设置为 False。这会将所有 JOIN 词组转换为 WHERE 子句，并且在左外连接的情况下使用 Oracle 的 (+) 运算符。
  
• 当使用 Unicode 时，NVARCHAR2 和 NCLOB 数据类型不再生成 DDL - 而是生成 VARCHAR2 和 CLOB。这是因为即使这些类型可用，它们在 Oracle 8 上似乎无法正常工作。NVARCHAR 和 NCLOB 类型将始终生成 NVARCHAR2 和 NCLOB。
  

同义词/DBLINK 反射

在使用反射与表对象时，方言可以选择性地搜索由同义词指示的表，可以是在本地或远程模式或通过 DBLINK 访问，通过将标志 oracle_resolve_synonyms=True 作为关键字参数传递给 Table 构造函数：

some_table = Table('some_table', autoload_with=some_engine,
                            oracle_resolve_synonyms=True)

当设置此标志时，给定的名称（例如上面的 some_table）将不仅在 ALL_TABLES 视图中搜索，还将在 ALL_SYNONYMS 视图中搜索，以查看此名称是否实际上是另一个名称的同义词。如果找到了同义词并且引用了 DBLINK，则 Oracle 方言知道如何使用 DBLINK 语法定位表的信息（例如 @dblink）。

oracle_resolve_synonyms 在接受反射参数的任何地方都被接受，包括诸如 MetaData.reflect() 和 Inspector.get_columns() 之类的方法。

如果不使用同义词，则应禁用此标志。

约束反射

Oracle 方言可以返回有关表上的外键、唯一约束和 CHECK 约束以及索引的信息。

可以使用Inspector.get_foreign_keys()、Inspector.get_unique_constraints()、Inspector.get_check_constraints() 和Inspector.get_indexes()获取关于这些约束的原始信息。

自版本 1.2 更改：Oracle 方言现在可以反映唯一约束和检查约束。

在Table级别使用反射时，Table也将包括这些约束。

请注意以下注意事项：

• 当使用Inspector.get_check_constraints()方法时，Oracle 为指定“NOT NULL”的列构建一个特殊的“IS NOT NULL”约束。 默认情况下，此约束不会被返回；要包括“IS NOT NULL”约束，传递标志include_all=True：

from sqlalchemy import create_engine, inspect
engine = create_engine("oracle+cx_oracle://s:t@dsn")
inspector = inspect(engine)
all_check_constraints = inspector.get_check_constraints(
    "some_table", include_all=True)

• 在大多数情况下，当反映一个Table时，唯一约束将不作为UniqueConstraint对象可用，因为在大多数情况下，Oracle 使用唯一索引来镜像唯一约束（例外情况似乎是当两个或更多个唯一约束表示相同的列时）；Table将使用设置了unique=True标志的Index来代替这些。
  
• Oracle 为表的主键创建一个隐式索引；此索引被排除在所有索引结果之外。
  
• 对于索引反映的列列表，不会包括以 SYS_NC 开头的列名。
  

具有 SYSTEM/SYSAUX 表空间的表名称

Inspector.get_table_names() 和 Inspector.get_temp_table_names() 方法分别返回当前引擎的表名列表。这些方法也是在操作中进行反射的一部分，例如MetaData.reflect()。默认情况下，这些操作将从操作中排除SYSTEM和SYSAUX表空间。为了更改此设置，可以在引擎级别使用exclude_tablespaces参数更改默认的排除表空间列表：

# exclude SYSAUX and SOME_TABLESPACE, but not SYSTEM
e = create_engine(
  "oracle+cx_oracle://scott:tiger@xe",
  exclude_tablespaces=["SYSAUX", "SOME_TABLESPACE"])

日期时间兼容性

Oracle 没有名为DATETIME的数据类型，它只有DATE，实际上可以存储日期和时间值。因此，Oracle 方言提供了一个DATE类型，它是DateTime的子类。此类型没有特殊行为，仅作为此类型的“标记”存在；此外，当反映数据库列并且类型报告为DATE时，将使用支持时间的DATE类型。

Oracle 表选项

在与Table结构一起使用 Oracle 时，CREATE TABLE 语句支持以下选项：

• ON COMMIT：

Table(
    "some_table", metadata, ...,
    prefixes=['GLOBAL TEMPORARY'], oracle_on_commit='PRESERVE ROWS')

• COMPRESS：

Table('mytable', metadata, Column('data', String(32)),
     oracle_compress=True)
 Table('mytable', metadata, Column('data', String(32)),
     oracle_compress=6)
The ``oracle_compress`` parameter accepts either an integer compression
level, or ``True`` to use the default compression level.
```## Oracle 特定索引选项

位图索引

你可以指定oracle_bitmap参数来创建位图索引，而不是 B 树索引：

Index('my_index', my_table.c.data, oracle_bitmap=True)

位图索引不能是唯一的，也不能被压缩。SQLAlchemy 不会检查这些限制，只有数据库会。

索引压缩

Oracle 对包含大量重复值的索引有更有效的存储模式。使用oracle_compress参数来打开键压缩：

Index('my_index', my_table.c.data, oracle_compress=True)
Index('my_index', my_table.c.data1, my_table.c.data2, unique=True,
       oracle_compress=1)

oracle_compress参数可以接受一个整数，指定要压缩的前缀列数，或者True以使用默认值（非唯一索引的所有列，唯一索引除最后一列外的所有列）。

Oracle 数据类型

与所有 SQLAlchemy 方言一样，所有已知与 Oracle 有效的大写类型都可以从顶层方言导入，无论它们来自sqlalchemy.types还是来自本地方言：

from sqlalchemy.dialects.oracle import (
    BFILE,
    BLOB,
    CHAR,
    CLOB,
    DATE,
    DOUBLE_PRECISION,
    FLOAT,
    INTERVAL,
    LONG,
    NCLOB,
    NCHAR,
    NUMBER,
    NVARCHAR,
    NVARCHAR2,
    RAW,
    TIMESTAMP,
    VARCHAR,
    VARCHAR2,
)

1.2.19 版中新增：将NCHAR添加到由 Oracle 方言导出的数据类型列表中。

特定于 Oracle 的类型，或具有特定于 Oracle 的构造参数的类型如下：

class sqlalchemy.dialects.oracle.BFILE

成员

init()

类签名

类sqlalchemy.dialects.oracle.BFILE (sqlalchemy.types.LargeBinary)

method __init__(length: int | None = None)

从 LargeBinary 的 sqlalchemy.types.LargeBinary.__init__ 方法继承

构造一个 LargeBinary 类型。

参数:

length – 可选，用于 DDL 语句中的列长度，适用于接受长度的二进制类型，例如 MySQL 的 BLOB 类型。

class sqlalchemy.dialects.oracle.BINARY_DOUBLE

成员

init()

类签名

类sqlalchemy.dialects.oracle.BINARY_DOUBLE (sqlalchemy.types.Double)

method __init__(precision: int | None = None, asdecimal: bool = False, decimal_return_scale: int | None = None)

从 Float 的 sqlalchemy.types.Float.__init__ 方法继承

构造一个 Float。

参数:

• precision –
  用于 DDL CREATE TABLE 中的数值精度。后端应该尝试确保此精度指示通用Float数据类型的数字位数。
  注意
  对于 Oracle 后端，在渲染 DDL 时，不接受 Float.precision 参数，因为 Oracle 不支持将浮点精度指定为小数位数。相反，请使用 Oracle 特定的 FLOAT 数据类型，并指定 FLOAT.binary_precision 参数。这是 SQLAlchemy 版本 2.0 中的新功能。
  要创建一个与数据库无关的 Float，并为 Oracle 单独指定二进制精度，请使用 TypeEngine.with_variant() 如下所示：

from sqlalchemy import Column
from sqlalchemy import Float
from sqlalchemy.dialects import oracle
Column(
    "float_data",
    Float(5).with_variant(oracle.FLOAT(binary_precision=16), "oracle")
)

• asdecimal – 与 Numeric 相同的标志，但默认为 False。请注意，将此标志设置为 True 会导致浮点数转换。
  
• decimal_return_scale – 在将浮点数转换为 Python  十进制数时使用的默认精度。由于十进制不准确性，浮点值通常会更长，而大多数浮点数据库类型没有“精度”概念，因此默认情况下，浮点类型在转换时会查找前十位小数点。指定此值将覆盖该长度。请注意，MySQL  浮点类型包括“精度”，如果未另行指定，则将使用“精度”作为 decimal_return_scale 的默认值。
  

class sqlalchemy.dialects.oracle.BINARY_FLOAT

SqlAlchemy 2.0 中文文档（五十一）（2）https://developer.aliyun.com/article/1563196