Read a CSV to insert data into Postgres SQL with Pyhton
<p>I want to read a csv file to insert data into postgres SQL with Python
but I have these error :</p>

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

<p>My code is:</p>

<pre><code>  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()` 
</code></pre>

<p>What does this error mean ?
Is it possible to have a working script ?</p>

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

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

<pre><code>passdata = """
    INSERT INTO project (project_code, program_name ) 
    VALUES (%s, %s)
"""
cursor.execute(passdata, (the_project_code, the_program_name))
</code></pre>

<p>Do not quote the <code>%s</code>. Psycopg will do it if necessary.</p>

<p>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 <a href="http://initd.org/psycopg/docs/usage.html#using-copy-to-and-copy-from" rel="nofollow"><code>copy_from</code></a> which is simpler and faster.</p>

python postgresql csv psycopg2