Python 读取txt插入数据库写入json到txt

最新推荐文章于 2024-06-14 10:32:43 发布
叶落无痕123
最新推荐文章于 2024-06-14 10:32:43 发布
阅读量433
点赞数
分类专栏： python 文章标签： python 数据库 json
原文链接：https://www.jianshu.com/p/7949eb7697e8
版权
python 专栏收录该内容
23 篇文章 3 订阅
订阅专栏
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

#翻译资源寻找并更新翻译表
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文件 插入翻译表（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)
#解析拆分Sheet1表结果插入到Sheet3表
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)
#读取翻译表内容，以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文件 插入翻译表（TbTrans）
fun_ReadTxt_InsertTrans("D:\\ja.txt")


#解析拆分Sheet1表结果插入到Sheet3表
fun_MakeSheet1_To_Sheet3

#更新翻译表
fun_Update_TransJapan()

#读取翻译表内容，以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()