郵便番号をPOSTGRESQLでデータベースにインポート

7178 ワード
CSV table DB PostgreSQL Terminal Terminal テキストリンク

MacでPostgreSQLを使用するためPostgres.appをインストール

Postgres.appはアプリケーションフォルダに配置する必要があり、名前を変更することはできません。パスを変更してしまうとライブラリが見つからなくなってしまいます。まずはアプリケーションフォルダにドラッグしてダブルクリックしインストールしましょう。ターミナル上で起動させます。

/Applications/Postgres.app/Contents/Versions/13/bin/psql

Postgreでデータベース・テーブル作成

create database db_name;

create database データベース名;を入力してpostgre内にデータベースを作ります。

drop database db_name;

drop database データベース名;で削除することができます。

\l

\lでデータベースの一覧を確認できます。

create table tb_name
(column1 char(21) NOT NULL,
column2 varchar NOT NULL,
column3 varchar NOT NULL,
column4 varchar NOT NULL);

create table テーブル名で改行して(カラム名 データ型 制約);でテーブルを作成します。データベース名を郵便番号（postal_code）、テーブル名を(post_tb)、カラム（列）名１行目はpostals(郵便)２行目はprefectures(都道府県)３行目はmunicipalities(市町村)４行目はaddress(住所)とします。データ型である長さｎ文字の固定長文字charの(　)内に日本語表示でおよそ１文字3バイト分を取り(7*3=21)ました。制約には一般的に列がNULLを取らないようにNOT NULLを置きます。長さｎ文字の可変長文字列varchar(n)は、ｎを取って直接NOT NULLをマークしました。\d postal_codeでテーブルの構造を表示してみます。 

                     Table "public.postal_code"
     Column     |       Type        | Collation | Nullable | Default 
----------------+-------------------+-----------+----------+---------
 postals        | character(21)     |           | not null | 
 prefectures    | character varying |           | not null | 
 municipalities | character varying |           | not null | 
 address        | character varying |           | not null |

CSV形式の郵便番号データを取得



郵便局の都道府県一覧（ZIP形式）より全国一括郵便番号をダウンロードしてきて、拡張子をCSV形式、エディタで表示できるようにエンコードをUTF-8に変換、さらに郵便番号、都道府県、市町村、住所以外を取り除き、ファイルを保存します。VS-CODEで表示できるか確認してみましょう。
それぞれ( , )カンマで区切られてテーブルに入れられそうになりました。

\COPYでCSVファイルをデータベースに保存

最後にターミナルからコマンドを使用してCSVファイルをデータベースに格納いたします。以下のコマンドを入力して下さい。
\COPY テーブル名 from 'PATH/CSVファイル'with csv

\copy postal_code from 'desktop/KEN_ALL.CSV'with csv

郵便番号KEN_ALLファイルをデスクトップに置いたため、パスは上記のように入力致しました。

COPY 124517

と表示されれば、POSTGRESQLデータベース内のpostal_codeテーブルの中に全国の郵便番号が入りました。select * from テーブル名;でテーブル内のデータ一覧を確認してみます。

select * from postal_code;

        postals        | prefectures |    municipalities  |          address                                    
-----------------------+-------------+--------------------+-----------------------------------
 600000                | 北海道       | 札幌市中央区         | 以下に掲載がない場合
 640941                | 北海道       | 札幌市中央区         | 旭ケ丘
 600041                | 北海道       | 札幌市中央区         | 大通東
 600042                | 北海道       | 札幌市中央区         | 大通西（１～１９丁目）
 640820                | 北海道       | 札幌市中央区         | 大通西（２０～２８丁目）
 600031                | 北海道       | 札幌市中央区         | 北一条東
 600001                | 北海道       | 札幌市中央区         | 北一条西（１～１９丁目）
 640821                | 北海道       | 札幌市中央区         | 北一条西（２０～２８丁目）
 600032                | 北海道       | 札幌市中央区         | 北二条東
 600002                | 北海道       | 札幌市中央区         | 北二条西（１～１９丁目）
 640822                | 北海道       | 札幌市中央区         | 北二条西（２０～２８丁目）
 600033                | 北海道       | 札幌市中央区         | 北三条東
 600003                | 北海道       | 札幌市中央区         | 北三条西（１～１９丁目）
 640823                | 北海道       | 札幌市中央区         | 北三条西（２０～３０丁目）
 600034                | 北海道       | 札幌市中央区         | 北四条東（１～８丁目）
 600004                | 北海道       | 札幌市中央区         | 北四条西（１～１９丁目）
 640824                | 北海道       | 札幌市中央区         | 北四条西（２０～３０丁目）
 600035                | 北海道       | 札幌市中央区         | 北五条東
 600005                | 北海道       | 札幌市中央区         | 北五条西（１～２４丁目）
 640825                | 北海道       | 札幌市中央区         | 北五条西（２５～２９丁目）
 600006                | 北海道       | 札幌市中央区         | 北六条西（１０～２５丁目）
 640826                | 北海道       | 札幌市中央区         | 北六条西（２６～２８丁目）

なんとか、無事に今回のタスクをこなせました。最終的なプロジェクトは本番サーバーで郵便番号検索できるまでなので！　まだまだ初級ですが一つずつ攻略して行こうと思います。