使用python脚本上传大数据集CSV文件到MySQL

使用python脚本上传大数据集CSV文件到MySQL

# -*- coding: utf-8 -*-
"""
Created on Thu Jun  7 14:54:14 2018

@author: YFX
"""
#---------------------------------------------------------用于上传所有题目的选项到MySQL
import datetime
start = datetime.datetime.now()#记录上传所需要时间
import csv
import pymysql
import pandas as pd
s=pd.DataFrame(pd.read_csv("D:\\yourFIle.csv", sep=';') )
s=s.iloc[:,[0,1,2,3]]#摘出需要的数据集列
#---------------------------------------------------------把所有panda dataframe的数据点转变为string
s = s.astype(str)
#---------------------------------------------------------save handled data set as csv
file_name1="d:\\newData_0609_1748.csv"#整理后数据集存盘
s.to_csv(file_name1, encoding= 'utf8')  

newData=s

#---------------------------------------------------------connect MySQL
mydb = pymysql.connect(host='localhost',
    user='root',
    passwd='数据库密码',
    db='test',charset="utf8")
#---------------------------------------------------------把第一列列名设置为idx
columnNames0=["idx"]+list(newData)
cursor = mydb.cursor()

#---------------------------------------------------------set table name in MySQL
table_name = 'test0609_2204_questOption'#设置数据库中新建表的名称
query = "CREATE TABLE IF NOT EXISTS "+table_name+" ("+" VARCHAR(250),".join(columnNames0) + " VARCHAR(250))"
print("Query is:" ,query)
#---------------------------------------------------------在MYSQL中建立表
cursor.execute(query)

f = open("d:\\newData_0609_1748.csv",'r',encoding="utf8")#打开之前存盘的数据集
h=0
columnNames1=columnNames0
for row in csv_data:
    h=h+1
    print(h)
    cursor.execute('INSERT INTO '+table_name+" ("+", ".join(columnNames1) + ")"+ ' VALUES('+'%s,'*(len(columnNames1)-1) +'%s)', row)

#常见问题
#not enough arguments for format string 则问题出在 'INSERT INTO '+table_name+" ("+", ".join(columnNames1) + ")"

#close the connection to the database.关闭数据库
mydb.commit()
cursor.close()
print("Done")
end = datetime.datetime.now()
print (end-start)
#