SQL¶

Odo 通过 SQLAlchemy 与 SQL 数据库交互。因此，odo 支持所有 SQLAlchemy 支持的数据库。通过第三方扩展，SQLAlchemy 支持大多数数据库。

警告

将 NumPy 数组等类数组对象放入数据库时，您必须以 Record datashape 的形式提供列名。

注意

没有列名，将数组放入数据库表是没有意义的，因为没有命名列的数据库表也没有意义。无法将没有命名列的数组放入数据库是故意的。

这是一个失败的例子

>>> import numpy as np
>>> from odo import odo
>>> x = np.zeros((10, 2))
>>> t = odo(x, 'sqlite:///db.db::x')  # this will NOT work
Traceback (most recent call last):
    ...
TypeError: dshape measure must be a record type e.g., "{a: int64, b: int64}". Input measure is ctype("float64")

这里是替代做法

>>> t = odo(x, 'sqlite:///db.db::x',  # works because columns are named
>>> ...     dshape='var * {a: float64, b: float64}')

URI¶

SQL URI 的简单和复杂示例

postgresql://::accounts
postgresql://username:password@54.252.14.53:10000/default::accounts

SQL URI 包含以下部分

方言协议: postgresql://
可选的认证信息: username:password@
带可选端口的主机名或网络位置: 54.252.14.53:10000
可选的数据库/模式名称: /default
带 :: 分隔符的表名: ::accounts

针对数据库执行 Odo¶

SQLAlchemy 允许对象绑定到特定的数据库连接。这被称为对象的“绑定”或对象是“已绑定”的。

默认情况下，odo 期望使用已绑定的 SQLAlchemy 对象或表的 URI。

例如，在使用 SQLAlchemy 对象时，必须确保将已绑定的元数据传递给表的构造函数。

>>> import sqlalchemy as sa
>>> sa.MetaData()
>>> tbl = sa.Table(
...     'tbl',
...     metadata,
...     sa.Column('a', sa.Integer, primary_key=True),
... )
>>> odo([[1], [2], [3]], tbl, dshape='var * {a: int}')  # this will NOT work
Traceback (most recent call last):
     ...
UnboundExecutionError: Table object 'tbl' is not bound to an Engine or Connection.  Execution can not proceed without a database to execute against.

我们有两种将元数据绑定到对象的方法：我们可以显式绑定我们的表，或者将其作为关键字参数传递给 odo。

这是一个使用已绑定元数据构造表的示例

>>> import sqlalchemy as sa
>>> metadata = sa.MetaData(bind='sqlite:///db.db')  # NOTE: pass the uri to the db here
>>> tbl = sa.Table(
...     'tbl',
...     metadata,
...     sa.Column('a', sa.Integer, primary_key=True),
... )
>>> odo([[1], [2], [3]], tbl)  # this know knows where to field the table.

这是一个将绑定传递给 odo 的示例

>>> import sqlalchemy as sa
>>> sa.MetaData()
>>> tbl = sa.Table(
...     'tbl',
...     metadata,
...     sa.Column('a', sa.Integer, primary_key=True),
... )
>>> bind = 'sqlite:///db.db'
>>> odo([[1], [2], [3]], tbl, dshape='var * {a: int}', bind=bind)  # pass the bind to odo here

此处，绑定可以是数据库 URI，也可以是 SQLAlchemy Engine 对象。

转换¶

SQL 数据库的默认输入和输出路径是使用 SQLAlchemy 库来处理 Python 字典的迭代器。此方法健壮但速度较慢。

sqlalchemy.Table <-> Iterator
sqlalchemy.Select <-> Iterator

对于越来越多的数据库子集（SQLite、MySQL、PostgreSQL、Hive、Redshift），我们还使用这些数据库附带的 CSV 或 JSON 工具。当这些工具可用时，它们通常比 Python->SQLAlchemy 路径快一个数量级。

sqlalchemy.Table <- CSV

主键和外键关系¶

0.3.4 版本新增。

警告

主键和外键关系处理是一个实验性功能，可能会有所更改。

Odo 对创建和发现带有主键和外键关系的关联式数据库表提供实验性支持。

创建带主键的新资源¶

我们使用 resource 函数创建一个新的 sqlalchemy.Table 对象，并在 primary_key 参数中指定主键

>>> from odo import resource
>>> dshape = 'var * {id: int64, name: string}'
>>> products = resource(
...     'sqlite:///db.db::products',
...     dshape=dshape,
...     primary_key=['id'],
... )
>>> products.c.id.primary_key
True

复合主键是通过传递构成主键的列列表来创建的。例如

>>> dshape = """
... var * {
...     product_no: int32,
...     product_sku: string,
...     name: ?string,
...     price: ?float64
... }
... """
>>> products = resource(
...     'sqlite:///%s::products' % fn,
...     dshape=dshape,
...     primary_key=['product_no', 'product_sku']
... )

此处，列对 product_no, product_sku 构成了 products 表的复合主键。

创建带外键关系的资源¶

创建带有外键关系的新资源仅略微复杂一些。

作为一个激励性示例，考虑两个表 products 和 orders。products 表将是主键示例中的表。orders 表将与 products 表存在多对一关系。我们可以这样创建它

>>> orders_dshape = """
... var * {
...    order_id: int64,
...    product_id: map[int64, {id: int64, name: string}]
... }
... """
>>> orders = resource(
...     'sqlite:///db.db::orders',
...     dshape=orders_dshape,
...     primary_key=['order_id'],
...     foreign_keys={
...         'product_id': products.c.id,
...     }
... )
>>> products.c.id in orders.c.product_id.foreign_keys
True

这里有两点需要注意。

指定引用列*类型*的通用语法是
map[<referring column type>, <measure of the table being referred to>]
仅知道类型不足以指定外键关系。我们还需要知道包含我们想要引用列的表。foreign_keys 参数对 resource() 函数满足了这一需求。它接受一个字典，将引用列名映射到被引用的 sqlalchemy.Column 实例或字符串（例如 products.id）。

还有一种使用类型变量的快捷语法，用于指定其引用表具有非常复杂数据形状的外键关系。

而不是将上面的 orders 表写为

var * {order_id: int64, product_id: map[int64, {id: int64, name: string}]}

我们可以将 map 类型的值部分替换为任何以大写字母开头的单词。通常这是一个单个大写字母，例如 T

var * {order_id: int64, product_id: map[int64, T]}

Odo 将通过对传递给 foreign_keys 关键字参数的列调用 discover() 来自动填充 T 的数据形状。

最后，请注意，如果主键和外键关系已存在于数据库中，则会自动发现它们，因此如果它们已在其他地方创建，则无需再指定。

更复杂的外键关系¶

Odo 支持创建和发现自引用外键关系，以及作为复合主键元素的外键。后者通常在通过连接表创建多对多关系时出现。

自引用关系最容易使用类型变量指定（有关其工作原理的描述，请参阅上一节）。以管理层次结构为例

>>> dshape = 'var * {eid: int64, name: ?string, mgr_eid: map[int64, T]}'
>>> t = resource(
...     'sqlite:///%s::employees' % fn,
...     dshape=dshape,
...     primary_key=['eid'],
...     foreign_keys={'mgr_eid': 'employees.eid'}
... )

注意

目前，odo 只递归一层就会终止，因为我们还没有一种真正表达 datashape 中递归类型的语法

这是一个使用修改版传统供应商和零件数据库创建连接表（其外键构成复合主键）的示例

>>> suppliers = resource(
...     'sqlite:///%s::suppliers' % fn,
...     dshape='var * {id: int64, name: string}',
...     primary_key=['id']
... )
>>> parts = resource(
...     'sqlite:///%s::parts' % fn,
...     dshape='var * {id: int64, name: string, region: string}',
...     primary_key=['id']
... )
>>> suppart = resource(
...     'sqlite:///%s::suppart' % fn,
...     dshape='var * {supp_id: map[int64, T], part_id: map[int64, U]}',
...     primary_key=['supp_id', 'part_id'],
...     foreign_keys={
...         'supp_id': suppliers.c.id,
...         'part_id': parts.c.id
...     }
... )
>>> from odo import discover
>>> print(discover(suppart))
var * {
    supp_id: map[int64, {id: int64, name: string}],
    part_id: map[int64, {id: int64, name: string, region: string}]
}

外键关系失败模式¶

某些数据库支持外键引用另一个表的复合主键中的一列的概念。例如

>>> product_dshape = """
... var * {
...     product_no: int32,
...     product_sku: string,
...     name: ?string,
...     price: ?float64
... }
... """
>>> products = resource(
...     'sqlite:///%s::products' % fn,
...     dshape=product_dshape,
...     primary_key=['product_no', 'product_sku']
... )
>>> orders_dshape = """
... var * {
...   order_id: int32,
...   product_no: map[int32, T],
...   quantity: ?int32
... }
... """
>>> orders = resource(
...     'sqlite:///%s::orders' % fn,
...     dshape=orders_dshape,
...     primary_key=['order_id'],
...     foreign_keys={
...         'product_no': products.c.product_no
...         # no reference to product_sku, okay for sqlite, but not postgres
...     }
... )

这里我们看到，当构建 orders 表时，只包含 products 表主键中的一列。

SQLite 是允许这种做法的数据库之一。其他数据库（如 PostgreSQL）如果包含外键的表没有引用复合主键的所有列，则会引发错误。

Odo 对此不持任何立场，因此如果数据库允许，odo 也会允许。这是一个有意为之的选择。

然而，这可能导致令人困惑的情况，即某个功能在 SQLite 中有效，但在 PostgreSQL 中无效。这些并非 odo 的错误，而是为了在潜在的大型现有系统中提供灵活性而做出的明确选择。

Amazon Redshift¶

在使用 Amazon Redshift 时，错误报告仍有许多不足之处。许多错误看起来像这样

InternalError: (psycopg2.InternalError) Load into table 'tmp0' failed.  Check 'stl_load_errors' system table for details.

如果您正在从 S3 读取 CSV 数据，请确保

分隔符正确。我们无法正确推断所有内容，因此您可能需要将该值作为例如 delimiter='|' 传入。

如果您的数据是 gzip 压缩文件，您传入了 compression='gzip' 关键字参数。

如果您仍然遇到错误，并且确定上述两项都正确，请在odo 问题跟踪器上报告错误

我们有一个未解决的问题 (#298)，用于讨论在使用 Redshift 时如何更好地处理错误报告问题。