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','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','','');

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

INSERT INTO "CorpData" VALUES(NULL,'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,S,15,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,74878,Anytown 334 - Unit 3 - Widgit Corp Distribution Center
60000023,56799,23487,Quietville 532 - Unit 4 - Widgit Corp Warehouse
60000027,100004,Somewhereville 544 - Unit 3 - ACME Corp Distribution Center
60000150,Quietville 312 - Unit 2 - ACME Corp Factory
60000155,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,OtherCounter5,OperationID4Counter,OperationID55Counter
100002,1
100004,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)