Re: Foreignkey troubles : some key look ups give me a ValueError: invalid literal for int() with base 10 error

I posted this question as a ticket :

kmtracey suggested the fix  ( http://code.djangoproject.com/ticket/15513) which is to use the ForeignKey.to_field to specify the column in the Parent table that the relation is mapped to.

I am copy-pasting his comments here:

The cause of the exception you are seeing is that per your Django models, the target column of the ForeignKey field in your Child model is the primary key field of Parent, not the ssn field. Primary key field of parent is an integer, so attempting to lookup a non-integer value raises an exception.

In order to tell Django that the target column for the ForeignKey field in child is the parent's ssn field, you need to specify to_field='ssn' on that ForeignKey definition. See: http://docs.djangoproject.com/en/1.2/ref/models/fields/#django.db.models.ForeignKey.to_field

Note though that your existing table definitions don't meet the requirements for a ForeignKey field here, because the Parent ssn field is not unique. Django's ForeignKey is a many-to-one relation, so the target column must be unique. See #11702. If you are not actually creating the tables via syncdb you may not initially see any error related to this failure to meet the requirements for a ForeignKey -- but if in fact you have duplicated values in that target field, you may see errors later on.

Your Django models also show evidence of bug #5725. The max_length values for all your CharFields are 3x higher than they should be. Easiest fix is to manually correct them to be the right value.

On Fri, Feb 25, 2011 at 3:29 PM, hari jayaram <harijay@gmail.com> wrote:

Hi Everyone ,

I am using the svn version of django to write a django app to hook into a legacy database.

I am having some problem with querying by a ForeignKey which is not a Primary key. The sql datatype for the foreign key  is  VARCHAR(256) , but lookups only succeed with integer fields . The original database has integer and non-integer values for this field. Only Non-int fields throw a ValueError when I try to use the filter in django.

here is my test case:

I have a Parent table and a child table.  Both parent and child have their own primary keys. The child table has the parent attribute called ssn ( in SQL a VARCHAR(256)) as a foreign key constraint. The SQL for my test case is given below.

After creating this test database  and then creating my models with django manage.py inspectdb and running syncdb , I get the following behavior (see below). The ForeignKey Lookup succeeds only for int fields but fails for non int fields. The test db and models.py is pasted below.

What am i doing wrong

Thanks 

Hari

 

 

>>> c = Child.objects.filter(parents_ssn="2354234234")

Suceeds!

>>> print c[0].name

werwer sdfgsdg

The following lookup fails

>>> cfails = Child.objects.filter(parents_ssn="g354234234c")

Traceback (most recent call last):

  File "<console>", line 1, in <module>

  File "/home/hari/djtrunk/django/db/models/manager.py", line 141, in filter

    return self.get_query_set().filter(*args, **kwargs)

  File "/home/hari/djtrunk/django/db/models/query.py", line 550, in filter

    return self._filter_or_exclude(False, *args, **kwargs)

  File "/home/hari/djtrunk/django/db/models/query.py", line 568, in _filter_or_exclude

    clone.query.add_q(Q(*args, **kwargs))

  File "/home/hari/djtrunk/django/db/models/sql/query.py", line 1170, in add_q

    can_reuse=used_aliases, force_having=force_having)

  File "/home/hari/djtrunk/django/db/models/sql/query.py", line 1105, in add_filter

    connector)

  File "/home/hari/djtrunk/django/db/models/sql/where.py", line 67, in add

    value = obj.prepare(lookup_type, value)

  File "/home/hari/djtrunk/django/db/models/sql/where.py", line 316, in prepare

    return self.field.get_prep_lookup(lookup_type, value)

  File "/home/hari/djtrunk/django/db/models/fields/related.py", line 136, in get_prep_lookup

    return self._pk_trace(value, 'get_prep_lookup', lookup_type)

  File "/home/hari/djtrunk/django/db/models/fields/related.py", line 209, in _pk_trace

    v = getattr(field, prep_func)(lookup_type, v, **kwargs)

  File "/home/hari/djtrunk/django/db/models/fields/__init__.py", line 882, in get_prep_lookup

    return super(IntegerField, self).get_prep_lookup(lookup_type, value)

  File "/home/hari/djtrunk/django/db/models/fields/__init__.py", line 292, in get_prep_lookup

    return self.get_prep_value(value)

  File "/home/hari/djtrunk/django/db/models/fields/__init__.py", line 876, in get_prep_value

    return int(value)

ValueError: invalid literal for int() with base 10: 'g354234234c'

################

models.py has:

################

from django.db import models

class Parent(models.Model):

    id = models.IntegerField(primary_key=True)

    name = models.CharField(max_length=384, blank=True)

    ssn = models.CharField(max_length=768, blank=True)

    class Meta:

        db_table = u'Parent'

        app_label = u'mydjapp'

class Child(models.Model):

    id = models.IntegerField(primary_key=True)

    name = models.CharField(max_length=384, blank=True)

    parents_ssn = models.ForeignKey(Parent, null=True, db_column='parents_ssn', blank=True)

    class Meta:

        db_table = u'child'

        app_label= u'mydjapp'

~                                       

#####################

The database columns are:

######################

mysql> select * from Parent;

+----+--------------+--------------+

| id | name         | ssn          |

+----+--------------+--------------+

|  1 | Aefwesk baob | 42s354234234 |

|  2 | Ask bob      | 2354234234   |

|  3 | Seelan Cyata | 2354234234c  |

|  4 | Hdel Abnot   | g354234234c  |

+----+--------------+--------------+

4 rows in set (0.00 sec)

mysql> select * from child;

+----+----------------+-------------+

| id | name           | parents_ssn |

+----+----------------+-------------+

|  1 | werwer sdfgsdg | 2354234234  |

|  2 | Hyadr Abnot    | g354234234c |

+----+----------------+-------------+

2 rows in set (0.00 sec)

##########################

The raw SQL test was setup :

#######################
CREATE TABLE `Parent` (
 `id` int(11) NOT NULL,
 `name` varchar(128) DEFAULT NULL,
 `ssn` varchar(256) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `parents_ssn_fk` (`ssn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


CREATE TABLE `child` (
 `id` int(11) NOT NULL,
 `name` varchar(128) DEFAULT NULL,
 `parents_ssn` varchar(256) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `parents_ssn` (`parents_ssn`),
 CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parents_ssn`) REFERENCES `Parent` (`ssn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.