【2022.07.04】executemany的插入前检查值是否存在

发包给数据库

def send_to_db(sql,data):
    conn = pymysql.connect(host='',
                           user='',
                           password='',
                           port=,
                           database='',
                           autocommit=True
                           )
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    affect_rows = cursor.executemany(sql, data)
    print('插入行数', affect_rows)
    cursor.close()
    conn.close()

在插入前检查是否已经存在相同数据（不含主键

def sql_generate(datebase_name, data_list):
    insert_sql = 'INSERT INTO `' + datebase_name + '`('
    count = 0
    all = len(data_list[0].keys())
    for j in data_list[0].keys():
        insert_sql = insert_sql + j
        count = count + 1
        if count<all:
            insert_sql = insert_sql + ','
    insert_sql = insert_sql + ') select '
    count = 0
    for j in data_list[0].keys():
        insert_sql = insert_sql + '%(' + j + ')s'
        count = count + 1
        if count < all:
            insert_sql = insert_sql + ','
    insert_sql = insert_sql + '\nFROM DUAL WHERE NOT EXISTS(SELECT * FROM ' + datebase_name +' WHERE '
    count = 0
    for j in data_list[0].keys():
        insert_sql = insert_sql + j +' = %(' + j + ')s'
        count = count + 1
        if count<all:
            insert_sql = insert_sql + ' and '
    insert_sql = insert_sql + ');'
    print(insert_sql)
    send_to_db(insert_sql, data_list)
    return insert_sql

普通的插入数据

def sql_generate(datebase_name, data_list):
    insert_sql = 'INSERT INTO `' + datebase_name + '`('
    count = 0
    all = len(data_list[0].keys())
    for j in data_list[0].keys():
        insert_sql = insert_sql + j
        count = count + 1
        if count<all:
            insert_sql = insert_sql + ','
    insert_sql = insert_sql + ') values('
    count = 0
    for j in data_list[0].keys():
        insert_sql = insert_sql + '%(' + j + ')s'
        count = count + 1
        if count < all:
            insert_sql = insert_sql + ','
    insert_sql = insert_sql + ');'
    send_to_db(insert_sql, data_list)
    return insert_sql