python调用百度翻译api

做国外项目的时候，项目做国际化，发现一些初始化表数据中仍然有中文，这部分国际化是无法配置的，只能手动翻译成英文，但是太多张表和数据，一条一条的翻译又太麻烦，所以有了调用百度翻译的核心接口进行自动翻译。

核心流程

1、设置mysql连接信息、百度翻译接口认证信息

2、确定需要翻译的表以及其中的字段

3、循环对table_list进行处理，备份，查询，翻译，重新update

核心代码

import hashlib
import json
import time

import pymysql.cursors
import requests

# 连接数据库
connect = pymysql.Connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='XXXXXXXXXXXX',
    db='test',
    charset='utf8'
)

cursor = connect.cursor()

url = "http://api.fanyi.baidu.com/api/trans/vip/translate"
password = "YYYYYYYYYYYYY"
appid = "ZZZZZZZZZZZZ"
slat = "1435660288"
lang_from = "zh"
lang_to = "en"


def translate(translate_text):
    time.sleep(1)
    if translate_text == "":
        return ""
    params = {
        "from": lang_from,
        "to": lang_to,
        "q": translate_text,
        "appid": appid,
        "salt": slat,
    }
    sign_str_origin = params['appid'] + params['q'] + params['salt'] + password
    sign = hashlib.md5(str.encode(sign_str_origin)).hexdigest()
    params['sign'] = sign
    res = requests.get(url, params=params)
    obj = json.loads(res.text)
    if 'trans_result' in obj:
        trans_result = obj['trans_result']
        for trans in trans_result:
            dst = str(trans['dst']).encode("utf-8").decode("unicode_escape")
            print(translate_text + "->" + dst)
            return dst
    print(translate_text + "->" + translate_text)
    return translate_text


def createObjBySql(sql, isOne):
    cursor.execute(sql)
    if cursor.rowcount <= 0:
        return None
    columns = []
    for column in cursor.description:
        columns.append(column[0])
    objList = []
    for row in cursor.fetchall():
        obj = {}
        for i in range(0, len(columns)):
            obj[columns[i]] = row[i]
        objList.append(obj)

    if isOne:
        return obj
    else:
        return objList


def back(sql, tableName):
    cursor.execute(sql)
    columns = []
    objList = []
    for column in cursor.description:
        columns.append(column[0])
    for row in cursor.fetchall():
        obj = {}
        for i in range(0, len(columns)):
            obj[columns[i]] = row[i]
        objList.append(obj)
    keyFieldStr = ""
    for i in range(0, len(columns)):
        if i < len(columns) - 1:
            keyFieldStr = keyFieldStr + str(columns[i])
            keyFieldStr = keyFieldStr + ","
        else:
            keyFieldStr = keyFieldStr + str(columns[i])
    currentTime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    file = open("./beifen/" + str(tableName) + ".txt", "a+")
    file.write("-------------" + str(currentTime) + "-------------------")
    file.write("\r\n")
    file.write("-------------" + str(sql) + "----------------------------")
    file.write("\r\n")
    file.write("\r\n")
    for obj in objList:
        valueStr = ""
        for i in range(0, len(columns)):
            if i < len(columns) - 1:
                if obj[columns[i]] is None:
                    valueStr = valueStr + "null"
                    valueStr = valueStr + ","
                else:
                    valueStr = valueStr + "'"
                    valueStr = valueStr + str(obj[columns[i]])
                    valueStr = valueStr + "'"
                    valueStr = valueStr + ","
            else:
                if obj[columns[i]] is None:
                    valueStr = valueStr + "null"
                else:
                    valueStr = valueStr + "'"
                    valueStr = valueStr + str(obj[columns[i]])
                    valueStr = valueStr + "'"
        insertSql = "insert into " + tableName + "(" + keyFieldStr + ")  values(" + valueStr + ");"
        file.write(insertSql)
        file.write("\r\n")
        print(insertSql)
    file.write("\r\n")
    file.write("\r\n")
    file.close()


if __name__ == '__main__':
    table_list = [
        {
            "table_name": "base_stock",
            "fields": ['name', "fullname"]
        }
    ]
    for table in table_list:
        name = table['table_name']
        back("select * from " + name, name)
        fields = table['fields']
        field_str = ""
        for field in fields:
            field_str = field_str + "," + field
        sql = "select id" + field_str + " from " + name + " "
        obj_list = createObjBySql(sql, False)
        print(json.dumps(obj_list, ensure_ascii=False))
        for obj in obj_list:
            for field in fields:
                if obj[field] is not None:
                    obj[field] = translate(obj[field])
                    field_update_str = ""
                    is_start = True
                    for field in fields:
                        if obj[field] is not None:
                            if not is_start:
                                field_update_str = field_update_str + "," + field + "='" + obj[field] + "'"
                            else:
                                field_update_str = field_update_str + field + "='" + obj[field] + "'"
                                is_start = False
                    if field_update_str != "":
                        sql = "update " + name + " set " + field_update_str + " where id=" + str(obj['id'])
                        print(sql)
                    cursor.execute(sql)
                    connect.commit()

中文数据表

![]

结果

注意点1：这份脚本只适合数据量比较小，主要是一些字典数据，因为百度翻译接口免费版的有并发限制，我直接设置了1秒一次，所以数据量大比较慢。