Python 读取Excel 数据 同步到数据库

难点

时间格式转化，时间格式化，double类型

excel文件内容

excel表格.png

源码

import pymysql
import xlrd
import xlwt
import datetime
from xlrd import xldate_as_tuple
import time



def read_excel():
    # 打开文件
    workbook = xlrd.open_workbook('D:\\有奖举报.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)
         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
        
         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:
            if(fun_get_vehichleInfo(sheet2.cell(row,5).value))==0:
                fun_Insert_to_db(sname,sphone,address,remark,reasone,time,nReportReward,time,time)
         except Exception as e:
            print ("Error: unable to fetch data"+e)

    print ('同步成功！')


def fun_Insert_to_db(sConcacts,sContactsPhone,sAddress,sRemark,sReason,tCreate,nReportReward,tAuitTime,tRewardTime):
    sql = "INSERT INTO TbAccusation (sContacts,sContactsPhone,sAddress,sRemark,sReason,tCreate,tAuitTime,tRewardTime,nReportReward) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    val = (sConcacts,sContactsPhone,sAddress,sRemark,sReason,tCreate,tAuitTime,tRewardTime,nReportReward)
    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)

# 打开数据库连接
#db = pymysql.connect("localhost", "root", "root", "JSHB",3306) #1.0旧版写法
db = pymysql.connect(host="localhost",user= "root", password="root", database="JSHB",port= 3306)


# 使用cursor()方法获取操作游标
cursor = db.cursor()

# SQL 查询语句
#sql = "SELECT * FROM  `TbOutdoorEnv`"
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()

结果

结果.png