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.

score 85 · Accepted Answer · answered Apr 14 '12 at 15:11

85

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"

answered Apr 14 '12 at 15:11

Jakob Bowyer

33,878
8
76
91

Thanks that works :) But do you know why the values are all in single quotes in the database? – Helen Neely Apr 14 '12 at 15:48
7

Try `"insert into testcsv(names, classes, mark) values(%s, %s, %s)", row` – Jakob Bowyer Apr 14 '12 at 15:51
Danke, if you have any other problems just post a new question :D – Jakob Bowyer Apr 14 '12 at 15:57
1

@JakobBowyer how ignore first line from csv. – Ashutosh SIngh Nov 23 '16 at 13:14
For this to work you will need to figure out and create the table with the right schema before loading any CSV. Also this approach will give you many problems if your data includes numeric columns and/or you have missing (NULL) values in your data. Those problem are automagically handled with the pandas/sqlalchemy approach bellow. – Luis Vazquez Jan 11 '22 at 21:27

score 20 · Answer 2 · edited Jan 31 '22 at 00:27

20

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

edited Jan 31 '22 at 00:27

Peter Szabo

1,056
2
14
30

answered Sep 23 '19 at 04:10

Harsha pps

2,012
2
25
35

For me, this produces the error message "No module named MySQLdb". Following https://stackoverflow.com/a/58246337/7746472 I used pymysql and changed the create engine bit to "mysql+pymysql://..." – Sebastian Nov 02 '20 at 21:59
Alternatively, you can solve it by installing `mysqlclient` as in this answer https://stackoverflow.com/a/5873259/11724813 – Kokokoko Jan 12 '21 at 20:38

score 10 · Answer 3 · edited Jul 04 '18 at 10:07

10

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)

edited Jul 04 '18 at 10:07

anothernode

5,100
13
43
62

answered Jul 04 '18 at 09:49

Mahesh Kumaran

887
2
12
30

score 5 · Answer 4 · edited Nov 10 '17 at 10:47

  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()

score 5 · Answer 5 · answered May 14 '20 at 07:24

5

using pymsql if it helps

import pymysql
import csv
db = pymysql.connect("localhost","root","12345678","data" )

cursor = db.cursor()
csv_data = csv.reader(open('test.csv'))
next(csv_data)
for row in csv_data:
    cursor.execute('INSERT INTO PM(col1,col2) VALUES(%s, %s)',row)

db.commit()
cursor.close()

answered May 14 '20 at 07:24

Thanks for the tip though, but this was asked over 8 years ago :) – Helen Neely May 16 '20 at 20:26
@HelenNeely agreed then its time to select my answer as correct answer, as open(file) wont work :P – May 18 '20 at 15:22
pretty primitive considering easier options available using pandas. – Murtaza Haji Jul 07 '20 at 06:34
@MurtazaHaji No, pretty straightforward, if we don't want to use pandas or/and sqalchemy or sqlite. – Geeocode Aug 30 '20 at 19:41
2

@HelenNeely regardless of when a question was asked, SO serves as a continual reference for people who have the same question as you did 8 years ago. Therefore it makes sense to continually provide new answers as the existing answers become irrelevant. – brycejl Sep 04 '20 at 15:58

score 1 · Answer 6 · edited Oct 08 '16 at 17:27

1

If it is a pandas data frame you could do:

Sending the data

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

to avoid the use of the for.

edited Oct 08 '16 at 17:27

Andreas Wolf

995
6
19

answered Oct 08 '16 at 15:26

Rafael Valero

21
1

2

It seems you quoted from a manual? Add a reference in such cases please, so people can read on in case they're interested (and to give proper reference to the original authors). – Andreas Wolf Oct 08 '16 at 16:43
This will not work if index name of your dataframe is not same as column name in database table. – Piyush S. Wanare May 25 '18 at 12:34

score 0 · Answer 7 · answered Dec 15 '20 at 07:58

0

Fastest way is to use MySQL bulk loader by "load data infile" statement. It is the fastest way by far than any way you can come up with in Python. If you have to use Python, you can call statement "load data infile" from Python itself.

answered Dec 15 '20 at 07:58

Jonas

39
1

Load CSV data into MySQL in Python

7 Answers7

Sending the data

Linked

Related