Raw SQLite3 command in Django

Question

I am trying to join 2 tables to display the orders that are matched to the username for the currently logged in user using SQLITE3, I have the SQL raw command written but I recieve the error: there is no such table Account. I have checked using the .tables sql command and the table is created and working. The program is also migrated without any errors but the Account table cant be found.

views.py

from .models import Account, Order
from django.db import connection

def account(request):
    # Retrieve the currently logged in user
    user=request.session.get("user_id")
    if user is not None:
    
    
      # Retrieve the user's orders using a SQL join
      with connection.cursor() as cursor:
          cursor.execute('''
              SELECT a.Username, a.Email, o.Name, o.Surname, o.part
              FROM Account a
              INNER JOIN "Order" o ON a.Username = o.Username
              WHERE a.id = %s
          ''', [user])
          rows = cursor.fetchall()
          
          orders = []
          for row in rows:
              order = {
                  'Username': row[0],
                  'Email': row[1],
                  'Name': row[2],
                  'Surname': row[3],
                  'Part': row[4],
              }
              orders.append(order)
      print(orders)
      # Render the orders template with the orders data
      return render(request, 'orders.html', {'orders': orders})
    else:
      return redirect(reverse("home"))

The Account in line 11 is giving the error.

models.py

class Account(models.Model):
  #Only one id can be created
  id = models.AutoField(primary_key=True)
  #Uses in built CharField which means no special characters can be saved in the database, if special characters are part of the Username, the data will not be saved
  Username = models.CharField(max_length=20)
  #Inbuilt EmailField, if email is not in correct format eg. TS_SHOP@gmail.com, then the value will not be saved
  Email= models.EmailField(max_length=20)
  
  Password=models.CharField(max_length=20)

#Table order which stores the customers order
class Order(models.Model):
  #primary key id
  id = models.AutoField(primary_key=True)
  Name = models.CharField(max_length=20)
  Surname = models.CharField(max_length=20)
  Address = models.CharField(max_length=100)
  CreditCard= models.CharField(max_length=19)
  Username = models.CharField(max_length=20)
  Email = models.EmailField(max_length=40)
  #validator only allows values shorter that 10 digits
  Zip = models.IntegerField(validators=[MaxValueValidator(999999999)],null=True)
  City = models.CharField(max_length=20)
  Country = models.CharField(max_length=20)
  part = models.CharField(max_length=20,null=True)
  quantity = models.IntegerField(default=1)```

score 0 · Answer 1 · answered Mar 20 '23 at 16:10

Django automatically derives the name of the database table from the name of your model class and the app that contains it (source). If you want to have a table named account, you have to configure it this way:

class Account(models.Model):
    ...

    class Meta:
        db_table = "account"

So, I believe, you don't have a table call Account in your DB, its name is <APP_NAME>_account. You have to call it this way. Or you can just change the Account model like above.

Raw SQLite3 command in Django

views.py

models.py

1 Answers1