Psycopg2 execute_values 将所有值作为文本发送 [英] Psycopg2 execute_values sending all values as text

问题描述

我在 postgres 中有这个表

创建表目标(一个jsonb 整数c 文本 []id 整数约束 id_fkey 外键 (id)参考 public.other_table(id) MATCH SIMPLE更新时无操作删除无操作，)

我想使用

从 psycopg2 插入数据

导入psycopg2导入 psycopg2.extras 作为附加# 数据格式为 dict, integer, list(string), string <- 用于获取 fkey iddata = [[extras.Json([{'a':1,'b':2}, {'d':3,'e':2}]), 1, ['hello', 'world'], 'ident1'],[extras.Json([{'a':4,'b':3}, {'d':1,'e':9}]), 5, ['hello2', 'world2'], 'ident2']]# 将数据转换为包含对象的元组列表x = [tuple(u) for u in data]# 向数据库中插入数据查询 = ('WITH ins (a, b, c, ident) AS ''(值 %s)''插入目标(a，b，c，id)''选择 ''ins.a''ins.b''ins.c''other_table.id''从 ''插入''LEFT JOIN other_table ON ins.ident = other_table.ident;')游标 = conn.cursor()extras.execute_values(光标，查询，x)

当我运行它时，我得到 error: column "a";是 json 类型，但表达式是文本类型.我试图通过在 SELECT 语句中添加类型转换来解决这个问题，但随后我在 c 和 b 中遇到了相同的错误.

最初我认为问题出在 WITH 语句中，但根据我之前问题的答案，情况似乎并非如此 Postgres`WITH ins AS ...` 将所有内容转换为文本

似乎 execute_values 将所有值作为带有 ' ' 的文本发送.

主要问题:我怎样才能让 execute_values 发送基于 Python 数据类型的值，而不仅仅是文本?

子问题:
如何确认 execute_values 实际上是将值作为带引号的文本发送?
execute_values 的模板参数的目的是什么 https://www.psycopg.org/docs/extras.html 有帮助吗?

解决方案

这个问题，正如 Adrian Klaver 所指出的在他们的评论中，也可以在这个答案，是输入在 CTE 中丢失了.

我们可以在 psql shell 中用一个例子来展示这一点:

CREATE TABLE test (col1 json);WITH cte (c) AS (VALUES ('{"a": 1}'))INSERT INTO test (col) SELECT c FROM cte;

导致

错误:列col"；是 json 类型，但表达式是文本类型

而此版本，具有指定的类型，成功:

WITH cte(c) AS (VALUES ('{"a": 1}'::json))INSERT INTO test (col) SELECT c FROM cte;

我们可以在 execute_values 中通过在模板参数中提供类型信息来模拟这一点:

extras.execute_values(cursor, query, data, template='(%s::json, %s, %s, %s)')

I have this table in postgres

CREATE TABLE target (
    a json
    b integer
    c text []
    id integer
    CONSTRAINT id_fkey FOREIGN KEY (id)
        REFERENCES public.other_table(id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
)

Which I would like to insert data to from psycopg2 using

import psycopg2
import psycopg2.extras as extras

# data is of the form dict, integer, list(string), string <- used to get fkey id
data = [[extras.Json([{'a':1,'b':2}, {'d':3,'e':2}]), 1, ['hello', 'world'], 'ident1'],
        [extras.Json([{'a':4,'b':3}, {'d':1,'e':9}]), 5, ['hello2', 'world2'], 'ident2']]


# convert data to list of tuples containing objects
x = [tuple(u) for u in data]

# insert data to the database
query = ('WITH ins (a, b, c, ident) AS '
         '(VALUES %s) '
         'INSERT INTO target (a, b, c, id) '
         'SELECT '
            'ins.a '
            'ins.b '
            'ins.c '
            'other_table.id'
        'FROM '
            'ins '
            'LEFT JOIN other_table ON ins.ident = other_table.ident;')

cursor = conn.cursor()

extras.execute_values(cursor, query, x)

When I run this I get the error: column "a" is of type json but expression is of type text. I tried to solve this by adding a type cast in the SELECT statement but then I got the same error for c and then for b.

Originally I thought the problem lies in the WITH statement but based on the answers to my previous question this seems to not be the case Postgres `WITH ins AS ...` casting everything as text

It seems that execute_values is sending all the values as text with ' '.

Main Question: How can I get execute_values to send the values based on their python data type rather than just as text?

Sub questions:
How can I confirm that execute_values is in fact sending the values as text with quotation marks?
What is the purpose of the template argument of execute_values https://www.psycopg.org/docs/extras.html and could that be of help?

解决方案

The issue, as Adrian Klaver points out in their comment, and also seen in this answer, is that the typing is lost in the CTE.

We can show this with an example in the psql shell:

CREATE TABLE test (col1 json);

WITH cte (c) AS (VALUES ('{"a": 1}'))
INSERT INTO test (col) SELECT c FROM cte; 

resulting in

ERROR:  column "col" is of type json but expression is of type text

whereas this version, with the type specified, succeeds:

WITH cte(c)  AS  (VALUES ('{"a": 1}'::json))
INSERT INTO test (col) SELECT c FROM cte;

We can mimic this in execute_valuesby providing the typing information in the template argument:

extras.execute_values(cursor, query, data, template='(%s::json, %s, %s, %s)')

这篇关于Psycopg2 execute_values 将所有值作为文本发送的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！