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?

Community
  • 1
  • 1
user3003374
  • 73
  • 1
  • 10
  • Is it the complete SQLQuerry You are using, coz I think something is missing here ? – Take_Care_ Mar 28 '17 at 12:46
  • 2
    Variables are a [PL/PGSQL language](https://www.postgresql.org/docs/current/static/plpgsql.html) 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? – Clodoaldo Neto Mar 28 '17 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 – user3003374 Mar 28 '17 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)""" )? – user3003374 Mar 28 '17 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. – Clodoaldo Neto Mar 28 '17 at 14:09
  • @Clodoaldo Neto: I have added the rest of the code which calls the sqlquery. If I was to pass the variable, I am assuming I would replace tempState inside sqlquery code with %s and then pass the variable at the end. Then I would pass the parameter when calling cur.copy_expert? Any guidance would be appreciated. – user3003374 Mar 28 '17 at 14:25
  • @Clodoaldo Neto: Sorry for the delayed response. I was able to finally get the execute method to pass parameters to the query. Thanks much for your assistance. – user3003374 Mar 30 '17 at 12:09

0 Answers0