Save all CSV files in mysql

2
  

Objective: To create a program in python that when picking the data of the file   CSV, save to MySql and generate a graph.

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

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

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

Follow 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()
    
asked by anonymous 03.05.2018 / 16:51

1 answer

3

Your call to cursor.execute is outside the block of for - simply match the line correctly and the problem must be resolved (but I have not 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)
    
03.05.2018 / 17:11