【Python】CSVのデータをPostgreSQLに格納

CSVのデータをそのままポスグレに設定する場合は、以下のようにPythonファイルを作ります。
PythonファイルからSQLファイルを実行させて、SQLファイル側でCSVファイル内をコピー→INSERTする形になる。
一度tmpテーブルを作成し、それにコピーする形になるのが肝。
これによって、例えばCSVファイルのデータだけでは足りない情報（テーブルへの登録日時や更新日時）を後から追加することができる。後述のSQLファイルではINSERTにて末尾に登録日時と更新日時を追加しています。
　
Pythonで一度取り込んでINSERTする形をとってもいいけど、psqlコマンドでSQLを実行する形にしておいたほうが、呼び出す側がどんな言語を使っていてもSQLの内容に影響を与えない(疎結合)の形になる、というメリットはあるかと。
まぁそこらへんは好みの問題。あと性能的な側面では考慮していないので、悪しからず。
　
ちなみに、Windowsで実行する場合、psqlを実行するのにパスワード入力を省略させるために以下の場所にpasswordファイルを作って格納しておく必要がある。ファイル名は「pgpass.conf」で固定です。
C:\Users\<ユーザ名>\AppData\Roaming\postgresql\pgpass.conf

<host名 or IPアドレス>:<ポート番号>:<DB名>:<ユーザ名>:<パスワード>
例：yyy.yyy.yyy.yyy:5432:postgres:postgres:password

Linuxの場合は、psqlを実行できるユーザでpythonを実行すればパスワードを要求されることはない。
　
【Pythonファイル】
gist.github.com
psqlの -vで変数を設定できます。文中の「file_name=」はSQLファイルに渡す変数です。SQLファイルのほうでは「:file_name」と記述することで渡された変数の中身を参照できます。
　
【SQLファイル】
gist.github.com
変数の指定は「:XXX」の形で書けば勝手に参照してくれます。シングルクォーテーションはいちいちエスケープ文字書くのが面倒なので変数で用意し、それを結合で利用してます。結合に特に＋とかは不要。続けて書くだけ。

なんでcopyコマンドを変数に入れてから実行する形にしてるの？というと、
まず前提として、copyコマンドには「copy」コマンドと「\copy」コマンドがある。(SQLファイル上、\\copyとなっているが、これはエスケープ処理の都合上こうなる)
前者の「copy」は、ポスグレが入っているマシンのローカルディスクのファイル(今回はCSVファイル)を指定することになる。
なのでポスグレが入っているマシンでSQLファイルを実行する場合は「copy」を使えばいい。
問題はアプリ層とデータベース層が分かれているシステムの場合で(まぁ普通はこの構成なんだけど)、
特にAWS Auroraを利用していた場合などは、Aurora内にSQLファイルやCSVファイルを置いておく、ということはできないので「copy」は使えない。
なのでその場合は後者の「\copy」を使う。これはSQLファイルを実行したマシンのディスクにあるファイル(CSVファイル)を指定することになる。
ただ厄介なのが、「copy」コマンドはSQLファイルに直書きしても動いてくれるが、「\copy」のほうはエスケープ文字の関係上、書けば簡単に動いてくれるわけではない。で、そこら辺を調べてると、海外の掲示板でコマンドをあらかじめ変数に入れて用意しとけばいいんだよ、との書き込みがあったのでその通りにしている、という感じになります。
　
SQLファイルのパスの指定はどちらかのプログラムファイルに寄せたほうがいいんだろうけど、まぁとりあえずこれで。