CODEWITHSUNDEEP
psycopg2
grammar
grammar

Reputation: 939

Syntactical error in INSERT using psycopg2

I am trying to INSERT a few records into a POSTGRES DB using psycopg2 package.

I am getting an error that I just cannot figure out. 

    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")

I have the below output (Contains the error)

('CONN: Insert Query is ', 'INSERT INTO pose_data (video_id, frame_num, human_num, keypoint_id, part_x, part_y, confidence) VALUES %s;') ('Error in PostgreSQL try block:', ProgrammingError('syntax error at or near "\'(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) VALUES \'(1, 1, 1,...\n
^\n',))

Upvotes: 0

Views: 55

Answers (1)

draz
draz

Reputation: 846

the variable %s is not replaced by its value in the statement

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

Upvotes: 0

Related Questions