Freshdesk API to Postgresql Table Issue

I am trying to take ticket details from an API and pipe them into a Postgres table, since the API has pagination, I have a function looping to continue grabbing tickets and updating the table with them until there are no more tickets to grab.

import requests
import json
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from time import sleep

url = 'https://<my_domain>.freshdesk.com/api/v2/tickets'
token = '<my_token>'
db_url = 'postgresql://<server_name>:<blah>/<database_name' # I promise the URL to the left is entered correctly on my actual script,lol.



def fetch_tickets(conn, page=1, per_page=100):
    headers = {'Authorization': 'Basic ' + token}
    params = {'page': page, 'per_page': per_page, 'updated_since': '2022-01-01'}
    response = requests.get(url, headers=headers, params=params)
    if response.status_code == 429:
        print('Rate limit exceeded. Waiting 60 seconds...')
        sleep(61)
    if response.status_code == 200:
        data = json.loads(response.content)
        flattened_data = pd.json_normalize(data)
        flattened_data.to_sql('freshdesk_tickets', conn, if_exists='replace', index=True)
        if len(flattened_data) == per_page:
            return True
    return False

engine = create_engine(db_url)

with engine.connect() as conn:
    more_tickets_remaining = True
    tickets_page = 1
    while more_tickets_remaining:
        more_tickets_remaining = fetch_tickets(conn, page=tickets_page)
        print("Loading...")
        tickets_page += 1
    conn.commit()

I was expecting tens of thousands of tickets to pull in to my freshdesk_tickets table, but it is only pulling in 100. I don't understand why. It seems to be looping just fine?

Solution

In your loop you are using if_exists='replace', this means that every time you enter the loop it drops the table, so at each iteration you are only adding 100, the previous are dropped.

From the docs

if_exists{‘fail’, ‘replace’, ‘append’}, default ‘fail’ How to behave if the table already exists.

fail: Raise a ValueError.
replace: Drop the table before inserting new values.
append: Insert new values to the existing table.

You should instead use if_exists='append'

flattened_data.to_sql('freshdesk_tickets', conn, if_exists='append', index=True)