通過該方法可以將dataframe直接寫入數(shù)據(jù)庫稠茂，省了很多麻煩
conn = create_engine('mysql+pymysql://root:123qwe@localhost:3306/sem?charset=utf8')
dataFrame = pd.read_excel(path)
dataFrame['date'] = [time.strftime("%Y-%m-%d")]*len(dataFrame)
dataFrame.to_sql('sem_real', con=conn, if_exists='append', index=False, index_label=False)
標(biāo)準(zhǔn)的pymysql流程
db = pymysql.connect("localhost", "root", "123qwe", "sem")
cursor = db.cursor()
data = {
'id': '20120001',
'name': 'Bob',
'age': 20
}
table = 'semrealtime'
keys = ','.join(dataJson.keys())
values = ','.join(['%s'] * len(dataJson))
sql = "INSERT INTO {table}({keys}) VALUE ({values})".format(table=table, keys=keys, values=values)
try:
cursor.execute(sql, tuple(data.values()))
db.commit()
except:
# 發(fā)生錯誤時(shí)回滾
db.rollback()
# 關(guān)閉數(shù)據(jù)庫連接
db.close()
