Excel表头和mysql字段名相同
- Excel字段 共3000条
- Mysql字段
- 通过pandas中的io.sql.to_sql实现导入数据库
mysql+pymysql://用户名:密码@数据库地址/数据库名?charset=utf8’
import pandas as pd
import sqlalchemy as sqla
import pymysql
import time
def pandas_import():
db=sqla.create_engine('mysql+pymysql://test:test123@127.0.0.1/db_test?charset=utf8')
df = pd.read_excel('./test.xlsx', header=[0]) # 第一行是表头
df['create_time'] = time.time() # 可忽略此行
pd.io.sql.to_sql(name='t_test_file_import', frame=df, con=db, index=False, if_exists='append') # 追加写入
if __name__ == '__main__':
curr_time_start = time.time()
print('开始时间为%s' % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())))
pandas_import()
curr_time_end = time.time()
print('结束时间为%s' % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())))
print('总计耗时为%s' % (curr_time_end - curr_time_start))
仅耗时0.285秒就将3000条数据写入数据库