时间戳

首页 > 解决方案 > SQL语句，如何在sqlite3中的id列表中加入一个id

python - SQL语句，如何在sqlite3中的id列表中加入一个id

问题描述

首先，我对 SQL 很陌生，所以实现可能不是传统的 :) 我正在使用sqlite3andpandas并且正在尝试使用 join 语句链接两个表（设备和测试）。我想查询与 Test_1 关联的所有设备，而不知道它的名称、ID 或有多少设备。例如，使用如下所示的 SQL 查询：

"SELECT * FROM Tests
JOIN Device ON Devices.id = Tests.Device_ids
WHERE Devices.name = 'Device_1'"

我遇到的问题是，我需要多个设备来执行测试。正如我使用的那样pandas dataFrame.to_sql()，sqlite3唯一的选项（如我所见）是将设备 ID 存储为列表的字符串（选项 1）或具有相同 ID 的条目（选项 2）。

设备               测试（选项 1）                  测试（选项 2）

| 编号 | 姓名     | | 编号 | 姓名 | 设备 ID   | | 编号 | 姓名 | 设备 ID   |
| 1 | 设备_1 | | 1 | 测试_1 | '[1, 3]' | | 1 | 测试_1 | 1 |
| 2 | 设备_2 | | 2 | 测试_2 | '[2, 3]' | | 1 | 测试_1 | 3 |      
| 3 | 设备_3 | | 2 | 测试_2 | 2 |
                                                        | 2 | 测试_2 | 3 |

选项1：到目前为止不起作用，但会是首选

选项 2：不返回 Device_3 作为 Test_1 的一部分

我已经有一个与上面类似的运行环境，所以我不能真正改变实现。这里有一些代码来澄清我想做的事情：

import pandas as pd
import sqlite3

con = sqlite3.connect(':memory:')

tests = {'id':[1,2], 'name':['test_1', 'test_2'], 'device_ids':['[1,3]','[2,3]']}
tests = pd.DataFrame(tests)
print(tests)
tests.to_sql('tests', con)


devices = {'id':[1,2,3], 'name':['Device_1', 'Device_2', 'Device_3']}
devices = pd.DataFrame(devices)
print(devices)
devices.to_sql('devices', con)

query = "SELECT * FROM tests JOIN devices ON devices.id = tests.device_ids WHERE devices.name = 'Device_1'"
pd.read_sql_query(query, con)

我正在寻找的输出是：

| 编号 | 姓名 | 设备 ID |    
| 1 | 测试_1 | '[1, 3]' |

标签： pythonpandassqlite

解决方案

首先，在 SQL 中使用联合查询的正确方法是每行有一个数据。这意味着唯一可用的选项是 2。但您可以使用以下代码轻松转换explode：

...
tests = {'id':[1,2], 'name':['test_1', 'test_2'], 'device_ids':[[1,3],[2,3]]}
tests = pd.DataFrame(tests).explode('device_ids')
print(tests)
tests.to_sql('tests', con)
...

然后要获得测试所需的设备，您可以使用：

con.execute("""SELECT devices.* FROM devices
JOIN tests ON tests.device_ids = devices.id
WHERE tests.name = ?""", ('test_1',)).fetchall()

它按预期给出：

[(0, 1, 'Device_1'), (2, 3, 'Device_3')]

推荐阅读