目录
近一直在做的就是各种读写数据的问题。。。。虽然我也不知道为什么。
1、数据的批量处理——executemany
个人觉得这个方法还是蛮好用的,主要的做法就是,把需要insert或者update的数据按相应的格式生成一个list,list的元素对应要写入的数据,用法如下。可以看到,它其实就是之前的单条执行SQL语句的批量执行版,所以只要把对应的数据构造好就可以了,也就是说,任何单条可执行的语句,只要数据打包好,都可以用这个来执行。
最神奇的是,这个可批量写的数据还蛮大的,今天用这个一次性批量写了80万条记录,速度也还行吧,几分钟,主要是没有跟单条的对比过时间花费。
data = []
for i in range(1000):
data.append((1+i,2+i,3+i)) # 每条记录对应的是一个tuple,其实我试过list也是可以的
insert_cmd = "insert into table (column1, column2, column3) values(%s,%s,%s)"
update_cmd = "update table set column1=%s, column2=%s where column3=%s"
con = pymysql.connect(host=host,port=port,user=user,password=password,db=database,charset=charset)
cur = con.cursor()
try:
cur.executemany(insert_cmd, data) # 批量插入的用例
cur.executemany(update_cmd, data) # 批量更新的用例
except Exception as e:
logging.info(e)
2、数据不存在则添加,存在则更新或者跳过
(1)存在则跳过,不存在则新增
insert ignore into这个方法应用的前提是你有一个唯一的主键或者表索引,为的就是在写入数据时,根据这个主键或者索引判断是插入数据,还是忽略即跳过这条。
感觉在有唯一键或索引的时候,这个方法非常好用。以下面的句子为例,假设我已经将a列设为唯一索引了,如果现在table里面已经存在一条数据为(1,5,8),那么下面这条语句不会插入任何数据,因为新插入的记录a=1已经在table中存在了;如果table中的数据为(2,2,3),那么下面这条语句就会insert (1,2,3)这条记录。
insert ignore into table (a, b, c) values(1,2,3)
(2)存在则更新,不存在则新增
方法1、
INSERT ... ON DUPLICATE KEY UPDATE,这个用法也是基于主键或者唯一索引的,个人感觉跟上面的理解异曲同工。都是基于索引或者键是否存在,存在的话执行某种操作,不存在就执行另一种操作。看了网上各种一模一样的例子,实在是讲得不清不楚,今天自己试着跑了一下,终于理解了怎么用了。
以下面句子为例,假设表在a上建立了唯一索引,现在执行下面这条语句,做的事情就是,如果table中不存在a=1的记录,就实现on前面的句子,也就是直接插入(1,2,3)这条记录;如果table中存在a=1这样的记录,那么就执行update后面的操作,即更新a=1这条记录,将这条记录的b更新为2,c更新为3。emmmm,其实就很好理解,总是判断哪个唯一的键或者索引存不存在,再做相应的操作。
insert into table (a,b,c) values (1,2,3) on duplicate key update b=2, c=3
方法2、
replace into的方式,这个也是根据主键或者唯一索引来的。但是这个方法比较坑的地方是,如果已经存在相应记录,做的事情是先删除这条记录,然后再新增一条记录,还是以例子来说。
假设a建立了唯一索引,现在运行下面的SQL语句。如果table中有a=1的记录存在,那么系统做的就是删除原来那条a=1的记录,再插入底下这条(1,2,3)的记录。如果不存在就直接新增一条。这个有个问题就是,如果你现在给表一个自增的id,通过这种方式更新数据的话,自增id会不间断的持续增大。个人不是很建议使用这种方式更新数据,之前也有看有的博主说到这个方法会产生碎片化的问题。
replace into table (a,b,c) values (1,2,3)
这个方式也可以在没有主键或者唯一索引的表上使用,这个时候它的功能就是直接insert记录,不会去跟原表中的任何记录做对比。也就是说不管原来有没有(1,2,3)这条记录,用上面这条语句,都会再增加一条(1,2,3)的记录。
3、mysql删除重复记录并且只保留一条
这个参考:【mysql】mysql删除重复记录并且只保留一条 。非常好用