PostgreSQL 类型错误:并非所有参数都在字符串格式化期间转换 [英] PostgreSQL TypeError: not all arguments converted during string formatting

问题描述

我正在 psycopg2 中执行一个与 PostgreSQL 数据库链接的查询.这是有问题的代码:

I am executing a query in psycopg2 linked up to a PostgreSQL database. Here is the code in question:

with open('dataFile.txt', 'r') as f:
    lines = f.readlines()
    newLines = [line[:-1] for line in lines]
    curr=conn.cursor()
    lineString = ','.join(newLines)
    curr.execute("SELECT fields.fieldkey FROM fields LEFT JOIN zone ON zone.fieldkey=fields.fieldkey WHERE zone.zonekey = %s;", (newLines[0]))
    rows = curr.fetchall()

连接到数据库没有问题，行 [0] 的类型绝对是字符串，我检查过.我的字符串格式的语法有问题吗?

There's no issue connecting to the DB, and the type of lines[0] is definitely string, I checked that. Is there something wrong in the syntax of my string formatting?

我得到的错误是:

TypeError: not all arguments converted during string formatting

推荐答案

lines[0] 后必须有一个逗号，以使其成为元组.

There must be a comma after lines[0] to make that a tuple.

curr.execute("""
    SELECT fields.fieldkey
    FROM fields
    LEFT JOIN zone ON zone.fieldkey=fields.fieldkey
    WHERE zone.zonekey = %s;
""", (lines[0],))

因为 execute 方法需要一个序列(或一个映射) 它遍历您提供的括号括起来的字符串.所以有必要明确地把它变成一个元组.使用 tuple 函数tuple 可以得到同样的结果，代码更清晰一个>:

Since the execute method is expecting a sequence (or a mapping) it iterates over the string you provided surrounded by parenthesis. So it is necessary to explicitly make that a tuple. The same result, with clearer code, can be had using the tuple function:

(tuple(lines[0]))

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