使用Jupyter连接到PostgreSQL

在查看存储在数据库中的数据时，能够重新汇总结果并通过图表进行确认是很方便的。同时，可以将从数据中提取和思考的内容进行记录，以便团队共享。如果不需要建立专门的仪表板，使用Jupyter Notebook作为前端是很方便的。

准备执行环境

使用Docker准备测试环境。使用Jupyter（jupyter/datascience-notebook）和PostgreSQL（postgres）这两个镜像。

首先，启动 PostgreSQL 并将其与 Jupyter 进行链接。由于我们将使用稍后介绍的 pagila 测试数据，因此数据库名称也要相应地设置。您可以通过使用官方的 Docker 镜像来配置它，只需提供相应的环境变量即可。

$ docker run -d --name=db \
             -e POSTGRES_DB=pagila \
             -e POSTGRES_USER=nbuser \
             -e POSTGRES_PASSWORD=nbuser_secret \
             postgres:9.5

接下来，我们将使用8080端口启动Notebook。为了能够从环境变量中获取数据库连接信息，我们会将数据库容器链接起来。链接后，可以直接使用主机名进行引用。

$ docker run -d --name=notebook \
             -p 8080:8888 \
             --link=db:db \
             jupyter/datascience-notebook

另外，在设置连接信息时，请根据实际执行环境进行配置。另外，从笔记本连接的用户如果设置为只读权限，可以防止意外销毁数据的错误。

准备测试数据

在PostgreSQL官方维基的示例数据库中，有一些记录。在这些记录中，我们尝试使用一个移植自MySQL的Sakila示例数据库的pagila。在pagila中，我们需要执行两个SQL文件：一个是表结构文件（pagila-schema.sql），另一个是数据文件（pagila-data.sql或pagila-insert-data.sql）。

从代码仓库获取文件，并启动工作容器以挂载代码仓库的内容。

$ git clone https://github.com/devrimgunduz/pagila.git
$ cd pagila
$ git checkout pagila-0.11.0
$ cd -

$ docker run --rm -it --link=db:db \
             -v `pwd`/pagila:/tmp/pagila \
             -u postgres \
             postgres:9.5 /bin/bash

在容器中，将连接信息记录在密码文件中，并使用psql命令执行SQL文件的内容。当启动容器时，它已经与db容器进行了链接，可以使用以“DB_ENV_”开头的环境变量来引用字符串。尽管命令的表述会变得冗长，但消除了硬编码的内容，这点很方便。

$ export PGPASSFILE=/tmp/.pgpass
$ echo "db:$DB_PORT_5432_TCP_PORT:$DB_ENV_POSTGRES_DB:$DB_ENV_POSTGRES_USER:$DB_ENV_POSTGRES_PASSWORD" > $PGPASSFILE
$ chmod 600 $PGPASSFILE

$ psql -h db -U $DB_ENV_POSTGRES_USER -d $DB_ENV_POSTGRES_DB < /tmp/pagila/pagila-schema.sql
$ psql -h db -U $DB_ENV_POSTGRES_USER -d $DB_ENV_POSTGRES_DB < /tmp/pagila/pagila-data.sql

完成数据注册后，您可以使用psql命令连接并检查表中的数据，这样会更好。

pagila=# \dt
              List of relations
 Schema |       Name       | Type  |  Owner   
--------+------------------+-------+----------
 public | actor            | table | postgres
 public | address          | table | postgres
 public | category         | table | postgres
 public | city             | table | postgres
 public | country          | table | postgres
 public | customer         | table | postgres
 public | film             | table | postgres
 public | film_actor       | table | postgres
 public | film_category    | table | postgres
 public | inventory        | table | postgres
 public | language         | table | postgres
 public | payment          | table | postgres
 public | payment_p2015_01 | table | postgres
 public | payment_p2015_02 | table | postgres
 public | payment_p2015_03 | table | postgres
 public | payment_p2015_04 | table | postgres
 public | payment_p2015_05 | table | postgres
 public | payment_p2015_06 | table | postgres
 public | rental           | table | postgres
 public | staff            | table | postgres
 public | store            | table | postgres
(21 rows)

pagila=# select * from actor order by actor_id limit 10;
 actor_id | first_name |  last_name   |     last_update     
----------+------------+--------------+---------------------
        1 | PENELOPE   | GUINESS      | 2016-02-15 09:34:33
        2 | NICK       | WAHLBERG     | 2016-02-15 09:34:33
        3 | ED         | CHASE        | 2016-02-15 09:34:33
        4 | JENNIFER   | DAVIS        | 2016-02-15 09:34:33
        5 | JOHNNY     | LOLLOBRIGIDA | 2016-02-15 09:34:33
        6 | BETTE      | NICHOLSON    | 2016-02-15 09:34:33
        7 | GRACE      | MOSTEL       | 2016-02-15 09:34:33
        8 | MATTHEW    | JOHANSSON    | 2016-02-15 09:34:33
        9 | JOE        | SWANK        | 2016-02-15 09:34:33
       10 | CHRISTIAN  | GABLE        | 2016-02-15 09:34:33
(10 rows)

在 Jupyter Notebook 中安装库。

打开笔记本，通过浏览器访问启动的端口号。在上述步骤中，它是通过8080号端口启动的。然后，启动Jupyter终端，并安装psycopg2用于PostgreSQL，以及ipython-sql用于简易执行SQL。

• Psycopg – PostgreSQL database adapter for Python

%%sql magic for IPython, hopefully evolving into full SQL client

$ conda install --yes --quiet 'psycopg2=2.6*'
$ conda install --yes --quiet -c sodre 'ipython-sql=0.3*'

关上终端，新建一个Python3的笔记本吧。

使用Jupyter Notebook连接数据库

现在，让我们试着从笔记本电脑连接到数据库。

import os
import psycopg2

pgconfig = {
    'host': 'db',
    'port': os.environ['DB_PORT_5432_TCP_PORT'],
    'database': os.environ['DB_ENV_POSTGRES_DB'],
    'user': os.environ['DB_ENV_POSTGRES_USER'],
    'password': os.environ['DB_ENV_POSTGRES_PASSWORD'],
}

conn = psycopg2.connect(**pgconfig)
cur = conn.cursor()
cur.execute("SELECT * FROM actor;")

cur.fetchone()

(1, 'PENELOPE', 'GUINESS', datetime.datetime(2016, 2, 15, 9, 34, 33))

cur.fetchone()

(2, 'NICK', 'WAHLBERG', datetime.datetime(2016, 2, 15, 9, 34, 33))

cur.close()
conn.close()

我已经确认常规的 Python 脚本可以运行。
接下来，我将使用 ipython-sql 来直接确认 SQL 执行结果。

%load_ext sql
dsl = 'postgres://{user}:{password}@{host}:{port}/{database}'.format(**pgconfig)
%sql $dsl

'Connected: nbuser@pagila'

%%sql
select * from actor order by actor_id limit 10

执行结果将以表格形式显示。

如果将SqlMagic.autopandas参数设置为True，它将以pandas的DataFrame形式处理结果集。

%config SqlMagic.autopandas = True
df = %sql select * from actor order by actor_id
df.describe()

不仅可以简单地获取数据，还可以成功地处理稍微复杂的 SQL。在内部，它通过 sqlalchemy 直接执行 SQL。

%%sql
WITH store_inv AS (
    SELECT store_id, COUNT(*) AS inventory_cnt, COUNT(DISTINCT film_id) AS film_cnt
    FROM inventory GROUP BY 1
), store_staff AS (
    SELECT store_id, COUNT(*) AS staff_cnt
    FROM staff GROUP BY 1
), store_customer AS (
    SELECT store_id, COUNT(*) AS customer_cnt, SUM(active) AS active_customer_cnt
    FROM customer GROUP BY 1
)
SELECT store.store_id, addr.address, addr.district, city.city, country.country,
       store_inv.inventory_cnt, store_inv.film_cnt,
       store_staff.staff_cnt,
       store_customer.customer_cnt, store_customer.active_customer_cnt
  FROM store
       LEFT JOIN address addr USING (address_id)
    　　  LEFT JOIN city USING (city_id)
    　　  LEFT JOIN country USING (country_id)
    　　  LEFT JOIN store_inv USING (store_id)
    　　  LEFT JOIN store_staff USING (store_id)
    　　  LEFT JOIN store_customer USING (store_id)
ORDER BY 1

进行数据汇总并绘制图表结果

你可以把结果作为Pandas数据框获取，然后可以使用任意的方法进行进一步的汇总。此外，你还可以使用Matplotlib绘制图表。由于在jupyter/datascience-notebook镜像中还包含seaborn，所以我们可以试着用它来绘制图表。

import seaborn as sns
%matplotlib inline

在租赁表中，包含了商品出借信息，出借日期记录在rental_date，归还日期记录在return_date。商品的ID用inventory_id表示，顾客的ID用customer_id表示。利用这些信息，我们将计算未归还DVD的顾客人数分布，并绘制条形图。

df = %sql select * from rental
df.head()

dt = []
for k in df.keys():
    print('{:20s} : {:,} ({:,})'.format(k, df[k].count(), df[k].nunique()))

## 結果
rental_id            : 16,044 (16,044)
rental_date          : 16,044 (15,815)
inventory_id         : 16,044 (4,580)
customer_id          : 16,044 (599)
return_date          : 15,861 (15,836)
staff_id             : 16,044 (2)
last_update          : 16,044 (3)

总共有16,044条租借信息，商品数量为4,580件，顾客数量为599人。另外，有15,861件归还日期已经过了，因此减去这些，可以说有183件尚未归还。

通过按顾客ID汇总未登记退还日期的记录，计算每个顾客的未退还DVD数量，然后通过进一步的汇总计算，得出存在未退还DVD的顾客数量的分布。

dd = df[df['return_date'].isnull()].groupby('customer_id').count()[['rental_id',]].reset_index()
dd.groupby('rental_id').count().reset_index().rename(
    columns={'rental_id': 'rental_cnt', 'customer_id': 'customer_cnt'})

我們試著用長條圖來表示這個分佈。

d = _
sns.barplot(x='rental_cnt', y='customer_cnt', data=d)

有很多客户只有一件未归还的DVD，但我也在视觉上确认了有一个客户有三件未归还的DVD。（虽然通过表格的数字就能理解这个内容）

使用变量替换执行查询。

观察上述图表，我们可以说持有三个未归还物品的客户可能是特殊情况，因此我们需要具体指定个人。

dd[dd['rental_id'] == 3]

可以通过知道customer ID为75的人是目标对象来确认，在customer表中具体查看。在这种情况下，可以使用ipython-sql的变量替换，而不是在SQL中硬编码数字。

在Notebook中，可以通过定义普通变量并在SQL语句中加上”:”来进行变量替换。

customer_id = 75
c = %sql select * from customer where customer_id = :customer_id
c.T

由于变量可以接受列表作为参数，因此不需要硬编码特定的ID。在SQL的WHERE子句中，可以使用IN。

customers = tuple([int(i) for i in dd[dd['rental_id'] == 3]['customer_id'].tolist()])
c = %sql select * from customer where customer_id in :customers
c.T

当然，可以得到相同的结果。

结束

我已经确认可以使用Jupyter Notebook连接到PostgreSQL并以数据框的形式获取结果。此外，我还使用pandas的功能对数据进行了处理，并在Notebook上绘制了图表。我认为可以记录思考过程时查看数据非常方便。

以下是一个中国人的母语解释：

参考：

• 現代のエンジニアのための強力なメモ帳 Jupyter notebookのすゝめ – クックパッド開発者ブログ

Jupyter から見た Treasure Data の使い方 – Qiita