###################################################################
# Insert a list of dictionaries into a SQLite table
###################################################################
import sqlite3

conn  = sqlite3.connect('/home/sultans/data/sqlite/sqlite.db')      #server database
#conn = sqlite3.connect('/sqlite/sqlite.db')                        #local database 
curs  = conn.cursor( )

list = [ {'student_id': 1, 'lname': 'Burns', 'fname': 'Barbara', 'ssn': '000-01-0001', 'sex': 'F'},  
         {'student_id': 2, 'lname': 'Cambria', 'fname': 'Vincent', 'ssn': '000-01-0002', 'sex': 'M'}, 
         {'student_id': 3, 'lname': 'Davidson', 'fname': 'Duncan', 'ssn': '000-01-0003', 'sex': 'M'} ]

table_name = 'xyz'                                  #assign a table name

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)

for sql in sql_inserts:
    print(sql)                                      #for debugging
    curs.execute(sql)                               #execute the sql insert

conn.commit()
curs.close()
conn.close()