0

I want to read a csv file to insert data into postgres SQL with Python but I have these error:

  cursor.execute(passdata)
  psycopg2.IntegrityError: duplicate key value violates unique constraint "prk_constraint_project"
  DETAIL:  Key (project_code)=(%s) already exists.

My code is:

  clinicalCSVINSERT = open(clinicalname, 'r')
  reader = csv.reader(clinicalCSVINSERT, delimiter='\t')
  passdata = "INSERT INTO project (project_code, program_name ) VALUES ('%s', '%s')";
  cursor.execute(passdata)
  conn.commit()`

What does this error mean? Is it possible to have a working script?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Nick Yellow
  • 329
  • 1
  • 3
  • 7
  • http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql – vks Oct 19 '16 at 16:20
  • My CSV contains data like that : ` file_uuid 1B3DB121-B766-486D-AB7D-65AB646CE5AC batch_number 304.63.0 project_code TCGA disease_code ACC day_of_dcc_upload 31 month_of_dcc_upload 3 year_of_dcc_upload 2016 (data are tab separate values) ` – Nick Yellow Oct 19 '16 at 16:31

1 Answers1

2

The immediate problem with your code is that you are trying to include the literal %s. Since you probably did run it more than once you already have a literal %s in that unique column hence the exception.

It is necessary to pass the values wrapped in an iterable as parameters to the execute method. The %s is just a value place holder.

passdata = """
    INSERT INTO project (project_code, program_name ) 
    VALUES (%s, %s)
"""
cursor.execute(passdata, (the_project_code, the_program_name))

Do not quote the %s. Psycopg will do it if necessary.

As your code does not include a loop it will only insert one row from the csv. There are some patterns to insert the whole file. If the requirements allow just use copy_from which is simpler and faster.

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260