Inserts a dictionary of column names, values into a MySQL database if a record doesn't currently exist based on a dictionary of constraints.

InsertIfNotExist.py

import MySQLdb as mdb
 
## Get the a hash ID from the database
## If it doesn't exist, add it to the database and return the new ID
## value_map is flattened before insertion so any single column should be in the entire dict.
## con (MySQLdb): current MySQLdb connection
## table (string): table name to update
## pk (string): column name of the primary key (id)
## value_map (dict): needs are necessary constraints, needsone are only-one-needed constraints, others are for insert if not exist
    ## { 
    ##   'needs': { column_name: value, ... }, 
    ##   'needsone': { column_name: value, ... },
    ##   'others': { column_name: value, ... }
    ## }
def insert_if_not_exist(con, table, pk, value_map):
    if type(value_map) == type({}):
        ## Build our where clause for the existence check
        where_clause = ''
        if value_map.has_key('needs'):
            where_clause += ' AND '.join(["%s = '%s'" % (k, mdb.escape_string(str(v))) for k, v in value_map['needs'].items()])
        if value_map.has_key('needs') and value_map.has_key('needsone'):
            where_clause += " AND "
        if value_map.has_key('needsone'):
            where_clause += '(%s) ' % ' OR '.join(["%s = '%s'" % (k, mdb.escape_string(str(v))) for k, v in value_map['needsone'].items()])
    else:
        raise TypeError("value_map must be a dict.")

    with closing(con.cursor(mdb.cursors.DictCursor)) as cur:
        try:
            cur.execute("SELECT * FROM %s WHERE %s" % (table, where_clause))
            entry = cur.fetchone()

            if entry == None:
                ## Build our insert values
                ## This is a little complex because order matters when inserting so we are guaranteeing order.
                ## This just flattens the dict, discarding the key names of the first level
                full_value_map = reduce(lambda x, y: dict(x.items() + y.items()), [dict([(k2, v2) for k2, v2 in v.items()]) for k, v in value_map.items()])
                insert_column_names = full_value_map.keys()
                insert_columns = ', '.join(insert_column_names)
                insert_values = ', '.join(["'%s'" % mdb.escape_string(str(full_value_map[k])) for k in insert_column_names])
                
                cur.execute("INSERT INTO %s(%s) VALUES (%s)" % (table, insert_columns, insert_values))
                entryid = con.insert_id()
            else:
                entryid = entry[pk]
        except Exception as e:
            entryid = ''
            print "Database error: %s" % e
    
    return entryid