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

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.