Load CSV data into MySQL in Python

Question

Not sure what I'm missing here but this code runs without any error message, but there's nothing in the table. I'm loading a CSV values in three columns into mysql table

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" 

Would appreciate if someone else could have a look.

Answer 1

I think you have to do mydb.commit() all the insert into.

Something like this

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

If you do not have the pandas and sqlalchemy libraries, install them using pip

pip install pandas pip install sqlalchemy 

We can use pandas and sqlalchemy to directly insert into the database

import csv import pandas as pd from sqlalchemy import create_engine, types  engine = create_engine('mysql://root:*Enter password here*@localhost/*Enter Databse name here*') # enter your password and database names here  df = pd.read_csv("Excel_file_name.csv",sep=',',quotechar='\'',encoding='utf8') # Replace Excel_file_name with your excel sheet name df.to_sql('Table_name',con=engine,index=False,if_exists='append') # Replace Table_name with your sql table name