时间戳

首页 > 解决方案 > 使用 psycopg2 插入的语法错误

psycopg2 - 使用 psycopg2 插入的语法错误

问题描述

我正在尝试使用 psycopg2 包将一些记录插入到 POSTGRES DB 中。

我收到一个我无法弄清楚的错误。

    import numpy as np
import psycopg2
#video_id, frame_num, human_num, keypoint_id, part_x, part_y, confidence
video_id = [1,1,1]
frame_id = [1,1,1]
human_num = [1,1,1]
keypoint_id = [0,1,2]
part_x = [0.33, 0.33, 0.33]
part_y = [0.66, 0.66, 0.66]
confidence = [0.34, 0.45, 0.67]

recs = np.core.records.fromarrays([video_id, frame_id, human_num,keypoint_id, part_x, part_y, confidence])
a_str = ",".join(map(str, recs))

user = 'XX'
pwd = 'XX'
host = 'XX'
port = 'XX'
database = 'XX'

try:
        connection = psycopg2.connect(user = user,
                                  password = pwd,
                                  host = host,
                                  port = port,
                                  database = database)
        #print ("CONN: Established Connection")
        cursor = connection.cursor()

        # Get the data in the required format
        data_sql =  a_str
        #print ("CONN: Converted to recs")

        # Prepare the INSERT Query  to insert keypoints into pose_data
        records_list_template = ','.join('%s' * len(data_sql))
        #print ("CONN: records_template", records_list_template)
        insert_query = 'INSERT INTO pose_data (video_id, frame_num, human_num, keypoint_id, part_x, part_y, confidence) VALUES %s;'
        print ("CONN: Insert Query is ", insert_query)
        # Execute the query
        #print("CONN: Writing ", data_sql)
        cursor.execute(insert_query, [data_sql])
except (Exception, psycopg2.Error) as error :
        print ("Error in PostgreSQL try block:", error)

finally:
    #closing database connection.
        if(connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

我有以下输出（包含错误）

('CONN: Insert Query is ', 'INSERT INTO pose_data (video_id, frame_num, human_num, keypoint_id, part_x, part_y, confidence) VALUES %s;') ('PostgreSQL try block 中的错误:', ProgrammingError('syntax error at或接近 "\'(1, 1, 1, 0, 0.33, 0.66, 0.34),(1, 1, 1, 1, 0.33, 0.66, 0.45),(1, 1, 1, 2, 0.33, 0.66, 0.67)\'"\nLINE 1: ..., keypoint_id, part_x, part_y, confidence) 值 \'(1, 1, 1,...\n
^\n',))

标签： psycopg2

解决方案

变量 %s 不会被它在语句中的值替换

insert_query = 'INSERT INTO pose_data (video_id, frame_num, human_num, keypoint_id, part_x, part_y, confidence) VALUES %s;'

推荐阅读