Psycopg2 : 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'
        )]
    }

I have the following callback for my app. The input is the region name passed through reg_col_name. On printing reg_col_name I do get the input option selected and it works.

The problem arises when I try to use that same variable to query within the postgres db. on printing sales_val I should receive a list of sales numbers but the ouput is just []

The table looks something like this.

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

Could it be something to do with the improper usage of the rollback() function ?

You query for records where the region column holds the literal string `reg_col_name`. This does not use your variable at all. Your query returns an empty result, hence your list is empty as well. Please see [Passing parameters to SQL queries](http://initd.org/psycopg/docs/usage.html#query-parameters) to learn how to pass the value of your variable to your query. Also, why do you do two queries and not just `SELECT fruits, sales FROM ...`? Also, also: your use of the `rollback()` function is not that improper. SELECT statements create transactions and it is good practice to terminate them. — shmee, Jul 04 '18 at 10:59
Thanks for the link I'll look into it. I query fruits and sales separately to store them into two different variable in order to plot the graph. If I query them in a single statement will it still work the same ? — moksha, Jul 04 '18 at 11:02
You'd have to change the lines iterating over the result, obviously. Fortunately, Python comes with handy builtin functions for that. [This answer](https://stackoverflow.com/a/7558990/4134674) gives you exactly what you need if you fetch both values in the same query. Basically: if your result would look like this `result = [("apple", 67), ("banana", 100)]` doing this `fruits, sales = zip(*result)` would give you `fruits = ('apple', 'banana')` and `sales = (67, 100)` — shmee, Jul 04 '18 at 11:11
Based on your suggestion I put up something like `SQL="SELECT fruits FROM react_table WHERE region = (%s)" cur.execute(SQL, reg_col_name)` but it gives me the error as `TypeError: not all arguments converted during string formatting` on `cur.execute(SQL,dat)` — moksha, Jul 05 '18 at 06:20
The parameter must be passed as a sequence, like so `cur.execute(SQL, (reg_col_name,))`. The trailing comma behind reg_col_name is important. — shmee, Jul 05 '18 at 07:44
Thats it, absolutely sorted. If you could just copy past some parts of your comments into the answer box, Ill close this topic. — moksha, Jul 05 '18 at 07:55

score 2 · Accepted Answer · answered Jul 05 '18 at 08:10

The code passes reg_col_name as a literal string to the query. In order to pass the variable value as a parameter you would do:

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

If you wanted to avoid executing two queries, your function could start like this:

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)

If you need fruits_val and sales_val to be lists, you can still convert them afterwards:

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

Psycopg2 : Querying using an external Variable In a Dash App

1 Answers1