pandas读写数据库

1. 读取MySQL数据库

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root:密码@IP:3306/数据库名')

sql = '''
      select * from data_1 limit 100;
      '''
df = pd.read_sql_query(sql, engine)
df

2. 新建 DataFrame 并存入数据库表中

3. 将CSV文件写入到MySQL中

import pandas as pd
from sqlalchemy import create_engine
import os
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
engine = create_engine('mysql+pymysql://root:密码@IP:3306/库名',encoding='utf8')

df1 = pd.read_csv("./task.csv", sep=',')

df1.to_sql('test1', engine, index= False)

编码报错：InternalError: (pymysql.err.InternalError) (1366, "Incorrect string value: '\\xE8\\xBE\\xBD\\xE4\\xB8\\xAD...' for column 'code' at row 1")

解决：

在Navicat Premium 15软件中，将库名编码按照以下修改：

即可！