################################################################################
# Execute SQL against a local SQLite database
# if select: Return results as a list of dictionaries 
# otherwise: Return a success message
################################################################################
import sqlite3
import re

def process(db_name, sql): 

    try:
        conn = sqlite3.connect('/sqlite/' + db_name + '.db')
        conn.row_factory = sqlite3.Row		            #return a dictionary (with col names/values)
        curs = conn.cursor( )
        curs.execute(sql)
    except Exception as e:       
        return([{'ERROR':f'Failed to execute SQL - {e}'}])

    if re.search("\s*SELECT ", sql, re.IGNORECASE):     #if sql contains SELECT    
        result = curs.fetchall( )                       #get the query result
        list   = [ ]                                    #create an empty list
        for row in result :                             #for each row in the result
            dict = { }                                  #create an empty dictionary
            for colname in row.keys( ):                 #for each column name in the row
                value = row[colname]                    #get the column value
                if value == None: value = ""
                dict[colname] = str(value)              #add the column name & value to dictionary
            list.append(dict)                           #add dictionary to the list

    else:                                               #must be INSERT/UPDATE/DELETE
        conn.commit()                                   #commit to database
        list = [{'RESULT':'Processed Successfully'}]

#   print(list)                                         #for debugging 
    curs.close()
    conn.close()
    
    return(list)