Pythonを使ってPostgreSQLのDBにデータを入れたい

Question

前提・実現したいこと

Pythonを使ってPostgreSQLのDBにCSVを入れたいです。

以下のコードだと次のエラーがでます。
そのためこれの解決もしくは別の方法での格納方法についてご教示ください。

条件として
・copy_fromを使うような、一度CSVを読み込むなどPythonでメモリを確保する必要がある方法は望んでいません。
・COPYだと動きますが、管理者アカウント？を使わずが望ましいです

発生している問題・エラーメッセージ

psycopg2.errors.SyntaxError: syntax error at or near "\"

該当のソースコード

import psycopg2

sql = r"\copy [テーブル] FROM [CSVファイル] WITH CSV HEADER;".format(table_name, filename)

with psycopg2.connect("****") as conn:
    with conn.cursor() as cur:
        cur.execute(sql)

補足情報（FW/ツールのバージョンなど）

Python3.X
モジュールは最新のもので考えていただいて構いません。

Answer 1

\copyはCOPYコマンドと異なりpsqlのコマンドですので、execute()で流し込むSQLとして使用することは出来ません。メモリ確保をしたくないということですので恐らく比較的ファイルサイズの大きなCSVファイルだと想像します。そのような場合、経験上INSERT文よりもCOPYコマンドのほうが(圧倒的に)高速ですので、特段の理由がない限りpsqlからCOPYコマンドを使うことをお勧めします。

遅くても構わないのでPythonで行いたいということであれば(copy_fromを使わない方法としては)、INSERT文を組み立てることになります。一行ずつだと遅いのでcommitはある程度まとまってからcommitするほうが良いです。

import csv
import psycopg2

sql = "INSERT INTO [テーブル] VALUES (%s, %s, %s)"  # %sはカラムの数だけ書く
commit_limit = 1000  # 1000回に1回コミットする
with psycopg2.connect("****") as conn, open('[CSVファイル]', 'r') as f:
    conn.autocommit = False
    with conn.cursor() as cur:
        reader = csv.reader(f)
        next(reader)  # ヘッダ行をスキップ
        for i, row in enumerate(reader):
            cur.execute(sql, row)
            if i % commit_limit == (commit_limit - 1):
                cur.commit()
        cur.commit()

あるいはPrepared statementを使って

import csv
import psycopg2

sql = "INSERT INTO [テーブル] VALUES (%s, %s, %s)"  # %sはカラムの数だけ書く
commit_limit = 1000  # 1000回に1回コミットする
with psycopg2.connect("****") as conn, open('[CSVファイル]', 'r') as f:
    with conn.cursor() as cur:
        reader = csv.reader(f)
        next(reader)  # ヘッダ行をスキップ
        rows = []
        for i, row in enumerate(reader):
            rows.append(row)
            if i % commit_limit == (commit_limit - 1):
                cur.executemany(sql, rows)
                rows = []
        if len(rows) > 0:        
            cur.executemany(sql, rows)

[追記]
どうやらpsycopg2からバルクインサートが使えるようなので参考URLを紹介。cur.executemany(sql, rows)をextras.execute_values(cur, sql, rows)にするだけで良いらしい。

• 1000万件のINSERTを映画1本分ぐらい時間節約できた話
• Postgresqlでバルクインサートする方法

Comments

[stevegehorsum]

ご回答ありがとうございました。
\copyコマンドのことや経験的な実行速度に関する話など非常にためになりました。
insertに関しては、1行ずつというのは確認していたのですがそれ以上の工夫の余地があることに気づかされましたので、検討してみようと思います。
有意義なリンクの共有もありがとうございました。

Answer 2

Python の Psycopg を用いて、COPYコマンドを実行する が使えるでしょうか？

Comments

[hentaiman]

copy_from使いたくないようです

[Orlofsky]

難しいね。COPY はSQLというよりユーティリティだから。