python 读取csv文件到mysql操作

修改文件编码为utf-8

1. wps 通过excel打开csv文件会造成文件编码格式的损坏，此时我们可以通过vim对数据格式进行修改
2. 需要注意的是，excel文件中数据是如何显示的vim文件中就会如何显示，因此诸如订单号之类的数字需要提前在excel之中转换为text或者是非数字的格式，否则会造成转换损失

> vim xxx.csv
-----------------------
:set fileencoding=utf-8
:wq!

我们也可以通过chardet包确定csv文件的编码格式

import chardetwith open(csv_path, 'rb') as f:result = chardet.detect(f.readline())encoding = result['encoding']
print(encoding)

读取文件到python然后转写入sql

import pymysql
from sqlalchemy import create_engine, Text, Integer, DateTime, Float
import pandas as pd
import os
from datetime import datetimecsv_path = "your_path/your_file.csv"
work_path = os.path.abspath(' ')# read data from csv to dataframe
df = pd.read_csv(csv_path,index_col=0,encoding="utf-8")# transfer datetime format         
dformat = format('%Y-%m-%d %H:%M:%S %Z') 
df["event_time"] = df["event_time"] \.apply(lambda date: datetime.strptime(date,dformat))# write dataframe into mysql table
engine = create_engine("mysql+pymysql://user:psassword@localhost:3306/da?charset=utf8mb4")
datatype={'event_time':DateTime,"order_id":Text,"product_id":Text,"category_id":Text,"category_code":Text,"brand":Text,"price":Float,"user_id":Text,"age":Integer,"sex":Text,"local":Text}
df.to_sql(name="sales_order_detail",con=engine,if_exists='append',index=True,index_label='id',dtype=datatype)# accquire connection object
conn = pymysql.connect(host="localhost",port=3306,user="root",password="19950623",db="da",charset="utf8")

查看包安装位置

import sys
import siteprint(sys.executable+"\r")
print(site.getsitepackages())