import CSV record into MySQL Database in Python

Question

I am trying to read a CSV file and import the record into a mysql table. CSV file looks like,

db_columns.csv :-

ID,SCHOOL_CODE,DISTNAME,AC_YEAR,SCHOOL_NAME,STATE_NAME,BLOCK_NAME,CLUSTER_NAME,VILLAGE_NAME,PINCODE,RURURB,ELECTRIC_YN,SCHMGT,LOWCLASS,HIGHCLASS,COMPUTER,CAL_YN,MEDINSTR1,MEDINSTR2,MEDINSTR3,MEDINSTR4
0,35010103903,ANDAMANS,2015-16,PRIMARY SCHOOL INVENT PUBLIC (PU),Andaman And Nicobar,BRC- SOUTH ANDAMAN,"DIET, GARACHARMA",BATHUBASTHI,744105,2,1,5,1,5,4,1,19,98,98,98
1,35010101902,ANDAMANS,2015-16,GOVT PRIMARY SCHOOL MAKKA PAHAR,Andaman And Nicobar,BRC- SOUTH ANDAMAN,GSSS RANGACHANG,CALICUT,744105,1,1,1,1,5,0,2,16,19,98,98

MySQL Table looks like,

+--------------+--------------+
| column_name  | column_type  |
+--------------+--------------+
| ID           | double       |
| SCHOOL_CODE  | double       |
| DISTNAME     | varchar(100) |
| AC_YEAR      | varchar(25)  |
| SCHOOL_NAME  | varchar(300) |
| STATE_NAME   | varchar(100) |
| BLOCK_NAME   | varchar(200) |
| CLUSTER_NAME | varchar(200) |
| VILLAGE_NAME | varchar(200) |
| PINCODE      | varchar(100) |
| RURURB       | double       |
| ELECTRIC_YN  | double       |
| SCHMGT       | double       |
| LOWCLASS     | double       |
| HIGHCLASS    | double       |
| COMPUTER     | double       |
| CAL_YN       | double       |
| MEDINSTR1    | double       |
| MEDINSTR2    | double       |
| MEDINSTR3    | double       |
| MEDINSTR4    | double       |
+--------------+--------------+
21 rows in set (1.20 sec)

Python code that I have written ,

#!/usr/bin/python
import MySQLdb
import csv

db = MySQLdb.connect(host="localhost",    # your host, usually localhost
                     user="admin",         # your username
                     passwd="admin",  # your password
                     db="School_Test")        # name of the data base

cur = db.cursor()

csv_data = csv.reader(file('db_columns.csv'))

for row in csv_data:

    cur.execute ("INSERT INTO part_table_test (ID,SCHOOL_CODE,DISTNAME,AC_YEAR,SCHOOL_NAME,STATE_NAME,BLOCK_NAME,CLUSTER_NAME,VILLAGE_NAME,PINCODE,RURURB,ELECTRIC_YN,SCHMGT,LOWCLASS,HIGHCLASS,COMPUTER,CAL_YN,MEDINSTR1,MEDINSTR2,MEDINSTR3,MEDINSTR4) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",row)

db.close()

DB and Table Name below,

DB Name :- School_Test
Table Name :- part_table_test
CSV File Name :- db_columns.csv

When I am running the code, it is giving me error :-

_mysql_exceptions.DataError: (1265, "Data truncated for column 'ID' at row 1")

I am trying to solve this error. What should be the recommended way to do this ?

You've inserted your ID as a double, when it should be an integer. — natn2323
– natn2323, Commented May 31, 2018 at 14:27
Little hint MySQL can directly read CVS files with LOAD DATA INFILE dev.mysql.com/doc/refman/8.0/en/load-data.html — Raymond Nijland
– Raymond Nijland, Commented May 31, 2018 at 14:30

matrop · Accepted Answer · 2018-05-31 14:35:59Z

2

When you create your reader, it will iterate over all lines of your csv files, including the first line, which contains the headers. Try to discard them by calling next before your for loop:

csv_data = csv.reader(file('db_columns.csv'))
next(csv_data) # discard header
for row in csv_data:
    cur.execute ("INSERT INTO part_table_test (ID,SCHOOL_CODE,DISTNAME,AC_YEAR,SCHOOL_NAME,STATE_NAME,BLOCK_NAME,CLUSTER_NAME,VILLAGE_NAME,PINCODE,RURURB,ELECTRIC_YN,SCHMGT,LOWCLASS,HIGHCLASS,COMPUTER,CAL_YN,MEDINSTR1,MEDINSTR2,MEDINSTR3,MEDINSTR4) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",row)

answered May 31, 2018 at 14:35

matrop

636 bronze badges

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

1 Comment

spencer7593 Over a year ago

Yes, it's that first line, the header line, that is causing MySQL to throw an error/warning, when it converts string "ID" to numeric (double). Fetching and discarding that first line from the file will avoid that. +10.

Dataichou · Accepted Answer · 2018-05-31 14:53:30Z

That usually means that the ID field in the CSV has more characters than the maximum allowed in the database for that column.

1) Try changing

    csv_data = csv.reader(file('db_columns.csv'), delimiter=',')

2) Also change the type of the ID to INT or BIGINT

3) Do not forget to skip the initial line (header) since it will insert the column names into the databases and fail

    count = 0
    for row in csv_data:
        if count < 1:
            continue
        else:
            cur.execute ("INSERT INTO part_table_test (ID,SCHOOL_CODE,DISTNAME,AC_YEAR,SCHOOL_NAME,STATE_NAME,BLOCK_NAME,CLUSTER_NAME,VILLAGE_NAME,PINCODE,RURURB,ELECTRIC_YN,SCHMGT,LOWCLASS,HIGHCLASS,COMPUTER,CAL_YN,MEDINSTR1,MEDINSTR2,MEDINSTR3,MEDINSTR4) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",row)
        count+=1

spencer7593 · Accepted Answer · 2018-05-31 14:36:13Z

0

Skip the first line in the CSV file, that header line, then process the remaining lines.

  csv_data = csv.reader(file('db_columns.csv'))

  # retrieve the header line and discard it
  next(csv_data, None)

  for row in csv_data:

I think the error is from the first line, the string value "ID" is being assigned to a double. That will evaluate to 0 in MySQL, but that conversion will cause MySQL to throw either an error or a warning. The behavior depends on the setting of sql_mode for the session.

answered May 31, 2018 at 14:36

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Collectives™ on Stack Overflow

import CSV record into MySQL Database in Python

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related