CODEWITHSUNDEEP
pythondjango
Kamil
Kamil

Reputation: 11

Why django is not detecting my table column?

I have a problem. I've created model called "Flower", everything works fine, i can create new "Flowers", i can get data from them etc. The problem is when I want to use column "owner_id" in SQL query I got an error that this column don't exist, despite I can use it to get data from objects (for example flower1.owner_id). I've deleted my sqlite database several times, made new migrations and used migrate but that still not worked.I also changed name of the column and re-created it, but that still doesn't helped.

My models.py:

from django.db import models
from django.contrib.auth.models import User

class Flower(models.Model):
name = models.CharField(max_length=80)
water_time = models.IntegerField()
owner_id = models.ForeignKey(User, on_delete=models.CASCADE, default=1)


def __str__(self):
    return self.name

My views.py (view, where i want to use it):

class workspaceView(generic.DetailView):
template_name = 'floris/workspace.html'

def get_object(self):
    with connection.cursor() as cursor:
        cursor.execute(f'SELECT id,name  FROM floris_Flower WHERE owner_id = {self.request.user.id}')
        row = cursor.fetchall()
    object = row
    print(object)
    if self.request.user.id == object.id:
        return object
    else:
        print('Error')

My urls.py:

from django.urls import path

from . import views

urlpatterns = [
    path('', views.index2, name='index2'),
    path('login/', views.loginView, name='login'),
    path('register/', views.register, name='register'),
    path('addflower/', views.AddPlantView, name='addflower'),
    path('index.html', views.logout_view, name='logout'),
    path('workspace/', views.workspaceView.as_view(), name='workspace'),
    path('profile/<str:username>/', views.ProfileView.as_view(), name='profile'),
    
]

And my error code:

OperationalError at /floris/workspace/
no such column: owner_id
Request Method: GET
Request URL:    http://127.0.0.1:8000/floris/workspace/
Django Version: 3.2.4
Exception Type: OperationalError
Exception Value:    
no such column: owner_id
Exception Location: /home/stazysta-kamil/.local/lib/python3.8/site-packages/django/db/backends/sqlite3/base.py, line 421, in execute
Python Executable:  /usr/bin/python3
Python Version: 3.8.10
Python Path:    
['/home/stazysta-kamil/Desktop/floris/mysite',
 '/usr/lib/python38.zip',
 '/usr/lib/python3.8',
 '/usr/lib/python3.8/lib-dynload',
 '/home/stazysta-kamil/.local/lib/python3.8/site-packages',
 '/usr/local/lib/python3.8/dist-packages',
 '/usr/lib/python3/dist-packages']
Server time:    Tue, 06 Jul 2021 10:43:32 +0000

Upvotes: 1

Views: 1369

Answers (2)

rosarioamantia
rosarioamantia

Reputation: 1

The problem is in field "owner_id" of Flower model. When you define a Foreign Key in model, is not necessary to add "_id" at the end since Django add it automatically

in this case, should be enough replace

owner_id = models.ForeignKey(User, on_delete=models.CASCADE, default=1)

with

owner = models.ForeignKey(User, on_delete=models.CASCADE, default=1)

Upvotes: 0

Niel Godfrey P. Ponciano
Niel Godfrey P. Ponciano

Reputation: 10699

Since owner_id is declared as a ForeignKey, it will be available in the actual SQL database as owner_id_id. The additional prefix _id is automatically appended by Django for that relational field. When using Django ORM, you would just access it via owner_id then Django will automatically handle things for you in the background but if you are using raw SQL command, then you have to use the actual table column name which is owner_id_id. If you don't want such behavior, set the db_column of the model field with the exact name you want e.g. owner_id = models.ForeignKey(User, on_delete=models.CASCADE, default=1, db_column="owner_id").

As stated in Django documentation:

Behind the scenes, Django appends "_id" to the field name to create its database column name. In the above example, the database table for the Car model will have a manufacturer_id column.

Related references:

Upvotes: 1

Related Questions