使用Python给Mysql数据库表去重以及排序

RemoveRepetition.py

#!/usr/bin/python2.7  
# -*- coding: utf-8 -*-  

import MySQLdb
import sys


def removeRepetition(argv):
    host = argv[0]
    username = argv[1]
    password = argv[2]
    dbname = argv[3]
    tablename = argv[4]
    withKey = True

    # 连接数据库
    db = MySQLdb.connect(host, username, password, dbname, charset='utf8' )
    cursor = db.cursor()
    try:
        # 查询主键
        cursor.execute('SELECT column_name FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE table_name=\'%s\' AND constraint_name=\'PRIMARY\''%tablename)
        key = cursor.fetchone()[0]
        # 查询主键是否自增，自增忽略主键
        cursor.execute('SELECT * FROM information_schema.columns WHERE table_name=\'%s\' AND column_name=\'%s\' AND extra=\'auto_increment\''%(tablename, key))
        withKey = len(cursor.fetchall()) == 0

        # 查询所有字段名，以逗号分隔
        cursor.execute('SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ",") FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = \'%s\' AND TABLE_NAME = \'%s\''%(dbname, tablename))
        allcols = cursor.fetchone()[0].split(',')

        if (not withKey) and (key in allcols):
            # 去除主键
            allcols.remove(key)

        # 拼接字段名
        allcolsName = ''
        for col in allcols:
            allcolsName += col + ','
        # 去掉最后一个逗号
        allcolsName = allcolsName[:-1]

        # 去重排序查询
        cursor.execute('select distinct %s from %s order by %s'%(allcolsName, tablename, allcolsName))
        rows = cursor.fetchall()

        # 清空表
        cursor.execute('truncate table ' + tablename)

        # 重新插入不重复的数据
        for row in rows:
            values = ''
            cursor.close()
            for v in row:
                cursor = db.cursor()
                if isinstance(v, unicode):
                    values += '\'%s\''%v
                elif isinstance(v, str):
                    values += u'\'%s\''%v
                else:
                    values += str(v)
                values += ','
            values = values[:-1]
            sql = 'insert into %s(%s) values(%s)'%(tablename, allcolsName, values)
            print sql
            cursor.execute(sql)
            cursor.close()
        db.commit()
    except Exception as e:
        print e
        cursor.close()
        db.rollback()

    db.close()



if __name__ == '__main__':
    try:
        removeRepetition(sys.argv[1:])
    except Exception as e:
        print e

RemoveRepetition.bat

@echo off 
cd  %~dp0
rem 去重排序是升序，一般用于数据类型只有字符串、数字类型和布尔类型的表，其他数据类型没测试
rem 参数一：127.0.0.1， 数据库IP地址
rem 参数二：root， 数据库用户名
rem 参数三：111111， 数据库密码
rem 参数四：dbname ， 数据库名称
rem 参数五：tablename， 数据库表名
python RemoveRepetition.py 127.0.0.1 root 111111dbname tablename
pause
echo exit