MySQLdb+动态组合批量插入sql+防重复（时间和空间代价）

问题描述：工作上有多张带中文名称（name）和英文名称（name_en）两个字段的表（name可以为null，有索引，数据量从十万到千万之间）供公司网站后台使用，由于使用人员添加数据的时候，没有很好的检查机制，会造成一个中文名对应多个英文名称情况发生，现在需要定时将多张表的数据导入一张新的表，保证一个中文名称对应一个英文名称（name不可以为null，有唯一性索引）

 

环境工具：python2.6 windowsXP

 

解决过程：1. 新建的表里面，中文名称（name）是唯一性索引unique，保证一个中文名称对应一个英文名称，有两种方案，第一种，在插入之前，用python的逻辑检查数据的重复情况；第二种，在插入之时，用sql自身的机制检查重复性

 

                   2. 考虑第二种方案，sql避免重复插入，有很多种如下

ignore关键字

根据（主键primary字段、唯一索引unique字段）区分记录的唯一性

      insert ignore into table_name(name, name_en) values('全国花生油平均价格', 'price of oil')

执行结果，name、name_en始终保持第一次插入的值，第一次插入时，返回影响行数为1，之后插入时，忽略insert，返回影响行数0

replace关键字

根据（主键primary字段、唯一索引unique字段）区分记录的唯一性

       replace into table_name(name, name_en) values('全国花生油平均价格', 'price of oil')

执行结果，name保持第一次插入的值，name_en更新为最后一次插入的值，第一次插入时，返回影响行数1，之后再插入时，先delete旧行，再insert新行，返回影响行数2

on duplicate key update关键字根据（主键primary字段、唯一索引unique字段）区分记录的唯一性

        insert into table_name(name, name_en) values('全国花生油平均价格', 'price of oil') on duplicate key update name_en = 'price of oil'

执行结果，name保持第一次插入的值，name_en更新为最后一次插入的值，第一次插入时，返回影响行数1，之后再插入时，update区分唯一性字段以外的其他字段，返回影响行数1

 

        考虑到数据的最新价值和效率问题，采用on duplicate key update关键字，但是在使用过程中，用python组合sql语句

insert into table_name(name, name_en) values(%s, %s) on duplicate key update name_en = %s

批量整合参数时，配四个参数，始终报错bug：not all arguments converted during string formatting，如果配三个参数，也报错SQL syntax语法问题

        通过报错信息回溯，Python26/Lib/site-packages/MySQLdb/cursors.py里面

提取参数的正则表达式

       restr = (r"\svalues\s*"
        r"(\(((?<!\\)'[^\)]*?\)[^\)]*(?<!\\)?'"
        r"|[^\(\)]|"
        r"(?:\([^\)]*\))"
        r")+\))")

def executemany(self, query, args):里面有

        m = insert_values.search(query)

这里只提取了values(%s, %s, %s)里面的参数，导致条件参数始终无法匹配上，报错；在此，改用

replace into table_name(name, name_en) values(%s, %s)

或者

insert into table_name(name, name_en) values(%s, %s) on duplicate key update name_en = values(name_en)

 

                  3. 考虑第三种方案，python逻辑处理重复问题，由于destination表和source表的数据量都很大，因此在判断数据是否重复的比较时，要考虑时间和空间的问题，也有两种方案

in关键字

select name, name_en from source #取源数据

result1 = dict(name, name_en)

select name, name_en from destination #取目的数据

result2 = dict(name, name_en)  

for name, name_en in result1:

     if name, name_en in result2: #检测源数据是否在目的数据

          update destination set name_en = %s where name = %s #在，update

     else:

          insert into destination(name, name_en) values(%s, %s) #不在，insert

分段排序orderby limit段外递增推进 binarySearch段内二分查找，段内丢弃result1 = []

result2 = []

currpage = 0

for i in range(pages1):

    startindex = i * pagenum

    select name, name_en from source where name_en is not null order by name limit startindex, pagenum

    result1 = result1.extends(dict(name, name_en))

    for name, name_en in result1:

        if name, name_en in result2: #检测源数据是否在目的数据

             update destination set name_en = %s where name = %s #在，update

             result2 = result2[result2.index((name,name_en))+1:len(result2)] #段内丢弃

             result1.remove(result1.index((name,name_en))) #删除存在元素

        else:

              for j in ranges(currpage, pages2): #段外递增推进

                   startindex = j * pagenum            

                   select name, name_en from source where name_en is not null order by name limit startindex, pagenum

                   result2 = dict(name, name_en)

                   if name, name_en in result2:

                       update destination set name_en = %s where name = %s #在，update

                       result2 = result2[result2.index((name,name_en)):len(result2)] #段内丢弃

                       result1.remove(result1.index((name,name_en))) #删除存在元素

                       currpage=j 段号增长

                       break

if result1:

      for name, name_en in result1:

            insert into destination(name, name_en) values(%s, %s) #不在，insert