psycopg2并非在格式化字符串期间转换所有参数 [英] psycopg2 not all arguments converted during string formatting

问题描述

我正在尝试使用psycopg2从python列表中的表中插入一行，但是在字符串格式方面遇到麻烦。

I am trying to use psycopg2 to insert a row into a table from a python list, but having trouble with the string formatting.

该表有4列类型（1043-varchar，1114-timestamp，1043-varchar，23-int4）。我也尝试用1082-date代替时间戳，用21-int2代替int4。

The table has 4 columns of types (1043-varchar, 1114-timestamp, 1043-varchar, 23-int4). I have also made attempts with 1082-date instead of timestamp, and 21-int2 instead of int4.

下面的语句在pgAdmin中或通过执行psycopg2游标而没有字符串格式：

The following statement works fine in pgAdmin or through a psycopg2 cursor execution without string formatting:

INSERT INTO ssurgo.distmd VALUES ('5', '2015-01-01', 'Successful', 4891);

但是，如果我这样做：

sql_text = "INSERT INTO ssurgo.distmd VALUES %s ;"
data  = ['5', '2015-01-01', 'Successful', 4891]
data[1] = date.today() # ensure psycopg2 recognizes a date using datetime
print(curs.mogrify(sql_text, data))

我得到：

TypeError: not all arguments converted during string formatting

$时转换的所有参数b $ b

如果我将日期保留为'2015-01-01'字符串而不是datetime.date对象，并且使用curs.execute（sql_text，data）而不是mogrify，则会遇到相同的错误。

I get the same error if I keep the date as a '2015-01-01' string instead of a datetime.date object, and if I use curs.execute(sql_text, data) rather than mogrify.

我正在传递一个列表，所以我认为这与我在其他问题中发现的创建非字符串序列，并显式转换为元组不能解决该错误。

I am passing a list, so I don't think this is related to the more common single tuple error I found in other questions that is necessary to create a non-string sequence, and explicitly converting to a tuple did not fix the error.

有人知道为什么psycopg2字符串格式化会产生错误吗？

Does anyone know why the psycopg2 string formatting is giving an error?

推荐答案

您可以保留原始代码，但将元组传入而不是列表中：

You can keep your original code but pass a tuple in instead of a list:

sql_text = "INSERT INTO ssurgo.distmd VALUES %s ;"
data  = ('5', date.today(), 'Successful', 4891)
print(curs.mogrify(sql_text, [data]))

请注意，您传递的是单个值，因此有必要将其包装为Psycopg期望的可迭代值。 Psycopg将元组修改为正确的值语法：一条记录​​

Notice that you are passing a single value so it is necessary to wrap it in an iterable which is what Psycopg expects. The tuple will be adapted by Psycopg to the correct values syntax: a record

这篇关于psycopg2并非在格式化字符串期间转换所有参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！