Ubuntu 18.04 + VsCode + Python3 + psycopg2(libpq-dev) から Posgtgres12 にアクセスしてみる

目的

・Ubuntu 18.04(その１)に Posgtgres12 をインストールする
・新規roleを作成する
・新規DB(demo)を作成する
・作成済のroleに対して新規作成DB(demo)に対する権限を付与する
・作成したテーブルに対して Ubuntu 18.04(その２) + VsCode + Python3 + psycopg2 からアクセスしてみる
※Ubuntu 18.04 に psycopg2 のインストするのが意外とめんどくさかったのでメモを残しておく
※libpq-devは昔からめんどくさかった気がする・・・
※psycopg2をビルドする場合は、リポジトを設定後 libpq-dev のみインストールすれば良い
※パッケージ関連で迷った時の apt search はやはり重要 なぜ pip にはないんだろう？？

Code RunnerでPython3をキックする場合の修正点

修正になったバージョンが不明なのだけど 1.46.1 の時点では以下の手順で修正する
・ファイル -> 基本設定 -> 設定
・検索ボックスに Code-runner:Executor Map を入力する

・setting.json で編集をクリックする
 ・修正前
    "code-runner.executorMap": {
    }
 ・修正後
    "code-runner.executorMap": {
        "python": "python3 -u"
    }

インストールの準備

・必要なパッケージをインストールする

$ sudo apt-get install curl ca-certificates gnupg

・鍵を入れる

$ sudo curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

・リポジトリの参照リストに追記する。
aptのリポジトリを順にみていくとv13まであるようだ
※http://apt.postgresql.org/pub/repos/apt/dists/focal-pgdg/13/

$ sudo apt-add-repository 'deb http://apt.postgresql.org/pub/repos/apt bionic-pgdg main'

/etc/apt/sources.list (の最後の行)に

deb http://apt.postgresql.org/pub/repos/apt bionic-pgdg main
# deb-src http://apt.postgresql.org/pub/repos/apt bionic-pgdg main

が追加されるが、以下の様に [arch=amd64] を追加する

deb [arch=amd64] http://apt.postgresql.org/pub/repos/apt bionic-pgdg main
# deb-src [arch=amd64] http://apt.postgresql.org/pub/repos/apt bionic-pgdg main

psycopg2のインストール

psycopg2をビルドする場合は、リポジトを設定後 libpq-dev のみインストールすれば良い

$ sudo apt search postgres | grep libpq
--> libpq-dev/bionic-pgdg,now 12.3-1.pgdg18.04+1 amd64
$ sudo apt install libpq-dev
$ sudo pip3 install psycopg2
Collecting psycopg2
  Downloading ～
Building wheels for collected packages: psycopg2
  Running setup.py bdist_wheel for psycopg2 ... done
  Stored in directory: ～
Successfully built

リポジトリを更新後にPostgresをインストールする

※postgresql-13 のレポジトリは今日現在では使用不可のようだ

$ sudo apt update
$ sudo apt install postgresql-12 pgadmin4

設定ファイルの修正

/etc/postgresql/12/main/postgresql.conf の修正
#コメントアウト
#listen_addresses = 'localhost' # what IP address(es) to listen on;

#以下に修正
listen_addresses = '*'           # what IP address(es) to listen on;

/etc/postgresql/12/main/pg_hba.conf を以下に修正
# "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            md5
host    all             all             192.168.5.0/24            md5
# IPv6 local connections:
#host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
#replication privilege.
#local   replication     all                                     peer
#host    replication     all             127.0.0.1/32            md5
#host    replication     all             ::1/128                 md5

上記２ファイルを修正後pgsqlを再起動する

$ sudo pg_ctlcluster 12 main restart

アクセス環境の設定

$ su - postgres
$ psql -U postgres
ユーザ postgres のパスワード:
psql (12.3 (Ubuntu 12.3-1.pgdg20.04+1))
"help"でヘルプを表示します。

postgres=#  alter role postgres password 'passwd';
ALTER ROLE
postgres=# create role demo with login password 'demo';
CREATE ROLE
postgres=# create database demo;
CREATE DATABASE
postgres=# grant all on database demo to demo;
GRANT

$ psql -U demo
ユーザ demo のパスワード:
psql (12.3 (Ubuntu 12.3-1.pgdg20.04+1))
"help"でヘルプを表示します。

demo=> \c demo
データベース"demo"にユーザ"demo"として接続しました。

※テーブル作成後は以下の様に表示される
demo=> \d
            リレーション一覧
 スキーマ |  名前   |    型    | 所有者
----------+---------+----------+--------
 public   | zipcode | テーブル | demo
(1 行)

対象テーブルに書き込むコード

Ubuntu 20.04 に Posgtgres をインストール後 C# + Npgsql でアクセスしてみる
と同じ

サンプルコード

Ubuntu 18.04にpsycopg2をインストール後動作を確認
以下は Ubuntu 18.04 + VsCode で作成＆動作確認

# Windows Add env PYTHONIOENCODING = UTF-8 & restart vscode
# coding:utf-8

import psycopg2

server   = '192.168.5.49' 
port= '5432'
database = 'demo' 
username = 'demo' 
password = 'passwd'
count = 0
# 接続文字列 - 空白文字がセパレータなのか？？
constr = 'host=' + server + ' port=' + port + ' dbname=' + database + ' user=' + username + ' password=' + password
conn = psycopg2.connect(constr)

cur = conn.cursor()

# 挿入したデータの件数を確認する
cur.execute("SELECT COUNT(*) FROM ZIPCODE")
row = cur.fetchone()

if row:
    print(row)

cur.close()
conn.close()

参考にしたサイトはこちら

NOT NULL制約(カラムにNULLの格納を許可するかどうか)
第8章 データ型
PythonからPostgreSQLに接続する方法
psycopg2 - Python-PostgreSQL Database Adapter
必要ライブラリがありません事案の救世主apt-file
Ubuntu 20.04 に Posgtgres をインストール後 C# + Npgsql でアクセスしてみる
Win10 + VsCode + Python3 + psycopg2 から Ubuntu 20.04 + Posgtgres12 にアクセスしてみる