I’m currently working on a Python project to grab data from a Microsoft SQL Server database and then turn it into a graph view using Pandas, however it does not seem to work because of the array I am getting from the database.

# Import Modules
import pandas as pd # Graph Module
import pyodbc # Microsoft SQL Server Module

# Connection string
conn_str = (
        "DRIVER={SQL Server Native Client 11.0};"
        "SERVER=server,port;"
        "DATABASE=db;"
        "UID=user;"
        "PWD=pass;"
)

conn  = pyodbc.connect(conn_str)
cursor = conn.cursor()
cursor.execute("SELECT DISTINCT processname AS 'Proces Naam' FROM table WHERE status = 'Aborted' ORDER BY processname ASC") 

result = cursor.fetchall()
print(result)

Current Output:

[('proces1', ), ('proces2', ), ('proces3', ), ('proces4', ), ('proces5', ), ('proces6', ), ('proces7', )]

Desired Output:

['proces1','proces2','proces3','proces4','proces5','proces6','proces7']

I’m trying to use this output for a pandas plot bar graph, and it currently doesn’t work with the Current Output I’ve shown above. I have tried to use result = cursor.fetchone() instead and then it does work, of course only with one result rather than the entire list of results I need in the graph.

The output of the result = cursor.fetchone() looks like this;

('proces1', )

Which is strange, because this output DOES work.

This right here is the code I use for the Pandas Graph:

# Set Dataset
dataset={
        'PROCESS':[result],
        'ABORTS':[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17] # just some random placeholder numbers. I know the result of the query is 17 rows of procesnames
}

# Create Graph
df = pd.DataFrame(data=dataset)
df.plot.bar(title="Aborts", x='PROCESS')

This is my current error code;

    "name": "ValueError",
    "message": "arrays must all be same length",

Does anyone know a good way to clean up this array or maybe have a better understanding of Pandas and how to get this graph to work? Thank you very much.

>Solution :

You can try:

# Set Dataset
dataset={
        'PROCESS':[r[0] for r in result],
        'ABORTS': np.random.randint(1, 10, len(result))
}

# Create Graph
df = pd.DataFrame(data=dataset)
df.plot.bar(title="Aborts", x='PROCESS', rot=45)
plt.tight_layout()
plt.show()