[Beginner] How can I get the data from mySQL

Hello.

I am trying to get the sensors according to a station id, from my MySQL database.
I have a link like this localhost:8080/console/21/sensors
My urls.py file (urlpatterns) contains this:

path('<int:idstation>/sensors', views.sensors, name='sensors'),

The problem is my view:

def sensors(request, idstation):
    print('toto')
    station = get_object_or_404(Sensors, stations_id_station=idstation)
    #station = Sensors.objets.filter(stations_id_station=idstation)
    return render(request, 'console/sensors.html', {'station':station,'error':'Erro appened'})

When I keep the above code

station = get_object_or_404(Sensors, stations_id_station=idstation)

I have this error:

get() returned more than one Sensors – it returned 10!

I understand that get_objet_or_404, will work id I am searching one value, but as there are several sensors for one station, I should use a another function which allow me to return several rows from my table. Is there something else, like search_objet_or_404()?

I also tried to comment the line station = get_object_or_404(Sensors, stations_id_station=idstation) and I uncomment the line station = Sensors.objets.filter(stations_id_station=idstation). This time I got that error

type object ‘Sensors’ has no attribute ‘objets’

I am a bit confused, because I already use a similar code without problems

def stations(request, fields_id_field):
    print('tata')
    stations_list = Stations.objects.filter(fields_id_field=fields_id_field, station_active=1)
    return render(request, 'console/stations.html', {'stations_list':stations_list})

Also, I imported my Sensors model. You can see how I wrote my views.py file, here

What did I miss and how can I do better?

many thanks

That’s the purpose of filter.

You probably want to review that entire page, along with Retrieving objects.

You have a typo here, which is why you’re getting:

it’s objects not objets.

Hello, thanks for your help. I am so sorry. How I could not see that??!!! I may be so tired :slight_smile:

Thank for the link, it very helpful.
May I continue with another similar question.

I can now print the station in a template (stations.html)

<h1>Station for field </h1>
  {% if stations_list %}
      <ul>
      {% for station in stations_list %}
          <li>
            <a href="{% url 'console:sensors' station.id_station %}">{{ station.station_name}} ({{ station.station_longname }}; Field: {{ station.fields_id_field.field_name }})</a>
            <ul>
              <li>
                test
              </li>
            </ul>
          </li>
      {% endfor %}
      </ul>
  {% else %}
      <p>No stations are available or actived.</p>
  {% endif %}

which print

and I can print all sensors for each station in another template (sensors.html)

  <h1>Sensors by Station</h1>
  {% if station %}
     <ul>
      {% for st in station %}
          <li>
            {{ st.id_sensor}} | {{ st.stations_id_station.station_longname }} | {{ st.sensor_types_id_sensor_type.sensor_type_longname }} | {{ st.chart_style_id_chart_style.chart_style_name }} | {{ st.sensor_active }} | {{ st.sensor_created }} | {{ st.chart_backgroundcolor_id_chart_backgroundcolor.colors_id_colors.color_name }}
          </li>
      {% endfor %}
      </ul>
  {% else %}
      <p>No stations are available or actived.</p>
  {% endif %}

which print

  • 18 | Météo 21 | Vitesse du vent | Line | 1 | Dec. 20, 2020, 10:51 p.m. | Blue
  • 19 | Météo 21 | Direction du vent | Bubble | 1 | Dec. 20, 2020, 10:53 p.m. | Yellow
  • 20 | Météo 21 | Radiation solaire | Line | 1 | Dec. 20, 2020, 11 p.m. | Yellow
  • 21 | Météo 21 | Batterie | Line | 1 | Dec. 20, 2020, 11:31 p.m. | Red
  • 31 | Météo 21 | Températures | Line | 1 | Jan. 29, 2021, 2:02 a.m. | Purple
  • 32 | Météo 21 | Pression | Line | 1 | Jan. 29, 2021, 2:04 a.m. | Yellow
  • 33 | Météo 21 | Humidité | Line | 1 | Jan. 29, 2021, 2:05 a.m. | Turquoise
  • 144 | Météo 21 | Pluie/arrosage | Bar | 1 | May 14, 2022, 12:37 p.m. | Blue

By the way, I am so impressed how it easy to work with several table without the JOIN as for PHP!!!

my goal is to merge that in order to have

  • Station1
    • sensor 1
    • sensor 2
    • sensor3
  • Station 2
    • sensor 1
    • sensor 4
    • sensor5

and so…

I do not want to have, as for the sensors.html template, the station name in each line.

Then I wonder, how can I merger two views?
How can I change my stations view, in order to have a listing of the stations and a sub listing of the sensors for each station?

Do you have a tips?

Thanks a lot for your patience and help

Nested loops.

Inside your for loop for stations, you iterate over the set of sensors for that station.

So where you have the lines <li>test</li>, you can replace those three lines with your loop from your other template.

Hello Ken,

Ok, that work fine
stations.html


  <h1>Station for field </h1>
  {% if stations_list %}
      <ul>
      {% for station in stations_list %}
          <li>
            <a href="{% url 'console:sensors' station.id_station %}">{{ station.id_station }}{{ station.station_name}} ({{ station.station_longname }}; Field: {{ station.fields_id_field.field_name }})</a>
            <ul>
              {% for se in sensors %}
                {% if se.stations_id_station.id_station == station.id_station %}
                <li>
                  {{ se.sensor_longname }}
                </li>
                {% endif %}
              {% endfor%}
            </ul>
          </li>
      {% endfor %}
      </ul>
  {% else %}
      <p>No stations are available or actived.</p>
  {% endif %}

view.py

def stations(request, fields_id_field):
    #stations_list = get_object_or_404(Stations, pk=field_id_field)
    stations_list = Stations.objects.filter(fields_id_field=fields_id_field, station_active=1)
    sensors = Sensors.objects.filter(sensor_active=1)
    return render(request, 'console/stations.html', {'stations_list':stations_list, 'sensors':sensors })

but I wonder, if there a better way to do that, may be without the {%if%} statment
{% if se.stations_id_station.id_station == station.id_station %}

The things I dislike, if you look at the view, ALL sensors are stored in the sensors varaiable.
sensors = Sensors.objects.filter(sensor_active=1)

I meen by ALL sensors, all sensors of all stations of all fields. Even the sensors of the inactive station. It’s not really optimal, isn’t?

I need to print all sensors of a station which belong to a selected field.

Then I wonder, if in the view, if we can filter the sensors of the stations listed in stations_list variable and comparing it with the column stations_id_station of Sensors

def stations(request, fields_id_field):
    #stations_list = get_object_or_404(Stations, pk=field_id_field)
    stations_list = Stations.objects.filter(fields_id_field=fields_id_field, station_active=1)
    sensors = Sensors.objects.filter(sensor_active=1)
    # Somethings like this:
    # sensors = Sensors.objects.filter(sensor_active=1, stations_id_station=station_list.id_station
    return render(request, 'console/stations.html', {'stations_list':stations_list, 'sensors':sensors })

You don’t need to do those comparisons. Django provides the facilities for retrieving related objects.

You can access the set of sensors for a specific station directly.

See the docs at Related objects reference | Django documentation | Django to get started.

Hello
It’s not really clear for me
The link you provided, look to be in models.py
My stations entry has a relation with a field and not with the sensors
My sensors entry has a relation with a station

Then if I understand, I should modify a line in models.py? but what will happen when I run ./manager migrate. It will modify my DB?

In my mind, I thouth removing that comparaison, would impact the view when I collect the sensors (here is the view )
I am mostly confuse, is when I will query the data, either in the stations variable or in the sensors variable.

Do you mind to provide me an example, I may better understand?
Thanks a lot for your patience again

No changes or additional code is required in the models. All this work occurs in the view.

There are examples are on that page. When you’re reading through the examples, think of “Blog” = “Stations” and “Entry” = “Sensors”.

(Note that the link I provided you in my immedately prior response goes along with the link I provided further up.)

So in the light of that situation, the section at Making queries | Django documentation | Django shows how the query is constructed such that the Entry objects retrieved are based on an attribute of the Blog.

(Note: There are more detailed examples available at Many-to-one relationships | Django documentation | Django)

To summarize as a starting point (and I really do encourage you to read the complete docs) - if you have a reference to an object, then the set of objects related to your object is available by default by a manager constructed with the name of the related class with a suffix of _set.

From the referenced docs:

In the above example, the methods below will be available on the manager blog.entry_set .

Meaning, if you have an object of type Blog named blog, then the set of all entries that refer to blog is blog.entry_set.all(). But you don’t need to use all on that manager. You can also use filter, or get or any of the other queryset manager methods.

Thnaks a lot for your clarification.
I understand a bit more but I am still block trying to reproduce following my database and my two tables.

My table stations does not contain a reference to the sensors table.
But the sensors table as a refenrence to the stations table which is stations_id_station
In my table stations, I have a colomn name station_longname. In my Sensors table, I also have a column name sensor_longname

Then following the link you provided me, I tried the following

s = Sensors.objects.filter(stations__station_longname) # this should look at the table Statons for the column station_longname, isn't?

but it return me the error

name ‘stations__station_longname’ is not defined

def stations(request, fields_id_field):
    #stations_list = get_object_or_404(Stations, pk=field_id_field)


    stations_list = Stations.objects.filter(fields_id_field=fields_id_field, station_active=1)
    print(stations_list)
    sensors = Sensors.objects.filter(sensor_active=1)

    #s = Sensors.objects.filter(stations__station_longname)
    s = Stations.objects.filter(sensors__sensor_longname)
    
    return render(request, 'console/stations.html', {'stations_list':stations_list, 'sensors':sensors })

I understand you are confused, but I’m not sure at the moment what you’re trying to do at this point. Please help me get back on track with what the objective is.

Going back to the examples from the docs.

If you have an instance of Blog named blog, then the set of entries related to blog are blog.entry_set.all()

If you want the set of Entry that are associated with blog but also match some criteria, it’s blog.entry_set.filter().

You would not create a separate query on Entry to retrieve those items.

Hello!!

Can I come back to that issue.
Let me resume, what I need know.
I need to list all fields and for each field, I need to list the stations for that field.

For now, I have that views

def index(request):
    """Return the active fields."""
    fields_list = Fields.objects.filter(field_active=1)
    stations_list = Stations.objects.filter(station_active=1)
    return render(request, 'map/slider.html', {'fields_list': fields_list, 'stations_list':stations_list})

On my template, I can easely list the fields and the stations. But ALL stations are listed for each field. I mean, all station for all fields are listed under each field. All is mixed.

I have 5 fields.

id_filed field name
1 field 1
2 field 2
3 field 3
4 field 4
5 field 5

In my Stations table, I have a column named ‘fields_id_field’

If the station1 belong to field 2, the value of fields_id_field will be 2
If the station4 belong to field 5, the value of fields_id_field will be 5
etc.

Here are my two models:

class Fields(models.Model):
    id_field = models.AutoField(primary_key=True)
    countries_country = models.ForeignKey(Countries, models.DO_NOTHING)
    states_id_state = models.ForeignKey('States', models.DO_NOTHING, db_column='states_id_state')
    field_name = models.CharField(max_length=20)
    field_longname = models.CharField(max_length=45, blank=True, null=True)
    field_lat = models.DecimalField(max_digits=8, decimal_places=6, blank=True, null=True)
    field_lng = models.DecimalField(max_digits=8, decimal_places=6, blank=True, null=True)
    field_alt = models.DecimalField(max_digits=6, decimal_places=2, blank=True, null=True)
    threshold = models.IntegerField(blank=True, null=True)
    city = models.CharField(max_length=45, blank=True, null=True)
    cp = models.CharField(max_length=45, blank=True, null=True)
    field_created = models.DateTimeField()
    field_active = models.BooleanField()

    def __str__(self):
        return '[%s] %s' % (self.id_field, self.field_longname)

class Stations(models.Model):
    id_station = models.AutoField(primary_key=True)
    fields_id_field = models.ForeignKey(Fields, models.DO_NOTHING, db_column='fields_id_field')
    stations_types_id_stations_type = models.IntegerField()
    station_name = models.CharField(max_length=20)
    station_longname = models.CharField(max_length=45, blank=True, null=True)
    station_active = models.BooleanField()
    station_archive = models.BooleanField()
    station_lat = models.FloatField(blank=True, null=True)
    station_lng = models.FloatField(blank=True, null=True)
    station_alt = models.DecimalField(max_digits=6, decimal_places=2, blank=True, null=True)
    installed = models.DateTimeField()
    station_description = models.TextField(blank=True, null=True)
    ttn_app_id = models.CharField(max_length=20)
    ttn_dev_id = models.CharField(max_length=20)
    ttn_hardware_serial = models.CharField(max_length=20)
    station_created = models.DateTimeField()
    station_order = models.IntegerField()
    map = models.BooleanField()

    class Meta:
        managed = False
        db_table = 'stations'

Here is my template slider.html

If you look at the for section for the stations {% for station in stations_list %}, it’s clear that Django can not list only the station for the corresponding field.

{% extends 'base.html' %}

{% block title %}
    <title>EcoMap - (slider)</title>
{% endblock %}

{% block slider %}
<div>

    {% if fields_list %}
        <h4>Terrains</h4>
        <ul>
        {% for field in fields_list %}
            <li>
                <a class="nav-link" href="{% url 'map:stations' field.id_field %}">{{ field.field_name}}</a>
            <ul>
                {% for station in stations_list %}
                <li>
                   <a href="{% url 'map:sensors' station.id_station %}">{{ station.id_station }}{{ station.station_name}} ({{ station.station_longname }}; Field: {{ station.fields_id_field }})</a>
                </li>
                {% endfor %}
            </ul>
            </li>
        {% endfor %}
        </ul>
    {% else %}
        <p>No fields are available.</p>
    {% endif %}
</div>
{% endblock %}

You previously wrote me, I do not need to do that, because Django can manage the relation.

<ul>
                {% for station in stations_list %}
                {% if station.fields_id_field.id_field == field.id_field %}
                <li>
                   <a href="{% url 'map:sensors' station.id_station %}">{{ station.id_station }}{{ station.station_name}} ({{ station.station_longname }}; Field: {{ station.fields_id_field }})</a>
                </li>
                {% endif %}

                {% endfor %}
            </ul>

But what I missed, then to do? The relation is not done
(PS: I also tried looking at Making Query link you sent me, without success :frowning: )

Many thanks

Ok, starting with this:

Adding the stations_list to your context does you no good.

Forget the template for the moment - based upon your models and the examples provided above, if you have an instance of Fields named a_field, how would you access the Stations for a_field?

(Note - this is only step one of a multi-step process - don’t try to jump ahead of me here. Ignore the rendering process for the moment and that your view is retrieving a list and not a single object, and focus only on this specific question.)

Dear KenWhitesell,

fields_list = Fields.objects.filter(field_active=1)

I understand fields_list will contain all my active fields
I also understand

field = Fields.objects.get(id=1)
#of may be: Fields.objects.get(id_field=1)

I will get only the field with the id 1
Then in my table, I have some colomn as field_name, field_longname, field_lat
Then if I would like to have the name of my filed, I can do

print(field.field_name)

Now if I look my first instance

fields_list = Fields.objects.filter(field_active=1)

How to debug
I would like to know what contain fields_list (let’s say, I do not know my table names)

With php we can do print_r(object). With Javascript, we can do console.log(object). With Python, I can do print(object) but the the result is not good

<QuerySet [<Fields: Fields object (1)>, <Fields: Fields object (2)>, <Fields: Fields object (4)>]>

The above result, does not help because I see Fields object(2), and not “the detail” of each level of the object

Next
Then as I wrote, I can access field as the following

field = Fields.objects.get(id=1)
print(field.id_field)
print(field.field_name)

how would you access the Stations for a_field?

That’s my problem. I can do the same

station = Stations.objects.get(id=1)
print(station.station_name)
print(station.station_longname)
print(station.fields_id_field) #https://github.com/pierrot10/Django/blob/master/ecosensors/console/models.py#L260

I am confuse from that point. As the Stations table has a relation with Fields table, because of ‘fields_id_field’, I could do
print(station.fields_id_field.field_name) # Display the name of field, from the station belong to
is correct?

But I need to get all fields to list it (fiert level of the li

fields_list = Fields.objects.filter(field_active=1)

  • Fields 1
    – ?
    – ?
  • Fields 2
    – ?
  • Fields 3

And list below the fields, the stations. (Second level of the li)

  • Fields 1
    – station 1
    – station2
  • Fields 2
    – station 3
  • Fields 3
    etc

So I do not know how to access a station for a field, from the field, if the relationship of the two table is declared in Stations table. (1xn)

def index(request):
    """Return the active fields."""
    fields_list = Fields.objects.filter(field_active=1)
    print(fields_list)
    return render(request, 'map/slider.html', {'fields_list': fields_list})

Again, I’m asking you to stay very narrowly focused on one specific point and not bring up additional issues yet.

You have one Field named a_field.

How would you think that you might be able to access the set of Station associated with a_field?

As a reminder:

and

Also, being familar with the Django shell to interactively explore your models is extremely useful for helping to understand how this all works. You should not need to actually run your application to test queries.

i am sorry, but I really do not understand.
I tried

fields_list = Fields.objects.filter(field_active=1)
fields_list.entry_set.all()

AttributeError: ‘QuerySet’ object has no attribute ‘entry_set’

I also tried with the same error

s = Stations.objects.filter(field_active=1)
s.entry_set.all()

Would it be a workaroud?

def fields(request):
    """Return the active fields with the matching stations according to a field."""
    st = {} # i am not sure, if the gabarit will like it :)
    for e in Fields.objects.filter(field_active=1):
        print(e.field_name)
        st['id_field'] = e.id_field
        st['field_name'] = e.field_name
        st['field_longname'] = e.field_longname 
        st['stations'] = Stations.objects.filter(station_active=1, id_field=e.id_field)

    return render(request, 'map/slider.html',{'fields_list':st})

Because a_field is not the same object as fields_list. The object a_field is one Fields. fields_list is a list of fields - something entirely different.

You are on the right track - a_field.stations_set.all() is the list of Stations related to a_field.

What this means is that you want to reference the stations_set attribute for each Fields.

In the original case defined, you can do this in your template where you’re iterating over fields_list. Each time through your loop, you have an instance of Fields. It’s that instance that you then want to iterate over stations_set.

This works when you want to iterate over all Stations for each Fields. This (as written) will not work when you want to apply further filters to Stations - but I want to make sure you’re clear on this before adding additional complexity.

Ok, but what’s a_filed?
is it:
a_field = Fields.objects.filter(field_active=1)
if yes, we are agree that is egal

a_field = Fields.objects.filter(field_active=1)
fields = Fields.objects.filter(field_active=1)

Then why the following does not work

fields = Fields.objects.filter(field_active=1)
fields_list = fields.stations_set.all()
print(fields)
print(fields_list)

‘QuerySet’ object has no attribute ‘stations_set’

No, because the filter function returns a queryset, not an individual instance.

Review the docs at

These distinctions are important!

So with your list of Field named field_list, you are iterating over that list in your template as:

In this case, field is a single Field, field_list is a queryset - even if field_list only has one element in it.