sql2predict.py

import redis
import psycopg2
from gradio_client import Client
import json
from datetime import datetime

redis_url = 'redis://default:[email protected]:7369'
r = redis.from_url(redis_url)
# run josh's algorithm
client = Client("https://diverseco-metaformer.hf.space/")

# Postgres database get the observations
def connect():
    conn = None
    try:
        # Connecting to your PostgreSQL server
        print('Connecting to the PostgreSQL database...')
        conn = psycopg2.connect('postgresql://postgres:[email protected]:7297/railway')
        # conn = psycopg2.connect('postgresql://postgres:[email protected]:6771/railway') #staging
        # conn = psycopg2.connect('postgresql://postgres:[email protected]:5772/railway') #development

    except (Exception, psycopg2.DatabaseError) as error:
        print(error)

    print("Connection successful")
    return conn

def predict_images():
    query = """SELECT "id", "awsCID", "name" FROM "Asset" WHERE "projectId" = 37 AND "classification" = 'Camera Traps'"""
    conn = connect()
    cur = conn.cursor()
    
    try:
        # Execute a simple SQL command
        cur.execute(query)
        
        # Fetch all the data returned by the database
        rows = cur.fetchall()
        for row in rows:
            image_cnt = generate_id('cnt:predict:image')
            # Set multiple field-value pairs using HMSET
            # Convert the date string to a datetime object
            # date_obj = datetime.strptime(row[2].split('_')[1], "%Y%m%d%H%M%S")

            # # Format the datetime object to the desired format
            # formatted_date = date_obj.strftime("%d/%m/%Y %H:%M")
            # fields_values = {
            #     'uuid': row[0],
            #     'awsCID': row[1],
            #     'name': row[2],
            #     'sensor': 'M300/RGB',
            #     'label': '❓',
            #     'author': '❓',
            #     'timestamp': formatted_date
            # }
            # r.hmset(f'image:{image_cnt}', fields_values)

            image_url = f'https://gainforest-transparency-dashboard.s3.amazonaws.com/{row[1]}'
            print(f'predicting {image_url}')

            result = client.predict(
                image_url,	
                api_name="/predict"
            )

            with open(result, 'r') as file:
                json_data = json.load(file)

            # Extract labels and confidences from JSON data
            labels = [data['label'] for data in json_data['confidences']]
            confidences = [data['confidence'] for data in json_data['confidences']]

            # Store labels and confidences in Redis using HMSET
            for label, confidence in zip(labels, confidences):
                pred_cnt = generate_id(f'cnt:prediction:{image_cnt}')
                fields_values = {
                    'label': label,
                    'confidence': confidence,
                }
                r.hmset(f'prediction:{image_cnt}:{pred_cnt}', fields_values)

            
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)

    finally:
        # Close the cursor and connection
        cur.close()
        conn.close()
        r.close()

# Function to generate auto-incremented IDs
def generate_id(key):
    return r.incr(key)

predict_images()