CODEWITHSUNDEEP
pythondatabasecsvsqlite
Luca
Luca

Reputation: 595

SQLite3 Columns Are Not Unique

I'm inserting data from some csv files into my SQLite3 database with a python script I wrote. When I run the script, it inserts the first row into the database, but gives this error when trying to inset the second:

sqlite3.IntegrityError: columns column_name1, column_name2 are not unique.

It is true the values in column_name1 and column_name2 are same in the first two rows of the csv file. But, this seems a bit strange to me, because reading about this error indicated that it signifies a uniqueness constraint on one or more of the database's columns. I checked the database details using SQLite Expert Personal, and it does not show any uniqueness constraints on the current table. Also, none of the fields that I am entering specify the primary key. It seems that the database automatically assigns those. Any thoughts on what could be causing this error? Thanks.

import sqlite3
import csv

if __name__ == '__main__' :

conn = sqlite3.connect('ts_database.sqlite3')
c = conn.cursor()

fileName = "file_name.csv"
f = open(fileName)
csv_f = csv.reader(f)

for row in csv_f:
    command = "INSERT INTO table_name(column_name1, column_name2, column_name3)"
    command += " VALUES (%s, '%s', %s);" % (row[0],row[1],row[2])
    print command
    c.execute(command)
    conn.commit()

f.close()

Upvotes: 2

Views: 2795

Answers (2)

Larry Lustig
Larry Lustig

Reputation: 51000

If SQLite is reporting an IntegrityError error it's very likely that there really is a PRIMARY KEY or UNIQUE KEY on those two columns and that you are mistaken when you state there is not. Ensure that you're really looking at the same instance of the database.

Also, do not write your SQL statement using string interpolation. It's dangerous and also difficult to get correct (as you probably know considering you have single quotes on one of the fields). Using parameterized statements in SQLite is very, very simple.

Upvotes: 2

BChow
BChow

Reputation: 471

The error may be due to duplicate column names in the INSERT INTO statement. I am guessing it is a typo and you meant column_name3 in the INSERT INTO statement.

Upvotes: 0

Related Questions