import pymysql
import xlrd
import xlwt
import datetime
from xlrd import xldate_as_tuple
import time
import re
import json
def read_excel():
# 打開文件
workbook = xlrd.open_workbook(r'F:\\有獎舉報.xlsx')
# 獲取所有sheet
print(workbook.sheet_names()) # [u'sheet1', u'sheet2']
sheet2_name = workbook.sheet_names()[0]
# 根據sheet索引或者名稱獲取sheet內容
sheet2 = workbook.sheet_by_index(0) # sheet索引從0開始
sheet2 = workbook.sheet_by_name(sheet2_name)
# sheet的名稱,行數,列數
print (sheet2.name,sheet2.nrows,sheet2.ncols)
# 獲取整行和整列的值（數組）
rows = sheet2.row_values(3) # 獲取第四行內容
cols = sheet2.col_values(2) # 獲取第三列內容
for row in range(sheet2.nrows):
if row==0:
continue
else:
print (sheet2.cell(row,3))
print (sheet2.cell(row,4))
sname = sheet2.cell(row,4).value
#sphone = str(sheet2.cell(row,5).value)
sphone = str('13952306687')
address = sheet2.cell(row,1).value
remark = sheet2.cell(row,3).value
reasone = sheet2.cell(row,2).value
stime = xlrd.xldate_as_datetime(sheet2.cell(row,6).value,0)
nReportReward = sheet2.cell(row,9).value
#print(stime)
#testtime = []
#data = testtime.append(stime.__str__())
time = datetime.datetime.strftime(stime, "%Y-%m-%d %H:%M:%S")
print (sname)
print (sphone)
print (address)
print (remark)
print (reasone)
print (time)
print (nReportReward)
try:
#print(fun_get_vehichleInfo(sheet2.cell(row,4).value,sheet2.cell(row,5).value,sheet2.cell(row,1).value,sheet2.cell(row,3).value,sheet2.cell(row,2).value,time,sheet2.cell(row,9).value))
if(fun_get_vehichleInfo(sheet2.cell(row,5).value))==0:
fun_Insert_to_db(sname,sphone,address,remark,reasone,time,nReportReward,time,time)
#fun_Insert_to_db(sname,sphone,address,remark,reasone)
except Exception as e:
print ("Error: unable to fetch data"+e)
print ('同步成功！')
#print (cols)
# 獲取單元格內容
#print (sheet2.cell(1,0).value.encode('utf-8'))
#print (sheet2.cell_value(1,0).encode('utf-8'))
#print (sheet2.row(1)[0].value.encode('utf-8'))
# 獲取單元格內容的數據類型
#print (sheet2.cell(1,0).ctype)
def fun_Insert_to_db(sConcacts,sContactsPhone,sAddress,sRemark,sReason,tCreate,nReportReward,tAuitTime,tRewardTime):
sql = "INSERT INTO TbAccusation (sConcacts,sContactsPhone,sAddress,sRemark,sReason,tCreate,nReportReward,tAuitTime,tRewardTime) VALUES (%s,%s,%s,%s,%s,%s,%d,%s,%s)"
val = (sConcacts,sContactsPhone,sAddress,sRemark,sReason,tCreate,nReportReward,tAuitTime,tRewardTime)
#sql = "INSERT INTO TbAccusation (sConcacts,sContactsPhone,sAddress,sRemark,sReason) VALUES (%s,%s,%s,%s,%s)"
#val = (sConcacts,sContactsPhone,sAddress,sRemark,sReason)
cursor.execute(sql, val)
db.commit()
def fun_Insert_to_Sheet3(sChina,sJapan):
sql = "INSERT INTO Sheet3 (sChina,sJapan) VALUES (%s,%s)"
sChina = re.sub(r'[^\w\s]','',sChina).strip()
sJapan = re.sub(r'[^\w\s]','',sJapan).strip()
val = (sChina,sJapan)
#sql = "INSERT INTO TbAccusation (sConcacts,sContactsPhone,sAddress,sRemark,sReason) VALUES (%s,%s,%s,%s,%s)"
#val = (sConcacts,sContactsPhone,sAddress,sRemark,sReason)
cursor.execute(sql, val)
db.commit()
def fun_Insert_to_TbTrans(sChina,sJapan):
sql = "INSERT INTO TbTrans (sChina,sJapan) VALUES (%s,%s)"
sChina = re.sub(r'[^\w\s]','',sChina).strip()
sJapan = re.sub(r'[^\w\s]','',sJapan).strip()
val = (sChina,sJapan)
cursor.execute(sql, val)
db.commit()
def fun_get_vehichleInfo(sphone):
sql = "SELECT * FROM TbAccusation WHERE sContactsPhone = %s"
val = (sphone)
return cursor.execute(sql,val)
def fun_Upadate_TbTrans(sJapan,sChina):
sql = "Update TbTrans set sJapan = %s where sChina = %s "
val = (sJapan,sChina)
cursor.execute(sql, val)
db.commit()
def fun_get_Sheet2(sChina):
sql = "SELECT * FROM Sheet2 WHERE sChina = %s"
val = (sChina)
cursor.execute(sql,val)
data = cursor.fetchone()
return data
def fun_get_Sheet3(sChina):
sql = "SELECT * FROM Sheet3 WHERE sChina = %s"
val = (sChina)
cursor.execute(sql,val)
data = cursor.fetchone()
return data
#Translation Resources Find and update translation tables
def fun_Update_TransJapan():
sqlTran = "SELECT * FROM TbTrans"
cursor.execute(sqlTran)
tranResults = cursor.fetchall()
for res in tranResults:
sChina = res[1]
if(fun_get_Sheet2(sChina))!=None:
sJapan = fun_get_Sheet2(sChina)[1]
fun_Upadate_TbTrans(sJapan,sChina)
else:
if(fun_get_Sheet3(sChina))!=None:
sJapan = fun_get_Sheet3(sChina)[2]
fun_Upadate_TbTrans(sJapan,sChina)
#讀取txt文件 Insert translation table（TbTrans）
def fun_ReadTxt_InsertTrans(filePath):
with open(filePath,encoding='utf-8') as f:
data = f.read()
print(data)
jsonData = json.loads(data)
for rowk in jsonData.keys():
print(rowk)
print(jsonData[rowk])
sContent = rowk
print(rowk,jsonData[rowk])
sChina = rowk.strip()
sJapan = jsonData[rowk].strip()
fun_Insert_to_TbTrans(sChina,sJapan)
#Parse splitSheet1The table results are inserted intoSheet3表
def fun_MakeSheet1_To_Sheet3():
sql = "SELECT * FROM `Sheet1`"
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
sContent = row[1]
print(sContent)
if( ":" in sContent ):
sChina = sContent.strip().split(":")[0]
sJapan = sContent.strip().split(":")[1]
fun_Insert_to_Sheet3(sChina,sJapan)
#Read the translation table content,以json格式寫入txt文本中
def fun_CreateJson_ToTxT(filePath):
sqlTran = "SELECT * FROM TbTrans"
cursor.execute(sqlTran)
tranResults = cursor.fetchall()
dicTran ={} # 聲明字典
for res in tranResults:
sKey = res[1]
sVal = res[2]
dicTran[sKey] = sVal # 存入字典
resJson = json.dumps(dicTran,ensure_ascii=False) # 解決亂碼
with open(filePath,'w') as f: #設置文件對象
f.write(resJson)
# 打開數據庫連接
db = pymysql.connect("localhost", "root", "123", "test",3306)
# 使用cursor()方法獲取操作游標
cursor = db.cursor()
#讀取txt文件 Insert translation table（TbTrans）
fun_ReadTxt_InsertTrans("D:\\ja.txt")
#Parse splitSheet1The table results are inserted intoSheet3表
fun_MakeSheet1_To_Sheet3
#Update translation table
fun_Update_TransJapan()
#Read the translation table content,以json格式寫入txt文本中
fun_CreateJson_ToTxT('D:\\JsonData.txt')
#read_excel()
# try:
# # 執行SQL語句
# cursor.execute(sql)
# print(cursor.rownumber)
# results = cursor.fetchall()
# for row in results:
# fname = row[0]
# lname = row[1]
# temp = row[2]
# humi = row[3]
# pm25 = row[4]
# # 打印結果
# print("fname=%s,lname=%s,temp=%s,humi=%s,pm25=%s" %
# (fname, lname, temp, humi, pm25))
# except:
# print ("Error: unable to fetch data")
# 關閉數據庫連接
db.close()
