SqlAlchemy 2.0 中文文档（五十一）（3）-阿里云开发者社区

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

DBAPI

python-oracledb 的文档和下载信息（如果适用）可在此处获取：oracle.github.io/python-oracledb/

连接

连接字符串：

oracle+oracledb://user:pass@hostname:port[/dbname][?service_name=<service>[&key=value&key=value...]]

python-oracledb 是由 Oracle 发布的用于取代 cx_Oracle 驱动程序的驱动程序。它与 cx_Oracle 完全兼容，并且具有“thin”客户端模式（不需要依赖项）和“thick”模式（与 cx_Oracle 一样使用 Oracle 客户端接口）。

另请参阅

cx_Oracle - cx_Oracle 的所有说明也适用于 oracledb 驱动程序。

SQLAlchemy oracledb 方言在同一方言名称下提供了同步和异步实现。根据引擎的创建方式选择适当的版本：

使用 oracle+oracledb://... 调用 create_engine() 将自动选择同步版本，例如：

from sqlalchemy import create_engine
sync_engine = create_engine("oracle+oracledb://scott:tiger@localhost/?service_name=XEPDB1")

使用 oracle+oracledb://... 调用 create_async_engine() 将自动选择异步版本，例如：

from sqlalchemy.ext.asyncio import create_async_engine
asyncio_engine = create_async_engine("oracle+oracledb://scott:tiger@localhost/?service_name=XEPDB1")

也可以明确指定方言的 asyncio 版本，使用 oracledb_async 后缀，如：

from sqlalchemy.ext.asyncio import create_async_engine
asyncio_engine = create_async_engine("oracle+oracledb_async://scott:tiger@localhost/?service_name=XEPDB1")

2.0.25 版本中的新功能：增加了对 oracledb 的异步版本的支持。

Thick 模式支持

默认情况下，python-oracledb 以 thin 模式启动，不需要在系统中安装 Oracle 客户端库。python-oracledb 驱动程序还支持“thick”模式，行为类似于 cx_oracle，需要安装 Oracle 客户端接口（OCI）。

要启用此模式，用户可以手动调用 oracledb.init_oracle_client，或通过将参数 thick_mode=True 传递给 create_engine() 来启用。要向 init_oracle_client 传递自定义参数，如 lib_dir 路径，可以将字典传递给此参数，如下所示：

engine = sa.create_engine("oracle+oracledb://...", thick_mode={
    "lib_dir": "/path/to/oracle/client/lib", "driver_name": "my-app"
})

另请参阅

python-oracledb.readthedocs.io/en/latest/api_manual/module.html#oracledb.init_oracle_client

2.0.0 版本中的新功能：增加了对 oracledb 驱动程序的支持。

对 Oracle 数据库的支持。

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

支持的 Oracle 版本

支持类型	版本
在 CI 中进行完整测试	18c
普通支持	11+
尽力而为	9+

DBAPI 支持

提供以下方言/DBAPI 选项。请参阅各自的 DBAPI 部分获取连接信息。

cx-Oracle
python-oracledb

自动增量行为

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

指定 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 没有“autoincrement”功能，SQLAlchemy 依赖序列来生成这些值。对于旧版的 Oracle，必须始终明确指定序列以启用自动增量。这与大多数文档示例不同，后者假设使用的是具有自动增量功能的数据库。要指定序列，请使用传递给列构造函数的 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 构造函数，用于指定自动增量列的选项。

指定 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，以使用默认生成模式，将 GENERATED AS IDENTITY 渲染到 DDL 中。它还支持将 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 起更改：如果由于在 Oracle 安装中常见的 v$transaction 视图上的权限问题而无法读取默认隔离级别，则默认隔离级别硬编码为“READ COMMITTED”，这是 1.3.21 之前的行为。

另请参阅

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

标识符大小写

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

最大标识符长度

Oracle 在 Oracle Server 版本 12.2 之后更改了默认的最大标识符长度。在此版本之前，长度为 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 1.4 之前的版本的应用程序因此受到数据库迁移的影响，希望在较短长度生成的名称上“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 方言现在对所有包括 ORM 和传统 Query 内部在内的 Select.limit() 和 Select.offset() 使用中都使用 FETCH FIRST N ROW / OFFSET N ROWS。要强制使用基于窗口函数的传统行为，请在 create_engine() 中指定 enable_offset_fetch=False 方言参数。

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

在使用传统模式或者将带有 limit/offset 的 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、SQL Server 中用于 LIMIT/OFFSET 的新“编译后”绑定参数。

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、SQL Server 中用于 LIMIT/OFFSET 的新“编译后”绑定参数。

RETURNING 支持

Oracle 数据库完全支持对使用单个绑定参数集合（即cursor.execute()风格语句；SQLAlchemy 通常不支持 executemany 语句）调用的 INSERT、UPDATE 和 DELETE 语句的 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 子句，并且在 LEFT OUTER JOIN 的情况下使用 Oracle 的 (+) 运算符。
当使用 Unicode 时，不再生成 NVARCHAR2 和 NCLOB 数据类型的 DDL - 而是生成 VARCHAR2 和 CLOB。这是因为即使这些类型在 Oracle 8 上是可用的，但在 Oracle 8 上似乎无法正确工作。NVARCHAR 和 NCLOB 类型将始终生成 NVARCHAR2 和 NCLOB。

同义词/DBLINK 反射

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

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

当设置了此标志时，将会在 ALL_TABLES 视图中搜索给定的名称（例如上面的 some_table），而且还会在 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 表选项

CREATE TABLE 短语与 Oracle 一起支持以下选项，与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_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 来使用默认值（对于非唯一索引是所有列，对于唯一索引是除最后一列外的所有列）。

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

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

DBAPI

连接

Thick 模式支持

DBAPI 支持

自动增量行为

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

使用 SEQUENCE（所有 Oracle 版本）

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

使用 SEQUENCE（所有 Oracle 版本）

事务隔离级别 / 自动提交

标识符大小写

最大标识符长度

LIMIT/OFFSET/FETCH 支持

LIMIT / OFFSET 模拟的注意事项（当无法使用 fetch() 方法时）

LIMIT / OFFSET 仿真注意事项（当无法使用 fetch()方法时）

RETURNING 支持

ON UPDATE CASCADE

Oracle 8 兼容性

同义词/DBLINK 反射

约束反射

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

日期时间兼容性

Oracle 表选项

Oracle 特定索引选项

位图索引

索引压缩

位图索引

索引压缩

热门文章

最新文章

相关电子书