使用psycopg2向postgreSQL插入多行数据时出现错误

Question

我有许多XML文件需要打开并处理，以产生大量行数据，然后插入到远程PostgreSQL数据库中的多个表中。

为了提取XML数据，我使用xml.etree.ElementTree解析XML树并根据需要提取元素。虽然我正在做许多事情，但基本操作是将特定元素（字符串或整数）放入多个字典之一。

经过更多处理后，我有许多需要插入到数据库中的字典。对于任何单个XML文件，我可能会在3个表中产生8-10,000行（或查询）。

在测试时，我将输出到SQL文件，然后手动运行查询。如果我有很多XML文件，这显然行不通。

因此，我尝试使用psycopg2自动化此过程。根据这个stackoverflow问题，我已尝试编写以下代码：

QueryData = ','.join(cur.mogrify('(%s,%s,%s)', row) for row in myData)
cur.execute('INSERT INTO DBTABLE' + QueryData)
cur.commit()

其中myData是一个元组列表[(a,b,c),(a,b,c),(a,b,c)...]，其内容是由xml.etree.ElementTree提取的数据和我自己计算的值的组合。

然而，当我尝试实际执行上述代码时，却会出现以下错误：

TypeError: sequence item 0: expected str instance, bytes found

如果我尝试将我的数据（每个元组元素）转换为str()，则会得到以下结果：

TypeError: encoding without a string argument

我是不是完全走错了路？我该如何做我需要的事情？我正在使用Python3。

附加信息

有人要求我展示数据的示例。

这是最简单的形式，它包含3个整数值以放入表格中。它的格式为：(document_id,item_index,item_code)

一个典型的示例是：(937, 138, 681)

我通常尝试转换的方法是：

(str(document_id),str(item_index),str(item_code))

我也尝试过另一种方式：

(bytes(document_id,'utf-8'),bytes(item_index,'utf-8'),bytes(item_code,'utf-8'))

后者还会报错：TypeError：缺少字符串参数的编码

- tanbog

---

我们能看到相关的数据以及您尝试进行转换的方式吗？ - danidee

Answer 1

Psycopg文档中指出，对于cur.mogrify：

返回的字符串总是一个字节字符串。

因此，为了使用这个技巧，你只需要将mogrify的结果解码回一个字符串，例如：

QueryData = ','.join(cur.mogrify('(%s,%s,%s)', row).decode('utf-8') for row in myData)
cur.execute('INSERT INTO DBTABLE' + QueryData)

然而，正如在这个StackOverflow问题中提到的，复制大量数据的最有效方法是使用COPY。您可以使用任何“Python文件对象”来执行此操作。以下是psycopg文档中的示例：

>>> f = StringIO("42\tfoo\n74\tbar\n")
>>> cur.copy_from(f, 'test', columns=('num', 'data'))
>>> cur.execute("select * from test where id > 5;")
>>> cur.fetchall()
[(6, 42, 'foo'), (7, 74, 'bar')]

- Agargara

Answer 2

您在表名后面缺少值，其他所有内容似乎都是正确的：

cursorPG.execute("INSERT INTO test VALUES "+','.join(cursorPG.mogrify('(%s,%s)',x) for x in mydata))

- Juan Albarracín

---

好的，我会添加那个，但它并不能解决问题。导致问题的代码行在执行之前。QueryData = ','.join(cur.mogrify('(%s,%s,%s)', row) for row in myData)仍然会抛出TypeError异常。 - tanbog

Answer 3

好的，我已经让它工作了... 但是我对我的解决方案为什么有效感到困惑。我将其发布为答案，但如果有人能够向我解释发生了什么，那就太好了：

基本上是这样的：

QueryData = ','.join(cur.mogrify('(%s,%s,%s)', row) for row in myData)
cur.execute('INSERT INTO DBTABLE' + QueryData)

必须更改为：

QueryData = b','.join(cur.mogrify(b'(%s,%s,%s)', row) for row in myData)
cur.execute(b'INSERT INTO DBTABLE' + QueryData)

这让我感觉相当不优雅。

- tanbog