i using csv parsing import csv files in sql. have few tables in mysql. wnat import csv file in table once. have use below script. in country able import state table did not show me following error
csv_spliter.py:74: warning: incorrect integer value: ''country_id'' column 'id' @ row 1 row) csv_spliter.py:74: warning: incorrect integer value: ''0'' column 'id' @ row 1 row) csv_spliter.py:74: warning: incorrect integer value: ''1'' column 'id' @ row 1 row) csv_spliter.py:74: warning: incorrect integer value: ''2'' column 'id' @ row 1 row) csv_spliter.py:74: warning: incorrect integer value: ''3'' column 'id' @ row 1 row) done traceback (most recent call last): file "csv_spliter.py", line 89, in <module> row) file "/usr/lib/python2.7/dist-packages/mysqldb/cursors.py", line 174, in execute self.errorhandler(self, exc, value) file "/usr/lib/python2.7/dist-packages/mysqldb/connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue _mysql_exceptions.integrityerror: (1452, 'cannot add or update child row: foreign key constraint fails (`lcm`.`state`, constraint `state_ibfk_1` foreign key (`country`) references `country` (`id`) on delete cascade)') you@you-desktop:~/desktop$ <i> # initialize empty ints , dicts name,cities,countries,states=[],[],[],[] open('ind.csv','rb') csvfile: reader = csv.reader(csvfile, delimiter=',') reader.next() #skip header row in reader: name.append(row[0]) cities.append(row[2]) states.append(row[3]) countries.append(row[4]) cl = list(set(countries)) sl = list(set(states)) citl = list(set(cities)) inf1 = list(set(name)) open('countries.csv','w') cfile: writer = csv.writer(cfile, delimiter=',') writer.writerow(['country_id','name']) i,x in enumerate(cl): writer.writerow([i,x]) open('state.csv','w') cfile: writer = csv.writer(cfile, delimiter=',') writer.writerow(['state_id','country_id','state']) i,x in enumerate(sl): writer.writerow([i,x,cl.index(countries[states.index(x)])]) open('cities.csv','w') cfile: writer = csv.writer(cfile,delimiter=',') writer.writerow(['city_id','city','st_id','country_id']) i,x in enumerate(citl): writer.writerow([i,x,sl.index(states[cities.index(x)]), cl.index(countries[cities.index(x)]) ]) open('inf123.csv','w') cfile: writer = csv.writer(cfile,delimiter=',') writer.writerow(['name_id', 'name','city_id','st_id','country_id']) i,x in enumerate(inf1): writer.writerow([i,x, citl.index(cities[name.index(x)]), sl.index(states[name.index(x)]), cl.index(countries[name.index(x)]) ]) import mysqldb import csv mydb = mysqldb.connect(host="localhost", # host user="root", # username passwd="root", # password db="abcm") # name of data base cursor = mydb.cursor() csv_data = csv.reader(file('countries.csv')) row in csv_data: cursor.execute('insert country(id, \ name )' \ 'values("%s", "%s")', row) #close connection database. mydb.commit() cursor.close() print "done" cursor = mydb.cursor() csv_data = csv.reader(file('state.csv')) row in csv_data: cursor.execute('insert state(id, \ country, name )' \ 'values("%s", "%s", "%s")', row) #close connection database. mydb.commit() cursor.close() print "done" </i> can 1 give me advice how can this?
are converting strings integers before dumping them in database?
i think that's incorrect integer value error messages coming from.
Comments
Post a Comment