如何快速将Python数组转换为PostgreSQL？

Question

如何快速将Python数组转换为PostgreSQL？

4

这是一个关于如何将Python中的数组-有符号整数数组转换为PostgreSQL中的int类型的后续问题：How to cast to int array in PostgreSQL?
我在思考如何快速进行转换。

import numpy as np; # use any data format of Python here
event = np.array([[1,2],[3,4]]);

在手动操作时，应将[]替换为{}并用'括起来。在PostgreSQL中，以下内容被接受为数据类型的语法。

...
FOR EACH ROW EXECUTE PROCEDURE insaft_function('{{1,2},{3,4}}');

@JohnMee的建议

str(event).replace('[','{').replace(']','}').replace('\n ',',')

@ErwinBrandstetter的建议

因为有SQL标准支持，所以请使用有符号整数。将其映射为int，所以只需在PostgreSQL端进行如下操作：

TG_ARGV::int[]

我希望你能遵循Erwin的建议。

简化版@ErwinBrandstetter答案的测试运行

为了让函数更加专注于这里的内容，我必须简化他的回答，将表名从函数中删除，只保留一个初始表measurements的触发器：

CREATE OR REPLACE FUNCTION f_create_my_trigger(_arg0 text)
  RETURNS void AS
$func$
BEGIN
EXECUTE format($$
    DROP TRIGGER IF EXISTS insaft_ids ON measurements;
    CREATE TRIGGER insaft_ids
    AFTER INSERT ON measurements
    FOR EACH ROW EXECUTE PROCEDURE insaft_function(%1$L)$$
    , _arg0
);

END
$func$ LANGUAGE plpgsql;

我奔跑：

sudo -u postgres psql detector -c "SELECT f_create_my_trigger('[[1,2],[3,4]]');"

但是输出为空：

 f_create_my_trigger 
---------------------

(1 row)

如何在Python中将映射到PostgreSQL 9.4的int？

- Léo Léopold Hertz 준영

2

可能是对这个问题的后续。 - John Mee

2

数据类型 无符号整数(unsigned integer) 需要小心处理。标准的Postgres仅支持SQL标准中定义的有符号整数。您可能希望将其映射为 bigint 而不是 int，以避免在上半部分发生溢出。或者安装 Peter Eisentraut 提供的扩展程序pguint。 - Erwin Brandstetter

1

数组字面量保持不变，那只是一个文本表示。但在Postgres端转换为bigint[]而不是int[]。在我的先前回答中，这将是TG_ARGV[0]::bigint[]。我无法对NumPy部分发表评论，因为我不使用它。 - Erwin Brandstetter

1

现在您将问题更改为“signed integer”后，可以在Postgres中映射到普通的“integer”。我不确定如何理解更新后的问题：“如何在Python中使用PostgreSQL 9.4的示例从bigint映射到int？”我假设您在Python中有一个（有符号的？）整数数字的二维数组？只需确保数字不会溢出。这是Postgres讨论的整数类型的规格说明。 - Erwin Brandstetter

@ErwinBrandstetter 谢谢！我误解了。所以在Postgres中只需保持为普通的integer。假设是有符号整数的二维数组。 - Léo Léopold Hertz 준영

显示剩余4条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

设置

您想使用与我在dba.SE上相关回答中概述的相同触发器函数创建触发器（重复？）。您需要向触发器函数传递值，以创建具有多个列值的多个行，因此需要二维数组。（但是我们可以使用任何明确定义的字符串！）

除了触发行的列值之外，将值传递给PL / pgSQL触发器函数（其他方式）的唯一方法是text参数，这些参数在函数内部作为特殊数组变量TG_ARGV []中的基于0的文本数组可访问。您可以传递可变数量的参数，但是我们之前讨论过代表您的二维数组的单个字符串文字。

输入来自一个二维 Python 数组，其元素为 带符号整数，可以放入 Postgres 类型 integer 中。使用 Postgres 类型 bigint 来覆盖无符号整数，如注释所述。

Python 中的文本表示如下：

[[1,2],[3,4]]

Postgres数组字面量的语法：

{{1,2},{3,4}}

你想要自动化这个过程。

完全自动化

你可以在客户端连接时连接CREATE TRIGGER语句的字符串，或者你可以将逻辑保存在服务器端函数中，然后只需传递参数。

下面是一个演示性的例子函数，它接受表名和传递给触发器函数的字符串。触发器函数insaft_function() 在你之前在dba.SE上提出的问题中定义。

CREATE OR REPLACE FUNCTION f_create_my_trigger(_tbl regclass, _arg0 text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format($$
      DROP TRIGGER IF EXISTS insaft_%1$s_ids ON %1$s;
      CREATE TRIGGER insaft_%1$s_ids
      AFTER INSERT ON %1$s
      FOR EACH ROW EXECUTE PROCEDURE insaft_function(%2$L)$$
                , _tbl
                , translate(_arg0, '[]', '{}')
      );
END 
$func$;

电话：

SELECT f_create_my_trigger('measurements', '[[1,2],[3,4]]');

或者：

SELECT f_create_my_trigger('some_other_table', '{{5,6},{7,8}}');

db<>fiddle 这里
_{旧版 sqlfiddle}

现在您可以传递[[1,2],[3,4]]（用方括号）或{{1,2},{3,4}}（用花括号）。两者都是相同的。 translate(_arg0，'[]'，'{}'将第一个转换为第二个形式。

此函数在创建新触发器之前删除同名触发器（如果存在）。您可能希望删除或保留此行：

DROP TRIGGER IF EXISTS insaft_%1$s_ids ON %1$s;

这将在调用的数据库角色的权限下运行。如果需要，您可以使其以超级用户（或任何其他）权限运行。请参见：

有没有一种方法可以禁用更新/删除，但仍允许触发器执行它们？

有很多方法可以实现这一点。具体要求取决于情况。

解释format() format()和数据类型regclass有助于安全地连接DDL命令，并使SQL注入成为不可能。请参见：

将表名作为PostgreSQL函数参数

第一个参数是“格式字符串”，后面跟着要嵌入到字符串中的参数。我使用美元引用，虽然在这个例子中并不是严格必需的，但通常对于连接包含单引号的长字符串来说是一个好主意：$$DROP TRIGGER ... $$。

"format()"是沿用了C函数"sprintf"的模式。"%1$s"是format()函数的格式说明符。它意味着在格式字符串后面的第一个（1$）参数将作为未引用的字符串（%s）插入，因此是：%1$s。在示例中，format的第一个参数是"_tbl"，"regclass"参数将自动呈现为合法标识符，必要时会使用双引号括起来，因此"format()"不需要做更多的工作。因此只需使用%s，而不是%I（标识符）。有关详细信息，请阅读上面链接的答案。
另一个使用的格式说明符是%2$L：第二个参数作为引用字符串文字。如果您对format()不熟悉，请尝试使用这些简单示例来理解： "

SELECT format('input -->|%s|<-- here', '[1,2]')
     , format('input -->|%s|<-- here', translate('[1,2]', '[]', '{}'))
     , format('input -->|%L|<-- here', translate('[1,2]', '[]', '{}'))
     , format('input -->|%I|<-- here', translate('[1,2]', '[]', '{}'));

而且请阅读手册。