Python/MySQL - import csv data into mysql table

Question

I am developing a python application to insert data in my db, through a .csv file. However when importing the file the following error appears:

Warning: (1262, 'Row 1 was truncated; it contained more data than there were input columns')
  result = self._query(query)

Basically for all lines in csv file. The data inside the file is as follows:

I can import that same way directly into the Workbench, but i would like to do it via the application.

Follow the code below:

from tkinter import *
import pymysql
import os
import os.path

tess = Tk()

def import_file():

    conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='omnia')
    print('connect successfull!')



    if os.path.exists('C:/temp/teste.csv'):
        statm = "LOAD DATA INFILE 'C:/temp/teste.csv' INTO TABLE testtable FIELDS TERMINATED BY ','"
        cursor = conn.cursor()
        cursor.execute(statm)

bt = Button(tess, text='browse file')
bt.place(x=10, y=10)

bt = Button(tess, text='import file', command=import_file)
bt.place(x=10, y=45)

tess.mainloop()

And also the table:

create table testTable(
    n_id int not null auto_increment,
    c_nome varchar(255),
    c_depto varchar(255),
    n_salario float,
     primary key (n_id));

Answer 1

Your table definition starts with an auto increment column, but your input data shows zero for all those columns. Therefore, don't import them.

Your input data lines end with your comma delimiter. In the CSV world, that means each line ends with an empty column. Skip it.

This SQL, not debugged, should work.

 LOAD DATA INFILE 'C:/temp/teste.csv' 
 INTO TABLE testtable
      (@dummy_id,c_nome,c_depto,n_salario,@dummy) 
FIELDS TERMINATED BY ',';

The @dummy columns in the column list tell the import to place those values into the dummy, rather than the table: in other words to skip them.

Answer 2

Your CSV contains 5 fields.

The instruction FIELDS TERMINATED BY ',' means that next field value starts after a comma anycase, and if the next char is linebreak this means that such field have a value of empty string ''.

Your table contains 4 fields only.

Either remove excess finalizing comma from the lines end, or use dummy user-defined variable:

LOAD DATA INFILE 'C:/temp/teste.csv' 
INTO TABLE testtable (n_id,c_nome,c_depto,n_salario,@dummy) 
FIELDS TERMINATED BY ',';