When working with the Stripe API, is it better to sort each request or store locally and perform queries?

Question

This is my first post, I've been lurking for a while.

Some context to my question;

I'm working with the Stripe API to pull transaction data and match these with booking numbers from another API source. (property reservations --> funds received for reconciliation)

I started by just making calls to the API and sorting the data in place using python 3, however it started to get very complicated and I thought I should persist the data in a mongodb stored on localhost. I began to do this, however I decided that storing the sorted data was still just as complicated and the request times were getting quite long, I thought, maybe I should pull all the stripe data and store it locally and then query whatever I needed.

So here I am, with a bunch of code I've written for both and still not alot of progress. I'm a bit lost with the next move. I feel like I should probably pick a path and stick with it. I'm a little unsure what is the "best practise" when working with API's, usually I would turn to YouTube, but I haven't been able to find a video which covers this specific scenario. The amount of data being pulled from the API would be around 100kb per request.

Here is the original code which would grab each query. Recently I've learnt I can use the expand method (I think this is what it's called) so I don't need to dig down so many levels in my for loop.

The goal was to get just the metadata which contains the booking reference numbers that can then be match against a response from my property management systems API. My code is a bit embarrassing, I've kinda just learnt it over the last little while in my downtime from work.

import csv
import datetime
import os
import pymongo
import stripe

""" 
We need to find a Valid reservation_ref or reservation_id in the booking.com Metadata. Then we need to match this to a property ID from our list of properties in the book file.

"""
myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["mydatabase"]
stripe_payouts = mydb["stripe_payouts"]
stripe.api_key = "sk_live_thisismyprivatekey"

r = stripe.Payout.list(limit=4)

payouts = []

for data in r['data']:
    if data['status'] == 'paid':
        p_id = data['id']
        amount = data['amount']
        meta = []
        txn = stripe.BalanceTransaction.list(payout=p_id)
        amount_str = str(amount)
        amount_dollar = str(amount / 100)
        txn_len = len(txn['data'])
        for x in range(txn_len):
            if x != 0:
                charge = (txn['data'][x]['source'])
                if charge.startswith("ch_"):
                    meta_req = stripe.Charge.retrieve(charge)
                    meta = list(meta_req['metadata'])
                elif charge.startswith("re_"):
                    meta_req = stripe.Refund.retrieve(charge)
                    meta = list(meta_req['metadata'])

                if stripe_payouts.find({"_id": p_id}).count() == 0:

                    payouts.append(
                        {
                            "_id": str(p_id),
                            "payout": str(p_id),
                            "transactions": txn['data'],
                            "metadata": {
                                charge: [meta]
                            }
                        }
                    )

# TODO: Add error exception to check for po id already in the database.

if len(payouts) != 0:
    x = stripe_payouts.insert_many(payouts)
    print("Inserted into Database ", len(x.inserted_ids), x.inserted_ids)
else:
    print("No entries made")

"_id": str(p_id),
"payout": str(p_id),
"transactions": txn['data'],
"metadata": {
charge: [meta]

This last section doesn't work properly, this is kinda where I stopped and starting calling all the data and storing it in mongodb locally.

I appreciate if you've read this wall of text this far.

Thanks

EDIT: I'm unsure what the best practise is for adding additional information, but I've messed with the code below per the answer given. I'm now getting a "Key error" when trying to insert the entries into the database. I feel like It's duplicating keys somehow.


payouts = []

def add_metadata(payout_id, transaction_type):
    transactions = stripe.BalanceTransaction.list(payout=payout_id, type=transaction_type, expand=['data.source'])
    for transaction in transactions.auto_paging_iter():
        meta = [transaction.source.metadata]
        if stripe_payouts.Collection.count_documents({"_id": payout_id}) == 0:
            payouts.append(
                {
                    transaction.id: transaction
                }
            )

for data in r['data']:
    p_id = data['id']
    add_metadata(p_id, 'charge')
    add_metadata(p_id, 'refund')


# TODO: Add error exception to check for po id already in the database.
if len(payouts) != 0:
    x = stripe_payouts.insert_many(payouts)
    #print(payouts)
    print("Inserted into Database ", len(x.inserted_ids), x.inserted_ids)
else:
    print("No entries made")```

cjav_dev · Accepted Answer

To answer your high level question. If you're frequently accessing the same data and that data isn't changing much then it can make sense to try to keep your local copy of the data in sync and make your frequent queries against your local data.

No need to be embarrassed by your code :) we've all been new at something at some point.

Looking at your code I noticed a few things:

Rather than fetch all payouts, then use an if statement to skip all except paid, instead you can pass another filter to only query those paid payouts.

r = stripe.Payout.list(limit=4, status='paid')

You mentioned the expand [B] feature of the API, but didn't use it so I wanted to share how you can do that here with an example. In this case, you're making 1 API call to get the list of payouts, then 1 API call per payout to get the transactions, then 1 API call per charge or refund to get the metadata for charges or metadata for refunds. This results in 1 * (n payouts) * (m charges or refunds) which is a pretty big number. To cut this down, let's pass expand=['data.source'] when fetching transactions which will include all of the metadata about the charge or refund along with the transaction.

transactions = stripe.BalanceTransaction.list(payout=p_id, expand=['data.source'])

Fetching the BalanceTransaction list like this will only work as long as your results fit on one "page" of results. The API returns paginated [A] results, so if you have more than 10 transactions per payout, this will miss some. Instead, you can use an auto-pagination feature of the stripe-python library to iterate over all results from the BalanceTransaction list.

for transaction in transactions.auto_paging_iter():

I'm not quite sure why we're skipping over index 0 with if x != 0: so that may need to be addressed elsewhere :D
I didn't see how or where amount_str or amount_dollar was actually used.
Rather than determining the type of the object by checking the ID prefix like ch_ or re_ you'll want to use the type attribute. Again in this case, it's better to filter by type so that you only get exactly the data you need from the API:

transactions = stripe.BalanceTransaction.list(payout=p_id, type='charge', expand=['data.source'])

I'm unable to test because I lack the same database that you have, but wanted to share a refactoring of your code that you may consider.

r = stripe.Payout.list(limit=4, status='paid')

payouts = []

for data in r['data']:
    p_id = data['id']
    amount = data['amount']
    meta = []
    amount_str = str(amount)
    amount_dollar = str(amount / 100)
    transactions = stripe.BalanceTransaction.list(payout=p_id, type='charge', expand=['data.source'])
    for transaction in transactions.auto_paging_iter():
        meta = list(transaction.source.metadata)
        if stripe_payouts.find({"_id": p_id}).count() == 0:
            payouts.append(
                {
                    "_id": str(p_id),
                    "payout": str(p_id),
                    "transactions": transactions,
                    "metadata": {
                        charge: [meta]
                    }
                }
            )
    transactions = stripe.BalanceTransaction.list(payout=p_id, type='refund', expand=['data.source'])
    for transaction in transactions.auto_paging_iter():
        meta = list(transaction.source.metadata)
        if stripe_payouts.find({"_id": p_id}).count() == 0:
            payouts.append(
                {
                    "_id": str(p_id),
                    "payout": str(p_id),
                    "transactions": transactions,
                    "metadata": {
                        charge: [meta]
                    }
                }
            )

# TODO: Add error exception to check for po id already in the database.
if len(payouts) != 0:
    x = stripe_payouts.insert_many(payouts)
    print("Inserted into Database ", len(x.inserted_ids), x.inserted_ids)
else:
    print("No entries made")

Here's a further refactoring using functions defined to encapsulate just the bit adding to the database:

r = stripe.Payout.list(limit=4, status='paid')

payouts = []

def add_metadata(payout_id, transaction_type):
    transactions = stripe.BalanceTransaction.list(payout=payout_id, type=transaction_tyep, expand=['data.source'])
    for transaction in transactions.auto_paging_iter():
        meta = list(transaction.source.metadata)
        if stripe_payouts.find({"_id": payout_id}).count() == 0:
            payouts.append(
                {
                    "_id": str(payout_id),
                    "payout": str(payout_id),
                    "transactions": transactions,
                    "metadata": {
                        charge: [meta]
                    }
                }
            )

for data in r['data']:
    p_id = data['id']
    add_metadata('charge')
    add_metadata('refund')


# TODO: Add error exception to check for po id already in the database.
if len(payouts) != 0:
    x = stripe_payouts.insert_many(payouts)
    print("Inserted into Database ", len(x.inserted_ids), x.inserted_ids)
else:
    print("No entries made")

[A] https://stripe.com/docs/api/pagination [B] https://stripe.com/docs/api/expanding_objects

When working with the Stripe API, is it better to sort each request or store locally and perform queries?

Answers (1)

Related Questions