mysql导入数据

SHOW VARIABLES LIKE "secure_file_priv";

load data infile "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/tianchi_mobile_recommend_train_user.csv" 
into table a fields terminated by',' ignore 1 lines;

python导入数据

【csv_sql】
df1=pd.read_csv('E:dataAnalyst_utf.csv')

df1.to_sql(name='dataanalyst',con='mysql+pymysql://root:mima@localhost:3306/data?charset=utf8',if_exists='append',index=False)
##if_exists='append'#fail 失败，无法写入；append当表存在即插入、表不在即新建；
##index=False#索引不会写入

sql到pandas

【一】
import pymysql

conn=pymysql.Connect(
    host='localhost',
    user='root',
    password='mima',
    db='data',
    port=3306,
    charset='utf8'
)
cur=conn.cursor()
cur.execute('select * from company')
data=cur.fetchall()
cur.close()
conn.close()

【二】#import sqlalchemy
import pymysql
import pandas as pd
from sqlalchemy import create_engine

sql='select * from company'
engine=create_engine('mysql+pymysql://root:mima@localhost:3306/data?charset=utf8')
df=pd.read_sql(sql,engine)