python 数据库去重操作

# -*- coding: UTF-8 -*-
"""
@version: python2.7.8
@author: XiangguoSun
@contact: sunxiangguodut@qq.com
@file: connectdb.py
@time: 2016/12/28 16:12
"""
import MySQLdb
import time

def 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 sys
from connectdb import connectdb
reload(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!"