Legacy database with a table, which has no separate primary key

[Alexander]

I have a database, which among others has the following tables:

CREATE TABLE users (
userId BIGINT PRIMARY KEY
);

CREATE TABLE movies (
movieId BIGINT PRIMARY KEY,
title varchar(255) NOT NULL
);

CREATE TABLE ratings (
userId BIGINT NOT NULL REFERENCES users(userId),
movieId BIGINT NOT NULL REFERENCES movies(movieId),
rating SMALLINT NOT NULL,
timestamp varchar(150) NOT NULL,
CONSTRAINT unique_userid_movieid UNIQUE(userId, movieId)
);

As you can see the 'rating' table has no separate primary key field.

Here are the models created by Django with some my corrections:

class Users(models.Model):
userid = models.BigIntegerField(primary_key=True)

def __unicode__(self):
return u"User %d" % self.userid

class Meta:
db_table = u'users'

class Movies(models.Model):
movieid = models.BigIntegerField(primary_key=True)
title = models.CharField(max_length=255)
users = models.ManyToManyField(Users, through='Ratings',
related_name="rated_movies")

def __unicode__(self):
return self.title

class Meta:
db_table = u'movies'

class Ratings(models.Model):
user = models.ForeignKey(Users, db_column='userid')
movie = models.ForeignKey(Movies, db_column='movieid')
rating = models.SmallIntegerField()
timestamp = models.BigIntegerField()

def __unicode__(self):
return u"%d" % self.rating

class Meta:
db_table = u'ratings'
unique_together = (("user", "movie"),)




So having these models i can do this:

In [1]: from django_orm.movielens.models import *
In [2]: u = Users.objects.get(pk=1)
In [3]: u.rated_movies.all()
Out[3]: [<Movies: Boomerang (1992)>,..]

or this:

In [4]: m = Movies.objects.get(pk=1)
In [5]: m.users.all().count()
Out[5]: 2264

But i can't get the "ratings" objects because trying this:

Ratings.objects.filter(user=1)

or this:

u.ratings_set.all()

causes the following error

DatabaseError: column ratings.id does not exist
LINE 1: SELECT "ratings"."id", "ratings"."userid",
"ratings"."moviei...

Is there a way to tell Django, that 'Ratings' models does not have a
separate primary key? Or my only option is to add the column to the
'ratings' table?

[Devin M]

I did some quck googling and found this.
http://stackoverflow.com/questions/605896/django-querying-read-only-view-with-no-primary-key

This is the response from them that seems most helpful.

"all you need is a column that is guaranteed to be unique for every
row. Set that to be 'primary_key = True in your model and Django will
be happy."

Regards,
Devin Morin

[Devin M]

Wait... I just looked at your sql and it looks like you would need to
use multiple-column primary keys which are unsupported by django.
http://code.djangoproject.com/ticket/373

On Oct 15, 4:15 am, Alexander <potapovsa...@gmail.com> wrote:

[Alexander]

Thanks for your reply.

[David De La Harpe Golden]

On 15/10/10 12:15, Alexander wrote:

> As you can see the 'rating' table has no separate primary key field.

Yeah, that is pretty commonplace (though not presently supported by
django), the natural primary key for a table may be composite...

> Here are the models created by Django with some my corrections:
>

[remember to set 'managed = False' on models you don't want django to
automanage. And your model class names should really be singular]

> DatabaseError: column ratings.id does not exist
> LINE 1: SELECT "ratings"."id", "ratings"."userid",
> "ratings"."moviei...
>
> Is there a way to tell Django, that 'Ratings' models does not have a
> separate primary key?

IFF you're only reading the data, not writing it, you can just flat out
lie to the django orm - make Rating unmanaged, tell it one of the
columns in Rating is the primary key (even if it's not really unique!),
and things may Happen To Work ...for reading. But it _will_ break
horribly upon writing and is undoubtedly unsupported by the django
developers.

> Or my only option is to add the column to the
> 'ratings' table?
>

That's a safer option if you can, and presently necessary if you want
writing to work via the django ORM for writing. Though it may annoy
many a relational purist, possibly including your local DBA.

A generally more powerful but more complicated ORM for python exists -
sqlalchemy - and can cope with composite keys, but obviously you don't
then get integration with the rest of django (there is a
django-sqlalchemy project but it wasn't far along last time I looked at
it, which was quite some time ago mind you).

[Alexander]

Thank you for such a comprehensive answer.

I chose to add the field to the table. It seems to be the best
solution.