forms.Select field to display the values I collect from a MS SQL query

Using Django Forms & APIs
1

Hello
I want a forms.Select field to display the values I collect from a MS SQL query

query code in views.py

def NoConformidad(request):
    with connections['nav_db'].cursor() as cursor:
        cursor.execute("SELECT No_,Name FROM Golmar$Customer;") 
        rawData = cursor.fetchall()
        result = []
        for r in rawData:
            result.append(list(r))
        contexto = {'consultas': result}
    return render(request,'reclamaciones/NoConformidad.html', contexto)

I have read about autocomplete but I can’t find anything concrete.
thank you

2

You would pass a queryset instead if a cursor, something like:

def NoConformidad(request):
    result = customer.objects.all()
    contexto = {'consultas': result}

And your html something like:

<select id="consultas" name="customer" class="consultas-dropdown">
{% for consulta in consultas %}
    <option value="{{ consulta.No_}}">{{ consulta.name }}</option>
{% endfor %}
<input type="submit" value="Submit"/>
</select>
3

If you are looking to use this to create the options within a select widget, this would be done in your form, not your view.

You can define a function that creates the 2-tuples of options for the ChoiceField, then use that function name as the parameter for the choices attribute of that field.

See choices for more details.

4

The function could be my ms sql query right?
Type this:

def consultanombre():
    with connections['nav_db'].cursor() as cursor:
        cursor.execute("SELECT No_,Name FROM Golmar$Customer WHERE Name  = 'DISTRIBUIDORES-7';") # Here
        rawData = cursor.fetchall()
        result = []
        for r in rawData:
            result.append(list(r))
        return tuple(result)

And on the other hand, should the function go in the forms.py file?

5

Looks like I’ve made some progress.

In the form I have the following:

def consultacodigocliente():
    with connections['nav_db'].cursor() as cursor:
        cursor.execute("SELECT No_,Name FROM Golmar$Customer;")
        rawData = cursor.fetchall()
        result = []
        for r in rawData:
            result.append(list(r))
        return tuple(result)

According to the query, it should show the No_ and the name but it only shows the name.

Another very strange thing is that if I modify the query for a different one, it no longer works or I simply eliminate the No_ field, it doesn’t work either.

cursor.execute("SELECT Name FROM Golmar$Customer;")

console error:

ValueError: not enough values to unpack (expected 2, got 1)

This same error occurs with any field in the table except if I put No_, Name

6

Just eyeballing it, it looks right. And, if this is a “one-location-only” type situation (in other words, you don’t need to use this query in multiple places in your code), then forms.py is as good a place as any.

Note: Assuming you know that there’s always going to be data returned, you don’t need to perform that transformation of the result set. You should be able to return “rawData” directly.

(If it were necessary to do so, this would also be a case where a list comprehension would be appropriate, too. e.g. result = [list(r) for r in rawData] )

7

It is curious because for me to get a different field it makes me put the field No_

Query that works:

def consultacodigocliente():
    with connections['nav_db'].cursor() as cursor:
        cursor.execute("SELECT No_,Name FROM Golmar$Customer;") # Here
        rawData = cursor.fetchall()
        
        return tuple(rawData)

Query that doesn’t work:

def consultacodigocliente():
    with connections['nav_db'].cursor() as cursor:
        cursor.execute("SELECT Name FROM Golmar$Customer;") # Here
        rawData = cursor.fetchall()
        
        return tuple(rawData)

Query that works:

def consultacodigocliente():
    with connections['nav_db'].cursor() as cursor:
        cursor.execute("SELECT No_,City FROM Golmar$Customer;") # Here
        rawData = cursor.fetchall()
        
        return tuple(rawData)
8

Correct. The “choices” being returned must be a sequence of 2-tuples. The individual results must consist of two values, what will be the “internal” representation of the value and the “external” value used as the visible select item.

9

Perfect, so I always have to put No_ but the field that I want to show.

I have also seen that in a normal way, without using tuples, it works like a normal query