0

I'm starting a pet project and would like some advice. I need to perform the following:

  1. Scrape JSON Data from a REST API
  2. Save the Data into my Postgresql table
  3. Paginate through the rest API and continusously bulk insert rows into my Postgresql table.

I have some specific questions regarding 2 and 3.

Question regarding #2

How do I bulk insert? When I make an API call I get 500 dictionaries, each is a row I'd like to insert. I've set up my Postgresql table and the code that handles persisting to the database. The only issue is I'm using

...
INSERT INTO ...
VALUES ...
...

Which is totally inefficient when I'm receiving 500 dictionaries in each API call.

Question regarding #3:

I'll be collecting trade data that is over 4 years old. Eventually I'll get to present day's data. How can I detect this, and not continuously call the API, receiving the same data as the previous call and inserting duplicate data into my table?

Thanks

P.S. If anyone has any design tips, that would be great. I have a FeedHandler class that does the inserting, a DataCaller class that does the querying. I wanted to know if anyone has any tips on building such a system.

Thanks.

  • For #2, look at stackoverflow.com/questions/758945/… – Berin Loritsch Oct 15 '19 at 0:59
  • Regarding #3, that is highly dependent on the API you are calling in #1. You'll have to figure that out based on the search and pagination capabilities. Well designed systems have a unique ID for every record, including transactions. (that's one of the reasons banks provide you a transaction ID). If you have an ID, then that is how you detect duplicates. – Berin Loritsch Oct 15 '19 at 1:01
  • @BerinLoritsch Thanks for your reply. I'm looking at this: bitmex.com/api/explorer/#!/Trade/Trade_get. If I'm provided with a unique identifier, does that mean I need to query for that identifier in the database before each insert/copy statement, and exit the program if a result is found? For example, if my timestamp is 2019-10-15, I tell my database DataCaller to start running queries on my DB before shipping over information to my FeedHandler? I'd imagine it working something like this: – Tomer Tzadok Oct 15 '19 at 12:37
  • @BerinLoritsch DataCaller retrieves information, my config file says check all data after 2019-09-15. I arrive at 2019-09-15. My DataCaller sends a query to the DB with that timestamp or tradeID. If it finds another trade with either that timestamp or that tradeID (via cursor.fetchone), then it sleeps a couple of seconds and then makes a new call. I'd imagine I'd have to keep track of what page I'm on as a paginate so I don't sleep for too long (whereby I'd be "skipping over" data. How does this sound? – Tomer Tzadok Oct 15 '19 at 12:40
  • The transaction id is how you know whether or not you've already inserted that information. If you already have it, in your database, just skip the data. At this point, I would just test what is needed to paginate appropriately. Once you have that figured out, the rest is relatively easy. Once you get to information you already have, you can abort the pagination process. I do recommend going from newest to oldest though. Once you've done the initial pull, it is the most efficient way to remain current. – Berin Loritsch Oct 15 '19 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.