Recently I had created a python script to automate an ETL process that I needed to run. The script was pretty simple- Fetching data from a table in a postgres database and inserting it into my oracle environment. The initial script took 10 mins to write but once I had to generate the SQL statement to insert the different columns it took me way longer to write the SQL because I kept messing up the quotes and escape characters as I was manually typing this out. I realized it would be much simpler if I just create a function that would generate the SQL statement and can be reused. Such reusable functions can be placed in a common library.

def insert(table, **kwargs):
    """ update/insert rows into objects table (update if the row already exists)
        given the key-value pairs in kwargs """
    keys = ["%s" % k for k in kwargs]
    values = ["'%s'" % v for v in kwargs.values()]
    sql = list()
    sql.append("INSERT INTO %s (" % table)
    sql.append(", ".join(keys))
    sql.append(") VALUES (")
    sql.append(", ".join(values))
    sql.append(")")
    return "".join(sql)
	
#Usage example
#print(insert('tableA', LogID=500, LoggedValue=5,BOTNAME=row['botName'],NAME=row['name'],MESSAGE=row['Message']))


def delete(table, **kwargs):
    """ deletes rows from table where **kwargs match """
    sql = list()
    sql.append("DELETE FROM %s " % table)
    if kwargs:
        sql.append("WHERE " + " AND ".join("%s = '%s'" % (k, v) for k, v in kwargs.items()))
    return "".join(sql)


#Usage example
#print(delete('tableA', LogID=500, LoggedValue=5))