在Python中将CSV数据加载到MySQL中

Question

不知道我在这里丢失了什么，但是此代码在没有任何错误消息的情况下运行，但表中没有任何内容。我将三列中的CSV值加载到mysql表中

import csv 
import MySQLdb 

mydb = MySQLdb.connect(host='localhost', 
    user='root', 
    passwd='', 
    db='mydb') 
cursor = mydb.cursor() 

csv_data = csv.reader(file('students.csv')) 
for row in csv_data: 

    cursor.execute('INSERT INTO testcsv(names, \ 
      classes, mark)' \ 
      'VALUES("%s", "%s", "%s")', 
      row) 
#close the connection to the database. 
cursor.close() 
print "Done" 

如果其他人可以看看，我们将不胜感激。

谢谢。

Answer 1

我觉得你必须做mydb.commit()所有的插入。

像这样的事情

import csv 
import MySQLdb 

mydb = MySQLdb.connect(host='localhost', 
    user='root', 
    passwd='', 
    db='mydb') 
cursor = mydb.cursor() 

csv_data = csv.reader(file('students.csv')) 
for row in csv_data: 

    cursor.execute('INSERT INTO testcsv(names, \ 
      classes, mark)' \ 
      'VALUES("%s", "%s", "%s")', 
      row) 
#close the connection to the database. 
mydb.commit() 
cursor.close() 
print "Done" 

Answer 2

如果是大熊猫的数据帧，你可以这样做：

发送数据

csv_data.to_sql=(con=mydb, name='<the name of your table>', 
    if_exists='replace', flavor='mysql') 

，以避免使用for的。

Answer 3

from __future__ import print_function 
import csv 
import MySQLdb 

print("Enter File To Be Export") 
conn = MySQLdb.connect(host="localhost", port=3306, user="root", passwd="", db="database") 
cursor = conn.cursor() 
#sql = 'CREATE DATABASE test1' 
sql ='''DROP TABLE IF EXISTS `test1`; CREATE TABLE test1 (policyID int, statecode varchar(255), county varchar(255))''' 
cursor.execute(sql) 

with open('C:/Users/Desktop/Code/python/sample.csv') as csvfile: 
    reader = csv.DictReader(csvfile, delimiter = ',') 
    for row in reader: 
     print(row['policyID'], row['statecode'], row['county']) 
     # insert 
     conn = MySQLdb.connect(host="localhost", port=3306, user="root", passwd="", db="database") 
     sql_statement = "INSERT INTO test1(policyID ,statecode,county) VALUES (%s,%s,%s)" 
     cur = conn.cursor() 
     cur.executemany(sql_statement,[(row['policyID'], row['statecode'], row['county'])]) 
     conn.escape_string(sql_statement) 
     conn.commit()