Help with API to DB Import

[mimuk]

I have a Server API Action that queries an API end point which contains around 7000 records. I can use a count condition to get say ‘50’ records and another condition to GET page 1.

I then have a repeat and query the database to check if the ID exists in a table, if it does, it skips and if not inserts some of the info as a record.
It works as expected.

But I want to then loop and get the next page and so on…

I have created a GET variable which default to 1 and set that as the page parameter.
Any thoughts how I can loop and then page 2 etc.

Your help is appreciated

[mebeingken]

Here’s an example of paging through an API and inserting the records into a db:

Overall structure:

total_pages is a global value that defaults to 1 so it always runs at least once:

current_page is a global value that defaults to 1 so it always runs at least once:

The api fetch using current_page:

Incrementing the current page – the global name needs to match the global name set previously:

Setting the total pages – the global name needs to match the global name set previously:

For 7,000 records this works great, but it will eventually break down when the time to process all records exceeds the script timeout, which can be dealt with, but probably a post for another day.

[mimuk]

Thank you @mebeingken
Will give this a try in the morning.

[mimuk]

Thanks @mebeingken
This is working wonderfully.
In your server actions are you inserting every record and not checking if exists?
I have mine running the query first and then the condition.

I tested running on my local docker and let it run for a few minutes, then stopped docker.

Any suggestions on a way to monitor its progress?
I have no way to identify how many its processed and how far through the import it is.
Once again, thanks

[mebeingken]

@mimuk

This is working wonderfully.

Great, glad it helped.

@mimuk

In your server actions are you inserting every record and not checking if exists?

This is just an example to show you how the while loop can be used; the logic inside the repeat can be anything you need. Doing a query before hand is one way of handling this, but you can also add a unique index to your database to control this, and then use a try/catch for the insert.

@mimuk

Any suggestions on a way to monitor its progress?

If you are speaking about how to monitor while developing, just keep an eye on the database records inserted.

[mimuk]

Thanks for the again for getting this in the right direction @mebeingken
Something strange is happening and I could do with some pointers.

My API doesnt return total pages, so I am selecting 50 records in my query, defaulting to page 1. I get the total pages by getting the totalrecords returned in the API and dividing by 50

It inserts correctly 50 records page 1 to page 20 (1000)
then strangley just 6 records from page 21 and then inserts record 1 from page 1 again and its all a bit random there on in.

I have created a test table to insert the record api id and its page from the insert step.

Here are my steps

[mimuk]

Cannot figure out why the QueryDB which is selecting where table uuid = repeats uuid and the condition is still inserting a record on a match and why when it gets to page 20 it randomly goes back to earlier pages.

Does it need to be a Single Database Query or Query Type Multiple Records?
BTW, I have used {{current_page.toNumber() +1}} as well

[mimuk]

As no-one seems to be able to offer any advise on the queries above.
Any suggestions on how I firstly import page 1 - 20 (1000 records) before it all goes wrong and then another call to import page 21 - 40 and so on?