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
– Carlos Robles, Commented May 22, 2020 at 16:52
I have added the code to the question and I am glad if you can have a look — python_coder
– python_coder, Commented May 22, 2020 at 16:59
For CSV migration need, pandas is not needed. Use the heavy library for its sole purpose: data analytics (not migration) — Parfait
– Parfait, Commented May 23, 2020 at 18:59

rdas · Accepted Answer · 2020-05-23 17:39:16Z

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, 2020 at 17:39

rdas

21.4k6 gold badges39 silver badges48 bronze badges

answered May 22, 2020 at 16:58

Pratik Joshi

4395 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

python_coder Over a year ago

I have added my code to the question. Can you suggest what is required to be modified please

Pratik Joshi Over a year ago

Could you give me a sample row of your csv file and also the testcsv table?

Pratik Joshi Over a year ago

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 Over a year ago

Did that help you?

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related