Win10 + VsCode + Python3 + psycopg2 から Ubuntu 20.04 + Posgtgres12 にアクセスしてみる

目的

Ubuntu 20.04 に Posgtgres12 をインストール後 C# + Npgsql でアクセスしてみる
でインストしたPostgresをもう少し触ってみる
・新規roleを作成する
・新規DB(demo)を作成する
・作成済のroleに対して新規作成DB(demo)に対する権限を付与する
・作成したテーブルに対して Win10 + VsCode + Python3 + psycopg2 からアクセスしてみる
・Ubuntu + VsCode + Python3 の場合の追加設定 Ubuntu 16.04 x64 + VsCode + Code Runner で Python3を使ってみる

アクセス環境の設定

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

postgres=# create role demo with login password 'passwd';
postgres=# create database demo;
postgres=# grant all on database demo to demo;

$ 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 行)

psycopg2のインストール(Windows版)

$ sudo pip3 install psycopg2

対象テーブル

データの並びは以下に準拠
郵便番号データの説明
対象データにユニークキーになりそうなデータが無いので
8桁のテキストを追加する
※Accessかリンクを張るときにも ユニークキーは必須なので
以下 role demo で作成する(自分はA5M2を使用)

create table zipcode(
    seq varchar(8) not null,
    prefcode varchar(3) null,
    kubuncode varchar(8) null,
    postal5 varchar(5) null,
    postal varchar(8) null,
    prefkana varchar(20) null,
    citykana varchar(40) null,
    addrkana varchar(80) null,
    prefkanji varchar(20) null,
    citykanji varchar(40) null,
    addrkanji varchar(80) null,
    flg1 smallint null,
    flg2 smallint null,
    flg3 smallint null,
    flg4 smallint null,
    flg5 smallint null,
    flg6 smallint null
) 

サンプルコード

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

import psycopg2

server   = '192.168.5.44' 
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()

# autocommit しない
conn.autocommit = False

cur.execute('TRUNCATE TABLE ZIPCODE')

# 一行ずつ読み込んで挿入する：17ISHIKA.CSVはsjis
with open("17ISHIKA.CSV", "r", encoding='shift-jis') as f:
    for line in f:
        count = count + 1
        ary = line[:-1].replace('"', '').split(",")
        sql = 'insert into zipcode (' \
            + '  seq' \
            + ', prefcode' \
            + ', kubuncode' \
            + ', postal5' \
            + ', postal' \
            + ', prefkana' \
            + ', citykana' \
            + ', addrkana' \
            + ', prefkanji' \
            + ', citykanji' \
            + ', addrkanji' \
            + ', flg1' \
            + ', flg2' \
            + ', flg3' \
            + ', flg4' \
            + ', flg5' \
            + ', flg6' \
            + ') values (' \
            + '  \'' + str(count).rjust(8, '0') + '\'' \
            + ', \'' + ary[0][:2] + '\'' \
            + ', \'' + ary[0] + '\'' \
            + ', \'' + ary[1] + '\'' \
            + ', \'' + ary[2] + '\'' \
            + ', \'' + ary[3] + '\'' \
            + ', \'' + ary[4] + '\'' \
            + ', \'' + ary[5] + '\'' \
            + ', \'' + ary[6] + '\'' \
            + ', \'' + ary[7] + '\'' \
            + ', \'' + ary[8] + '\'' \
            + ',   ' + ary[9]  \
            + ',   ' + ary[10] \
            + ',   ' + ary[11] \
            + ',   ' + ary[12] \
            + ',   ' + ary[13] \
            + ',   ' + ary[14] \
            + ')'
        cur.execute(sql)

conn.commit()
# 挿入したデータの件数を確認する
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