################################################################################
# - Select SQLite data into a Python list of dictionaries
# - Insert a list of dictionaries into a SQLite table (table must exist)
################################################################################
import sqlite3

def select(db_name, sql):
    conn = sqlite3.connect('/sqlite/' + db_name + '.db')
    conn.row_factory = sqlite3.Row		        #return a dictionary (with col names/values)
    curs = conn.cursor( )
    
    list = [ ]					                #create an empty list

    try:
        curs.execute(sql)
    except Exception as e:
        print(F"Could not execute {sql} \n {e}")
        return(list)

    result = curs.fetchall( )			        #get the query result

    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] = value		        #add the column name & value to dictionary
        list.append(dict)				        #add dictionary to the list

    curs.close()
    conn.close()
    
    return(list)
    
###################################################################
# Insert a list of dictionaries into a SQLite table
# Table must pre-exist
###################################################################
def insert(list, db_name, table_name):
    conn = sqlite3.connect('/sqlite/' + db_name + '.db')
    curs = conn.cursor( )

    sql_inserts = [ ]                                   #create an empty list to hold the sql

    for dict in list:                                   #loop thru list of dictionaries
        col_names  = dict.keys( )                       #get the column names
        col_values  = dict.values( )                    #get the column values
        sql   = "INSERT INTO " + table_name 
        sql += " (" + ", ".join(col_names) + ") "       #add the column names
        sql += "VALUES("
        for col_value in col_values:                    #for each column value
            if isinstance(col_value, str):              #if the value is a string
                sql += "'" + col_value + "', "          #add single quotes around it
            else:
                sql += str(col_value) + ", "            #no quotes around the numeric
        sql  = sql[:len(sql)-2]                         #get rid of the last comma
        sql += ')'                                      #add a final ) 

        sql_inserts.append(sql)

#   print(sql_inserts)                                  #for debugging

    for sql in sql_inserts:
        try:
            curs.execute(sql)                           #execute the sql insert
        except Exception as e:
            print(F"Could not execute {sql} \n {e}")
            return(False)

    conn.commit()
    curs.close()
    conn.close()
    
    return(True)