python - 查找具有特定子行组合的父级 - SQLite 与 Python

Question

这个问题有几个部分。我在 Python 2.7 中使用 sqlite3，但我不太关心确切的语法，而更关心我需要使用的方法。我认为问这个问题的最好方法是描述我当前的数据库设计，以及我想要完成的事情。一般来说，我是数据库的新手，所以如果我不总是使用正确的命名法，我深表歉意。

我正在为制冷系统建模(使用 Modelica——知道这并不重要)，我正在使用数据库来管理输入数据、结果数据以及用于该数据的模型。

我的顶级父表是 Model，它包含以下列:

id, name, version, date_created

我在 Model 下的子表称为 Design。它用于为设计输入参数和所用模型的每个组合创建一个唯一的 ID。它包含的列是:

id, model_id, date_created

然后我在 Design 下有两个子表，一个称为 Input，另一个称为 Result。我们现在可以只看输入，因为一个示例就足够了。输入的列是:

id, value, design_id, parameter_id, component_id

parameter_id 和component_id 是各自表的外键。Parameter 表有以下列:

id, name, units 

Parameter under name 的一些示例行是:长度、宽度、速度、温度、压力(还有几十个)。组件表包含以下列:

id, name

name 下 Component 的一些示例行是:compressor、heat_exchanger、valve。

最终，在我的程序中，我想在数据库中搜索特定设计。我希望能够搜索特定设计以获取该设计的特定结果，或者了解之前是否已经运行过具有该设计的模型仿真，以避免重新运行相同的数据点。

我还希望能够获取给定设计的所有参数，并将其插入到我用 Python 创建的类中，然后使用该类为我的模型提供输入。如果它有助于解决问题，我创建的类是基于组件的。因此，例如，我有一个压缩机类，具有 compressor.speed、compressor.stroke、compressor.piston_size 等属性。这些属性中的每一个都应该在参数表中有自己的行。

那么，我将如何有效地查询该数据库以查找是否存在与具有特定值的一长串参数(假设超过 100 个)相匹配的设计？附带说明一下，我的 friend 帮我设计了这个数据库。他了解数据库，但对我的应用程序不是很了解。有可能我针对我想要完成的目标设计得很糟糕。

这是一张简单的图片，试图将具有特定值的特定参数组合映射到 design_id，为了简单起见，我在其中取出了 component_id:

Picture of simplified tables

Answer 1

只需连接必要的表。您的模式正确地反射(reflect)了规范化(将表分成逻辑分组)并且可以针对一对多关系进行扩展。具体来说，要回答您的问题——那么，我如何有效地查询该数据库以查找是否存在与具有特定值的一长串参数(假设超过 100 个)相匹配的设计？——考虑以下方法:

Inner Join with Where 子句

对于少数参数，使用带有 WHERE...IN() 子句的内部连接。下面返回由 input 和 parameters 表连接的 design 字段，针对特定参数名称进行过滤，您可以在其中让 Python 作为参数化值传递，甚至在循环:

SELECT d.id, d.model_id, d.date_created
FROM design d
INNER JOIN input i ON d.id = i.design_id
INNER JOIN parameters p ON p.id = i.parameter_id
WHERE p.name IN ('param1', 'param2', 'param3', 'param4', 'param5', ...)

使用临时表进行内部连接

如果长列表中的值超过 100+，请考虑将参数表过滤为特定参数值的临时表:

# CREATE EMPTY TABLE (SAME STRUCTURE AS parameters)
sql = "CREATE TABLE tempparams AS SELECT id, name, units FROM parameters WHERE 0;"
cur.execute(sql)
db.commit()

# ITERATIVELY APPEND TO TEMP
for i in paramslist:                                  # LIST OF 100+ ITEMS
    sql = "INSERT INTO tempparams (id, name, units) \
           SELECT p.id, p.name, p.units \
           FROM parameters p \
           WHERE p.name = ?;"
    cur.execute(sql, i)                               # CURSOR OBJECT COMMAND PASSING PARAM
    db.commit()                                       # DB OBJECT COMMIT ACTION 

然后，将主设计和输入表与包含特定参数的新临时表连接起来:

SELECT d.id, d.model_id, d.date_created
FROM design d
INNER JOIN input i ON d.id = i.design_id
INNER JOIN tempparams t ON t.id = i.parameter_id 

同样的过程也可以用于组件表。

*将图片移至问题部分