从SQL查询生成可绘制的DataTable [英] Generate plotly DataTable from SQL query

问题描述

对于dash和python来说我是一个新手，我目前正在尝试为datatable创建一个callback，它可以从计算机上的SQL中检索. 但是，我无法这样做.

I'm quite a new to dash and python, I am currently trying to have a callback for datatable which retrieves from the SQL on my computer. However, I am not able to do so.

知道为什么吗?我收到此错误:

Any idea why? I got this error:

columns = [{'num': i, 'id': i} for i in data.columns],data=[]
NameError: name 'data' is not defined

这是我到目前为止所执行的；

Here what I have implemented so far;

import pandas as pd
import dash
import dash_table
from dash.dependencies import Output, Input
import dash_core_components as dcc
import dash_html_components as html
from random import random
import plotly
import pyodbc
global data
def connectSQLServer(driver, server, db):
    connSQLServer = pyodbc.connect(
        r'DRIVER={' + driver + '};'
        r'SERVER=' + server + ';'
        r'DATABASE=' + db + ';'
        r'Trusted_Connection=yes;',
        autocommit=True
    )
    return connSQLServer


app = dash.Dash()

app.layout = html.Div([dash_table.DataTable(id='raw_data',

                                            columns = [{'num': i, 'id': i} for i in data.columns],data=[]
                                            #data = data.to_dict('records')
                                            ),
                       dcc.Interval(id='interval_component',
                                    interval=1000,
                                    n_intervals=0
                                    )
                       ])


@app.callback(Output('raw_data', 'data'), [Input('interval_component', 'n_intervals')])
def update_table(n_intervals):

    dataSQL = []  # set an empty list
    global data
    sql_conn = connectSQLServer(
        'ODBC Driver 13 for SQL Server', 'DESKTOP\SQL', 'display')
    cursor = sql_conn.cursor()
    cursor.execute("SELECT num,ID FROM dbo.stats")
    rows = cursor.fetchall()
    for row in rows:
        dataSQL.append(list(row))
        labels = ['num', 'id']
        data = pd.DataFrame.from_records(dataSQL, columns=labels)
        data = data.to_dict('records')
        return {'data': [data]}


if __name__ == '__main__':
    app.run_server(port=5000, debug=True)

推荐答案

您已经设置了Output传递给data道具，因此您的返回值应该只是data变量，就像这样:

You've set up the Output to pass to the data prop, so your return value should simply be the data variable, like this:

return data

Dash通过将其放入字典中，正在寻找数据道具中不存在的数据道具，这就是为什么您会得到未定义数据的错误的原因.

By putting it into a dictionary, Dash is looking for a data prop of the data prop, which does not exist, and that's why you get the error that data is not defined.

这篇关于从SQL查询生成可绘制的DataTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！