How to cerate a QBarchart from database

[LT-K101]

Hi,
I have created a Barchart with the code below. Getting the of female and males bars to match with category Diploma is the problem am facing now, when I try a new query for Diploma it rather shows where the Bachelor category is. Please below is my code and the output image, I need help please.Thanks in advance.

    def second_create_bar(self):
        try:
            # The QBarSet class represents a set of bars in the bar chart.
            # It groups several bars into a bar set

            male_barchart = ''' SELECT COUNT(qualification) AS Male_Count FROM test WHERE gender='Male' and qualification='Bachelor' ORDER BY qualification ASC'''

            male_barchart_sql = cur.execute(male_barchart).fetchall()

            series = QBarSeries()
            #series = QPercentBarSeries()

            set0 = QBarSet("Male")

            for male in male_barchart_sql:

                set0 << male[0]
                series.append(set0)


            female_barchart = ''' SELECT COUNT(qualification) AS Female_Count FROM test WHERE gender='Female' and qualification='Bachelor' ORDER BY qualification ASC'''

            female_barchart_sql = cur.execute(female_barchart).fetchall()

            set1 = QBarSet("Female")

            for female in female_barchart_sql:

                set1 << female[0]
                series.append(set1)

            chart = QChart()
            chart.addSeries(series)
            chart.setTitle("Gender - Academic Qualifications ")
            chart.setAnimationOptions(QChart.SeriesAnimations)

            categories = ["Bachelor","Diploma","Masters"]
            axis = QBarCategoryAxis()
            axis.append(categories)
            chart.createDefaultAxes()
            chart.setAxisX(axis, series)

            chart.legend().setVisible(True)
            chart.legend().setAlignment(Qt.AlignBottom)

            chartView = QChartView(chart)
            chartView.setRenderHint(QPainter.Antialiasing)

            self.ui.widget_321.setChart(chart)

        except Exception as e:
            print(e)

[JonB]

@LT-K101
Never used Qt charts, but guessing. Your set0/set1 only each contain a single element. I would imagine that you need multiple elements in the sets if you want multiple bars for the X axis categories?

https://doc.qt.io/qt-6/qbarset.html#details

A bar set contains one data value for each category. The first value of a set is assumed to belong to the first category, the second one to the second category, and so on.

So if you change two lines:

set0 << male[0] << 2
set1 << female[0] << 3

does that populate your Diploma category?

[LT-K101]

@JonB Yes set0 << male[0] << 2 and set1 << female[0] << 3 it did populate the Diploma category as shown in the image below.

But getting the values for the Diploma category is the difficulty now. I was thinking I should write another query for Diploma category and pass the variable holding the values to both set0 and set1 . Please I stand to be corrected

[JonB]

@LT-K101
I don't understand what your difficulty is. You will need two queries, just as you presently have for qualification='Bachelor' but with 'Diploma', and the results need to be appended to set0 for Male and to set1 for Female.

[LT-K101]

@JonB After adding the two queries I realized the Bachelor category's values affects the Diploma category values as shown in the image below. Because when I run the queries in sql editor for gender='Female' and qualification = 'Bachelor', it returns 2. For gender='Female' and qualification = 'Diploma' , it returns 3. Then for gender='Male' and qualification = 'Bachelor', it returns 1. For gender='Male' and qualification = 'Diploma' it returns 2. Below is the line code which generated this chart. I don't know what I'm doing wrong Please advice.Thanks.

male_barchart = ''' SELECT first_qualification, COUNT(*) AS Male_Count FROM temporary_staff WHERE gender='Male' and first_qualification='Bachelor'  '''

male_barchart_sql = cur.execute(male_barchart).fetchall()


male_barchart = ''' SELECT first_qualification, COUNT(*) AS Male_Count FROM temporary_staff WHERE gender='Male' and first_qualification='Diploma'  '''

male_barchart_sql = cur.execute(male_barchart).fetchall()


series = QBarSeries()
set0 = QBarSet("Male")

for male in male_barchart_sql:
    set0 << male[1] << male[1]
    series.append(set0)


female_barchart = ''' SELECT first_qualification, COUNT(*) AS Female_Count FROM temporary_staff WHERE gender='Female' and first_qualification='Bachelor'  '''

female_barchart_sql = cur.execute(female_barchart).fetchall()

female_barchart = ''' SELECT first_qualification, COUNT(*) AS Female_Count FROM temporary_staff WHERE gender='Female' and first_qualification='Diploma'  '''

female_barchart_sql = cur.execute(female_barchart).fetchall()

set1 = QBarSet("Female")

for female in female_barchart_sql:
    set1 << female[1] << female[1]
    series.append(set1)

[JonB]

@LT-K101
In both male & female cases, you run the Bachelor query, assign result to variable, and then immediately run the Diploma query assigning to same variable. You do nothing with the first Bachelor result. Nor can set0 << male[1] << male[1] be right. You need to build the set0 list as you go along, or keep the query results separate, it needs to contain first query's result and second query's.

[LT-K101]

@JonB Please I'm really confused with your explanation. I tried the code below and I'm getting this error message unsupported operand type(s) for <<: 'QBarSet' and 'list'. Could you kindly help correct this please. Thanks

########################### Male Query ############################
mbachelor_category = ''' SELECT first_qualification, COUNT(*) AS Male_Count FROM temporary_staff WHERE gender='Female' and first_qualification='Bachelor' '''
mbachelor_query = cur.execute(mbachelor_category).fetchall()

mdiploma_category = ''' SELECT first_qualification, COUNT(*) AS Male_Count FROM temporary_staff WHERE gender='Female' and first_qualification='Diploma' '''
mdiploma_query = cur.execute(mdiploma_category).fetchall()

series = QBarSeries()
set0 = QBarSet("Male")

set0 << mbachelor_query << mdiploma_query
series.append(set0)

############################# Female Query ########################
fbachelor_category = ''' SELECT first_qualification, COUNT(*) AS Female_Count FROM temporary_staff WHERE gender='Female' and first_qualification='Bachelor' '''
fbachelor_query = cur.execute(fbachelor_category).fetchall()

fdiploma_category = ''' SELECT first_qualification, COUNT(*) AS Female_Count FROM temporary_staff WHERE gender='Female' and first_qualification='Diploma' '''
fdiploma_query = cur.execute(fdiploma_category).fetchall()


set1 = QBarSet("Female")
set1 << fbachelor_query << fdiploma_query

[JonB]

@LT-K101 said in How to cerate a QBarchart from database:

@JonB Please I'm really confused with your explanation.

That's a shame, because I am really careful to explain slowly and clearly and the issue is so simple.

I would guess that mbachelor_category is a 2-element list, and you cannot add a list to a QBarSet via << operator. So add each element one at a time, or whatever, just like you used to.

[LT-K101]

@JonB Sorry for bothering you with this please I just I want to know how this is done. I tried the following code and I got the female values correct but not for the male. Male Diploma should be 2 but it's showing 3 and male bachelor should be 1 but it's showing 2 as in the image below.

mbachelor_category = ''' SELECT first_qualification, COUNT(*) AS Male_Count FROM temporary_staff WHERE gender='Female' and first_qualification='Bachelor' '''
mbachelor_query = cur.execute(mbachelor_category).fetchall()

mdiploma_category = ''' SELECT first_qualification, COUNT(*) AS Male_Count FROM temporary_staff WHERE gender='Female' and first_qualification='Diploma' '''
mdiploma_query = cur.execute(mdiploma_category).fetchall()

series = QBarSeries()
set0 = QBarSet("Male")

        
for bachelor in mbachelor_query:
    set0 << bachelor[1]
    series.append(set0)

for diploma in mdiploma_query:
    set0 << diploma[1]
    series.append(set0)

############################################### Female Query #####

fbachelor_category = ''' SELECT first_qualification, COUNT(*) AS Female_Count FROM temporary_staff WHERE gender='Female' and first_qualification='Bachelor' '''
fbachelor_query = cur.execute(fbachelor_category).fetchall()


fdiploma_category = ''' SELECT first_qualification, COUNT(*) AS Female_Count FROM temporary_staff WHERE gender='Female' and first_qualification='Diploma' '''
fdiploma_query = cur.execute(fdiploma_category).fetchall()


set1 = QBarSet("Female")
      
for bachelor in fbachelor_query:
       set1 << bachelor[1]
       series.append(set1)

for diploma in fdiploma_query:
    set1 << diploma[1]
    series.append(set1)

[JonB]

@LT-K101 said in How to cerate a QBarchart from database:

but not for the male

Look at your SQL queries for them.

[LT-K101]

@JonB I got it right this time. Thanks a lot I really appreciate your help.