How to import csv into sqlite3 databate with columns matching with each other?

Question

I'm trying to import a .csv file into my Django db.sqlite3 and I followed exactly as the the instructions from a Youtube video. However, there are a couple of rows in my .csv file that have the same latitude and longtitude values and sqlite3 fails to import some rows as the latitute and longtitude already appear in the table.

sqlite> .import venues.csv myapi_venue
venues.csv:17: INSERT failed: UNIQUE constraint failed: myapi_venue.name
venues.csv:44: INSERT failed: UNIQUE constraint failed: myapi_venue.name
venues.csv:49: INSERT failed: UNIQUE constraint failed: myapi_venue.name
venues.csv:60: INSERT failed: UNIQUE constraint failed: myapi_venue.name
venues.csv:66: INSERT failed: UNIQUE constraint failed: myapi_venue.name
venues.csv:73: INSERT failed: UNIQUE constraint failed: myapi_venue.name
venues.csv:80: INSERT failed: UNIQUE constraint failed: myapi_venue.name

I tried to force the model to have unique as False, but it doesn't help.

class venue(models.Model):
    
    name = models.CharField(_("name"), primary_key=True, max_length=300)
    address = models.CharField(_("address"), max_length=300, unique=False)
    categories = models.CharField(_("category"), null=True, max_length=300, unique=False)
    latitude = models.FloatField(_("latitude"), max_length=150, unique=False)
    longitude = models.FloatField(_("longitude"), max_length=150, unique=False)

When I try migrate the changes, it shows this:

$ python manage.py makemigrations
  No changes detected

How do I import all the rows into the database?

So i can prove that the primary keys are unique: conflicting rows

I think I found the reason why it shows errors. The primary key in the model is matched with a different column in the csv for some reason. order of columns is messed up

The `unique` constraint is for the `name`, not the `categories`. — Willem Van Onsem, Jul 24 '21 at 08:39
You created rows with the same `name.` The primary key has to unique. — Klaus D., Jul 24 '21 at 08:42
@WillemVanOnsem Yes, but the two rows have different names. Like the only two columns that the values are the same are 'latitude' and 'longtitude'. — Shoreo, Jul 24 '21 at 08:54
@KlausD. My primary keys are unique but it fails to import them. — Shoreo, Jul 24 '21 at 08:54
@Shoreo: the error "UNIQUE constraint failed: myapi_venue.name" says otherwise. — Willem Van Onsem, Jul 24 '21 at 08:55
@WillemVanOnsem I have attached an image showing the row that was not imported in highlight. — Shoreo, Jul 24 '21 at 09:03
Can you please copy the first 44 rows, for example with [pastebin](https://pastebin.com/). — Willem Van Onsem, Jul 24 '21 at 09:09

score -1 · Answer 1 · answered Jul 24 '21 at 09:53

-1

So I eventualy had to delete the original table and create a new one so the columns are in the correct order. Followed solutions from how to recreate a deleted table with django migrations

answered Jul 24 '21 at 09:53

Shoreo

1
1

Deleting everything is not a fix. – Klaus D. Jul 24 '21 at 10:19
@KlausD. didn't find a way to reorder the table – Shoreo Jul 24 '21 at 10:41
An answer should give advice on how fix the given problem. Destroying and rebuilding is not a fix in that sense. You don't buy a new car if a tire is flat. – Klaus D. Jul 24 '21 at 10:55

How to import csv into sqlite3 databate with columns matching with each other?

1 Answers1