Skip to content
Advertisement

adding many to many relations is producing an empty queryset

I have a model called Occurrence with a number of foreign key and manytomany relationships (i’ve only shown a few fields here). I am having issues when attempting to create any manytomany relation to an Occurrence instance after I have loaded bulk data to my postgres (postgis) database using the sqlalchemy engine. Before this upload of data I can make relations as usual with:

Occurrence.objects.get(ind='1010106').minmat.add(Material.objects.get(code='Li'))

I then check the relation was successful with:

Occurrence.objects.get(ind='1010106').minmat.all()

which returns a queryset of all the relations. I have dropped the database and after a fresh migrations everything worked as it should, but once I reloaded the data which consists of tables up to 150,000 rows, I am no longer able to create these relations. No errors occur, but no additions are mad to the model instance after running add().

Here is the model:

class Occurrence(models.Model):
    ind = models.CharField(max_length=14, blank=False, null=False, primary_key=True)
    minmat = models.ManyToManyField(Material, related_name="minmat_occurrence", blank=True,)
    geom = models.PointField(srid=4202) 

    def __str__(self):
        return self.ind


class Material(models.Model):
    code = models.CharField(max_length=6, primary_key=True)
    name = models.CharField(max_length=50, blank=False, null=False)
    category = models.ManyToManyField(MaterialCategory, related_name="category_material", blank=True)

    def natural_key(self):
        return self.name

Everything works properly before loading the data, so I assume the issue lies with loading the data outside of django, but every other aspect of django works as usual.

I’ve been stuck on this one for a while so any help would be much appreciated

Advertisement

Answer

So, the issue was related to the auto incrementing id column of the automatically created many to many through table managed by django. For the minmat field above, this table is called gp_occurrence_minmat (‘gp’ is the app name). The automatically created table has three fields; id, occurrence_id & material_id. The id field is an auto incrementing field and this is where my problem was. I was generating the table with pandas with these three columns and then loading it to the db with the to_sql command. No error arises when doing this and there are no issues when querying data, but, as stated above the issues arise when attempting to add new relationships. I solved the problem by dropping the id column of these tables and loaded the dataframe to db with only the occurren_id and material_id columns. sql automatically generates the id column and I was then able to add relations with the add() method with success.

9 People found this is helpful
Advertisement