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  quot  s quot    quot  s quot    quot  s quot                row   close the connection to the database  cursor close   print  quot Done quot   Would appreciate if someone else could have a look

User · Answer

Fastest way is to use MySQL bulk loader by  quot load data infile quot  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  quot load data infile quot  from Python itself

User · Answer

If it is a pandas data frame you could do   Sending the data  csv data to sql  con mydb  name   lt the name of your table gt      if exists  replace   flavor  mysql     to avoid the use of the for

User · Answer

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

User · Answer

If you do not have the pandas and sqlalchemy libraries  import 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

User · Answer

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

User · Answer

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

User · Answer

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

[python] Load CSV data into MySQL in Python

Examples related to python

Examples related to mysql