import pymysql



connection = pymysql.connect(host='localhost', user='root', password='Ro3118086', db='billing', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)


def create_update_stmt(primary='id', **rows):
    fields = list(rows.keys())
    fields.remove(primary)
    stmt = ', '.join("{0}=VALUES({0})".format(k) for k in fields)
    return stmt

def upsert_stmt(table, primary='id', **rows):
    fields = rows.keys()
    cols = ', '.join('{}'.format(col) for col in fields)
    vals = ', '.join('%({})s'.format(col) for col in fields)
    sql = 'INSERT INTO {0} ({1}) VALUES ({2}) ON DUPLICATE KEY UPDATE '.format(table, cols, vals)
    sql += create_update_stmt(primary, **rows)
    return sql

def upsert_db(table,primary, **kwargs):
    with connection.cursor() as cur:
        qry = upsert_stmt(table,primary=primary, **kwargs)
        cur.execute(qry, kwargs)
        connection.commit()