你能成为任何你想成为的人
0%

对数据库批量翻译

发表于 更新于

用国外的源码编译了魔兽世界的服务器，其中的数据库信息都是英文的，虽然有些老的内容国内已经有成熟的汉化，但用的很多大量的新内容没有人汉化过，手动去翻译真的会疯掉，作为一向喜欢用技术手段‘偷懒’的我必须做点什么。。

脚本其实很简单，但用到的知识点较多，主要是python的mysqlDB、urllib、xlwt这几个模块。
先说下我的环境：
1.python 2.7
2.mysql 5.5.6

下面是主要几个模块的解释，完整代码最后。

连接数据库获取要翻译的字段

1
2
3
4
5
6
7
8
9
def connectMysql():
    db = MySQLdb.connect("127.0.0.1","root","root","database" )
    cursor = db.cursor()
    return cursor,db

cursor,db = connectMysql()
sql = 'select entry,NAME from item_template WHERE entry > 65000 and entry < 70000'
state = cursor.execute(sql)

借力百度翻译

我是抓包捞了百度的翻译接口，其实市面还有很多其它第三方翻译，看过有道、Google翻译等，都可以提供有偿的api接口，但花钱玩不是我的作风。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
def test_go(entry,name):
    url = "http://fanyi.baidu.com/v2transapi"
    values = {
        "from": "en",
        "to": "zh",
        "query": "%s"%name,
        "transtype": "realtime",
        "simple_means_flag": "3",
    }
    headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:53.0) Gecko/20100101 Firefox/53.0',
                "Accept": "*/*",
                "Accept-Language": "zh-CN,zh;q=0.8,en-US;q=0.5,en;q=0.3",
                "Referer": "http://fanyi.baidu.com/?aldtype=16047",
                "Connection": "keep-alive",
                "X-Requested-With": "XMLHttpRequest",
                "Content-Type": "application/x-www-form-urlencoded; charset=UTF-8",
                "Cookie":"BIDUPSID=124ADB9BC1148083F3FD928F0AB6E0D9; PSTM=1463040002; __cfduid=d3ce7d30fd22790d102423710107feb9c1464238819; BDUSS=mlKUU8tRTFOeWhnNXVmVDFmS01JZklWUnUwN09vUk9ObUhzRkxkTjVMYjJxdVZZSVFBQUFBJCQAAAAAAAAAAAEAAACj9i0bdDg4MDIxNnQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAPYdvlj2Hb5Yf; BAIDUCUID=++; BAIDUID=0DBED8BF3B45F6955CA3FFDA47BDEF62:FG=1; BDORZ=B490B5EBF6F3CD402E515D22BCDA1598; H_PS_PSSID=1462_13551_21125_22746_17001_22917; PSINO=3; locale=zh; Hm_lvt_64ecd82404c51e03dc91cb9e8c025574=1494913008; Hm_lpvt_64ecd82404c51e03dc91cb9e8c025574=1494913008; from_lang_often=%5B%7B%22value%22%3A%22en%22%2C%22text%22%3A%22%u82F1%u8BED%22%7D%2C%7B%22value%22%3A%22zh%22%2C%22text%22%3A%22%u4E2D%u6587%22%7D%5D; to_lang_often=%5B%7B%22value%22%3A%22zh%22%2C%22text%22%3A%22%u4E2D%u6587%22%7D%2C%7B%22value%22%3A%22en%22%2C%22text%22%3A%22%u82F1%u8BED%22%7D%5D; REALTIME_TRANS_SWITCH=1; FANYI_WORD_SWITCH=1; HISTORY_SWITCH=1; SOUND_SPD_SWITCH=1; SOUND_PREFER_SWITCH=1"
    }
    zhcnData = ''
    data = urllib.urlencode(values)
    req = urllib2.Request(url, data, headers)
    # 因为有时会有些莫名其妙的网络错误，加了判断
    try:
        response = urllib2.urlopen(req)
        responsedata = response.read()
        hjosn = json.loads(responsedata)
        dst = hjosn['trans_result']['data'][0]['dst']
        zhcnData = dst.encode('utf8')
    except:
        print ("this is error with entry ：%s"%entry)
    return zhcnData

来个更吊的，直接捞178魔兽数据的资料

178还没有添加军团物品的数据，但前面的几个版本的汉化还是很到位的。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
def get_178(entry):
    url = "http://db.178.com/wow/cn/item/%s.html"%entry
    req = urllib2.Request(url)
    try:
        response = urllib2.urlopen(req)
        responsedata = response.read()
        compile_rule = ur'<title>.*</title>'
        title_list = re.findall(compile_rule, responsedata)
        if title_list == []:
            title = ''
        else:
            title = title_list[0][7:-8]
            title = title.replace(' - 物品 - NGA178魔兽世界数据库- 德拉诺之王数据库- 简体 6.2','')
        return title

    except:
        print ("this is error with entry ：%s"%entry)

把翻译结果写入EXCEL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#新建excel表空间
xls = xlwt.Workbook()
sheet = xls.add_sheet("Sheet1")
row = 0

for data in cursor.fetchall():
    entry = data[0]
    enNanme =  data[1]
    cnName = test_go(entry,enNanme)
	#cnName = get_178(entry)
    if cnName != '':
        sheet.write(row, 0, entry)
        sheet.write(row, 1, enNanme)
        sheet.write(row, 2, cnName.decode('utf8'))
        row += 1
    print row,entry,enNanme,cnName

xls.save('translate.xls')

至此，我们在脚本的同级目录下，得到了一个翻译后的excel，3列数据，entry、enname、cnname，接下来就是用Navicate导入数据中一个新表，然后就是用update的方式去更新我们的外语老表了，

1
update a , b set a.name= b.name where a.entry = b.entry;

完整代码如下：translate.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
# -*-coding:utf-8-*-
__author__ = "orion-c"

import MySQLdb
import urllib
import urllib2
import json
import xlwt

def connectMysql():
    db = MySQLdb.connect("127","root","root","database" )
    cursor = db.cursor()
    return cursor,db

def test_go(entry,name):
    url = "http://fanyi.baidu.com/v2transapi"
    values = {
        "from": "en",
        "to": "zh",
        "query": "%s"%name,
        "transtype": "realtime",
        "simple_means_flag": "3",
    }
    headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:53.0) Gecko/20100101 Firefox/53.0',
                "Accept": "*/*",
                "Accept-Language": "zh-CN,zh;q=0.8,en-US;q=0.5,en;q=0.3",
                "Referer": "http://fanyi.baidu.com/?aldtype=16047",
                "Connection": "keep-alive",
                "X-Requested-With": "XMLHttpRequest",
                "Content-Type": "application/x-www-form-urlencoded; charset=UTF-8",
                "Cookie":"BIDUPSID=124ADB9BC1148083F3FD928F0AB6E0D9; PSTM=1463040002; __cfduid=d3ce7d30fd22790d102423710107feb9c1464238819; BDUSS=mlKUU8tRTFOeWhnNXVmVDFmS01JZklWUnUwN09vUk9ObUhzRkxkTjVMYjJxdVZZSVFBQUFBJCQAAAAAAAAAAAEAAACj9i0bdDg4MDIxNnQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAPYdvlj2Hb5Yf; BAIDUCUID=++; BAIDUID=0DBED8BF3B45F6955CA3FFDA47BDEF62:FG=1; BDORZ=B490B5EBF6F3CD402E515D22BCDA1598; H_PS_PSSID=1462_13551_21125_22746_17001_22917; PSINO=3; locale=zh; Hm_lvt_64ecd82404c51e03dc91cb9e8c025574=1494913008; Hm_lpvt_64ecd82404c51e03dc91cb9e8c025574=1494913008; from_lang_often=%5B%7B%22value%22%3A%22en%22%2C%22text%22%3A%22%u82F1%u8BED%22%7D%2C%7B%22value%22%3A%22zh%22%2C%22text%22%3A%22%u4E2D%u6587%22%7D%5D; to_lang_often=%5B%7B%22value%22%3A%22zh%22%2C%22text%22%3A%22%u4E2D%u6587%22%7D%2C%7B%22value%22%3A%22en%22%2C%22text%22%3A%22%u82F1%u8BED%22%7D%5D; REALTIME_TRANS_SWITCH=1; FANYI_WORD_SWITCH=1; HISTORY_SWITCH=1; SOUND_SPD_SWITCH=1; SOUND_PREFER_SWITCH=1"
    }
    zhcnData = ''
    data = urllib.urlencode(values)
    req = urllib2.Request(url, data, headers)
    # req = urllib2.Request(url)
    try:
        response = urllib2.urlopen(req)
        responsedata = response.read()
        hjosn = json.loads(responsedata)
        dst = hjosn['trans_result']['data'][0]['dst']
        zhcnData = dst.encode('utf8')
    except:
        print ("this is error with entry ：%s"%entry)
    return zhcnData

cursor,db = connectMysql()
#后面的条件自己定义哦
sql = 'select entry,NAME from item_template WHERE entry > 70000 and entry < 80000'
state = cursor.execute(sql)

#新建excel表空间
xls = xlwt.Workbook()
sheet = xls.add_sheet("Sheet1")
row = 0

for data in cursor.fetchall():
    entry = data[0]
    enNanme =  data[1]
    cnName = test_go(entry,enNanme)
    if cnName != '':
        sheet.write(row, 0, entry)
        sheet.write(row, 1, enNanme)
        sheet.write(row, 2, cnName.decode('utf8'))
        row += 1
    print row,entry,enNanme,cnName

xls.save('translate.xls')