How can I implement dynamic variables into my GET API requests using postgREST and python?

Question

I'm building an API using PostgREST... I'm confused on how-to replicate the query below, more specifically, utilizing the parameters inputted by users = %s.

SELECT symbol, date, adj_close 
FROM api.security_price 
WHERE security_price.symbol IN %s AND date 
> (SELECT MAX(date) FROM api.security_price) - interval '1 years' 
ORDER by date;

In python, it looks something like this = get_data_from_db("SELECT symbol, date, adj_close FROM api.security_price WHERE security_price.symbol IN %s AND date > (SELECT MAX(date) FROM api.security_price) - interval '1 years' ORDER by date;", [placeholders])

Where I pass placeholders, the second argument, which holds the value(s) inputted by the user.

How would I be able to accomplish user-generated GET requests using an enviroment variable like placeholders to create the exact same examples above?.

here is the postgREST documentation, postgres autogenerates api schema based on table/column names, pretty straightforward. However they don't mention case-scenarios like mine.

score 0 · Accepted Answer · answered Dec 22 '20 at 15:45

Create a view, like:

create view foo as 
  select 
    symbol, 
    date, 
    adj_close
  from api.security_price 
  where 
    date > (select max(date) from api.security_price) - interval '1 years' 
  order by 
    date;

Use an HTTP request like:

GET /foo?symbol=in.(APPL,GOOG)

How can I implement dynamic variables into my GET API requests using postgREST and python?

1 Answers1