Delete identical row in many MySQL tables
I am looking for deleting some identical rows present in 4 databases. Each database has a table named Identity_Individu
. Don't worry about this process, this is the only way to find data between databases, because Data Cross Relation is not allowed with Django.
I have this part:
#settings.py file
BDD = ('default', 'DS_Douane', 'DS_Impots', 'DS_Finance')
#views.py file
@login_required
def Identity_Deleting(request) :
query_number = request.GET.get('q6')
if query_number :
query_number_list = Individu.objects.filter(NumeroIdentification__iexact=query_number)
else :
query_number_list = Individu.objects.none()
form = IndividuFormulaire(request.POST or None, instance = query_number_list.first())
if "Delete" in request.POST :
for element in settings.BDD :
form = Individu.objects.filter(pk=query_number_list).delete().using(element)
return HttpResponseRedirect(reverse('Home'))
I have a problem:
(1093, "Identity_individu table" is listed twice, both as a target for DELETE and as a separate data source ")
How do I use .delete()
c using()
to delete all rows that match a condition in each table?
I tried .delete(using=element)
but got:
delete () got unexpected keyword argument 'using'
source to share
Here is the solution after we talked :)
@login_required
def Identity_Deleting(request) :
query_number = request.GET.get('q6')
if query_number :
query_number_list = Individu.objects.filter(NumeroIdentification__iexact=query_number)
else :
query_number_list = Individu.objects.none()
instance = query_number_list.first()
form = IndividuFormulaire(request.POST or None, instance = query_number_list.first())
if "Delete" in request.POST :
ids_to_delete = list(query_number_list.values_list('id', flat=True))
for element in settings.BDD :
form = Individu.objects.using(element).filter(pk__in=ids_to_delete).delete()
return HttpResponseRedirect(reverse('Home'))
There are two things:
- The first query result
query_number_list
should be saved inlist
for later use -
.using()
should be used before.delete()
source to share
This is a mysql limitation, see this answer .
Try modifying your query to evaluate a set of queries instead of using a subquery with list
:
Individu.objects.filter(pk__in=list(query_number_list.values_list('id', flat=True))).delete().using(element)
or if there is always only 1 Individu
, you might need something like:
@login_required
def Identity_Deleting(request) :
query_number = request.GET.get('q6')
if query_number :
individu = Individu.objects.filter(NumeroIdentification__iexact=query_number).first()
else :
individu = None
form = IndividuFormulaire(request.POST or None, instance = individu)
if "Delete" in request.POST:
if individu is not None:
for element in settings.BDD :
Individu.objects.filter(pk=individu.pk).delete().using(element)
return HttpResponseRedirect(reverse('Home'))
source to share