问题描述:工作上有多张带中文名称(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