python – 使用带有sqlite3的多个键从多个表中提取数据进行计数

答案

CL.的答案就是诀窍！我最终使用了一个Python脚本(可以在下一节中查看,名为“更新：正在解决问题”),并且一旦我正确设置了数据库,以便将ID列设置为整数键(或者,如果是是不可能的,数字)和名称列被设置为文本然后它工作！

更新：努力寻找答案

我尝试运行如下所示的.py文件：

import sqlite3
conn = sqlite3.connect('data.db')
c = conn.cursor()

c.executescript("""
UPDATE CorpData
SET OperationID4Counter =
      (SELECT COUNT(*)
       FROM PlantData JOIN OperationData
                      ON PlantName LIKE '%' || OperationName
       WHERE OperationID IN (SELECT OperationID
                             FROM ServiceData
                             WHERE ServiceID = 512)
         AND CorpID = CorpData.CorpID)
        """)

并得到此错误：sqlite3.OperationalError：不明确的列名：OperationID.我猜这是因为我们加入了PlantData和OperationData,两者都有一个名为OperationID的列.当我更改该行代码以读取WHERE OperationData.OperationID IN(SELECT OperationID或WHERE PlantData.OperationID IN(SELECT OperationID,它运行但我最终在OperationID4Counter列下的CorpData表的所有行中都为零).

我想我们很接近,但没有雪茄.我认为ON StationName LIKE’％’||有些不对劲OperationName行,因为当我将它更改为ON StationName LIKE’％house’时(如果我理解这一点,应该在’house’中得到所有结果,包括Warehouse)我仍然以OperationID4Counter的所有零结束(甚至虽然它应该至少计算具有OperationID4的仓库.)

CL.要求提供一些.dump信息,以查看此数据库中正在使用的类型.我没有指定任何东西所以它刚刚使用默认值.另请注意,各个表的列数多于我在示例中显示的列数(但请注意,这些列与此问题无关,因为它们处理与手头问题无关的数据.)例如,一个PlantData表的.dump如下所示：

INSERT INTO "PlantData" VALUES('60015145','0','0','50000000','10000','15','386
8','1000181','30003830','20000560','10000048','Anytown 334 - Unit 3 - Widgit Corp Logistics Center','-1.444E+12','-71312793600','-9.25528E+11','0.5','0.025','4
');

OperationData中的.dump片段如下所示：

INSERT INTO "OperationData" VALUES('20','45','Manufacturing','','0','0','0','0',
'0','','','','','');

而CorpData的.dump文章看起来像这样：

INSERT INTO "CorpData" VALUES(NULL,0,'1000158','Shapeset',' S',' N',' 500005','
XYZ Consortium',' 20','6','7','1','5','0');

背景&数据样本

我有4个表 – 其中3个我想在某些条件下从中提取数据以增加计数器,然后将此计数器作为新列添加到第4个.第四个表,让我们称之为CorpData(我想添加更多数据)目前看起来像这样,通常有10-50行(注意我用逗号来显示列分隔符)：

CorpID, CorpName, Size, Type, PlantCount, OtherCounter1, OtherCounter2, OtherCounter3, OtherCounter4, OtherCounter5
100002, Widgit Corp, G, R, 25, 1, 5, 4, 3, 0
100004, ACME Corp, G, S, 15, 15, 4, 25, 28, 1

值得注意的是CorpID(一种独特的钥匙)和PlantCount,它是该公司拥有多少工厂(即设施)的柜台.

这些附加数据源表中的第一个,我们称之为OperationData,具有这样的数据并且有大约50行：

OperationID, OperationName, Description
1, Warehouse, This facility stores items
2, Distribution Center, Items are brought her from Warehouses to be distributed
3, Factory, Goods are manufactured here

第二,ServiceData有大约700行,看起来像这样：

OperationID, ServiceID
1, 4
1, 25
1, 33
1, 105
1, 19505
1, 32590
2, 4
2, 25
2, 55
2, 199
2, 19505
2, 335679
2, 529934
3, 2
3, 105
3, 55
3, 170
3, 48907

每个ServiceID都在另一个表中解释,但我想搜索一个或两个我将指定的ServiceID,如4和55.

最后一个数据表,我们称之为PlantData,有所有公司的所有工厂的详细信息,因此它有大约5200行,如下所示：

PlantID, CorpID, CityID, CountryID, PlantName
60000004, 100002, 74900, 34590, Somewhereville 123 - Widgit Corp Warehouse
60000007, 100002, 74878, 34590, Anytown 334 - Unit 3 - Widgit Corp Distribution Center
60000023, 100002, 56799, 23487, Quietville 532 - Unit 4 - Widgit Corp Warehouse
60000027, 100004, 74900, 34590, Somewhereville 544 - Unit 3 - ACME Corp Distribution Center
60000150, 100004, 56799, 23487, Quietville 312 - Unit 2 - ACME Corp Factory
60000155, 100004, 56799, 23487, Quietville 312 - Unit 4 - ACME Corp Warehouse

请注意以下几点：1)此表中的CorpID与我的起始表中的CorpID匹配2)给定CorpID的CorpName将始终显示在PlantName中3)PlantName还包含一个OperationName 4)一个CityID可以包含多个公司的工厂以及多个同一公司的工厂. 4)作为旁注,这只是这个表的一小部分,如果你计算给定的CorpID在这个表中显示的所有时间,它将与该CorpID的PlantCount相同(所以这可以用作检查某种以确保没有错过任何植物.)

问题

我想在CorpData表中添加两个新列,它们都是计数 – 第一个将计算公司拥有ServiceID 4的工厂数量,第二个计算公司拥有ServiceID 55的工厂数量为此,我需要查看长PlantData表,从每个PlantName中解析出OperationName(来自OperationData表),检查找到OperationName的相应OperationID(在OperationData表中),并查看是否列出了OperationID ServiceID(来自ServiceData表)有问题(第一种情况下为4,第二种情况下为55).

我打算使用sqlite3和我存储在.db文件中的4个表来执行此操作,但是如果您能够说明为什么我应该在sqlite3上使用该选项,我可能会对其他选项开放.

目标

鉴于此处的示例,我的最终目标是使CorpData表看起来像这样：

CorpID, CorpName, Size, Type, PlantCount, OtherCounter1, OtherCounter2, OtherCounter3, OtherCounter4, OtherCounter5, OperationID4Counter, OperationID55Counter
100002, Widgit Corp, G, R, 25, 1, 5, 4, 3, 0, 3, 1
100004, ACME Corp, G, S, 15, 15, 4, 25, 28, 1, 2, 2

这是因为Widgit Corp有两个仓库和一个配送中心,仓库和配送中心都有OperationID 4,但只有配送中心和工厂有OperationID 55,ACME公司有1个工厂,仓库和配送中心,工厂没有具有OperationID 4,但它们具有OperationID 55.

其他说明

以下是我认为可能会使这一点更棘手的一些事情：

> PlantName包含多个单词,它可能有也可能没有单元X – 部分. OperationName并不总是只有一个字长,而CorpName也可以比两个字更多(或更少).因此,在PlantName中查找OperationName可能需要查看整个事物,而不是试图将其拆分成某种方式来搜索可能包含OperationName的块.
>当我们沿着PlantData表工作并计算匹配时,我们必须继续检查另外两个表,以查看是否应该计算PlantData中的行.我担心如果这段代码没有正确构建,它可能会变得非常慢.
>对我来说这很棘手,至少要把所有这些都包裹起来,这样可能很容易引用错误的表或错过了从OperationName到相应的OperationID等搜索步骤.

解决方法:

UPDATE CorpData
SET OperationID4Counter =
      (SELECT COUNT(*)
       FROM PlantData JOIN OperationData
                      ON PlantName LIKE '%' || OperationName
       WHERE OperationData.OperationID IN (SELECT OperationID
                                           FROM ServiceData
                                           WHERE ServiceID = 4)
         AND CorpID = CorpData.CorpID)

【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！