查询从昨天的数据库返回结果（类似于Xfiles，但用于数据）

Question

我在Python身上看到了一些东西，简直让我大吃一惊！你知道吗

当我运行以下脚本时，得到的结果与数据库中的结果不匹配：

import pyodbc

conn = pyodbc.connect('DSN=example;Description=example;UID=example;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=example;Database = example')
cursor = conn.cursor()

QS = """select
                    cct.product_group,
                    cct.website,
                    SUM(cct.sales_value) as sales,
                    SUM(cct.sales_value) - SUM(cct.cogs) +  SUM(cct.carriage_reclaim) - SUM(cct.carriage_costs) - SUM(cct.carriage_costs_estimate) - SUM(cct.metapack_costs) - SUM(cct.mktg_costs) - SUM(cct.mktg_costs_estimate) - SUM(cct.finance_costs) - SUM(cct.royalty) - SUM(cct.scrap_costs) as cavo,
                    sum(cct.budget_sales_value) as budget_sales,
                    sum(cct.budget_sales_phased) as budget_sales_phased,
                    sum(budget_sales_value - budget_cogs + budget_carriage_reclaim - budget_carriage_costs - budget_metapack_costs - budget_mktg_costs - budget_affiliate_costs - budget_offline_costs - budget_royalty - budget_finance_costs - budget_scrap_costs) as budget_cavo
                from
                        Phocas_TG.dbo.cavo_cube_trans cct
                    where
                        cct.date between '2016-02-01' and '2016-02-01'
                    and
                        cct.product_group in ('BATH', 'HEAT', 'SOLA')
                group by
                    cct.product_group,
                    cct.website
                """

cursor.execute(QS)

row = 1

while row:
    row = cursor.fetchone()
    print row

结果（错误）：

(u'SOLA', u'Beam LED', 0.0, 0.0, 162742.97946200587, 0.0, 18193.81407224502)
(u'BATH', u'Best Bathrooms', 0.0, 0.0, 41916.63833219, 0.0, 8616.793372278)
(u'HEAT', u'Best Bathrooms', 0.0, 0.0, 6608.969775007, 0.0, 2156.7834558070012)
(u'HEAT', u'Best Heating', 0.0, 0.0, 1221384.659527106, 0.0, 377841.5168052811)
(u'HEAT', u'Best Heating DE', 0.0, 0.0, 29173.095597537005, 0.0, 8475.738008001004)
(u'HEAT', u'Best Heating IE', 0.0, 0.0, 22200.070480626007, 0.0, 7635.299995903)
(u'BATH', u'Big Bathroom Shop', 0.0, 0.0, 194083.00040568004, 0.0, 24954.13424082598)
(u'HEAT', u'Big Bathroom Shop', 0.0, 0.0, 22149.000000066, 0.0, 8705.787840001001)
(u'SOLA', u'Big Bathroom Shop', 0.0, 0.0, 5100.480000258, 0.0, 1083.4538447940001)
(u'BATH', u'Cheap Suites', 0.0, 0.0, 96801.99990245397, 0.0, 14238.596937037)
(u'HEAT', u'Cheap Suites', 0.0, 0.0, 106569.44176593704, 0.0, 33754.97526726799)
(u'BATH', u'Hudson Reed CA', 0.0, 0.0, 37082.100495228, 0.0, 12856.460342323007)
(u'HEAT', u'Hudson Reed CA', 0.0, 0.0, 9353.568286208001, 0.0, 880.9490233900004)
(u'BATH', u'Hudson Reed DE', 0.0, 0.0, 101559.039541116, 0.0, 23292.634339761993)
(u'HEAT', u'Hudson Reed DE', 0.0, 0.0, 113215.76626921304, 0.0, 36837.99376355698)
(u'SOLA', u'Hudson Reed DE', 0.0, 0.0, 4854.377637117, 0.0, 1737.9875217079998)
(u'BATH', u'Hudson Reed ES', 0.0, 0.0, 19604.31999891699, 0.0, 4734.427979470002)
(u'HEAT', u'Hudson Reed ES', 0.0, 0.0, 34249.141983546, 0.0, 10209.710455291004)
(u'SOLA', u'Hudson Reed ES', 0.0, 0.0, 289.80000006, 0.0, 116.75621339399999)
(u'BATH', u'Hudson Reed FR', 0.0, 0.0, 347181.18108432164, 0.0, 69657.60552098301)
(u'HEAT', u'Hudson Reed FR', 0.0, 0.0, 226974.89734840707, 0.0, 66110.17134250299)
(u'SOLA', u'Hudson Reed FR', 0.0, 0.0, 4854.377637116998, 0.0, 1737.7115216809996)
(u'BATH', u'Hudson Reed IT', 0.0, 0.0, 16166.474983498987, 0.0, 3377.9398536559997)
(u'HEAT', u'Hudson Reed IT', 0.0, 0.0, 44038.997226871994, 0.0, 10705.928342997002)
(u'SOLA', u'Hudson Reed IT', 0.0, 0.0, 289.80000006, 0.0, 116.75621339399996)
(u'BATH', u'Hudson Reed NL', 0.0, 0.0, 109605.65601838099, 0.0, 21186.901077737002)
(u'HEAT', u'Hudson Reed NL', 0.0, 0.0, 111566.249953006, 0.0, 25728.021958158002)
(u'SOLA', u'Hudson Reed NL', 0.0, 0.0, 289.80000006, 0.0, 116.75621338999997)
(u'BATH', u'Hudson Reed US', 0.0, 0.0, 183865.59700310798, 0.0, 71254.06636748096)
(u'HEAT', u'Hudson Reed US', 0.0, 0.0, 45660.18595451701, 0.0, 14384.518580477998)
(u'BATH', u'Trueshopping', 0.0, 0.0, 79736.07721019002, 0.0, 17654.34511784)
(u'HEAT', u'Trueshopping', 0.0, 0.0, 74418.77915825398, 0.0, 21116.580455338993)
None

但是，如果我运行相同的脚本，但稍微更改sql字符串（在其中一行中添加一点空格），我会得到正确的结果：

import pyodbc

conn = pyodbc.connect('DSN=example;Description=example;UID=example;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=example;Database = example')
cursor = conn.cursor()

QS = """select
                     cct.product_group,
                    cct.website,
                    SUM(cct.sales_value) as sales,
                    SUM(cct.sales_value) - SUM(cct.cogs) +  SUM(cct.carriage_reclaim) - SUM(cct.carriage_costs) - SUM(cct.carriage_costs_estimate) - SUM(cct.metapack_costs) - SUM(cct.mktg_costs) - SUM(cct.mktg_costs_estimate) - SUM(cct.finance_costs) - SUM(cct.royalty) - SUM(cct.scrap_costs) as cavo,
                    sum(cct.budget_sales_value) as budget_sales,
                    sum(cct.budget_sales_phased) as budget_sales_phased,
                    sum(budget_sales_value - budget_cogs + budget_carriage_reclaim - budget_carriage_costs - budget_metapack_costs - budget_mktg_costs - budget_affiliate_costs - budget_offline_costs - budget_royalty - budget_finance_costs - budget_scrap_costs) as budget_cavo
                from
                        Phocas_TG.dbo.cavo_cube_trans cct
                    where
                        cct.date between '2016-02-01' and '2016-02-01'
                    and
                        cct.product_group in ('BATH', 'HEAT', 'SOLA')
                group by
                    cct.product_group,
                    cct.website
                """

cursor.execute(QS)

row = 1

while row:
    row = cursor.fetchone()
    print row

结果（正确）：

(u'SOLA', u'Beam LED', 5065.771499999995, 96.61683876775501, 162742.97946200587, 10194.260000000007, 18193.814072245008)
(u'BATH', u'Best Bathrooms', 2953.044100000001, 92.97357616908354, 41916.63833219, 2635.92, 8616.793372277998)
(u'HEAT', u'Best Bathrooms', 714.1080000000002, 242.6900400000002, 6608.969775007001, 411.96000000000004, 2156.7834558070003)
(u'SOLA', u'Best Eco Shop', 476.55, 90.5512, 0.0, 0.0, 0.0)
(u'HEAT', u'Best Heating', 67855.21000000008, 20803.223713668747, 1221384.6595271053, 75820.06999999998, 377841.516805281)
(u'HEAT', u'Best Heating DE', 764.414853, -74.59615568499949, 29173.095597537005, 1804.9900000000002, 8475.738008001008)
(u'HEAT', u'Best Heating IE', 511.684638, 145.72208938499998, 22200.070480626004, 1384.869999999999, 7635.299995903002)
(u'BATH', u'Beste Badezimmer', 605.427752, 136.0132520000001, 0.0, 0.0, 0.0)
(u'BATH', u'Big Bathroom Shop', 9055.997500000003, 1090.7121487611153, 194083.00040568007, 12434.420000000002, 24954.134240825995)
(u'HEAT', u'Big Bathroom Shop', 1637.3744000000004, 624.0700689273556, 22149.000000066, 1391.72, 8705.787840001)
(u'SOLA', u'Big Bathroom Shop', 0.0, 0.0, 5100.480000257999, 319.48999999999995, 1083.453844794)
(u'BATH', u'Cheap Suites', 6708.027599999998, 1218.3929213940442, 96801.99990245402, 6082.849999999999, 14238.596937036995)
(u'HEAT', u'Cheap Suites', 7877.586600000003, 3094.981148824446, 106569.44176593701, 6610.709999999997, 33754.975267268004)
(u'BATH', u'Hudson Reed CA', 2450.690545, 520.74435478, 37082.100495228, 2355.16, 12856.460342323011)
(u'HEAT', u'Hudson Reed CA', 198.955982, 43.108281999999996, 9353.568286207998, 593.02, 880.94902339)
(u'BATH', u'Hudson Reed DE', 7626.790740999998, 1737.584896192042, 101559.039541116, 6444.300000000002, 23292.634339761993)
(u'HEAT', u'Hudson Reed DE', 10397.459475000007, 3731.6650875108417, 113215.76626921303, 7008.19, 36837.993763557)
(u'SOLA', u'Hudson Reed DE', 0.0, 0.0, 4854.377637117, 304.08000000000004, 1737.9875217079998)
(u'BATH', u'Hudson Reed ES', 2748.9035340000005, 965.6333561828495, 19604.319998916995, 1216.6799999999998, 4734.42797947)
(u'HEAT', u'Hudson Reed ES', 1702.1610810000002, 589.3538634675374, 34249.141983546, 2118.4200000000005, 10209.710455291004)
(u'SOLA', u'Hudson Reed ES', 0.0, 0.0, 289.80000006, 18.16, 116.75621339399999)
(u'BATH', u'Hudson Reed FR', 47273.83856200002, 14384.879203607652, 347181.18108432164, 21603.31999999999, 69657.60552098295)
(u'HEAT', u'Hudson Reed FR', 19740.58251500001, 6704.351543201098, 226974.89734840702, 13947.230000000001, 66110.171342503)
(u'SOLA', u'Hudson Reed FR', 0.0, 0.0, 4854.377637116998, 304.08000000000004, 1737.7115216809996)
(u'BATH', u'Hudson Reed IT', 1520.260361, 221.10746836602254, 16166.474983499003, 1003.3399999999998, 3377.939853656)
(u'HEAT', u'Hudson Reed IT', 3123.910959, 667.0734018041572, 44038.997226871994, 2706.38, 10705.928342997002)
(u'SOLA', u'Hudson Reed IT', 0.0, 0.0, 289.80000005999995, 18.16, 116.75621339399997)
(u'BATH', u'Hudson Reed NL', 16583.111918, 5610.879630999159, 109605.65601838099, 6872.79, 21186.901077737002)
(u'HEAT', u'Hudson Reed NL', 10411.469125, 3712.277327632342, 111566.249953006, 6818.089999999997, 25728.021958158024)
(u'SOLA', u'Hudson Reed NL', 0.0, 0.0, 289.80000006, 18.16, 116.75621338999997)
(u'BATH', u'Hudson Reed US', 8481.261439000004, 3675.9046331535123, 183865.59700310792, 11516.219999999996, 71254.06636748099)
(u'HEAT', u'Hudson Reed US', 2097.8916030000005, 938.4614114663307, 45660.18595451701, 2833.7799999999993, 14384.518580478)
(u'BATH', u'Trueshopping', 5265.242654999999, 583.9883320369803, 79736.07721019002, 5047.579999999999, 17654.345117839996)
(u'HEAT', u'Trueshopping', 5519.389900000001, 1508.0805920000003, 74418.77915825396, 4634.490000000001, 21116.580455338986)
(u'SOLA', u'Trueshopping', 777.320599, 268.97614415250007, 0.0, 0.0, 0.0)
None

如果有帮助的话，数据库本质上就是一个每晚都要重建的数据立方体。第一次查询的结果似乎与昨天的数据库相匹配——这可能是某种缓存系统吗？已经运行的查询的结果存储在哪里？你知道吗

我太糊涂了！有人见过这个吗？你知道吗