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+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
