getting error while dumping the data from excel sheet to mysql database

Question

I am importing the specific data from excel sheet and dumping that data to mysql database. But while doing that i am getting the error

       $ python dtz_db.py
dtz_db.py:43: Warning: Field 'datetime' doesn't have a default value
  cursor.execute(query2, values2)
dtz_db.py:43: Warning: Data truncated for column 'lease_start_date' at row 1
  cursor.execute(query2, values2)
dtz_db.py:43: Warning: Data truncated for column 'lease_end_date' at row 1
  cursor.execute(query2, values2)
dtz_db.py:43: Warning: Incorrect integer value: '' for column 'lease' at row 1
  cursor.execute(query2, values2)
dtz_db.py:43: Warning: Incorrect integer value: '' for column 'leased' at row 1
  cursor.execute(query2, values2)
Traceback (most recent call last):
  File "dtz_db.py", line 44, in <module>
    cursor.execute(query1, values1)
  File "c:\Python27\lib\site-packages\MySQLdb\cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "c:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defau
lterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.IntegrityError: (1452, 'Cannot add or update a child row: a fo
reign key constraint fails (`dtz_new`.`property_property`, CONSTRAINT `lease_id_
refs_id_816819bc` FOREIGN KEY (`lease_id`) REFERENCES `property_propertylease` (
`id`))')

my python file is this cod is for selecting the specific data from the excel file and dump that data to mysql database

import xlrd
import MySQLdb
book = xlrd.open_workbook("dtz11.xls")
sheet = book.sheet_by_name("Sheet1")
database = MySQLdb.connect (host="localhost", user="root", passwd="", db="dtz_new")
cursor = database.cursor()
query1 = """INSERT INTO property_property( name,  inpection_date) VALUES(%s, %s )"""
query2 = """INSERT INTO property_propertylease( lease_start_date,  lease_end_date, lease, leased) VALUES(%s, %s, %s, %s)"""
for r in range(1, sheet.nrows):
    gaurav2         = sheet.cell(r,1).value
    gaurav3         = sheet.cell(r,2).value
    gaurav8         = sheet.cell(r,18).value
    gaurav9         = sheet.cell(r,19).value
    gaurav10        = sheet.cell(r,20).value
    gaurav11        = sheet.cell(r,21).value
    values1 = (gaurav2, gaurav3)
    values2 = (gaurav8, gaurav9, gaurav10, gaurav11)
    cursor.execute(query2, values2)
    cursor.execute(query1, values1)
cursor.close()
database.commit()
database.close()
print "dumped successfully"
columns = str(sheet.ncols)
rows = str(sheet.nrows)
print "I just imported "+ columns+ " columns and "+ rows+" rows to MySQL!"

and my db table schema is enter image description here

Please help me to resolve this problem,,,, Thanks a lot

Russell Uhl · Accepted Answer · 2014-06-05 12:42:44Z

1

Your insert statement asserts that there will be 8 variables provided to the DB, but you only give it 7. Start there.

(I know neither python nor excel interaction so I haven't posted any code. I suspect the problem is entirely in that INSERT statement though.)

Edit: So the foreign key constraint error means that according to your schema, property_property's lease_id points to data in another table (property_propertylease). Since you haven't given this second table any data, the insert fails.

Put another way, your insert statement populates a parent table. The parent table is attempting to reference data in a child table that does not exist.

edited Jun 5, 2014 at 12:42

answered Jun 5, 2014 at 12:36

Russell Uhl

4,8112 gold badges22 silver badges30 bronze badges

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

4 Comments

Wagh Over a year ago

How can i do this any hint,,,,,,,,,,?

Russell Uhl Over a year ago

put data in the child table first, or remove the foreign key constraint from the table. The FK constraint exists for EXACTLY this reason...to prevent one table from pointing to another when the second table is MISSING the data

Wagh Over a year ago

But in application FK is necessary to relate the data,,,,, Second option is best, But at a time how can i put data in both parent and child table because both are related to each other.

Russell Uhl Over a year ago

That gets a bit tricky. you'll have to do something like insert everything EXCEPT lease_id into table A, then add your data to table B, then update table A again with the lease_id.

Collectives™ on Stack Overflow

getting error while dumping the data from excel sheet to mysql database

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related