[FIXED] get id from category name

Issue

I'm working on my application and i need to get the id for a specific category. for instance if the category is japanese it would give me an id of 2. i'm using sqlalchemy in python here is how i have my connection set up

url = f"postgresql://postgres:postgres@{dbendpoint}:5432/postgres"
engine_db = create_engine(url)

and this is how im setting up the pandas dataframe before sending it to postgres

df = pd.DataFrame()
df['name'] = name
df['address'] = addy
df['categoryID'] = null

where it says null is where i will plan to execute the query to grab the id

Solution

Assuming that you have the categories in a database table

"category"

category_id  category_name
-----------  -------------
          1  Canadian    
          2  Japanese    
          3  Australian  

you can pull that into a DataFrame and then convert the contents to a dict:

category_df = pd.read_sql_query(
    "SELECT category_id, category_name FROM category", engine
)
category_dict = {
    row.category_name: row.category_id
    for row in category_df.itertuples(index=False)
}
print(category_dict)
# {'Canadian': 1, 'Japanese': 2, 'Australian': 3}

print(category_dict["Japanese"])  # 2

Answered By - Gord Thompson