CODEWITHSUNDEEP
pythonmysqldjangosqlite
Arthur M
Arthur M

Reputation: 458

Django sqlite column not found error

I'm having a little trouble querying my sqlite DB. Has anyone seen this before? It may very well be that I am doing something silly

Here we have the schema:

BEGIN;
CREATE TABLE "homepage_user" (
    "username" varchar(20) NOT NULL PRIMARY KEY,
    "first_name" varchar(20) NOT NULL,
    "last_name" varchar(20) NOT NULL
)
;
CREATE TABLE "homepage_oncall" (
    "id" integer NOT NULL PRIMARY KEY,
    "user_id" varchar(20) NOT NULL REFERENCES "homepage_user" ("username"),
    "start_date" datetime,
    "end_date" datetime
)
;

COMMIT;

And then when I query that data from django's interactive shell:

...
>>> cursor.execute('SELECT user_id from homepage_oncall').fetchall()
[(u'amencke',), (u'jdoe',), (u'jbloggs',)]

However - any attempt to query 'homepage_oncall.user_id' will be met with a stern rebuke: 

>>> query = cursor.execute('SELECT first_name, last_name FROM homepage_user WHERE homepage_user.username = homepage_oncall.user_id AND NOW() BETWEEN homepage_oncall.start_date AND homepage_oncall.end_date')
...
DatabaseError: no such column: homepage_oncall.user_id

I had a look around on here and it seems this issue surfaces after editing a schema after it was created - but (after failing to solve the problem with 'manage.py migrate' I actually deleted and recreated the entire database to rule that out - there was nothing in it anyway

Thanks, Arthur

Upvotes: 2

Views: 243

Answers (1)

Emma
Emma

Reputation: 1081

Your problem is in your SQL. You SELECT from one table and try to apply a WHERE condition on another table without performing an implicit or explicit JOIN.

Also Django provides a pretty nice ORM, so I would recommend sticking to it instead of running raw queries against the database especially if you are not well versed with writing SQL queries

Upvotes: 1

Related Questions