-- coding: utf8 --

生成sql语句

import MySQLdb

import pymysql
import sys
import random
import string

defaultencoding = 'utf-8'
if sys.getdefaultencoding() != defaultencoding:
reload(sys)
sys.setdefaultencoding(defaultencoding)

def getdb(db_name):

server_info = open(path + "/SqlServerInfo.txt", "r")

connectInfo = []

for line in server_info:

h = line.strip().decode('gbk').encode('utf-8')

connectInfo.append(h)

password_info = open("E:/DB_backup/passwd.txt", "r")
passwd = []
for line in password_info:
    v = line.strip().decode('gbk').encode('utf-8')
    passwd.append(v)

# 连接

host = connectInfo[0].split("=")[1]

user = connectInfo[1].split("=")[1]

charset = connectInfo[2].split("=")[1]

db = pymysql.connect(host='localhost', user='root', passwd=passwd[0], db=db_name, port=16033,charset='utf8')

server_info.close()

password_info.close()
return db

def connectMySql(db_name, exc_name, name_id):
db = getdb(db_name)
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# cursor.execute("SELECT %s FROM %s;" % (name_id, exc_name))
cursor.execute("SELECT %s FROM %s order by guid asc;" % (name_id, exc_name))
data = cursor.fetchall() # 所有数据

updateName(db, cursor, data, db_name, exc_name, name_id)

db.close() # 关闭数据库连接

def upSql(db_name, exc_name, name_id):
db = getdb(db_name)
# 使用cursor()方法获取操作游标
cursor = db.cursor()
cursor.execute("SELECT %s FROM %s;" % (name_id, exc_name))
data = cursor.fetchall() # 所有数据
return data

def updateName(db, cursor, data, db_name, exc_name, name_id):
f = open("E:/BlockWord.txt", "r")
words = []
print "提取过滤词"
for line in f:
word = line.strip().decode('gbk').encode('utf-8')
words.append(word)

nameList = []
print "提取用户名"
num = 0
changeNameList = []
print "start"
for i in data:
    name = i[0]
    nameList.append(i[0])
    print "up in"
    num += 1
    str = ''.join(words)  # 优化方案，先把屏蔽词串联起来，用户名先去过滤一遍再循环
    job = 0
    for k in name:
        if k in str:
            job += 1
    if job > 0:
        count = 0
        for i in range(words.__len__()):  # 遍历每一个敏感词
            if words[i] in name:  # 判断是否包含敏感词
                count += 1
                print "has block word"
                result = name.replace(words[i], '*')
                if result in nameList:
                    result =  result +"%s"%num #commonNameChange(words[i], changeNameList, result)# result + "%s" % num
                if result not in changeNameList:
                    changeNameList.append(result)
                nameList[num - 1] = result
        if count > 0:
            sql = "update %s  set %s = '%s' where %s = '%s';" % (exc_name, name_id, nameList[num - 1], "guid", num)
            try:
                cursor.execute(sql)  # 执行sql语句
                db.commit()  # 提交到数据库执行
            except:
                db.rollback()  # 发生错误后回滚

print"替换完成"
f.close()

从a-zA-Z0-9生成指定数量的随机1个字符

def randomStr():
ran_str = ''.join(random.sample(string.ascii_letters + string.digits, 1))
return ran_str

重复名处理

def commonNameChange(words, nameList, result):
state = True
while(state == True):
if '' not in result and '+' not in result:
result = result.replace(words, '')
elif '+' not in result and '' in result:
result = result.replace('', '+')
else:
result = result.replace('+', randomStr())
# break

    if result in nameList:
        state = True
    else:
        state = False
return result

if name == "main":
db_name = sys.argv[1]
exc_name = 'characters'
name_id = 'name'
connectMySql(db_name, exc_name, name_id)

path = sys.argv[4].replace("\", "/")

db_info = open(path + "/db_info.txt", "r")

list = []

for line in db_info:

info = line.strip()

list.append(info)

for k in list:#批量数据库处理

db_name = k.split()[0]

exc_name = k.split()[1]

name_id = k.split()[2]

connectMySql(db_name, exc_name, name_id)