CODEWITHSUNDEEP
pythonmysqldjangodjango-modelsdjango-admin
Alex John
Alex John

Reputation: 3

Django keeps on putting quotes around table name in mysql query and the query wont work

Table name in MySql is usercreds and the query that django generates works without the quotes around the table name. The model is created using inspectdb command from django, Im trying to django admin panel as a db manager.

models.py:

# This is an auto-generated Django model module.
# You'll have to do the following manually to clean this up:
#   * Rearrange models' order
#   * Make sure each model has one field with primary_key=True
#   * Make sure each ForeignKey has `on_delete` set to the desired behavior.
#   * Remove `managed = False` lines if you wish to allow Django to create, modify, and delete the table
# Feel free to rename the models, but don't rename db_table values or field names.
from django.db import models


class Usercreds(models.Model):
    id = models.IntegerField(primary_key=True)
    name = models.CharField(max_length=20, blank=True, null=True)
    password = models.CharField(max_length=20, blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'usercreds'

settings.py:

"""
Django settings for admindb project.

Generated by 'django-admin startproject' using Django 2.2.11.

For more information on this file, see
https://docs.djangoproject.com/en/2.2/topics/settings/

For the full list of settings and their values, see
https://docs.djangoproject.com/en/2.2/ref/settings/
"""

import os

# Build paths inside the project like this: os.path.join(BASE_DIR, ...)
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))


# Quick-start development settings - unsuitable for production
# See https://docs.djangoproject.com/en/2.2/howto/deployment/checklist/

# SECURITY WARNING: keep the secret key used in production secret!
SECRET_KEY = '(4n(txlca35l2nk$(#@^p9iin_a5m#&lvu7w=!-8tbb^7e0+33'

# SECURITY WARNING: don't run with debug turned on in production!
DEBUG = True

ALLOWED_HOSTS = []


# Application definition

INSTALLED_APPS = [
    'dbmanagepsql',
    'dbmanagemysql',
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
]

MIDDLEWARE = [
    'django.middleware.security.SecurityMiddleware',
    'django.contrib.sessions.middleware.SessionMiddleware',
    'django.middleware.common.CommonMiddleware',
    'django.middleware.csrf.CsrfViewMiddleware',
    'django.contrib.auth.middleware.AuthenticationMiddleware',
    'django.contrib.messages.middleware.MessageMiddleware',
    'django.middleware.clickjacking.XFrameOptionsMiddleware',
]

ROOT_URLCONF = 'admindb.urls'

TEMPLATES = [
    {
        'BACKEND': 'django.template.backends.django.DjangoTemplates',
        'DIRS': [],
        'APP_DIRS': True,
        'OPTIONS': {
            'context_processors': [
                'django.template.context_processors.debug',
                'django.template.context_processors.request',
                'django.contrib.auth.context_processors.auth',
                'django.contrib.messages.context_processors.messages',
            ],
        },
    },
]

WSGI_APPLICATION = 'admindb.wsgi.application'


# Database
# https://docs.djangoproject.com/en/2.2/ref/settings/#databases

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'testdb',
        'USER': 'postgres',
        'PASSWORD': 'wtfigo!!',
        'HOST': 'localhost',
        'PORT': '5432',
    },
    'mysql': {
        'ENGINE': 'django.db.backends.mysql',
        'OPTIONS': {
            'init_command': 'SET default_storage_engine=INNODB',
            'database': 'testdb',
            'user' : 'root',
            'password' : 'Ftpol018@',
        },
    }
}

# Password validation
# https://docs.djangoproject.com/en/2.2/ref/settings/#auth-password-validators

AUTH_PASSWORD_VALIDATORS = [
    {
        'NAME': 'django.contrib.auth.password_validation.UserAttributeSimilarityValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.MinimumLengthValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.CommonPasswordValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.NumericPasswordValidator',
    },
]


# Internationalization
# https://docs.djangoproject.com/en/2.2/topics/i18n/

LANGUAGE_CODE = 'en-us'

TIME_ZONE = 'UTC'

USE_I18N = True

USE_L10N = True

USE_TZ = True


# Static files (CSS, JavaScript, Images)
# https://docs.djangoproject.com/en/2.2/howto/static-files/

STATIC_URL = '/static/'

the error I get is: 

ProgrammingError at /admin/dbmanagemysql/usercreds/
relation "usercreds" does not exist
LINE 1: SELECT COUNT(*) AS "__count" FROM "usercreds"

Traceback from django: 

Environment:


Request Method: GET
Request URL: http://localhost:8000/admin/dbmanagemysql/usercreds/

Django Version: 2.2.11
Python Version: 3.6.9
Installed Applications:
['dbmanagepsql',
 'dbmanagemysql',
 'django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.staticfiles']
Installed Middleware:
['django.middleware.security.SecurityMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware']



Traceback:

File "/home/alex/.local/lib/python3.6/site-packages/django/db/backends/utils.py" in _execute
  84.                 return self.cursor.execute(sql, params)

The above exception (relation "usercreds" does not exist
LINE 1: SELECT COUNT(*) AS "__count" FROM "usercreds"
                                          ^
) was the direct cause of the following exception:

File "/home/alex/.local/lib/python3.6/site-packages/django/core/handlers/exception.py" in inner
  34.             response = get_response(request)

File "/home/alex/.local/lib/python3.6/site-packages/django/core/handlers/base.py" in _get_response
  115.                 response = self.process_exception_by_middleware(e, request)

File "/home/alex/.local/lib/python3.6/site-packages/django/core/handlers/base.py" in _get_response
  113.                 response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "/home/alex/.local/lib/python3.6/site-packages/django/contrib/admin/options.py" in wrapper
  606.                 return self.admin_site.admin_view(view)(*args, **kwargs)

File "/home/alex/.local/lib/python3.6/site-packages/django/utils/decorators.py" in _wrapped_view
  142.                     response = view_func(request, *args, **kwargs)

File "/home/alex/.local/lib/python3.6/site-packages/django/views/decorators/cache.py" in _wrapped_view_func
  44.         response = view_func(request, *args, **kwargs)

File "/home/alex/.local/lib/python3.6/site-packages/django/contrib/admin/sites.py" in inner
  223.             return view(request, *args, **kwargs)

File "/home/alex/.local/lib/python3.6/site-packages/django/utils/decorators.py" in _wrapper
  45.         return bound_method(*args, **kwargs)

File "/home/alex/.local/lib/python3.6/site-packages/django/utils/decorators.py" in _wrapped_view
  142.                     response = view_func(request, *args, **kwargs)

File "/home/alex/.local/lib/python3.6/site-packages/django/contrib/admin/options.py" in changelist_view
  1685.             cl = self.get_changelist_instance(request)

File "/home/alex/.local/lib/python3.6/site-packages/django/contrib/admin/options.py" in get_changelist_instance
  744.             sortable_by,

File "/home/alex/.local/lib/python3.6/site-packages/django/contrib/admin/views/main.py" in __init__
  82.         self.get_results(request)

File "/home/alex/.local/lib/python3.6/site-packages/django/contrib/admin/views/main.py" in get_results
  210.         result_count = paginator.count

File "/home/alex/.local/lib/python3.6/site-packages/django/utils/functional.py" in __get__
  80.         res = instance.__dict__[self.name] = self.func(instance)

File "/home/alex/.local/lib/python3.6/site-packages/django/core/paginator.py" in count
  91.             return c()

File "/home/alex/.local/lib/python3.6/site-packages/django/db/models/query.py" in count
  392.         return self.query.get_count(using=self.db)

File "/home/alex/.local/lib/python3.6/site-packages/django/db/models/sql/query.py" in get_count
  504.         number = obj.get_aggregation(using, ['__count'])['__count']

File "/home/alex/.local/lib/python3.6/site-packages/django/db/models/sql/query.py" in get_aggregation
  489.         result = compiler.execute_sql(SINGLE)

File "/home/alex/.local/lib/python3.6/site-packages/django/db/models/sql/compiler.py" in execute_sql
  1140.             cursor.execute(sql, params)

File "/home/alex/.local/lib/python3.6/site-packages/django/db/backends/utils.py" in execute
  99.             return super().execute(sql, params)

File "/home/alex/.local/lib/python3.6/site-packages/django/db/backends/utils.py" in execute
  67.         return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)

File "/home/alex/.local/lib/python3.6/site-packages/django/db/backends/utils.py" in _execute_with_wrappers
  76.         return executor(sql, params, many, context)

File "/home/alex/.local/lib/python3.6/site-packages/django/db/backends/utils.py" in _execute
  84.                 return self.cursor.execute(sql, params)

File "/home/alex/.local/lib/python3.6/site-packages/django/db/utils.py" in __exit__
  89.                 raise dj_exc_value.with_traceback(traceback) from exc_value

File "/home/alex/.local/lib/python3.6/site-packages/django/db/backends/utils.py" in _execute
  84.                 return self.cursor.execute(sql, params)

Exception Type: ProgrammingError at /admin/dbmanagemysql/usercreds/
Exception Value: relation "usercreds" does not exist
LINE 1: SELECT COUNT(*) AS "__count" FROM "usercreds"
                                          ^

If there is someway to remove the quotes or no generate the queries with the quotes would solve the problem. I have tried renaming the table name in models.py but nothing seems to work. If you guys are familiar with this or someway to make use the django admin panel as a db manager ui to perform the CRUD operations on any db, please help me out.

Upvotes: 0

Views: 405

Answers (1)

Victor
Victor

Reputation: 2919

The issue is not that Django is putting quotes around your table name, but that you are querying the wrong database for the table. In your case, it is querying your postgres database, because that's what you set as default, which is the database alias Django uses when you have not specified any other specific database to use instead.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'testdb',
        'USER': 'postgres',
        'PASSWORD': 'wtfigo!!',
        'HOST': 'localhost',
        'PORT': '5432',
    },
    'mysql': {
        'ENGINE': 'django.db.backends.mysql',
        'OPTIONS': {
            'init_command': 'SET default_storage_engine=INNODB',
            'database': 'testdb',
            'user' : 'root',
            'password' : 'Ftpol018@',
        },
    }
}

To demonstrate that this is the case, you can use .using('db_alias') to select the right database to use

all_usercreds = Usercreds.objects.using('mysql').all()

If you don't want to always call .using whenever you are going to reference that model, you can define a custom database router and register it in settings.DATABASE_ROUTERS like the following: my_app.db_routers.py

from my_app.models import Usercreds
class CustomDatabaseRouter(object):
    def db_for_read(self, model, **hints):        
        if model == Usercreds:
            return 'mysql'
        return None

    def db_for_write(self, model, **hints):
        if model == Usercreds:
            return 'mysql'
        return None

app/settings.py

DATABASE_ROUTERS = ('my_app.db_routers.CustomDatabaseRouter',)

Source: https://docs.djangoproject.com/en/3.0/topics/db/multi-db/

Upvotes: 1

Related Questions