Connecting database data to streamlit-echart

[Avidan_Rothman]

Summary

I am trying to connect my database to a simple line chart using the streamlit-echarts. I know the axis’ I want to use for the chart but having a hard time figuring out the syntax for the line chart code.

Steps to reproduce

Code snippet:

mydb = connector.connect(host="00.000.000.000", database = 'FreeWeibo',user="abc", passwd="abc",use_pure=True)
query = "Select * from FreeWeiboPosts"
data = pd.read_sql(query,mydb)
st.dataframe(data)
mydb.close() #close the connection

import inspect
import textwrap
import streamlit as st

from streamlit_echarts import st_echarts

option = {
    "xAxis": {
        "type": Time_Created,
        data: [Time_Created],
    },
    "yAxis": {"type": censored},
    "series": [{data: [0,1], "type": "line"}],
}
st_echarts(
    options=option, height="400px",
)

If applicable, please provide the steps we should take to reproduce the error or specified behavior.

Just running this code.

Explain what you expect to happen when you run the code above.

I am trying to have the bar chart display in the x axis based of  the column from the database that is called "censored"  and the y axis to display the column called "time_created". When I run the code I wanted it to display in a bar chart the columns I explained above where the x-axis is the censored column pulled from the database it should display in one bar censored which in the database is represented as "1" and not censored as a "0" and the y-axis is the column time created to show over what period of time the posts were censored. 

Explain the undesired behavior or error you see when you run the code above.
If you're seeing an error message, share the full contents of the error message here.

NameError: name 'Time_Created' is not defined

Traceback:

File “C:\Users\avida\anaconda3\envs\FreeWeibo_working\lib\site-packages\streamlit\runtime\scriptrunner\script_runner.py”, line 564, in _run_script
exec(code, module.dict)File “C:\Users\avida\FreeWeibo\FreeWeibo.py”, line 39, in
“type”: Time_Created,

- Streamlit version: 1.15.1
- Python version: 3.9.15
- Using Conda
- OS version: Windows 10
- Browser version: Chrome

### Requirements file

Using Conda? PipEnv? PyEnv? Pex? Share the contents of your requirements file here.
Not sure what a requirements file is? Check out [this doc](https://docs.streamlit.io/streamlit-cloud/get-started/deploy-an-app/app-dependencies) and add a requirements file to your app.

### Links

* Link to your GitHub repo:
* Link to your deployed app:

### Additional information

If needed, add any other context about the problem here.

[TylerS]

Hi there @Avidan_Rothman, make sure you are “quoting” your variable names (such as “Time_Created”, “censored”) otherwise python will look for these objects in the namespace but you have no defined them anywhere. Try following the specs shown here.

[Avidan_Rothman]

ok thank you for that tip I did try and follow the specs that you linked but I am having a hard time distinguishing where I am mapping into the echart the data being pulled from the database as in the “time_created” and “censored” are columns in the database that I am trying to define in the echart.

[TylerS]

If you quote your variables, what’s the error that get?

[Avidan_Rothman]

TypeError: unhashable type: ‘DataFrame’

Traceback:

File "C:\Users\avida\anaconda3\envs\FreeWeibo_working\lib\site-packages\streamlit\runtime\scriptrunner\script_runner.py", line 564, in _run_script
    exec(code, module.__dict__)File "C:\Users\avida\FreeWeibo\FreeWeibo.py", line 38, in <module>
    "xAxis": {

[TylerS]

Make sure you are passing a list or a pd.Series to the “data”: portion. I think right now you are passing a dataframe.

[Avidan_Rothman]

How would I do that? I am sorry I am very new to Python and Streamlit.

[TylerS]

No problem. Could you try running something like this? You’ll have to include your import connector as well.

import streamlit as st
import pandas as pd
from streamlit_echarts import st_echarts


mydb = connector.connect(host="00.000.000.000", database = 'FreeWeibo',user="abc", passwd="abc",use_pure=True)
query = "Select * from FreeWeiboPosts"
data = pd.read_sql(query,mydb)
st.dataframe(data)
mydb.close() #close the connection


option = {
    "xAxis": {
        "type": "category",
        "data": data['Time_Created'],
    },
    "yAxis": {"type": "value"},
    "series": [{"data": data['censored'], "type": "line"}],
}
st_echarts(
    options=option, height="400px",
)

[Avidan_Rothman]

I got this error:
MarshallComponentException: (‘Could not convert component args to JSON’, TypeError(‘Object of type Series is not JSON serializable’))

Traceback:

File "C:\Users\avida\FreeWeibo\FreeWeibo.py", line 40, in <module>
    st_echarts(File "C:\Users\avida\anaconda3\envs\FreeWeibo_working\lib\site-packages\streamlit_echarts\__init__.py", line 80, in st_echarts
    return _component_func(

[TylerS]

import streamlit as st
import pandas as pd
from streamlit_echarts import st_echarts


mydb = connector.connect(host="00.000.000.000", database = 'FreeWeibo',user="abc", passwd="abc",use_pure=True)
query = "Select * from FreeWeiboPosts"
data = pd.read_sql(query,mydb)
st.dataframe(data)
mydb.close() #close the connection


option = {
    "xAxis": {
        "type": "category",
        "data": data['Time_Created'].to_list(),
    },
    "yAxis": {"type": "value"},
    "series": [{"data": data['censored'].to_list(), "type": "line"}],
}
st_echarts(
    options=option, height="400px",
)```

[Avidan_Rothman]

still have an issue with the “st_echarts(” line getting this error.
MarshallComponentException: (‘Could not convert component args to JSON’, TypeError(‘Object of type Timestamp is not JSON serializable’))

Traceback:

File "C:\Users\avida\FreeWeibo\FreeWeibo.py", line 40, in <module>
    st_echarts(File "C:\Users\avida\anaconda3\envs\FreeWeibo_working\lib\site-packages\streamlit_echarts\__init__.py", line 80, in st_echarts
    return _component_func(

[TylerS]

Check out this issue on Stack Overflow. I would recommend Googling the problems when you encounter them to see if there is a solution already posted somewhere.

[Avidan_Rothman]

so you think it could be related to the time_created data?

[TylerS]

Yep!

[Avidan_Rothman]

So I tried adding this line and it didn’t seem to help. Is the syntax correct here?
json_str = json.dumps({‘time_created’ : str(data)})

[Avidan_Rothman]

Here is the full code:
json_str = json.dumps({‘time_created’ : str(data)})
option = {
“xAxis”: {
“type”: “category”,
“data”: data[‘time_created’].to_list(),

},
"yAxis": {"type": "value"},
"series": [{"data": data['censored'].to_list(), "type": "line"}],

}
st_echarts(
options=option, height=“400px”,
)

[TylerS]

Maybe you could try something like this?

import streamlit as st
import pandas as pd
from streamlit_echarts import st_echarts


mydb = connector.connect(host="00.000.000.000", database = 'FreeWeibo',user="abc", passwd="abc",use_pure=True)
query = "Select * from FreeWeiboPosts"
data = pd.read_sql(query,mydb)
st.dataframe(data)
mydb.close() #close the connection


option = {
    "xAxis": {
        "type": "category",
        "data": data['Time_Created'].dt.strftime('%Y-%m-%d %H:%M:%S').to_list(),
    },
    "yAxis": {"type": "value"},
    "series": [{"data": data['censored'].to_list(), "type": "line"}],
}
st_echarts(
    options=option, height="400px",
)

[Avidan_Rothman]

So now I got a different error where there are cells in the column time created that have broken data I tried creating a line to clean the code but I am getting this error:
AttributeError: ‘Series’ object has no attribute ‘remove’

Traceback:

File "C:\Users\avida\anaconda3\envs\FreeWeibo_working\lib\site-packages\streamlit\runtime\scriptrunner\script_runner.py", line 564, in _run_script
    exec(code, module.__dict__)File "C:\Users\avida\FreeWeibo\FreeWeibo.py", line 35, in <module>
    data['time_created'].remove('N', 'NaN',"2013"),File "C:\Users\avida\anaconda3\envs\FreeWeibo_working\lib\site-packages\pandas\core\generic.py", line 5902, in __getattr__
    return object.__getattribute__(self, name)

Here is the code I used:
data.dropna()
data[‘time_created’].remove(‘N’, ‘NaN’,“2013”),
option = {
“xAxis”: {
“type”: “category”,
“data”: data[‘time_created’].dt.strftime(‘%y-%m-%d %h:%m:%s’).to_list(),
},
“yAxis”: {“type”: “value”},
“series”: [{“data”: data[‘censored’].to_list(), “type”: “line”}],
}
st_echarts(
options=option, height=“400px”,
)

[TylerS]

Check out the docs for pandas Series here.

[Avidan_Rothman]

I realized I do not need the .drop line but when I remove that line and rerun I get this error:
SyntaxError: Unexpected token ‘N’, …“9:52:39”, NaN, “2013”… is not valid JSON.

What does this mean?