pandas将dataFrame写入mysql数据库

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执行错误的坑可查看以下

DatabaseError: Execution failed on sql ‘SELECT name FROM sqlite_master WHERE type=‘table‘ AND name=?_weixin_43425561的博客-CSDN博客

你可能感兴趣的