sql2redis.py

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

# Read the JSON file
# Read the JSON file
with open('./uuids.json', 'r') as file:
    uuid_list = json.load(file)

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", "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:
            # Set multiple field-value pairs using HMSET
            # Convert the date string to a datetime object
            if row[0] not in uuid_list:
                print(row[0])
                image_cnt = generate_id('cnt:image')
                if row[2].split('_')[0] == 'DJI':
                    if len(row[2].split('_')[1]) < 5:
                        print(f'adding DJI Mavic3: {row[2]}')

                        fields_values = {
                            'uuid': row[0],
                            'awsCID': row[1],
                            'name': row[2],
                            'sensor': 'Mavic3/RGB',
                            'label': '❓',
                            'author': '❓',
                            'timestamp': row[2].split('_')[1]
                        }

                    else:
                        print(f'adding DJI M300: {row[2]}')
                        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)
                
                else:
                    print(f'adding Rover: {row[2]}')
                    # Format the datetime object to the desired format
                    fields_values = {
                        'uuid': row[0],
                        'awsCID': row[1],
                        'name': row[2],
                        'sensor': 'Rover/RGB',
                        'label': '❓',
                        'author': '❓',
                        'timestamp': row[2].split('_')[0]
                    }
                    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': 'Rover/Acoustic',
                'label': '❓',
                'author': '❓',
                'label_at': '❓',
                'timestamp': '6th June 2023, 6 am'
            }
            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()