Question 108766 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – How to get the Incremental data from config file to get last 5 days data only

Currently i am using a config file in python to get incremental data from an api. Below is a snippet of the config file

[
 {
   "id" : 123,
   "input":{
    "name" :    ,
    "path" :     ,
    "format":     , 
    "paramters":{
                 "table_name" : "test"
      },
     "query" : "select * from test where type_id='1234' and time>= '2023-04-20T00:46:26+00:00'"
  },
]

Now i am manually changing the TIME path of the sql query but i want a solution where i dont need to manually change the date.

I did try a bunch of different ways but at the end i am not getting any value at my output file unless i give a date

  1. time >= ‘DATE(NOW())-INTERVAL 7 DAY’

  2. time = DateDiff(left(time,19), today(), day) <5 – this should ideally give incremental value in sql but here i am not able to pass this statement in python

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks to @mrblue6 and others help, i just need to rewrite the line containing the time and place the quotations correctly :-

    query = ""query""":"""select* from test where type_id='1234' and time>=" "'"+formatted_string+"'"  (the file was not running without the quotes correct)

with open('abcd.txt) as f:
lines = f.readLines()

lines[10] = f'{query}"n'
with open('abcd.txt','w') as file:
 file.writeLines(lines)

  2. 0

    You don’t have to make it a json, but it will possibly be easier for you to parse a json config because it can be turned into a dictionary, making it very easy to parse. But txt file will also work if thats what you want.

    #import datetime module
import datetime
#Get datetime for right now
tod = datetime.datetime.now()
#create time delta of how many days to go back
d = datetime.timedelta(days = 5)
#simple subtraction
a = tod - d
#format the date to the format you want. The 00:00 at the end is to just set milliseconds to 0 because datetime can't parse ms like that (I think?)
formatted_string = a.strftime("%Y-%m-%dT%H:%M:%S+00:00")
print(formatted_string)
#prints 2023-04-19T18:39:38+00:00 (ie. 5 days before when I ran this)

#I will assume here you open and read your config file
#assuming query looks like this in the config: "query" : "select * from test where type_id='1234' and time>= "
#query variable is your query from the config file
#Initialising query below because I don't have your config file
query = "select * from test where type_id='1234' and time>= "
query += formatted_string
print(query)
#prints select * from test where type_id='1234' and time>= 2023-04-19T18:42:23+00:00

#now you can write this back to your config and do whatever else is needed
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search