Jupyter Notebook で PostgreSQL を使う

# はじめに
jupyterからpostgreSQLを使用できるよう環境を構築します。

# 参考
- [CentOS で PostgreSQL を使ってみよう！(2)](https://lets.postgresql.jp/documents/tutorial/centos/2)
- [postgresqlにログインできない（Ident authentication failedエラー）](http://blog.atwata.com/db/2017/09/07/postgresql-login.html)
- [GitHub - catherinedevlin/ipython-sql](https://github.com/catherinedevlin/ipython-sql)

# 環境
- Amazon Linux 2
- jupyterはインストール済み

# 手順
## postgreSQLをインストール
[このサイト](https://www.postgresql.org/download/linux/redhat/)にアクセスして、PostgreSQL Yum Repositoryの欄にインストールしたいバージョンやOSを指定すると、yumのコマンドが表示されます。
今回はバージョン11、CentOS7としました。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/211712/d1451274-52ac-064f-20ab-79556275bd64.png)

表示されたコマンドでインストールしていきます。
4.Install the repository RPM:

```
# yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm

読み込んだプラグイン:extras_suggestions, langpacks, priorities, update-motd
pgdg-centos11-11-2.noarch.rpm                                             | 4.8 kB  00:00:00
/var/tmp/yum-root-EFCp1J/pgdg-centos11-11-2.noarch.rpm を調べています: pgdg-centos11-11-2.noarch
/var/tmp/yum-root-EFCp1J/pgdg-centos11-11-2.noarch.rpm をインストール済みとして設定しています
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ pgdg-centos11.noarch 0:11-2 を インストール
--> 依存性解決を終了しました。
amzn2-core/2/x86_64                                                       | 2.4 kB  00:00:00

依存性を解決しました

=================================================================================================
 Package                アーキテクチャー
                                        バージョン     リポジトリー                         容量
=================================================================================================
インストール中:
 pgdg-centos11          noarch          11-2           /pgdg-centos11-11-2.noarch          2.7 k

トランザクションの要約
=================================================================================================
インストール  1 パッケージ

合計容量: 2.7 k
インストール容量: 2.7 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  インストール中          : pgdg-centos11-11-2.noarch                                        1/1
  検証中                  : pgdg-centos11-11-2.noarch                                        1/1

インストール:
  pgdg-centos11.noarch 0:11-2

完了しました!
```

5.Install the client packages:
がっ……駄目っ……!

```
# yum install postgresql11

読み込んだプラグイン:extras_suggestions, langpacks, priorities, update-motd
amzn2-core                                                                | 2.4 kB  00:00:00
https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-2-x86_64/repodata/repomd.xml: [Errno 14] HTTPS Error 404 - Not Found
他のミラーを試します。


 One of the configured repositories failed (PostgreSQL 11 2 - x86_64),
 and yum doesn't have enough cached data to continue. At this point the only
 safe thing yum can do is fail. There are a few ways to work "fix" this:

     1. Contact the upstream for the repository and get them to fix the problem.

     2. Reconfigure the baseurl/etc. for the repository, to point to a working
        upstream. This is most often useful if you are using a newer
        distribution release than is supported by the repository (and the
        packages for the previous distribution release still work).

     3. Run the command with the repository temporarily disabled
            yum --disablerepo=pgdg11 ...

     4. Disable the repository permanently, so yum won't use it by default. Yum
        will then just ignore the repository until you permanently enable it
        again or use --enablerepo for temporary usage:

            yum-config-manager --disable pgdg11
        or
            subscription-manager repos --disable=pgdg11

     5. Configure the failing repository to be skipped, if it is unavailable.
        Note that yum will try to contact the repo. when it runs most commands,
        so will have to try and fail each time (and thus. yum will be be much
        slower). If it is a very temporary problem though, this is often a nice
        compromise:

            yum-config-manager --save --setopt=pgdg11.skip_if_unavailable=true

failure: repodata/repomd.xml from pgdg11: [Errno 256] No more mirrors to try.
https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-2-x86_64/repodata/repomd.xml: [Errno 14] HTTPS Error 404 - Not Found
```

404 not foundになってしまいました。見に行っているURLを見てみます。
`https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-2-x86_64/repodata/repomd.xml`
このrhel-2-x86_64の部分が古くて404になっているようです。

rpmの中身を確認してみます。

```
# cat /etc/yum.repos.d/pgdg-11-centos.repo

[pgdg11]
name=PostgreSQL 11 $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-11

[pgdg11-source]
name=PostgreSQL 11 $releasever - $basearch - Source
failovermethod=priority
baseurl=https://download.postgresql.org/pub/repos/yum/srpms/11/redhat/rhel-$releasever-$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-11

[pgdg11-updates-testing]
name=PostgreSQL 11 $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/testing/11/redhat/rhel-$releasever-$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-11

[pgdg11-source-updates-testing]
name=PostgreSQL 11 $releasever - $basearch - Source
failovermethod=priority
baseurl=https://download.postgresql.org/pub/repos/yum/srpms/testing/11/redhat/rhel-$releasever-$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-11
```
`enabled=1`となっている部分の`baseurl`は次のようになっています。

```
baseurl=https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-$releasever-$basearch
```
`$releasever`の値が2になっているせいで、正しく動作しないようです。
`$releasever`の値は`/etc/yum.conf`設定ファイルにある`distroverpkg=value`に該当します。

```
# cat /etc/yum.conf

[main]
cachedir=/var/cache/yum/$basearch/$releasever
keepcache=0
debuglevel=2
logfile=/var/log/yum.log
exactarch=1
obsoletes=1
gpgcheck=1
plugins=1
installonly_limit=3
distroverpkg=system-release
timeout=5
retries=7


#  This is the default, if you make this bigger yum won't see if the metadata
# is newer on the remote and so you'll "gain" the bandwidth of not having to
# download the new metadata and "pay" for it by yum not having correct
# information.
#  It is esp. important, to have correct metadata, for distributions like
# Fedora which don't keep old packages around. If you don't like this checking
# interupting your command line usage, it's much better to have something
# manually check the metadata once an hour (yum-updatesd will do this).
# metadata_expire=90m

# PUT YOUR REPOS HERE OR IN separate files named file.repo
# in /etc/yum.repos.d
```
どうもamazon linuxのリリースバージョンである2が入ってしまうようです。

```
# cat /proc/version

Linux version 4.14.88-88.73.amzn2.x86_64 (mockbuild@ip-10-0-1-212) (gcc version 7.3.1 20180303 (Red Hat 7.3.1-5) (GCC)) #1 SMP Thu Dec 13 18:04:55 UTC 2018
```

そこで`baseurl`を書き換えます。

```
vi /etc/yum.repos.d/pgdg-11-centos.repo

[pgdg11]
# name=PostgreSQL 11 $releasever - $basearch
name=PostgreSQL 11 7 - $basearch
# baseurl=https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-$releasever-$basearch
baseurl=https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-11
```
`yum install postgresql11`でインストールします。

```
# yum install postgresql11
読み込んだプラグイン:extras_suggestions, langpacks, priorities, update-motd
amzn2-core                                                                | 2.4 kB  00:00:00
pgdg11                                                                    | 4.1 kB  00:00:00
(1/2): pgdg11/x86_64/group_gz                                             |  245 B  00:00:00
pgdg11/x86_64/primary_db       FAILED
https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/repodata/7d54a8188234c66b14bfb44661b7870065efc37e143933f6a1165a553263ce77-primary.sqlite.bz2: [Errno 14] HTTPS Error 404 - Not Found
他のミラーを試します。
pgdg11/x86_64/primary_db                                                  | 141 kB  00:00:01
6 packages excluded due to repository priority protections
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ postgresql11.x86_64 0:11.1-1PGDG.rhel7 を インストール
--> 依存性の処理をしています: postgresql11-libs(x86-64) = 11.1-1PGDG.rhel7 のパッケージ: postgresql11-11.1-1PGDG.rhel7.x86_64
--> 依存性の処理をしています: libpq.so.5()(64bit) のパッケージ: postgresql11-11.1-1PGDG.rhel7.x86_64
--> トランザクションの確認を実行しています。
---> パッケージ postgresql11-libs.x86_64 0:11.1-1PGDG.rhel7 を インストール
--> 依存性解決を終了しました。

依存性を解決しました

=================================================================================================
 Package                     アーキテクチャー バージョン                  リポジトリー      容量
=================================================================================================
インストール中:
 postgresql11                x86_64           11.1-1PGDG.rhel7            pgdg11           1.6 M
依存性関連でのインストールをします:
 postgresql11-libs           x86_64           11.1-1PGDG.rhel7            pgdg11           359 k

トランザクションの要約
=================================================================================================
インストール  1 パッケージ (+1 個の依存関係のパッケージ)

総ダウンロード容量: 2.0 M
インストール容量: 10 M
Is this ok [y/d/N]: y
Downloading packages:
(1/2): postgresql11-11.1-1PGDG.rhel7.x86_64.rpm                           | 1.6 MB  00:00:01
(2/2): postgresql11-libs-11.1-1PGDG.rhel7.x86_64.rpm                      | 359 kB  00:00:01
-------------------------------------------------------------------------------------------------
合計                                                             1.2 MB/s | 2.0 MB  00:00:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  インストール中          : postgresql11-libs-11.1-1PGDG.rhel7.x86_64                        1/2
  インストール中          : postgresql11-11.1-1PGDG.rhel7.x86_64                             2/2
  検証中                  : postgresql11-11.1-1PGDG.rhel7.x86_64                             1/2
  検証中                  : postgresql11-libs-11.1-1PGDG.rhel7.x86_64                        2/2

インストール:
  postgresql11.x86_64 0:11.1-1PGDG.rhel7

依存性関連をインストールしました:
  postgresql11-libs.x86_64 0:11.1-1PGDG.rhel7

完了しました!
```

`yum install postgresql11-server`でサーバプログラムをインストールします。

```
# yum install postgresql11-server
読み込んだプラグイン:extras_suggestions, langpacks, priorities, update-motd
6 packages excluded due to repository priority protections
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ postgresql11-server.x86_64 0:11.1-1PGDG.rhel7 を インストール
--> 依存性解決を終了しました。

依存性を解決しました

=================================================================================================
 Package                      アーキテクチャー
                                              バージョン                   リポジトリー     容量
=================================================================================================
インストール中:
 postgresql11-server          x86_64          11.1-1PGDG.rhel7             pgdg11          4.7 M

トランザクションの要約
=================================================================================================
インストール  1 パッケージ

総ダウンロード容量: 4.7 M
インストール容量: 19 M
Is this ok [y/d/N]: y
Downloading packages:
postgresql11-server-11.1-1PGDG.rhel7.x86_64.rpm                           | 4.7 MB  00:00:04
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  インストール中          : postgresql11-server-11.1-1PGDG.rhel7.x86_64                      1/1
  検証中                  : postgresql11-server-11.1-1PGDG.rhel7.x86_64                      1/1

インストール:
  postgresql11-server.x86_64 0:11.1-1PGDG.rhel7

完了しました!
```

インストール先は`/usr/pgsql-11`です。
次にデータベースの初期化と自動起動を設定します。

```
# /usr/pgsql-11/bin/postgresql-11-setup initdb
Initializing database ... OK

# systemctl enable postgresql-11
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-11.service to /usr/lib/systemd/system/postgresql-11.service.

# systemctl start postgresql-11
```

パスが通っているか確認します。

```
# psql --version
psql (PostgreSQL) 11.1
```

postgresユーザが作成されていることを確認します。

```
# cat /etc/passwd

postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash
```

## 設定ファイルを編集
ipアドレスとポートを設定します。

```
# vi /var/lib/pgsql/11/data/postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = 'localhost'          # what IP address(es) to listen on; # コメントアウト解除
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart) # コメントアウト解除
```

サービスを再起動します。

```
systemctl restart postgresql-11
```

## データベースにアクセス
ユーザpostgresにsuします。

```
# su postgres
bash-4.2$
```
`psql -l`でデータベースの一覧を表示します。

```
ash-4.2$ psql -l
                                         データベース一覧
   名前    |  所有者  | エンコーディング |  照合順序   | Ctype(変換演算子) |     アクセス権限

-----------+----------+------------------+-------------+-------------------+---------------------
--
 postgres  | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       |
 template0 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres
 +
           |          |                  |             |                   | postgres=CTc/postgre
s
 template1 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres
 +
           |          |                  |             |                   | postgres=CTc/postgre
s
(3 行)
```
データベースにアクセスできました。

## データベースの認証設定
次に認証まわりの設定をします。
ユーザpostgresにパスワードを設定します。パスワードは"postgres"とします。

```
# su - postgres
最終ログイン: 2019/01/03 (木) 19:39:37 JST日時 pts/0

-bash-4.2$ psql
psql (11.1)
"help" でヘルプを表示します。

postgres=# alter role postgres with password 'postgres';
ALTER ROLE
```
データベース接続時の認証方法を設定します。一番右の`METHOD`を`md5`にすれば、パスワード認証になります。

```
# vi /var/lib/pgsql/11/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
# local   all             all                                     peer #コメントアウト
local   all             all                                     md5 # 追加
# IPv4 local connections:
# host    all             all             127.0.0.1/32            ident #コメントアウト
host    all             all             127.0.0.1/32            md5 # 追加
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
```

サービスを再起動します。

```
systemctl restart postgresql-11
```
これで認証の設定が完了です。これができていないとjupyterから接続しようとしたときに、`OperationalError: FATAL:  ユーザ"postgres"のIdent認証に失敗しました`というエラーが出ます。

## おまけ
この状態だとデータベースに何も入っていないので、サンプルデータを登録します。
[こちら](http://www.postgresqltutorial.com/postgresql-sample-database/)からDVD Rental sample databaseをダウンロードします。

サーバに配備し、解凍します。

```
# unzip dvdrental.zip
Archive:  dvdrental.zip
inflating: dvdrental.tar
```
データベースを作成します。まず、suして、

```
# su postgres
bash-4.2$
```
postgresユーザに切り替えます。

```
bash-4.2$ psql -U postgres
psql (11.1)
"help" でヘルプを表示します。

postgres=#
```
この状態で`CREATE DATABASE`コマンドを打ちます。

```
postgres=# CREATE DATABASE dvdrental;
CREATE DATABASE
```

\lコマンドでデータベース一覧が見られます。

```
postgres=# \l
                                         データベース一覧
   名前    |  所有者  | エンコーディング |  照合順序   | Ctype(変換演算子) |
 アクセス権限
-----------+----------+------------------+-------------+-------------------+----
-------------------
 dvdrental | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       |
 postgres  | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       |
 template0 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/
postgres          +
           |          |                  |             |                   | pos
tgres=CTc/postgres
 template1 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/
postgres          +
           |          |                  |             |                   | pos
tgres=CTc/postgres
(4 行)
```
解凍したデータをデータベースに投入します。`\q`でデータベースから戻り、下記コマンドを実行します。

```
bash-4.2$ pg_restore -U postgres -d dvdrental ./dvdrental.tar

pg_restore: [アーカイバ(db)] TOC処理中にエラーがありました:
pg_restore: [アーカイバ(db)] TOCエントリ6; 2615 2200 SCHEMA public postgresのエ ラーです
pg_restore: [アーカイバ(db)] could not execute query: ERROR:  スキーマ"public"はすでに存在します
   コマンド: CREATE SCHEMA public;

警告: リストアにてエラーを無視しました: 1
```
警告は無視して大丈夫です（多分）。
リストアされたデータベースを見てみます。

```
bash-4.2$ psql dvdrental
psql (11.1)
"help" でヘルプを表示します。

dvdrental=# \d
                       リレーション一覧
 スキーマ |            名前            |     型     |  所有者
----------+----------------------------+------------+----------
 public   | actor                      | テーブル   | postgres
 public   | actor_actor_id_seq         | シーケンス | postgres
 public   | actor_info                 | ビュー     | postgres
 public   | address                    | テーブル   | postgres
 public   | address_address_id_seq     | シーケンス | postgres
 public   | category                   | テーブル   | postgres
 public   | category_category_id_seq   | シーケンス | postgres
 public   | city                       | テーブル   | postgres
 public   | city_city_id_seq           | シーケンス | postgres
 public   | country                    | テーブル   | postgres
 public   | country_country_id_seq     | シーケンス | postgres
 public   | customer                   | テーブル   | postgres
 public   | customer_customer_id_seq   | シーケンス | postgres
 public   | customer_list              | ビュー     | postgres
 public   | film                       | テーブル   | postgres
 public   | film_actor                 | テーブル   | postgres
 public   | film_category              | テーブル   | postgres
 public   | film_film_id_seq           | シーケンス | postgres
 public   | film_list                  | ビュー     | postgres
 public   | inventory                  | テーブル   | postgres
 public   | inventory_inventory_id_seq | シーケンス | postgres
 public   | language                   | テーブル   | postgres
 public   | language_language_id_seq   | シーケンス | postgres
 public   | nicer_but_slower_film_list | ビュー     | postgres
 public   | payment                    | テーブル   | postgres
 public   | payment_payment_id_seq     | シーケンス | postgres
 public   | rental                     | テーブル   | postgres
 public   | rental_rental_id_seq       | シーケンス | postgres
 public   | sales_by_film_category     | ビュー     | postgres
 public   | sales_by_store             | ビュー     | postgres
 public   | staff                      | テーブル   | postgres
 public   | staff_list                 | ビュー     | postgres
 public   | staff_staff_id_seq         | シーケンス | postgres
 public   | store                      | テーブル   | postgres
 public   | store_store_id_seq         | シーケンス | postgres
(35 行)
```
これでサンプルデータベースを作成できました。

## pythonモジュールのインストール
pythonのモジュール`psycopg2`をインストールします。

```
# conda install psycopg2

Solving environment: done

## Package Plan ##

  environment location: /root/anaconda3/envs/py37

  added / updated specs:
    - psycopg2


The following packages will be downloaded:

    package                    |            build
    ---------------------------|-----------------
    libpq-11.1                 |       h20c2e04_0         2.6 MB
    psycopg2-2.7.6.1           |   py36h1ba5d50_0         309 KB
    ------------------------------------------------------------
                                           Total:         2.9 MB

The following NEW packages will be INSTALLED:

    libpq:    11.1-h20c2e04_0
    psycopg2: 2.7.6.1-py36h1ba5d50_0

Proceed ([y]/n)? y


Downloading and Extracting Packages
libpq-11.1           | 2.6 MB    | ###################################################### | 100%
psycopg2-2.7.6.1     | 309 KB    | ###################################################### | 100%
Preparing transaction: done
Verifying transaction: done
Executing transaction: done
```

pythonのモジュール`ipython-sql`をインストールします。通常のcondaでは入らないので下記のコマンドを使用します。参考：[conda-forge  / packages / ipython-sql 0.3.9](https://anaconda.org/conda-forge/ipython-sql)
もしくはpipでインストールします。

```
conda install -c conda-forge ipython-sql 
```



## jupyter notebookでデータベースを操作（psycopg2）
jupyter notebookを開きます。

pythonのモジュール`psycopg2`を使用した接続をしてみます。この接続の仕方はスクリプトで書いた場合の接続と同じで、webアプリケーションでデータベースを使用するときの接続方法です。

まずデータベースに接続します。コネクションを張ります。

```python
import psycopg2

conn = psycopg2.connect(
    host="localhost", # IPアドレス
    database="rentaldvd", # DB名
    port="5432", # ポート
    user="postgres", # ユーザ名
    password="postgres" # パスワード
)
```

select文を発行します。`cursor.fetchall()`で、クエリの実行結果すべてをリストとして受け取ります。`pd.DataFrame(results)`とすればデータフレームになりますが、ヘッダーはありません。

```python
cursor = conn.cursor() #  Cursor オブジェクト作成
cursor.execute("SELECT * FROM city") # クエリ実行
results = cursor.fetchall() # クエリの応答をすべてresultsに格納

for row in results:
  print(row)

conn.commit() # クエリ実行結果をコミット
cursor.close() # Cursor オブジェクトを閉じる
conn.close() # コネクションを切断

# 以下出力
(1, 'A Corua (La Corua)', 87, datetime.datetime(2006, 2, 15, 9, 45, 25))
(2, 'Abha', 82, datetime.datetime(2006, 2, 15, 9, 45, 25))
(3, 'Abu Dhabi', 101, datetime.datetime(2006, 2, 15, 9, 45, 25))
(4, 'Acua', 60, datetime.datetime(2006, 2, 15, 9, 45, 25))
(5, 'Adana', 97, datetime.datetime(2006, 2, 15, 9, 45, 25))
(6, 'Addis Abeba', 31, datetime.datetime(2006, 2, 15, 9, 45, 25))
(7, 'Aden', 107, datetime.datetime(2006, 2, 15, 9, 45, 25))
(8, 'Adoni', 44, datetime.datetime(2006, 2, 15, 9, 45, 25))
(9, 'Ahmadnagar', 44, datetime.datetime(2006, 2, 15, 9, 45, 25))
(10, 'Akishima', 50, datetime.datetime(2006, 2, 15, 9, 45, 25))
...
```

クエリの実行結果を1行ずつ読み出したい場合は`cursor.fetchone()`を使用します。呼び出すたびにシーケンスが進み、最後は`None`を返します。

```python
cursor = conn.cursor()
cursor.execute("SELECT * FROM city")

record = cursor.fetchone()
while record != None:
    print(record)
    record = cursor.fetchone()

conn.commit()
cursor.close()
```
ただし、クエリ実行時点で検索結果はすべてクライアントに送信されているので、メモリの節約にはなりません。メモリ節約のためにはサーバサイドカーソルにする必要があり、これには名前付きカーソルを使用します。参考：[PythonとDB: DBIのcursorを理解する](https://qiita.com/knoguchi/items/3d5631505b3f08fa37cc)

```python
with conn.cursor('hoge') as cursor:
    cursor.execute("SELECT * FROM city")
    
    record = cursor.fetchone()
    while record != None:
        print(record)
        record = cursor.fetchone()

conn.commit()
conn.close()
```

## jupyter notebookでデータベースを操作（ipython-sql）
より直接的にSQLを書きたい場合はマジックコマンドを使用します。`%load_ext sql`でSQL拡張します。

```python
%load_ext sql
# dsl = 'postgres://{user}:{password}@{host}:{port}/{database}'
dsl = 'postgres://postgres:postgres@localhost:5432/dvdrental'
%sql $dsl

# 以下は出力
The sql extension is already loaded. To reload it, use:
  %reload_ext sql
'Connected: postgres@dvdrental'
```

select文は次のように書きます。

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

# 以下は出力
 * postgres://postgres:***@localhost:5432/dvdrental
10 rows affected.
```
![image.png](https://qiita-image-store.s3.amazonaws.com/0/211712/732f0ab7-62f1-d832-2db5-6c9badae7f5a.png)

ちなみに実行結果は`_`に格納されています。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/211712/4952ad76-6292-34d6-e9c6-14fae4328a44.png)

型は`sql.run.ResultSet`という型になっています。

```python
type(_)

# 以下は出力
sql.run.ResultSet
```
もちろん変数に格納することもできます。%が二つから一つになります。

```python
result = %sql select * from actor order by actor_id limit 10

# 以下は出力
 * postgres://postgres:***@localhost:5432/dvdrental
10 rows affected.
```
![image.png](https://qiita-image-store.s3.amazonaws.com/0/211712/813180f7-b709-ccf0-ff05-562eaa9df747.png)

`.DataFrame()`でデータフレームに変換できます。

```
result.DataFrame()
```
もしくは下記を書いておくことで自動でpandasに変換してくれます。

```python
%config SqlMagic.autopandas = True

df = %sql select * from actor order by actor_id
type(df)

# 以下は出力
 * postgres://postgres:***@localhost:5432/dvdrental
200 rows affected.
pandas.core.frame.DataFrame
```

## 注意
`\d`などのpsqlコマンドを使用する場合は[pgspecial](https://pypi.org/project/pgspecial/)を追加で導入する必要があります。

# おわりに
jupyterからpostgreSQLを使えるようにしました。これでSQLでのデータ成型とpandasやmatplotlibを用いた可視化など組み合わせて利用できるようになりました。