Using the output of a SQL query to draw a graph

I have a problem with the stacked graphs.

When I try to use the output of a query (here the variable "result’ as the coordinate y to draw my graph, it doesn’t work. But it works for a normal bar char. For some reason when I add the “result” variable into the y=[result, 10, 20], nothing happens. Can someone help me please ? Thank you

Imports
import plotly.graph_objects as go
import pymysql as sql
from Models.Requetesfrontend import connection

x = [
[“N-2”, “N-1”, “N”]
]

# SQL Query

with connection.cursor() as cursor:
sql = 'SELECT COUNT(panelname) ’
'FROM panel ’
cursor.execute(sql)
result = cursor.fetchall()
print(result)

=> The output of this query is 110.

Graphs

fig = go.Figure(go.Bar(x=x, y=[result, 10, 20], name=‘N-2 and N-1’))

fig.update_layout(barmode=‘stack’)
fig.show()


But when I use a normal bar char with the following code it works :

import plotly.express as px
wide_df = px.data.medals_wide()

fig = px.bar(wide_df, x=[‘Product1’], y=result, title=“test1”,
labels={“value”: “count”, “variable”: “medal”})

fig.show()

Based on the code, I would say that result is a list.

In the first example, you provide y=[result, 10, 20], which probably translates to y=[[110], 10, 20].
In the second example, you provide y=result, which probably translates to y=[110].

Try this in the first example:
y=[result[0], 10, 20]

If it doesn’t work, can you Copy&Paste the exact output of print(result) here?

Edit: You probably also have to change x to x = ["N-2", "N-1", "N"] and the syntax of fig = go.Figure to

fig = go.Figure([go.Bar(x=x, y=[result, 10, 20], name=‘N-2 and N-1’)])

→ brackets added around go.Bar

See also here: Bar charts in Python

2 Likes

Thank you very much for the reply ! I added the brackets, and changed y as you said

(y = [result[0], 10, 20])

Now the second value of 10 is displayed (it didn’t before, though the 20 worked). But the value from result[0] still doesn’t appear.

Here is the exact output of the query :

[{‘COUNT(panelname)’: 110}]

It looks like you are using a DictCursor, like in the example here: Examples — PyMySQL 0.7.2 documentation

By using fetchall(), your result is therefore a list of dicts: [{"COUNT(panelname)": 110}]

To only get the value of 110, you not only have to access it by the list index number, but also by the dict key. That means:

result[0]["COUNT(panelname)"]

Since your SQL statement will only return one row, you could change fetchall() to fetchone() and then access the result like this:

result["COUNT(panelname)"]

Let me know if that works!

1 Like

It was that ! Thank you so much, I was stuck in my work for a trifle like this :grinning: