RawQuery ValueError: too many values to unpack (expected 2)

Using Django
1

Hi, I am a beginner on Django and I need help on my django 2.2 application.

I have virtual machines and schedules with a manytomany relationship. I am trying to create a new schedule but I have an error. Basically in my schedule creation form in a column I display the list of VMs. So we can select which machine to assign a schedule. But what I would like is to display in another column the existing schedules I tried with a raw query, It’s display well, but When I tried to send my form an error occured: My error

ValueError: too many values to unpack (expected 2)

This is my models :

class VirtualMachine(models.Model):
    name = models.CharField(max_length=200)
    vm = models.CharField(max_length=32)
    cpu_count = models.IntegerField(default=0)
    power_state = models.CharField(max_length=16, default='POWERED_OFF')
    memory_size_MiB = models.IntegerField(default=0)
    vmware = models.ForeignKey(Vmware, on_delete=models.CASCADE)
    lock = models.BooleanField(default=False)
    criticity = models.CharField(max_length=10, default='medium')
    existing = models.BooleanField(default=False)
​
class VmSchedule(models.Model):
    schedule = models.CharField(max_length=32, blank=False)
    action = models.CharField(max_length=14, blank=False, default="POWERED_ON")
    dow = models.CharField(max_length=1, blank=False)
    dom = models.CharField(max_length=2, blank=False)
    mon = models.CharField(max_length=2, blank=False)
    h = models.CharField(max_length=2, blank=False)
    m = models.CharField(max_length=2, blank=False)
    pause = models.BooleanField(default=False)
    vms = models.ManyToManyField(VirtualMachine, blank=True)

My template

{% for i in form.vms.field.choices %}
    <tr class="clickable_vm">
        <td>
            <label for="{{ i.0 }}">
                <input type="checkbox" name="vms" value="{{ i.0 }}" class="displaynone" id="{{ i.0 }}">{{ i.1 }}
            </label>
        </td>
        <td class="schedule-name">{% for j in i.2 %}{{ j }}<br/>{% endfor %}</td>
    </tr>
{% endfor %}

My Raw queryset

cursor = connection.cursor()
cursor.execute(
    ' SELECT vmware_virtualmachine.id,'
    ' vmware_virtualmachine.name,'
    ' group_concat( appli_vmschedule.schedule) as "schedule"'
    ' FROM vmware_virtualmachine'
    ' LEFT OUTER JOIN appli_vmschedule_vms'
    ' ON (vmware_virtualmachine.id = appli_vmschedule_vms.virtualmachine_id)'
    ' LEFT OUTER JOIN appli_vmschedule'
    ' ON (appli_vmschedule_vms.vmschedule_id = appli_vmschedule.id)'
    ' group by vmware_virtualmachine.name')
vm_sche_list = cursor.fetchall()
vm_sche_list = [list(ele) for ele in vm_sche_list]
for i in vm_sche_list:
    if i[2] is None:
        i[2] = ''
    else:
        i[2] = list(i[2].split(','))

My Form

vms = MultipleChoiceField(required=True,
                              widget=forms.SelectMultiple(attrs={
                                'class': 'form-control form-form shadow-none '
                                'td-margin-bottom-5 textarea_custom',
                                'placeholder': 'Name'}),
                              choices=vm_sche_list,
                              )

My function schedule create

def vm_schedule_create(request):
    if request.method == 'POST':
        print(request.POST)
        form = VmScheduleForm(request.POST)
        if form.is_valid():
            myform = form.save(commit=False)
            form.save()
            messages.success(request,
                             f'schedule {myform.schedule} has been created')
            return redirect('appli:vm_dashboard')
        else:
            return render(request, 'appli/vm/vm_schedule_create.html',
                          {'form': form})
    else:
        form = VmScheduleForm()
        return render(request, 'appli/vm/vm_schedule_create.html',
                      {'form': form})

What weird is when I tried to send my form just with this raw query it’s working
https://dpaste.org/KrxR

is there another way to do what I want with a queryset ? OR a solution to my actual code ?

Traceback

Request Method: POST
Request URL: http://127.0.0.1:8000/appli/vm/schedule/create
​
Django Version: 2.2.14
Python Version: 3.8.10
Installed Applications:
['celery_progress',
 'django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.staticfiles',
 'django.contrib.admindocs',
 'accounts.apps.AccountsConfig',
 'arista.apps.AristaConfig',
 'appli.apps.AppliConfig',
 'fortigate.apps.FortigateConfig',
 'objects.apps.ObjectsConfig',
 'partials.apps.PartialsConfig',
 'phpipam.apps.PhpipamConfig',
 'vmware.apps.VmwareConfig',
 'forticonnect.apps.ForticonnectConfig']
Installed Middleware:
['django.middleware.security.SecurityMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware']
​
​
​
Traceback:
​
File "C:\Users\ibouzidi\PycharmProjects\api\venv\lib\site-packages\django\core\handlers\exception.py" in inner
  34.             response = get_response(request)
​
File "C:\Users\ibouzidi\PycharmProjects\api\venv\lib\site-packages\django\core\handlers\base.py" in _get_response
  115.                 response = self.process_exception_by_middleware(e, request)
​
File "C:\Users\ibouzidi\PycharmProjects\api\venv\lib\site-packages\django\core\handlers\base.py" in _get_response
  113.                 response = wrapped_callback(request, *callback_args, **callback_kwargs)
​
File "C:\Users\ibouzidi\PycharmProjects\api\venv\lib\site-packages\django\contrib\auth\decorators.py" in _wrapped_view
  21.                 return view_func(request, *args, **kwargs)
​
File "C:\Users\ibouzidi\PycharmProjects\api\appli\views\vm.py" in vm_schedule_create
  465.         if form.is_valid():
​
File "C:\Users\ibouzidi\PycharmProjects\api\venv\lib\site-packages\django\forms\forms.py" in is_valid
  185.         return self.is_bound and not self.errors
​
File "C:\Users\ibouzidi\PycharmProjects\api\venv\lib\site-packages\django\forms\forms.py" in errors
  180.             self.full_clean()
​
File "C:\Users\ibouzidi\PycharmProjects\api\venv\lib\site-packages\django\forms\forms.py" in full_clean
  381.         self._clean_fields()
​
File "C:\Users\ibouzidi\PycharmProjects\api\venv\lib\site-packages\django\forms\forms.py" in _clean_fields
  399.                     value = field.clean(value)
​
File "C:\Users\ibouzidi\PycharmProjects\api\venv\lib\site-packages\django\forms\fields.py" in clean
  149.         self.validate(value)
​
File "C:\Users\ibouzidi\PycharmProjects\api\venv\lib\site-packages\django\forms\fields.py" in validate
  869.             if not self.valid_value(val):
​
File "C:\Users\ibouzidi\PycharmProjects\api\venv\lib\site-packages\django\forms\fields.py" in valid_value
  809.         for k, v in self.choices:
​
Exception Type: ValueError at /appli/vm/schedule/create
Exception Value: too many values to unpack (expected 2)
2

Unfortunately, you didn’t post the complete traceback, so I’m having to make a bit of a guess here - but I did see something that appears to possibly be a problem.

See the docs on choices. It must be an iterable of 2-tuples. However, from what I can see of your query, it’s going to return a list with each element containing 3 elements.

3

I posted the full traceback.
Yes I figured it, out but I don’t know how to solve the problem. I tried to change the maybe MultipleChoiceField but no good.

4

How you would do it using a queryset ? look at this Many-to-many relationships | Django documentation | Django
In this example we have Article and Publication. Like me VirtualMachines and Schedules

I want to create new Schedules throw a form.
With a queryset I can already display all VirtualMachine in the form.
But I also want to diplsay the schedules that are already associated with a VirtualMachine.
I used a custom tag to recover the schedules with the VM pk. but it’s not efficient because to many queries between the VirtualMachines and Schedules…

5

Couple quick thoughts -

  • Get rid of the “group_concat”, and return the results as proper relational data through the ORM. This will keep you from having to do all that string manipulation and artificial list creation / reorganization.

  • You’re rendering the form field manually, so there’s not much sense in specifying a widget. However, it does look like you’re effectively recreating the CheckboxSelectMultiple - that might be a better base if that’s the direction you wish to start from.

[Edit: the previous text was written prior to your most recent response. Moving on…]

I don’t see any where clauses in your raw query, so the basic ORM-based solution is something like:
queryset = VirtualMachine.objects.all().prefetch_related('vmschedule_set')

In your template you can then iterate over the queryset for the individual VirtualMachine instances, and then iterate over vmschedule_set for each VirtualMachine instance.

1 Like
6

Thank you again for this helpful answer. I definitely improved a lot. didn’t about prefetch_related.

In the end I didn’t go through a queryset because my initial problem was the loading time. When I had more than 2000 VMs. it took 4 seconds to load, even with a simple queryset like display only VM.
I used a raw SQL but I transformed the result of the query into a dictionary where I could iterate on it.
Some think like this :

mydict = dict()
for ele in vm_sche_list:
  mydict[ele[0]] = [ele[1], ele[2]]
for i, j in mydict.items():
...
7

Still not necessary. See the values clause. (Also, at only 2000 rows, I’d be looking at other factors to explain why there’s a 4-second response, and that includes carefully evaluating both the view and the template.)

You’ll find things to work a lot easier for you in the long run if you try to work with Django rather than around it. There are cases where it may be necessary to use raw SQL - this isn’t one of them.