def makeCoffeeTranslate(coffeeSql, coffeeFile):
print("begin makeCoffeeTranslate")
# 打开数据库连接
# db = pymysql.connect("192.168.1.207", "root", "", "CHARACTER_SETS")
db = pymysql.connect(host=host,
port=port,
user=user,
passwd=passwd,
db=database,
charset='utf8mb4')
cursor = db.cursor()
allList = et.excelToList(coffeeFile)["RECORDS"]
for item in allList:
ID = item[0]
vit = item[2]
# SQL 查询语句
if item[0] == 'Id':
continue
sql = coffeeSql.format(vit, str(int(ID)))
print(sql)
try:
# 执行SQL语句
cursor.execute(sql)
db.commit()
# 获取所有记录列表
print(cursor.rowcount)
except:
print("Error: unable to fetch data")
# 关闭数据库连接
db.close()
print("begin makeCoffeeTranslate success")
def splitOneXlsToXlsFile(xlsFile):
if not xlsFile:
print("no xls file for split !!!")
return
dddd = et.excelToList(xlsFile)
for k in dddd:
for i in range(len((dddd[k]))):
if i == 0:
for j in range(len((dddd[k][i]))):
#todo 可以无限加 smjb
dddd[k][i][j] = dddd[k][i][j].replace('序号', 'Id')
dddd[k][i][j] = dddd[k][i][j].replace('英文', 'English')
dddd[k][i][j] = dddd[k][i][j].replace('越南', 'Vietnam')
for k in dddd:
dat = {}
dat[k] = dddd[k]
fPath, fname = os.path.split(xlsFile)
saveXlsFile = fPath + splitFile + k + ".xls"
if not os.path.isdir(fPath + splitFile):
mkdir(fPath + splitFile)
et.makeExcel(dat, saveXlsFile)