python panda读取excel_Python pandas读取excel并编写MySQL,pythonpandas,Excel,写入,mysql

#!/usr/bin/python

# -*- coding: UTF-8 -*-

import pandas as pd

import time

import mysql.connector as mysqlconnector

from apscheduler.schedulers.blocking import BlockingScheduler

from sqlalchemy import create_engine

from sqlalchemy.types import BIGINT,VARCHAR,INT,TIMESTAMP,TEXT

def deviceInfoToHistory():

excelFile = 'F:\\tools\\python\\Excel_20200617.xlsx'

df = pd.read_excel(excelFile, usecols=[1,2,3,4,5,6,7,8,9,10])

connect_info2 = 'mysql+mysqlconnector://root:123456@127.0.0.1:3306/history?charset=utf8'

engine2 = create_engine(connect_info2) #use sqlalchemy to build link-engine

print("连接数据库成功")

print("write df to table 'info_history'")

df.to_sql(name = 'info_history',

con = engine2,

if_exists = 'append',

index = False,

dtype = {'id':BIGINT(),

'bluetooth_mac':VARCHAR(32),

'lastest_report_time':TIMESTAMP,

}

)

print("写入数据库成功")

def job():

print('main-start:', time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))

deviceInfoToHistory()

print('main-end:', time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))

job()