Can I use a python argument in a postgres variable for a query call?-CodePudding

I want to create a python function that takes a start date and end date as arguments. I want the dates passed to the function to be the start date and end date in my postgres query. I'm struggling with understanding if I pass the argument from the function to postgres or if I can declare the variable in postgres and then somehow assign it to the python argument?

I'm also not sure if this is possible or if I'm going about it the wrong way.

def get_data(start_time, end_time):
    _format = datetime.datetime.strptime("2017-11-22",'%Y-%m-%d')
    start_time = _format.strftime('%Y%m%d')
    end_time = _format.strftime('%Y%m%d')

    query = '''
    DO $$
    DECLARE start_date;
    DECLARE end_date; 
    BEGIN
      start_date := 'start_time' ;
      end_date := 'end_time':
      SELECT *
      FROM   datatable
      WHERE  time > start_date AND time < end_date
      ORDER BY time ASC;
      -- ...
      END $$;
      ''' 
    
    cur.execute(query)

CodePudding user response：

You could try f strings. In front of the lead comment add a f (lowercase) then surround your variables with curly brackets.

I believe you need to be on Python 3.6>. And more information about f strings is found in PEP 498.

def get_data(start_time, end_time):
_format = datetime.datetime.strptime("2017-11-22",'%Y-%m-%d')
start_time = _format.strftime('%Y%m%d')
end_time = _format.strftime('%Y%m%d')

query = f'''
DO $$   
BEGIN
  SELECT *
  FROM   datatable
  WHERE  time > {start_date} AND time < {end_date}
  ORDER BY time ASC;
  -- ...
  END $$;
  '''     
cur.execute(query)

CodePudding user response：

You may need to check your query properly. However, to answer your question, inorder to pass argument as variables of postgres query, you can use the format function

def get_data():
    _format = datetime.datetime.strptime("2017-11-22", '%Y-%m-%d')
    start_time = _format.strftime('%Y%m%d')
    end_time = _format.strftime('%Y%m%d')

    query = '''
    DO $$
    DECLARE start_date;
    DECLARE end_date; 
    BEGIN
      start_date := {} ;
      end_date := {}:
      SELECT *
      FROM   datatable
      WHERE  time > start_date AND time < end_date
      ORDER BY time ASC;
      -- ...
      END $$;
      '''.format(start_time, end_time)

And you can even still use the arguments in the function:

def get_data(start_time, end_time):
    start_time = datetime.datetime.strptime(start_time, '%Y-%m-%d')
    end_time = datetime.datetime.strptime(end_time, '%Y-%m-%d')

    query = '''
    DO $$
    DECLARE start_date;
    DECLARE end_date; 
    BEGIN
      start_date := {} ;
      end_date := {}:
      SELECT *
      FROM   datatable
      WHERE  time > start_date AND time < end_date
      ORDER BY time ASC;
      -- ...
      END $$;
      '''.format(start_time, end_time)
    return query