如何使用 PostgreSQL 来简化 Python 代码

图片来源

Opensource.com

Python 是一种编程语言，可让您快速工作并更有效地集成系统，而 PostgreSQL 是世界上最先进的开源数据库。这两者可以很好地协同工作。本文介绍了如何在解决简单问题时充分利用 PostgreSQL (psql)。尽管使用 Python 代码来解决问题很诱人，但这并不总是最佳选择。

SQL 具有相当多的处理能力，将 SQL 集成到您的工作流程中通常意味着编写更少的代码行。正如 Edsger Dijkstra 所说，代码行是花费的行：

这种实践普遍存在一种令人安心的错觉，即程序就像任何其他设备一样，唯一承认的区别在于它们的制造可能需要一种新型的工匠，即程序员。由此，只需一小步即可将“程序员生产力”衡量为“每月生产的代码行数”。这是一个非常昂贵的衡量单位，因为它鼓励编写乏味的代码，但今天我不太关注从纯粹的商业角度来看，这是一个多么愚蠢的单位。我今天的观点是，如果我们希望计算代码行数，我们不应将它们视为“生产的行数”，而应视为“花费的行数”：当前传统的智慧是如此愚蠢，以至于将该计数记在账簿的错误一侧。
— Dijkstra, “论真正教授计算机科学的残酷性。”

通过使用 SQL，您可以编写更少的代码，因此您可以在更短的时间内编写应用程序。

一个简单的用例

为了检验 PostgreSQL 和 Python 如何协同工作，我们将使用纽约证券交易所 (NYSE) 的“纽约证券交易所上市公司每日 NYSE 集团交易量”数据集。要下载数据，请访问 Facts & Figures Interactive Viewer，单击市场活动，然后单击纽约证券交易所上市公司每日 NYSE 集团交易量。然后单击顶部的“Excel”符号（实际上是一个使用制表符作为分隔符的 CSV 文件），将“factbook.xls”文件保存到您的计算机，打开它并删除标题，然后将其加载到 PostgreSQL 表中。

加载数据集

以下是数据的外观。它包括逗号分隔的千位符和美元符号，因此我们无法轻易地将数字作为数字进行处理。

2010	1/4/2010	1,425,504,460	4,628,115	$38,495,460,645
2010	1/5/2010	1,754,011,750	5,394,016	$43,932,043,406
2010	1/6/2010	1,655,507,953	5,494,460	$43,816,749,660
2010	1/7/2010	1,797,810,789	5,674,297	$44,104,237,184

为了改变这一点，我们可以创建一个临时的表定义，一旦数据加载完成，由于 alter table 命令，它将被转换为正确的 SQL 数据类型。

begin;

create table factbook
 (
   year    int,
   date    date,
   shares  text,
   trades  text,
   dollars text
 );

\copy factbook from 'factbook.csv' with delimiter E'\t' null ''

alter table factbook
   alter shares
    type bigint
   using replace(shares, ',', '')::bigint,

   alter trades
    type bigint
   using replace(trades, ',', '')::bigint,
   
   alter dollars
    type bigint
   using substring(replace(dollars, ',', '') from 2)::numeric;

commit;

我们可以使用 PostgreSQL 的 copy 功能将数据从 CSV 文件流式传输到我们的表中。\copy 变体是 psql 特有的命令，它启动客户端/服务器数据流式传输，读取本地文件，并通过任何已建立的 PostgreSQL 连接发送其内容。

应用程序代码和 SQL

此文件中包含大量数据，因此让我们在此示例中使用 2017 年 2 月的数据。以下查询列出了 2017 年 2 月份的所有条目

\set start '2017-02-01'

  select date,
         to_char(shares, '99G999G999G999') as shares,
         to_char(trades, '99G999G999') as trades,
         to_char(dollars, 'L99G999G999G999') as dollars
    from factbook
   where date >= date :'start'
     and date  < date :'start' + interval '1 month'
order by date;

我们使用 psql 应用程序来运行此查询，psql 支持使用变量。\set 命令将 '2017-02-01' 值设置为变量 start，然后我们可以使用表达式 :'start' 重用该变量。

编写 date :'start' 等同于 date '2017-02-01'——这在 PostgreSQL 中称为装饰文字表达式。这允许我们设置文字值的数据类型，以便 PostgreSQL 查询解析器不必猜测或从上下文中推断它。

此 SQL 查询还使用 interval 数据类型来计算月末，当然，在本例中是二月的最后一天。将 1 month 的 interval 值添加到该月的第一天会得到下个月的第一天，因此我们使用“小于” (<) 严格运算符将这一天从我们的结果集中排除。

to_char() 函数（在 PostgreSQL 文档关于数据类型格式化函数的部分中进行了文档说明）会将数字转换为其文本表示形式，并对转换进行详细控制。该格式由模板模式组成。我们将使用以下模式

具有指定位数的数值
L：货币符号（使用区域设置）
G：分组分隔符（使用区域设置）

还有其他用于数字格式化的模板模式——请参阅 PostgreSQL 文档以供参考。

以下是我们的查询结果

    date    │     shares      │   trades    │     dollars      
════════════╪═════════════════╪═════════════╪══════════════════
 2017-02-01 │   1,161,001,502 │   5,217,859 │ $ 44,660,060,305
 2017-02-02 │   1,128,144,760 │   4,586,343 │ $ 43,276,102,903
 2017-02-03 │   1,084,735,476 │   4,396,485 │ $ 42,801,562,275
 2017-02-06 │     954,533,086 │   3,817,270 │ $ 37,300,908,120
 2017-02-07 │   1,037,660,897 │   4,220,252 │ $ 39,754,062,721
 2017-02-08 │   1,100,076,176 │   4,410,966 │ $ 40,491,648,732
 2017-02-09 │   1,081,638,761 │   4,462,009 │ $ 40,169,585,511
 2017-02-10 │   1,021,379,481 │   4,028,745 │ $ 38,347,515,768
 2017-02-13 │   1,020,482,007 │   3,963,509 │ $ 38,745,317,913
 2017-02-14 │   1,041,009,698 │   4,299,974 │ $ 40,737,106,101
 2017-02-15 │   1,120,119,333 │   4,424,251 │ $ 43,802,653,477
 2017-02-16 │   1,091,339,672 │   4,461,548 │ $ 41,956,691,405
 2017-02-17 │   1,160,693,221 │   4,132,233 │ $ 48,862,504,551
 2017-02-21 │   1,103,777,644 │   4,323,282 │ $ 44,416,927,777
 2017-02-22 │   1,064,236,648 │   4,169,982 │ $ 41,137,731,714
 2017-02-23 │   1,192,772,644 │   4,839,887 │ $ 44,254,446,593
 2017-02-24 │   1,187,320,171 │   4,656,770 │ $ 45,229,398,830
 2017-02-27 │   1,132,693,382 │   4,243,911 │ $ 43,613,734,358
 2017-02-28 │   1,455,597,403 │   4,789,769 │ $ 57,874,495,227
(19 rows)

数据集仅包含 2017 年 2 月的 19 天数据（纽约证券交易所开放的日期）。如果我们想要显示每个日历日的条目，并用匹配的数据或零值填充缺失的日期，该怎么办？

以下是典型的 Python 实现

#! /usr/bin/env python3

import sys
import psycopg2
import psycopg2.extras
from calendar import Calendar

CONNSTRING = "dbname=yesql application_name=factbook"


def fetch_month_data(year, month):
    "Fetch a month of data from the database"
    date = "%d-%02d-01" % (year, month)
    sql = """
  select date, shares, trades, dollars
    from factbook
   where date >= date %s
     and date  < date %s + interval '1 month'
order by date;
"""
    pgconn = psycopg2.connect(CONNSTRING)
    curs = pgconn.cursor()
    curs.execute(sql, (date, date))

    res = {}
    for (date, shares, trades, dollars) in curs.fetchall():
        res[date] = (shares, trades, dollars)

    return res


def list_book_for_month(year, month):
    """List all days for given month, and for each
    day list fact book entry.
    """
    data = fetch_month_data(year, month)

    cal = Calendar()
    print("%12s | %12s | %12s | %12s" %
          ("day", "shares", "trades", "dollars"))
    print("%12s-+-%12s-+-%12s-+-%12s" %
          ("-" * 12, "-" * 12, "-" * 12, "-" * 12))

    for day in cal.itermonthdates(year, month):
        if day.month != month:
            continue
        if day in data:
            shares, trades, dollars = data[day]
        else:
            shares, trades, dollars = 0, 0, 0

        print("%12s | %12s | %12s | %12s" %
              (day, shares, trades, dollars))


if __name__ == '__main__':
    year = int(sys.argv[1])
    month = int(sys.argv[2])

    list_book_for_month(year, month)

在此实现中，我们使用上述 SQL 查询来获取我们的结果集，并且将其存储在字典中。字典的键是月份中的日期，因此我们可以循环遍历日历的日期列表，在有匹配数据时检索匹配数据，并在没有数据时安装默认结果集（例如，零）。

以下是运行该程序的输出。如您所见，我们选择了类似于 psql 输出的输出，这使得比较达到相同结果所需的工作量变得更容易。

$ ./factbook-month.py 2017 2
         day |       shares |       trades |      dollars
-------------+--------------+--------------+-------------
  2017-02-01 |   1161001502 |      5217859 |  44660060305
  2017-02-02 |   1128144760 |      4586343 |  43276102903
  2017-02-03 |   1084735476 |      4396485 |  42801562275
  2017-02-04 |            0 |            0 |            0
  2017-02-05 |            0 |            0 |            0
  2017-02-06 |    954533086 |      3817270 |  37300908120
  2017-02-07 |   1037660897 |      4220252 |  39754062721
  2017-02-08 |   1100076176 |      4410966 |  40491648732
  2017-02-09 |   1081638761 |      4462009 |  40169585511
  2017-02-10 |   1021379481 |      4028745 |  38347515768
  2017-02-11 |            0 |            0 |            0
  2017-02-12 |            0 |            0 |            0
  2017-02-13 |   1020482007 |      3963509 |  38745317913
  2017-02-14 |   1041009698 |      4299974 |  40737106101
  2017-02-15 |   1120119333 |      4424251 |  43802653477
  2017-02-16 |   1091339672 |      4461548 |  41956691405
  2017-02-17 |   1160693221 |      4132233 |  48862504551
  2017-02-18 |            0 |            0 |            0
  2017-02-19 |            0 |            0 |            0
  2017-02-20 |            0 |            0 |            0
  2017-02-21 |   1103777644 |      4323282 |  44416927777
  2017-02-22 |   1064236648 |      4169982 |  41137731714
  2017-02-23 |   1192772644 |      4839887 |  44254446593
  2017-02-24 |   1187320171 |      4656770 |  45229398830
  2017-02-25 |            0 |            0 |            0
  2017-02-26 |            0 |            0 |            0
  2017-02-27 |   1132693382 |      4243911 |  43613734358
  2017-02-28 |   1455597403 |      4789769 |  57874495227

PostgreSQL 高级函数

同样的事情可以通过单个 SQL 查询完成，而无需在解决问题上“花费”任何应用程序代码

  select cast(calendar.entry as date) as date,
         coalesce(shares, 0) as shares,
         coalesce(trades, 0) as trades,
         to_char(
             coalesce(dollars, 0),
             'L99G999G999G999'
         ) as dollars
    from /*
          * Generate the target month's calendar then LEFT JOIN
          * each day against the factbook dataset, so as to have
          * every day in the result set, whether or not we have a
          * book entry for the day.
          */
         generate_series(date :'start',
                         date :'start' + interval '1 month'
                                       - interval '1 day',
                         interval '1 day'
         )
         as calendar(entry)
         left join factbook
                on factbook.date = calendar.entry
order by date;

在此查询中，我们使用了几种您可能不熟悉的基本 SQL 和 PostgreSQL 技术

SQL 接受以 -- comment 样式编写的注释，从开头运行到行尾，或者使用 C 样式的 /* comment */ 样式。与任何编程语言一样，注释最好用于注释意图，否则可能很难从代码本身反向工程出来。
generate_series() 是 PostgreSQL 集合返回函数，其文档内容为：“生成一系列值，从开始到结束，步长为 step。”由于 PostgreSQL 知道它的日历，因此很容易使用该月的第一天作为查询中的单个参数来生成给定月份的所有天数。
generate_series() 是包含性的，很像 BETWEEN 运算符，因此我们使用表达式 - interval '1 day' 排除下个月的第一天。
cast(calendar.entry as date) 表达式将生成的 calendar.entry（它是 generate_series() 函数调用的结果）转换为 date 数据类型。我们需要使用 cast，因为 generate_series() 函数返回一组时间戳条目，这与我们在本练习中无关。
我们生成的 calendar 表和 factbook 表之间的 left join 将保留每个 calendar 行，并且仅当两个表的 date 列具有相同的值时才将 factbook 行与其关联。当在 factbook 中找不到 calendar.date 时，factbook 列（year、date、shares、trades 和 dollars）将填充 NULL 值。
Coalesce 返回其参数中第一个不为 null 的参数。因此，表达式 coalesce(shares, 0) as shares 要么是我们在 factbook 表中为此 calendar.date 行找到的股份数，要么是在我们没有找到 calendar.date 的条目时为 0。此外，left join 保留了我们的结果集行，并使用 NULL 值填充了 factbook 列。

最后，这是此查询的结果

    date    │   shares   │ trades  │     dollars      
════════════╪════════════╪═════════╪══════════════════
 2017-02-01 │ 1161001502 │ 5217859 │ $ 44,660,060,305
 2017-02-02 │ 1128144760 │ 4586343 │ $ 43,276,102,903
 2017-02-03 │ 1084735476 │ 4396485 │ $ 42,801,562,275
 2017-02-04 │          0 │       0 │ $              0
 2017-02-05 │          0 │       0 │ $              0
 2017-02-06 │  954533086 │ 3817270 │ $ 37,300,908,120
 2017-02-07 │ 1037660897 │ 4220252 │ $ 39,754,062,721
 2017-02-08 │ 1100076176 │ 4410966 │ $ 40,491,648,732
 2017-02-09 │ 1081638761 │ 4462009 │ $ 40,169,585,511
 2017-02-10 │ 1021379481 │ 4028745 │ $ 38,347,515,768
 2017-02-11 │          0 │       0 │ $              0
 2017-02-12 │          0 │       0 │ $              0
 2017-02-13 │ 1020482007 │ 3963509 │ $ 38,745,317,913
 2017-02-14 │ 1041009698 │ 4299974 │ $ 40,737,106,101
 2017-02-15 │ 1120119333 │ 4424251 │ $ 43,802,653,477
 2017-02-16 │ 1091339672 │ 4461548 │ $ 41,956,691,405
 2017-02-17 │ 1160693221 │ 4132233 │ $ 48,862,504,551
 2017-02-18 │          0 │       0 │ $              0
 2017-02-19 │          0 │       0 │ $              0
 2017-02-20 │          0 │       0 │ $              0
 2017-02-21 │ 1103777644 │ 4323282 │ $ 44,416,927,777
 2017-02-22 │ 1064236648 │ 4169982 │ $ 41,137,731,714
 2017-02-23 │ 1192772644 │ 4839887 │ $ 44,254,446,593
 2017-02-24 │ 1187320171 │ 4656770 │ $ 45,229,398,830
 2017-02-25 │          0 │       0 │ $              0
 2017-02-26 │          0 │       0 │ $              0
 2017-02-27 │ 1132693382 │ 4243911 │ $ 43,613,734,358
 2017-02-28 │ 1455597403 │ 4789769 │ $ 57,874,495,227
(28 rows)

请注意，我们用一个简单的 SQL 查询替换了 60 行 Python 代码。从长远来看，这意味着更少的代码需要维护，并且实现也更有效率。在这里，Python 正在执行 Hash Join Nested Loop，而 PostgreSQL 选择在两个有序关系上执行 Merge Left Join。

计算每周变化

想象一下，分析部门现在希望我们提供每天的每周差异。这意味着我们需要添加一列，其中包含在每个日期和前一周的同一天之间计算出的 dollars 列的百分比变化。

我使用“每周百分比差异”示例，因为它既是经典分析需求（尽管可能主要在营销圈中），而且（以我的经验）开发人员的第一个反应很少是编写 SQL 查询来完成所有数学运算。

此外，日历对于计算周数不是很有帮助，但对于 PostgreSQL 来说，此任务就像拼写单词 week 一样容易

with computed_data as
(
  select cast(date as date)   as date,
         to_char(date, 'Dy')  as day,
         coalesce(dollars, 0) as dollars,
         lag(dollars, 1)
           over(
             partition by extract('isodow' from date)
                 order by date
           )
         as last_week_dollars
    from /*
          * Generate the month calendar, plus a week before
          * so that we have values to compare dollars against
          * even for the first week of the month.
          */
         generate_series(date :'start' - interval '1 week',
                         date :'start' + interval '1 month'
                                       - interval '1 day',
                         interval '1 day'
         )
         as calendar(date)
         left join factbook using(date)
)
  select date, day,
         to_char(
             coalesce(dollars, 0),
             'L99G999G999G999'
         ) as dollars,
         case when dollars is not null
               and dollars <> 0
              then round(  100.0
                         * (dollars - last_week_dollars)
                         / dollars
                       , 2)
          end
         as "WoW %"
    from computed_data
   where date >= date :'start'
order by date;

为了在 SQL 中实现此用例，我们需要在 1992 年的 SQL 标准中出现的窗口函数，但在 SQL 课程中经常跳过。SQL 语句中最后执行的是 windows 函数，远在 join 操作和 where 子句之后。因此，如果我们想查看 2 月 1 日之前的整整一周，我们需要将我们的日历选择向过去扩展一周，然后再次限制我们向调用者发布的数据。

这就是为什么我们使用公共表表达式——查询的 WITH 部分——来获取我们需要扩展的数据集，包括计算出的 last_week_dollars 列。

表达式 extract('isodow' from date) 是一项标准的 SQL 功能，允许按照 ISO 规则计算星期几。用作 partition by 框架子句，它允许一行与任何其他具有相同 isodow 的行成为对等行。然后，当按日期排序时，lag() 窗口函数可以引用前一个对等dollars值；这就是我们要与当前dollars值进行比较的数字。

然后，computed_data 结果集在查询的主要部分中用作我们从中获取数据的关系，并且这次计算更容易，因为我们只是将经典的差异百分比公式应用于 dollars 和 last_week_dollars 列。

以下是运行此查询的结果

    date    │ day │     dollars      │ WoW %  
════════════╪═════╪══════════════════╪════════
 2017-02-01 │ Wed │ $ 44,660,060,305 │  -2.21
 2017-02-02 │ Thu │ $ 43,276,102,903 │   1.71
 2017-02-03 │ Fri │ $ 42,801,562,275 │  10.86
 2017-02-04 │ Sat │ $              0 │      ¤
 2017-02-05 │ Sun │ $              0 │      ¤
 2017-02-06 │ Mon │ $ 37,300,908,120 │  -9.64
 2017-02-07 │ Tue │ $ 39,754,062,721 │ -37.41
 2017-02-08 │ Wed │ $ 40,491,648,732 │ -10.29
 2017-02-09 │ Thu │ $ 40,169,585,511 │  -7.73
 2017-02-10 │ Fri │ $ 38,347,515,768 │ -11.61
 2017-02-11 │ Sat │ $              0 │      ¤
 2017-02-12 │ Sun │ $              0 │      ¤
 2017-02-13 │ Mon │ $ 38,745,317,913 │   3.73
 2017-02-14 │ Tue │ $ 40,737,106,101 │   2.41
 2017-02-15 │ Wed │ $ 43,802,653,477 │   7.56
 2017-02-16 │ Thu │ $ 41,956,691,405 │   4.26
 2017-02-17 │ Fri │ $ 48,862,504,551 │  21.52
 2017-02-18 │ Sat │ $              0 │      ¤
 2017-02-19 │ Sun │ $              0 │      ¤
 2017-02-20 │ Mon │ $              0 │      ¤
 2017-02-21 │ Tue │ $ 44,416,927,777 │   8.28
 2017-02-22 │ Wed │ $ 41,137,731,714 │  -6.48
 2017-02-23 │ Thu │ $ 44,254,446,593 │   5.19
 2017-02-24 │ Fri │ $ 45,229,398,830 │  -8.03
 2017-02-25 │ Sat │ $              0 │      ¤
 2017-02-26 │ Sun │ $              0 │      ¤
 2017-02-27 │ Mon │ $ 43,613,734,358 │      ¤
 2017-02-28 │ Tue │ $ 57,874,495,227 │  23.25
(28 rows)

祝您编写代码愉快，并且由于 SQL 也是代码，祝您编写 SQL 愉快！

本文摘自 Dimitri Fontaine 的著作《Mastering PostgreSQL in Application Development》，该书解释了如何用简单的查询替换数千行代码。本书更详细地介绍了这些主题，并提供了许多其他示例，以便您可以掌握 PostgreSQL 并发出 SQL 查询，从而准确获取您需要的结果集。

标签

Python