psycopg2执行UPDATE报错将字段值识别为不存在的列该如何解决

错误根因分析 #

• PostgreSQL语法规范中，双引号"用于包裹表名、列名这类标识符，单引号'才用于包裹字符串字面量。你给mediaurl赋值时用双引号包裹URL字符串，PostgreSQL会将该字符串识别为列名，直接触发你看到的UndefinedColumn报错。
• 直接使用f字符串拼接SQL语句，存在极高SQL注入风险，同时也极易因字符串特殊字符、转义逻辑问题触发各类语法错误，比如你的mediakey是字符串类型，拼接时没有加引号，就算解决了mediaurl的引号问题也会后续报错。
• psycopg2默认开启事务，你把COMMIT写在查询语句内部属于不规范用法，正确提交方式是调用连接对象的commit()方法。

解决方案 #

使用psycopg2官方提供的参数化查询接口，不要手动拼接SQL，正确代码如下：

cur = conn.cursor()
url = "https://shofi-mod.s3.us-east-2.amazonaws.com/" + str(rawbucketkey)
# 统一用%s作为参数占位符，不需要手动加任何引号
query = """UPDATE contentcreatorcontentfeedposts_contentfeedpost
            SET picturemediatype = %s, mediakey = %s,
            mediaurl= %s, active = %s, postsubmit = %s
            WHERE contentcreator_id = %s AND id = %s;"""
# 所有参数按顺序放到execute的第二个参数元组中
cur.execute(query, (True, rawbucketkey, url, True, False, userid, contentpostid))
# 提交事务
conn.commit()
cur.close()

补充说明 #

• 所有数值、字符串、布尔类型的参数都不需要手动处理引号和转义，psycopg2会自动根据参数类型做符合PostgreSQL规范的处理，完全避免语法错误。
• 参数化查询从根源上规避了SQL注入风险，是操作数据库的标准最佳实践。
• 如果你不需要手动控制事务，可以在创建数据库连接时设置conn.autocommit = True，后续就不需要额外调用commit()方法。

内容的提问来源于stack exchange，提问作者Christopher Jakob