Using Python Plotly to Visualize MySQL Data: Step‑by‑Step Example

The author learned to use the Python data‑visualization library Plotly and needed to display data stored in a MySQL database; the first step was extracting a column from the database and generating a local HTML file.

Below is the complete code used for connecting to the database, fetching the data, filtering it, and creating a Plotly scatter chart. The code is kept intact for reference:

#!/usr/bin/python
# coding=utf-8

import pymysql
import plotly.plotly
from plotly.graph_objs import *
import plotly.graph_objs as abc  # 必须

host = "192.168.10.111"
user = "*****"
passwd = "*****"
db = "api_test"
port = 3306
charset = "utf8"
# 新建链接
conn = pymysql.connect(
    host=host,
    port=port,
    user=user,
    passwd=passwd,
    db=db,
    charset=charset,
)
# 获取链接
cur = conn.cursor()
# 执行sql
re = cur.execute("SELECT * FROM api_result WHERE api_name = \"/article/list/userfeed\"")
# 返回结果
dfs = cur.fetchall()
# 存放查询结果
sss = []
# 排除异常数据
for row in dfs:
    if row[7] < 1:
        sss.append(row[7])
# 关闭链接
cur.close()
conn.commit()
conn.close()
# 获取结果长度
length = sss.__len__()
# 新建x轴数组
listx = []
for i in range(length):
    listx.append(i)
data_1 = abc.Scatter(
    x=listx,
    y=sss,
    name='test1',  # 名称
    mode='markers',
    # 格式
    marker=dict(
        size=10,  # 点的大小
        color="rgba(255,47,167,.9)",  # 点的颜色
        line=dict(  # 点外围环的属性
            width=2,  # 环的宽度
            color='rgb(2,2,2)'  # 环的颜色
        )
    )
)
data1 = Data([data_1])
plotly.offline.plot(data1)

Following the official Plotly tutorial, the chart was beautified by adjusting point colors and the outer‑ring properties.

The resulting visualization is displayed below (image).

At the end of the article, a curated list of selected technical articles and non‑technical articles is provided for further reading.