时间戳

首页 > 解决方案 > 将excel导入mysql使用python并转换日期戳

python - 将excel导入mysql使用python并转换日期戳

问题描述

我需要 excel 中的数据 - date(20180301) 和 time(121220) 像这种 int 类型。我想将 excel 导入 mysql 使用 python 转换 datestamp (%t-%m-%d %H:%M:%S) 为一个库。

import xlrd
import pymysql
import time
import datetime
#open the workbook and define rhe worksheet
book  = xlrd.open_workbook("d:/waiting2.xlsx")
sheet = book.sheet_by_index(0)
ts = time.time()
timestamp = datetime.datetime.formtimestamp(ts).strftime('%t-%m-%d %H:%M:%S')
#sheet = book.sheet_by_index(0)

#establish a mysql connection
database = pymysql.connect(host="localhost", user='root', password='1234',db='test')

#get the cursor, which is used to traverse the database, line by line
cursor = database.cursor()

try:
    cursor.execute("""INSERT into test(date+time) values($s)""", (timestamp))
    database.commit()
except:
    database.rollback()

database.close()

AttributeError：类型对象'datetime.datetime'没有属性'formtimestamp'

标签： pythonmysql

解决方案

就像例外是什么一样'datetime.datetime' has no attribute 'formtimestamp'，因为它应该是fromtimestamp（看到区别吗？）。更重要的是，您可以使用“熊猫”模块来处理数据工作。

import pandas as pd
df = pd.read_excel('d:/waiting2.xlsx')
from sqlalchemy import create_engine
connect = create_engine('mysql+pymysql://root:root@localhost:3306/test')
# assume that you are using mysql as your DBM and 3306 as the port.
df.to_sql('test', connect, index=False, if_exists='append')
# and before this you should convert datatime string yourself.

推荐阅读