CSV file to MySQL database, inserts aren't working

Question

I am trying to get a CSV file to a MYSQL database, but it never appears! I dont know if I messed something up, but you guys should take a look.

    # its time for fetching data :)
   import csv
   import urllib2
   path = 'C:\\Users\\User\\Desktop\\Downloads\\'
   response = urllib2.urlopen('https://files.ontario.ca/opendata/births_by_year_and_month_per_place_of_event_city_2010_0.csv')
   html = response.read()

   file=open( path +"births.CSV", "r")
   reader = csv.reader(file)



   for row in reader:
       print row

   #Server Connection to MySQL:

   import MySQLdb
   conn = MySQLdb.connect(host= "localhost",
                     user="root",
                     passwd="admin",
                     db="cool")
   x = conn.cursor()

   try:
      x.execute("""INSERT INTO import (test) VALUES (%s,%s,%s,%s)""",(row))
      conn.commit()
   except:
      conn.rollback()

   conn.close()

It ends with exit code 0, prints everything, but never inserts into the database. Here is a sample of the print

['2010', 'Sep', 'THUNDER BAY', '132']
['2010', 'Sep', 'TIMMINS', '59']
['2010', 'Sep', 'TORONTO', '3412']
['2010', 'Sep', 'WELLAND', '58']
['2010', 'Sep', 'WELLINGTON NORTH', '6']
['2010', 'Sep', 'WINDSOR', '337']
['2010', 'Sep', 'WOODSTOCK', '50']

Process finished with exit code 0

EDIT Im not sure if the %s part has something to do with it, but i need help. Thanks!

Edit 2 Great! Thanks to all of you people who have helped me. But now there's a new edit, and i cant seem to find out whats going wrong. Here is the new code!

     import csv
     import urllib2
     path = 'C:\\Users\\hongyu\\Desktop\\Downloads\\'
     response =  urllib2.urlopen('https://files.ontario.ca/opendata/births_by_year_and_month_per_ place_of_event_city_2010_0.csv')
     html = response.read()

     file=open( path +"births.CSV", "r")
     reader = csv.reader(file)
     #Server Connection to MySQL:

     import MySQLdb
     conn = MySQLdb.connect(host= "localhost",
                       user="root",
                       passwd="admin",
                       db="cool")

     for row in reader:
         x = conn.cursor()

         try:
             x.execute("""INSERT INTO testing (year, month, city, num)          VALUES (%s,%s,%s,%s)""", (row))
             conn.commit()
         except :
            conn.rollback()
             print(row)
             print("This row failed to insert")

     conn.close()

Now when I run the code, it would tell me what row failed to insert. But there were alot of rows that failed. Here is a bit of the result.

  ['1994', 'Dec', 'KAPUSKASING', '10']
  This row failed to insert
  ['1994', 'Dec', 'KENORA', '29']
  This row failed to insert
  ['1994', 'Dec', 'KINCARDINE', '11']
  This row failed to insert
  ['1994', 'Dec', 'KINGSTON', '198']
  This row failed to insert
  ['1994', 'Dec', 'KIRKLAND LAKE', '13']
  This row failed to insert
  ['1994', 'Dec', 'KITCHENER', '363']
  This row failed to insert
  ['1994', 'Dec', 'LEAMINGTON', '31']
  This row failed to insert

Any help? the database is missing almost everything, and here is a little bit of that. http://prntscr.com/h6dhod P.S: Its not just citys starting with a, there is also Toronto.

You're trying to insert 4 values but you only have 1 column name in the column list. — Barmar
– Barmar, Commented Nov 2, 2017 at 2:13

Barmar · Accepted Answer · 2017-11-02 02:21:01Z

0

Since you're trying to insert 4 values, you need to have 4 column names in the column list.

  x.execute("""INSERT INTO import (year, month, city, num) VALUES (%s,%s,%s,%s)""",(row))

Replace the column names with the actual columns in your table.

And this line needs to be inside the for row in reader: loop, which should be after you open the MySQL connection. Otherwise, you're only inserting the last row of the file.

edited Nov 2, 2017 at 2:21

answered Nov 2, 2017 at 2:15

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

Comments

Tim Biegeleisen · Accepted Answer · 2017-11-02 02:17:03Z

0

I would suggest that you consider using MySQL's LOAD DATA here:

conn = MySQLdb.connect(host= "localhost",
                 user="root",
                 passwd="admin",
                 db="cool")
cursor = conn.cursor()
query = "LOAD DATA INFILE '/path/to/your/file.csv' INTO TABLE import FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\\\'"
cursor.execute( query )
conn.commit()

This will get around needing to worry about having the correct column count, and also should perform much better than executing many insert statements.

answered Nov 2, 2017 at 2:17

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Comments

Rain Lai · Accepted Answer · 2017-11-02 02:17:59Z

-2

please understand CSV = comma separated values. you will need to explode it first.

$item = explode(",",$row);   //which will give you the array

and in your insert u might use $item[0], $item[1], and so on

answered Nov 2, 2017 at 2:17

Rain Lai

1701 silver badge10 bronze badges

1 Comment

Ken White Over a year ago

The question is tagged Python. I don't believe your answer is in that language, however.

Collectives™ on Stack Overflow

CSV file to MySQL database, inserts aren't working

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related