python - 精神科PG2 : Querying using an external Variable In a Dash App

Question

@app.callback(
    Output('react-graph','figure'),
    [Input('reg_col','value')]
)

def update_graph(reg_col_name):

    print(reg_col_name)
    cur.execute("SELECT fruits FROM react_table WHERE region = 'reg_col_name'")
    fruits1=cur.fetchall()
    fruits_val = [fruit[0] for fruit in fruits1]
    cur.execute("SELECT sales FROM react_table WHERE region = 'reg_col_name'")
    sales1=cur.fetchall()
    sales_val = [sales[0] for sales in sales1]
    print(sales_val)
    clo = conn.rollback()


    return {
        'data': [go.Bar(
            x=fruits_val, y=sales_val, name='SF'
        )]
    }

我的应用程序有以下回调。输入是通过 reg_col_name 传递的区域名称。在打印 reg_col_name 时，我确实选择了输入选项并且它有效。

当我尝试使用相同的变量在 postgres 数据库中查询时，问题就出现了。在打印 sales_val 时，我应该收到销售数字列表，但输出只是 []

表格看起来像这样。

Region  Fruits   Sales
reg1    apple     67
reg1    banana    100
reg1    mango     38
reg1    pineapple 78
reg1    peach     60
reg1    watermelon63
reg2    apple     10
reg2    banana    64
reg2    mango     42
reg2    pineapple 16
reg2    peach     68
reg2    watermelon21
reg3    apple     7
reg3    banana    59
reg3    mango     72
reg3    pineapple 4
reg3    peach     96
reg3    watermelon63
reg4    apple     83
reg4    banana    32
reg4    mango     17
reg4    pineapple 20
reg4    peach     83
reg4    watermelon71

是否与rollback()函数使用不当有关？

Answer 1

代码将 reg_col_name 作为文字字符串传递给查询。为了pass the variable value as a parameter你会这样做:

cur.execute("SELECT fruits FROM react_table WHERE region = (%s)", (reg_col_name,))

如果您想避免执行两个查询，您的函数可以这样开始:

def update_graph(reg_col_name):
    cur.execute("SELECT fruits, sales FROM react_table WHERE region = (%s)", (reg_col_name,))
    result = cur.fetchall()
    fruits_val, sales_val = zip(*result)

如果您需要将 fruits_val 和 sales_val 作为列表，您仍然可以随后将它们转换:

fruits_val = list(fruits_val)
sales_val = list(sales_val)