时间戳

首页 > 解决方案 > 通过 SQLAlchemy 在 SQLLite 中寻找数组的替代品

python-3.x - 通过 SQLAlchemy 在 SQLLite 中寻找数组的替代品

问题描述

我正在使用带有 SQLAlchemy 的 SQLlite，并且我在数据库中有如下值：

id name    value
 1  ionic   "alpha, beta , gama, teta"

我需要按名称和值进行选择查询，使用作为参数：

name : ionic
value: "beta, gama"

因此，如果名称对应，则在值中找到作为参数传递的任何值，返回 id。但是如果值是“alpha, ronda”就失败了。

我被迫使用 SQLLite，而且我知道 SQLlite 中没有数组，那么有哪些选项。

标签： python-3.xsqlitesqlalchemy

解决方案

在传统 SQL 中，列表是作为连接表完成的。在这种情况下，是一对多的关系。一件事可以有很多价值。

create table things (
  id serial primary key,
  name text not null
);

create table thing_values (
  thing_id references things(id),
  value text not null,
  unique(value, thing_id)
);

然后，您将它们连接在一起以获得每件事的价值。

select *
from things t
join thing_values tv on t.id = tv.thing_id;

要查找与所有值匹配的事物，您需要搜索与任何值匹配的所有行，然后计算每个事物匹配的行数。

select thing_id as id
from thing_values
where value in ('beta', 'gamma')
group by thing_id
having count(*) = 2

在您的情况下，您查找了 2 个值，因此您只想要匹配两次的东西。由于我们有一个唯一的子句，thing_values因此不能有重复项。如果有，您必须使用having count(distinct value) = 2.

有关更多详细信息，请参阅此答案。

最后，可以将其与CTE连接在一起以获取匹配事物的名称。

with thing_matches as (
  select thing_id as id
  from thing_values
  where value in ('beta', 'gamma')
  group by thing_id
  having count(*) = 2
)
select t.name
from things t
join thing_matches tm on tm.id = t.id

dbfiddle来演示。

这似乎比仅仅存储一个逗号分隔的字段要多得多。确实如此，但只是在短期内。从长远来看，当您了解 SQL 的关系世界观时，以这种方式组织事物将变得更加自然，而且效率也更高。

通过逗号分隔的列表进行搜索需要数据库扫描每一行以查找想要找到具有给定值的事物的每个查询。这是全表扫描，它们是数据库性能的祸根。（其他数据库有更高级的索引可以索引这种搜索，但 AFAIK SQLite 没有）。

相反，where value in ('beta', 'gamma')它是一个简单的相等检查，可以在我们设置的索引上完成unique(value, thing_id)。唯一约束是通过索引完成的，通过将valueSQLite 放在首位，可以使用此唯一索引按值进行搜索。

所以上面的查询将在一个大表上执行得非常快。而like查询必须扫描表的每一行。

最后，如果您确实选择使用逗号分隔，则必须小心不要意外匹配部分。考虑当值重叠时会发生什么。

天真的方法就像......

1   ionic   alpha,beta,gamma,theta
2   doric   al,bet,gams,the

where value like '%beta%'  # it matches 1

where value like '%bet%'   # whoopsie, it matches both

相反，用前导和尾随逗号清楚地描述您的价值观。这消除了开始和结束周围的边缘情况，因此可以通过包含逗号来减少匹配。

1   ionic   ,alpha,beta,gamma,theta,
2   doric   ,al,bet,gams,the,

where value like '%,beta,%'  # it matches 1

where value like '%,bet,%'     # it matches 2

这些边缘情况是连接表值得麻烦的另一个原因。

推荐阅读