Save entire CSV file to mysql

Question

Goal: Create a program in python that when taking the file data CSV, save to Mysql and generate a chart.

What has been done so far: A python program, where it reads the file and prints the data in the terminal as a test.

Does this work? Yes, you are printing the data correctly on the terminal.

The problem: The file has 27 lines, each one should be inserted in the database. When I try to insert, it only inserts the line 27.

Follows the code:

# -*- coding: utf-8 -*-
import csv
import MySQLdb

mydb = MySQLdb.connect(host='localhost',
    user='root',
    passwd='',
    db='1mydb')
cursor = mydb.cursor()

with open('brazil-microcephaly-2016-01-23-table-1.csv', 'r') as csvfile:
    zikareader = csv.reader(csvfile, delimiter =' ', quotechar ='|')
    zikareader = csv.reader(file('brazil-microcephaly-2016-01-23-table-1.csv'))
    for row in zikareader:
        print('-'.join(row))
    cursor.execute('INSERT INTO testcsv(no, \
        state, cases_under_investigation, cases_confirmed, cases_discarded, cases_reported_total)' \
        'VALUES("%s", "%s", "%s","%s", "%s", "%s")', row)

    mydb.commit()
    cursor.close()
    print "Done"


try:
    # for Python2
    from Tkinter import *
except ImportError:
    # for Python3
    from tkinter import *

def main():
    row_count = sum(1 for row in zikareader)  # fileObject is your csv.reader

    master = Tk()
    master.title("Laboratório de Redes, 6º ADS, 2018/1")

    label_titulo = Label(master, text="Análise dos dados", anchor = N, height = 15, width = 40, font = ("Helvetica", 20))
    label_titulo.pack()

    mainloop()
main()

Answer 1

Your call to cursor.execute is outside the block of for - simply idente the line correctly and the problem should be solved (but I haven’t checked if the rest of the program is correct)

for row in zikareader:
    print('-'.join(row))
    cursor.execute('INSERT INTO testcsv(no, \
    state, cases_under_investigation, cases_confirmed, cases_discarded, cases_reported_total)' \
    'VALUES("%s", "%s", "%s","%s", "%s", "%s")', row)