import pyodbc
import pandas as pd
import os
import json
import yaml

with open('creds.yaml', 'r') as file:
   creds = yaml.safe_load(file)

server = creds['usfoodcon']['server'] 
database = creds['usfoodcon']['database'] 
username = creds['usfoodcon']['username'] 
password = creds['usfoodcon']['password']  
cnxn = pyodbc.connect('DRIVER={PostgreSQL Unicode};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

query = '''SELECT "Country"
, "Commodity"
, "UOM"
, "Category"
, "FoodValue"
, "FoodValue" "DollarValue"
, "YearNum"

FROM us_food_imports
where "Country" NOT IN (
	'REST OF WORLD',
	'WORLD (Quantity)',
	'WORLD'	
)
and (
	"Category" ILIKE 'Meats'
	or "Category" ILIKE 'Animals'
)
and "YearNum" = 2021
order by "Country"
;
'''

df = pd.read_sql(query, cnxn)
print(df.head(10))

     Country                          Commodity        UOM Category  \
0  AUSTRALIA         Frozen red meats and parts  Million $    Meats   
1  AUSTRALIA         Fresh or chilled red meats  Million $    Meats   
2  AUSTRALIA  Total red meats, fresh and frozen  Million $    Meats   
3     BRAZIL  Total red meats, fresh and frozen  Million $    Meats   
4     BRAZIL                     Prepared meats  Million $    Meats   
5     BRAZIL         Frozen red meats and parts  Million $    Meats   
6     CANADA                          Bird eggs  Million $  Animals   
7     CANADA                     Prepared meats  Million $    Meats   
8     CANADA               Fowl and other meats  Million $    Meats   
9     CANADA  Total red meats, fresh and frozen  Million $    Meats   

   FoodValue  DollarValue  YearNum  
0      650.4        650.4     2021  
1      649.3        649.3     2021  
2     2304.1       2304.1     2021  
3      365.0        365.0     2021  
4      496.2        496.2     2021  
5      355.5        355.5     2021  
6       46.2         46.2     2021  
7      653.4        653.4     2021  
8      125.3        125.3     2021  
9     3495.2       3495.2     2021  

C:\Users\dfurt\AppData\Local\Temp\ipykernel_25864\159074362.py:1: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql(query, cnxn)

import matplotlib.pyplot as plt

# Prepare the data
commodities = df['Commodity'].unique()
countries = df['Country'].unique()
dollar_values = df.groupby('Country')['DollarValue'].sum().values

# Plot the data
plt.bar(countries, dollar_values)
plt.xlabel('Country')
plt.ylabel('Dollar Value (in millions)')
plt.title('US Imported Commodity Value by Country')
plt.xticks(rotation=90)
plt.show()

cursor.close()
cnxn.close()