问题描述:工作上有多张带中文名称(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
相关推荐
mysql数据库设计文件 mysql数据库设计文件 mysql数据库设计文件
MySQLDB.sql
连接MySQLdb1-MySQLdb-1.2.5.zip MySQLdb1-MySQLdb-1.2.5.zip MySQLdb1-MySQLdb-1.2.5.zip MySQLdb1-MySQLdb-1.2.5.zip
使用MySQLdb连接数据库执行sql语句时,有以下几种传递参数的方法。 1.不传递参数 conn = MySQLdb.connect(user=root,passwd=123456,host=192.168.101.23,db=cmdb) orange_id = 98 sql = select * from orange where...
这个包里有两个包,都可以用来在windows上安装python-MySQLdb,选择其中一种就可以!我比较喜欢用-master那个包,因为只要解压后执行python setup.py install就可以了。另一个包在安装时若出现找不到python2.7的安装...
MysqlDB pymysql操作类库
官方mysqldb用户手册,英文版,方便使用
python2.6-MySQLdb,包括python2.6安装包、MySQLdb for python2.6安装包、MySQLdb使用配置,为想学习Python操作mysql数据库的同学提供了极大的方便。。。
MySQLdb 1.2.3 for Python 2.7 (Windows)
本文主要内容python MySQLdb数据库批量插入insert,更新update的: 1.python MySQLdb的使用,写了一个基类让其他的sqldb继承这样比较方便,数据库的ip, port等信息使用json配置文件 2.常见的查找,批量插入更新 ...
windows环境下配置python3 mysqldb
python安装模块MySQLdb时,提示python2.7未安装,安装失败时,命令行python register.py,然后重新安装MySQLdb,就能正确安装。
MySQLdb模块英文版使用手册,内置搜索功能,方便查找定位使用语法
windows mysqldb for python2.5 in windows
鸟人封装的MySQLdb操作接口,包含读写分离、断线重连的逻辑,对于具体应用可以继承和扩展
Python的mysql插件,该包是64位的。
封装了mysqldb方法方便调用。直接在脚本引用
MySQL-python-1.2.5.win-amd64-py2.7
此资源为pthon和MySQLdb进行数据库开发的安装文件,有32位和64位版本