import pandas as pd from sqlalchemy import create_engine # pd读取CSV,header=0表示第一行有列名,sep通过逗号区分 datas = pd.read_csv('112.csv', header=0, sep=',', encoding='gbk') # 修改时间格式,将2020/12/13 10:24的时间转化为2020-12-13 10:24 datas['created_at'] = datas['created_at'].apply(lambda x: x[0:4] + "-" + x[5:7] + "-" + x[8:]) datas['updated_at'] = datas['updated_at'].apply(lambda x: x[0:4] + "-" + x[5:7] + "-" + x[8:]) # conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', database='jt_dev') # 使用sqlalchemy连接mysql数据库 conn = create_engine('mysql+pymysql://root:123456@localhost:3306/jt_dev', encoding='utf-8') # 执行to_sql必须用到sqlalchemy的engine,否则会报错无法执行 pd.io.sql.to_sql(datas, "system_risks", conn, schema='jt_dev', if_exists='append', index=False)
to_sql执行错误的坑可查看以下