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?

2
  • stackoverflow.com/questions/1109061/… Commented Oct 19, 2016 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) ` Commented Oct 19, 2016 at 16:31

1 Answer 1

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.

Sign up to request clarification or add additional context in comments.

3 Comments

Thank you for your quick answer,
my CSV has 2 columns ;
the first one contains several data including project_code and program_name, the second columns contains data corresponding to the first column

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.