Load CSV data into MySQL in Python

The above answer seems good. But another way of doing this is adding the auto commit option along with the db connect. This automatically commits every other operations performed in the db, avoiding the use of mentioning sql.commit() every time.

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

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"

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

Tags:

Python

Mysql