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.
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 wrongThanksHari>>> c = Child.objects.filter(parents_ssn="2354234234")Suceeds!>>> print c[0].namewerwer sdfgsdgThe 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 filterreturn self.get_query_set().filter(*args, **kwargs)File "/home/hari/djtrunk/django/db/models/query.py", line 550, in filterreturn self._filter_or_exclude(False, *args, **kwargs)File "/home/hari/djtrunk/django/db/models/query.py", line 568, in _filter_or_excludeclone.query.add_q(Q(*args, **kwargs))File "/home/hari/djtrunk/django/db/models/sql/query.py", line 1170, in add_qcan_reuse=used_aliases, force_having=force_having)File "/home/hari/djtrunk/django/db/models/sql/query.py", line 1105, in add_filterconnector)File "/home/hari/djtrunk/django/db/models/sql/where.py", line 67, in addvalue = obj.prepare(lookup_type, value)File "/home/hari/djtrunk/django/db/models/sql/where.py", line 316, in preparereturn self.field.get_prep_lookup(lookup_type, value)File "/home/hari/djtrunk/django/db/models/fields/related.py", line 136, in get_prep_lookupreturn self._pk_trace(value, 'get_prep_lookup', lookup_type)File "/home/hari/djtrunk/django/db/models/fields/related.py", line 209, in _pk_tracev = getattr(field, prep_func)(lookup_type, v, **kwargs)File "/home/hari/djtrunk/django/db/models/fields/__init__.py", line 882, in get_prep_lookupreturn super(IntegerField, self).get_prep_lookup(lookup_type, value)File "/home/hari/djtrunk/django/db/models/fields/__init__.py", line 292, in get_prep_lookupreturn self.get_prep_value(value)File "/home/hari/djtrunk/django/db/models/fields/__init__.py", line 876, in get_prep_valuereturn int(value)ValueError: invalid literal for int() with base 10: 'g354234234c'################models.py has:################from django.db import modelsclass 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.
No comments:
Post a Comment