0

I have a variable whose value is set by the user each time the person runs a query. I am running this script in python which then makes a call to a postgres database. I am hoping to first assigning a value to the variable in one piece of sql code and then I am hoping to call that variable in a second piece of sql code.

Here is my current code:

First my code to set the postgres variable:

sql_stateVar=(""" 
                  tempState VARCHAR:= %s
              """
              ,(stateVar,))
cur.execute(*sql_stateVar)

Then with this variable set in postgres, I am going to call this stored variable:

sqlquery=("""
    SELECT UPPER(b.state) AS state,
    FROM opentable_full b
    WHERE UPPER(b.state)=UPPER(tempState)
    GROUP BY UPPER(b.state)
""")

Then I have the following code:

    outputquery = "COPY({0}) TO STDOUT WITH CSV HEADER".format(sqlquery)

    with open('resultsfile', 'w') as f:
        cur.copy_expert(outputquery, file=f)

I have seen several answers regarding the limitations of setting variables in postgres, but as I am new to postgres, I am not sure if these answers are what I need.

For example: Is there a way to define a named constant in a PostgreSQL query?
AND
How to declare local variables in postgresql?

7
  • Is it the complete SQLQuerry You are using, coz I think something is missing here ? Commented Mar 28, 2017 at 12:46
  • 2
    Variables are a PL/PGSQL language feature and they only exist inside the function in which they are declared. Why can't you keep the variable in Python and pass it to the query? Commented Mar 28, 2017 at 12:54
  • @Take_Care: Yes, I had removed some portions of the query. But as a result, the SQLQuery was now incorrect. I have made some edits to correct this issue Commented Mar 28, 2017 at 13:46
  • @Clodoaldo Neto: That is what I originally tried, but for some reason this did not work. Hence, I went down the path of declaring the variable in postgres. If I was to go down that path, can I call on a python-set variable (e.g., stateTemp) inside a sql statement called from within a python script (e.g."""( sqlquery)""" )? Commented Mar 28, 2017 at 14:03
  • Use the Psycopg execute method to pass parameters to the query. Explain the context for which you want to keep the client state. Commented Mar 28, 2017 at 14:09

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.