sql2redis.py

import redis
import psycopg2
from gradio_client import Client
import json

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 get_all_images():
    query = """SELECT "id", "awsCID" 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:image')
            # Set multiple field-value pairs using HMSET
            fields_values = {
                'uuid': row[0],
                'awsCID': row[1],
                'sensor': 'Rover/RGB',
                'label': '❓',
                'author': '❓',
                'timestamp': '❓'
            }
            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()

def get_all_sounds():
    query = """SELECT "id", "awsCID" FROM "Asset" WHERE "projectId" = 37 AND "classification" = 'Bioacoustics'"""
    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:
            sound_cnt = generate_id('cnt:sound')
            # Set multiple field-value pairs using HMSET
            fields_values = {
                'uuid': row[0],
                'awsCID': row[1],
                'sensor': 'Speedybee/Acoustic',
                'label': '❓',
                'author': '❓',
                'label_at': '❓',
                'timestamp': '❓'
            }
            r.hmset(f'sound:{sound_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)

r.flushdb()
get_all_images()
get_all_sounds()