python - 使用python将大文本数据插入postgres

Question

我正在尝试将长 xml 字符串作为文本批量插入到 postgresql 9.1 数据库中。我正在使用 Python 3.2 和 pyscopg2。我将 xml 字符串括在 $$ 中，并在查询字符串中使用命名变量。例如:

query = "insert into cms_object_metadata (cms_object_id, cms_object_metadata_data, cms_object_metadata_type_id, \
         cms_object_metadata_status_id) values ((select id from cms_objects where cms_object_ident = %(objIdent)s), \
         $$%(objMetaString)s$$, (select id from cms_object_metadata_types where cms_object_metadata_type_name = 'PDAT'), \
         (select id from cms_object_metadata_status where cms_object_metadata_status_name = 'active'))"

然后我构造一个字典对象如下:

dataDict = {'objIdent':objIdent, 'objMetaString':objMetaString}

传入 objIdent 和 objMetaString 值。我使用以下代码进行插入:

dbCursor.execute(query, dataDict)

当它将 objMetaString 值插入数据库时​​，它在字符串周围包含单引号。如果我将值追加到查询字符串中并在没有命名变量的情况下执行插入，则不会。例如:

query = "insert into cms_object_metadata (cms_object_id, cms_object_metadata_data, cms_object_metadata_type_id, \
         cms_object_metadata_status_id) values ((select id from cms_objects where cms_object_ident = %s), \
         $$%s$$, (select id from cms_object_metadata_types where cms_object_metadata_type_name = 'PDAT'), \
         (select id from cms_object_metadata_status where cms_object_metadata_status_name = 'active'))" % (objIdent, objMetaString)

和插入:

dbCursor.execute(query)

我的问题是如何使用命名变量和 $$ 批量插入大型文本数据。如果可能的话，我真的不想对这个字符串进行预处理或后处理，因为它们可能很大并且包含未知数量的单引号或其他需要分隔的符号。我已经阅读了以下文档并在 stackoverflow 上搜索了答案，但没有找到解决方案:

• Pyscopg2 docs

• Postgresql docs

Answer 1

总结评论线程。这样做:

query = "insert into cms_object_metadata (cms_object_id, cms_object_metadata_data, cms_object_metadata_type_id, \
         cms_object_metadata_status_id) values ((select id from cms_objects where cms_object_ident = %(objIdent)s), \
         %(objMetaString)s, (select id from cms_object_metadata_types where cms_object_metadata_type_name = 'PDAT'), \
         (select id from cms_object_metadata_status where cms_object_metadata_status_name = 'active'))"

dataDict = {'objIdent':objIdent, 'objMetaString':objMetaString}

dbCursor.execute(query, dataDict)

不要在查询中的 %(objMetaString)s 占位符周围加上引号。必要时引用值是驱动程序的工作。