Read a csv file in python and insert the result into a MYSQL database

Question

I have a csv file with the number of people infected by covid19 per country and per day. I have created a MYSQL database, a table with all the columns that the CSV file has and now I need to insert the rows into the database I need to have a python code to achieve the task

import pandas as pd
import mysql.connector


mydb = mysql.connector.connect(
  host=
  user="root",
  passwd=
  database="covid19"
)

#Inserting data into the database

mycursor = mydb.cursor()

dataframe = pd.read_csv("total_cases.csv")
print(dataframe)

for row in dataframe:
     print(index)
     mycursor.execute("INSERT INTO covid_per_day_per_country (date, World, 
     Afghanistan, Albania, Algeria, Andorra"))
     mydb.commit()
     cursor.close()

what did you try so far? where are you going to run this code? — Carlos Robles, May 22 '20 at 16:52
I have added the code to the question and I am glad if you can have a look — python_coder, May 22 '20 at 16:59
For CSV migration need, `pandas` is not needed. Use the heavy library for its sole purpose: data analytics (not migration) — Parfait, May 23 '20 at 18:59

score 0 · Answer 1 · edited May 23 '20 at 17:39

0

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

Something like this

import csv
import MySQLdb
mydb = mysql.connector.connect(
  host=
  user="root",
  passwd=
  database="covid19"
)
mycursor = mydb.cursor()

dataframe = csv.reader(file('total_cases.csv'))
print(dataframe)

for row in dataframe:
     mycursor.execute('INSERT INTO covid_per_day_per_country (date, World, Afghanistan, Albania, Algeria, Andorra") VALUES("%s", "%s", "%s", "%s", "%s", "%s")', row)
mydb.commit()
cursor.close()

Please refer this: Load CSV data into MySQL in Python

edited May 23 '20 at 17:39

rdas

20,604
6
33
46

answered May 22 '20 at 16:58

Pratik Joshi

389
4
13

1

I have added my code to the question. Can you suggest what is required to be modified please – python_coder May 22 '20 at 17:03
Could you give me a sample row of your csv file and also the testcsv table? – Pratik Joshi May 22 '20 at 17:05
I've updated my code accordingly. Please take a look. It should work if your database table columns and csv columns are the same. – Pratik Joshi May 22 '20 at 17:13
Did that help you? – Pratik Joshi May 23 '20 at 13:30

Read a csv file in python and insert the result into a MYSQL database

1 Answers1