python 数据库去重操作

来源：互联网发布：游戏开发程序员招聘网编辑：程序博客网时间：2024/04/27 16:51

# -*- coding: UTF-8 -*-"""@version: python2.7.8@author: XiangguoSun@contact: sunxiangguodut@qq.com@file: connectdb.py@time: 2016/12/28 16:12"""import MySQLdbimport timedef connectdb():    while True:        try:            db = MySQLdb.connect("localhost", "root", "root", "douban")            return db        except MySQLdb.Error,e:            print e.args,"(connectdb) we will try again after 10 seconds..."            time.sleep(10)

下面是对数据库进行操作

#!/usr/bin/env python# -*- coding: UTF-8 -*-"""@version: python2.7.8 @author: XiangguoSun@contact: sunxiangguodut@qq.com@file: dbfunction.py@time: 2017/2/10 11:08@software: PyCharm"""import sysfrom connectdb import connectdbreload(sys)sys.setdefaultencoding('utf-8')def fun(tablename):    db = connectdb()    cursor = db.cursor()    print "start transform",tablename,"..."    sql = "SELECT COUNT(*) as repetitions, uid, time " \          "FROM " + tablename + " " \                                "GROUP BY uid, time " \                                "HAVING repetitions > 1"    cursor.execute(sql)    an = cursor.fetchall()    print "duplicate data: ",len(an)    sql = "SELECT uid, `time` " \          "FROM " + tablename + " " \          "GROUP BY uid, time"    cursor.execute(sql)    print "create tmp..."    sql = "CREATE TABLE tmp SELECT `uid`, `likers_count`, `time`, `url`, `comments_count`, `activity`, `type`, `content`, `crawtime`" \          "FROM " + tablename + " GROUP BY uid, time"    cursor.execute(sql)    print "create tmp done!"    print "drop table "+tablename+"..."    sql = "DROP TABLE " + tablename    cursor.execute(sql)    print "drop table "+tablename+"done!"    print "rename table tmp as"+tablename+"..."    sql = "ALTER TABLE tmp RENAME TO " + tablename    cursor.execute(sql)    print "rename done!"    print "add key..."    sql = "ALTER TABLE " + tablename + " ADD PRIMARY KEY (`uid`,`time`)"    cursor.execute(sql)    print "key added done!"    print "succeed!"db = connectdb()for i in range(1,200):    fun("userlifestream_"+str(i))print "all done!"

请注意转义符号的使用··，尤其是在sql语句GROUP 等

还有增加联合主键：没有转意字符会报错

转义操作符是键盘上~键对应的

print "add key..."sql = "ALTER TABLE " + tablename + " ADD PRIMARY KEY (`uid`,`time`)"cursor.execute(sql)print "key added done!"

0 0