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

问题描述

我正在尝试执行一个简单的查询，但无论我如何传递参数都会收到此错误.

I'm trying execute a simple query, but getting this error no matter how I pass the parameters.

这是查询(我使用 Trac db 对象连接到数据库):

Here is the query (I'm using Trac db object to connect to a DB):

cursor.execute("""SELECT name FROM "%s".customer WHERE firm_id='%s'""" % (schema, each['id']))

schema 和 each['id'] 都是简单的字符串

schema and each['id'] both are simple strings

print("""SELECT name FROM "%s".customer WHERE firm_id='%s'""" % (schema, each['id']))

结果:SELECT name FROM "Planing".customer WHEREfirm_id='135'

错误是在 firm_id= 之后删除引号，但这种方式参数被视为一个整数，而 ::text 会导致完全相同的错误.

There is on error is a remove quote after firm_id=, but that way parameter is treated a an integer and ::text leads to the very same error.

推荐答案

数据库查询中传递变量建议不要使用字符串插值，但是使用字符串插值设置表名就可以了，只要不是外部的输入或您限制允许的值.试试:

It is recommended to not use string interpolation for passing variables in database queries, but using string interpolation to set the table name is fine as long as it's not an external input or you restrict the allowed value. Try:

cursor.execute("""
    SELECT name FROM %s.customer WHERE firm_id=%%s
    """ % schema, (each['id'],))

DB API 使用规则提供针对数据库编程的指南.

Rules for DB API usage provides guidance for programming against the database.

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