0

I have been trying to run this query to fetch contact data from the Freshdesk API and pipe it into a Postgres table using sqlalchemy in Python. I keep getting the following error: psycopg2.errors.UndefinedColumn: column "other_companies" of relation "freshdesk_contacts" does not exist

I'm not sure how that can be, since my understanding is that my python script creates the freshdesk_contacts table based off of the columns in the pandas dataframe that has all the Freshdesk API data in it.

Anyone have any ideas of what could be going wrong?

Also, I've included a picture as an example of the json output for one company. this is an example of the json output for 1 contact

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


# Load sensitive information
url = 'https://<my_domain>.freshdesk.com/api/v2/contacts'
token = '<my_token>'
db_url = '<my_db_url'


# Define a function to handle API requests and insert data into the database
def fetch_contacts(conn, page=1, per_page=100):
    headers = {'Authorization': 'Basic ' + token}
    params = {'page': page, 'per_page': per_page}
    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_contacts', conn, if_exists='append', index=True)
        if len(flattened_data) == per_page:
            return True, page+1 # return the next page number
    return False, page # return the current page number

engine = create_engine(db_url)

# Fetch tickets and insert data into the database
with engine.connect() as conn:
    more_contacts_remaining = True
    contact_page = 1
    while more_contacts_remaining:
        more_contacts_remaining, contact_page = fetch_contacts(conn, page=contact_page)
        print('Loading...')
    conn.commit()
Anna Bodily
  • 13
  • 3
  • was the column `other_companies` always present? IOW, when you ran this script before, you claimed it worked (your previous question), was `other_companies` present even then? Can you open the table using pgadmin or any database viewer and check if the column exists? if it does not exists, the easiest way to debug is to drop that table, and run this script to recreate, ofc you will lose data, the other way is to add that column manually, no data loss – python_user Mar 25 '23 at 02:00
  • @python_user this error has been happening even when I have dropped the table before hand. In fact I have only run this script after I have made sure the table doesn't exist in postgres. – Anna Bodily Mar 27 '23 at 16:09
  • can you try manually specifying all the columns to the `dtype` parameter in `to_sql`? you can try something along https://stackoverflow.com/questions/34383000/pandas-to-sql-all-columns-as-nvarchar, without seeing your json, it is a bit difficult to provide an answer – python_user Mar 27 '23 at 17:47
  • It seems as if the column that is mentioned in the error message isn't a column in the json. I'm not sure. I updated the question to include an example of the json output. – Anna Bodily Mar 27 '23 at 19:02
  • so it appears the first json does not have this, but one / some of the later json(s) have this, as the script only creates the table if it does not exist, it creates it without this column, and for a json that does have this column, it throws that error, can you try adding `other_companies` (`None` or some sensible default you decide as per the requirement) to every json if it does not already exist? and did you try the dtype suggestion in my last comment? – python_user Mar 28 '23 at 01:20
  • @python_user adding other companies to the dtype argument stopped the error, which is great! However, it is still only pulling 10,000 contacts into Postgresql when I know there is more like 29,000 saved in Freshdesk. Thoughts on what could be causing that? – Anna Bodily Mar 28 '23 at 20:31
  • looks like 9k is a limitation of Freshdesk API, you can not do much if they have this limit – python_user Mar 29 '23 at 01:36
  • I can't find anything in the documentation about such a limit, where are you seeing that info? – Anna Bodily Mar 29 '23 at 16:43
  • my bad, that is for not for this api, are you getting more than 10k records from the api response and only postgres only has 10k? – python_user Mar 30 '23 at 01:19
  • correct. when I run it on postman, I can get all the way up to 28,800, but only 10,000 are pulling into postgres. – Anna Bodily Mar 31 '23 at 15:41
  • I don't see anything in the code that limits the rows inserted, if you are sure your loop works, which 10000 are in postgres? the first 10000 or last 10000? also is your postgres instanced managed (like aws) or self hosted or just setup in your local? you should probably check your privileges there – python_user Mar 31 '23 at 18:53

0 Answers0