psycopg2でUUID型のカラムを含むテーブルにNOT EXISTSを使ってexecute_values関数でバルクインサートしたい

Question

実現したいこと

postgresqlのテーブルでUUID型のカラムを含むテーブルにバルクインサートしたいです。
NOT EXISTS句でチェックをしてインサートをしたいのですが、NOT EXISTS句を含めるとエラーが出てうまくいきません。

前提

テーブル定義

\c postgres

CREATE SCHEMA TEST;

CREATE TABLE TEST.USER (
  id UUID,
  name VARCHAR(100)
);

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

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-24-01916b6c2dc7> in <module>
     18 with get_connection() as conn:
     19     with conn.cursor() as cur:
---> 20         extras.execute_values(cur, sql, params)

~/python_env/ml/lib/python3.7/site-packages/psycopg2/extras.py in execute_values(cur, sql, argslist, template, page_size, fetch)
   1294         parts = pre[:]
   1295         for args in page:
-> 1296             parts.append(cur.mogrify(template, args))
   1297             parts.append(b',')
   1298         parts[-1:] = post

ValueError: only bytes values expected, got NoneType

該当のソースコード

import psycopg2
import uuid
from psycopg2 import extras
from psycopg2.extensions import ISQLQuote

# DB接続情報 
DB_HOST = 'localhost'
DB_PORT = '5432'
DB_NAME = 'postgres'
DB_USER = 'postgres'
DB_PASS = 'password'
SCHEMA = "TEST"

def get_connection():
    return psycopg2.connect(
        f"postgresql://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
    )

psycopg2.extensions.register_adapter(uuid.UUID, ISQLQuote)
sql = f"""
      INSERT INTO {SCHEMA}.USER
        SELECT v.id, v.name
        FROM (VALUES %s) AS v(id, name)
        WHERE NOT EXISTS (
          SELECT 1 FROM {SCHEMA}.USER WHERE name = v.name
        );
      """
params = [
    (uuid.uuid4(), 'test1'),
    (uuid.uuid4(), "test2")
]
with get_connection() as conn:
    with conn.cursor() as cur:
        extras.execute_values(cur, sql, params)

試したこと

上記ソースコードのsql, params変数の箇所を下記のように修正すると正常にインサートされます。

# sqlはNOT EXISTSを使わない形
sql = f"""
      INSERT INTO {SCHEMA}.USER
      VALUES %s;
      """
# paramsはUUID型から文字列型に変更
params = [
    (str(uuid.uuid4()), 'test1'),
    (str(uuid.uuid4()), ’test2’)
]

下記のようなNOT EXISTSを使うSQLだと、

sql = f"""
      INSERT INTO {SCHEMA}.USER
        SELECT v.id, v.name
        FROM (VALUES %s) AS v(id, name)
        WHERE NOT EXISTS (
          SELECT 1 FROM {SCHEMA}.USER WHERE name = v.name
        );
      """

下記のようなエラーになります。

---------------------------------------------------------------------------
DatatypeMismatch                          Traceback (most recent call last)
<ipython-input-26-a21cf7e66973> in <module>
     19 with get_connection() as conn:
     20     with conn.cursor() as cur:
---> 21         extras.execute_values(cur, sql1, params)

~/python_env/ml/lib/python3.7/site-packages/psycopg2/extras.py in execute_values(cur, sql, argslist, template, page_size, fetch)
   1297             parts.append(b',')
   1298         parts[-1:] = post
-> 1299         cur.execute(b''.join(parts))
   1300         if fetch:
   1301             result.extend(cur.fetchall())

DatatypeMismatch: column "id" is of type uuid but expression is of type text
LINE 3:         SELECT v.id, v.name
                       ^
HINT:  You will need to rewrite or cast the expression.

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

環境はDockerを使用しcomposeファイルは下記になります。

volumes:
  testdb:
    external: false
    name: testdb

services:
  postgres:
    container_name: testdb
    image: postgres:15.3
    volumes:
      - testdb:/var/lib/postgresql/data
      - ./script:/docker-entrypoint-initdb.d
    ports:
      - "5432:5432"
    environment:
      TZ: Asia/Tokyo
      POSTGRES_DB: postgres
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
      POSTGRES_ROOT_PASSWORD: root
      POSTGRES_INITDB_ARGS: "--encoding=UTF-8"
    hostname: postgres
    restart: always

Comments

[ikedas]

・NOT EXISTSを含む形、含まない形の両方を併記してはどうでしょうか。どちらか一方しか記載していないので両者がどう異なるのか分かりにくいです。
・psychopgをよく知らないのですが、「values(%s)」のように記述されている一方対応する値のタプルは2つの要素を持っています。よいのでしょうか。

[tama1422]

psycopg2のexecute_values関数では下記にもある通り、1つのプレースホルダーじゃないとエラーになるため、「values(%s)」の記述で問題ないと思います。
https://www.psycopg.org/docs/extras.html#psycopg2.extras.execute_values
sql – the query to execute. It must contain a single %s placeholder, which will be replaced by a VALUES list. Example: "INSERT INTO mytable (id, f1, f2) VALUES %s".
また、試したことに少し追記しました

[ikedas]

なるほど。この点は問題なさそうですね。

で、「該当のソースコード」のほうではuuidをstrにキャストしておらず、「試したこと」のほうではキャストしています。またエラーも
> DatatypeMismatch: column "id" is of type uuid but expression is of type text
> ...
> HINT: You will need to rewrite or cast the expression.
と言っていますので、キャストされていないのではないでしょうか。

[melian]

本題とは無関係かもしれませんが、

> #paramsはUUID型から文字列型に変更

str型に変更していますが、

psycopg2.extensions.register_adapter(uuid.UUID, ISQLQuote)

を、

psycopg2.extras.register_uuid()

にすれば必要ないかと思います。psycopg2 のソースコードを読むと、register_uuid() の方は、"Create the UUID type and an uuid.UUID adapter" となっていて、UUID type も用意しています。

[tama1422]

あまり詳しくなく、キャストの方法がわかっていません。sqlのプレイスホルダー部分(%s)を下記のようにしてみてもSyntaxError: syntax error at or near "CAST"のエラーが出てしまいました。
CAST(%s AS UUID)
あまり理解できていませんが、該当ソースコードのところをご指摘の通り下記の通り変更したら、うまくINSERTできました。ソースコード読んで理解を深めようと思います。ありがとうございます。
psycopg2.extensions.register_adapter(uuid.UUID, ISQLQuote)
を、
psycopg2.extras.register_uuid()

Answer 1

解決方法

下記を追加することで、PythonのUUID型の変数をpostgresqlのUUID型のカラムにインサートできる。
psycopg2.extras.register_uuid()

補足

まだちゃんと理解できていませんが、整理すると下記のような感じだと思います。間違っていたらご指摘いただけると助かります。

基本的には文字列型の変数もUUID形式になっていれば、UUID型のカラムにインサートできるが、下記のようにNOT EXIST句を含んだINSERTの場合、UUIDが文字列型だとDatatypeMismatchのエラーが起きてしまう。
なので、UUID型で値を渡す必要があるが、そのままではpsycopg2ではPythonのUUID型を認識できずに、ValueErrorが出てしなう。この問題を解決するため、上記の解決方法によりpsycopg2でPythonのUUID型に対応できるようにする。

sql = f"""
      INSERT INTO {SCHEMA}.USER
        SELECT v.id, v.name
        FROM (VALUES %s) AS v(id, name)
        WHERE NOT EXISTS (
          SELECT 1 FROM {SCHEMA}.USER WHERE name = v.name
        );
      """