Home > Net >  django - I can't use the Q filter model with 2 related databases
django - I can't use the Q filter model with 2 related databases

Time：10-13

I have the 2 following models related with 2 different databases at models.py:

class AnalogicalValues(models.Model):
    id = models.BigAutoField(primary_key=True)
    date = models.DateField()
    description = models.ForeignKey(ExpensesDescription, models.DO_NOTHING)
    continent_id = models.ForeignKey(
        'WorldContinent', db_column='continent_id', on_delete=models.DO_NOTHING
    )
    (...)hide code(...)
    city_id = models.ForeignKey(
        'WorldCity', db_column='city_id', verbose_name='City', on_delete=models.DO_NOTHING
    )
    value = models.FloatField()
    comments = models.CharField(max_length=255, blank=True, null=True)
    user_id = models.ForeignKey(User, db_column='user_id', on_delete=models.DO_NOTHING)

    class Meta:
        managed = False
        db_table = 'analogical_values'
        ordering = ('-date', '-id')


class WorldCity(models.Model):
    id = models.AutoField(primary_key=True, unique=True)
    name = models.CharField(max_length=255, verbose_name='City')
    continent = models.ForeignKey(WorldContinent, models.DO_NOTHING)
    country = models.ForeignKey(WorldCountry, models.DO_NOTHING)
    subcountry = models.ForeignKey(WorldSubcountry, models.DO_NOTHING)
    last_update_db = models.DateTimeField()

    class Meta:
        managed = False
        db_table = 'cities'
        ordering = ('name',)
        verbose_name_plural = 'List of World Cities'
        verbose_name = 'World City'
    
    def __str__(self):
        return self.name

The relationship between them are city_id from AnalogicalValues and id from WorldCity and each model is mapped of respective database at routers.py. The description field on AnalogicalValues is a foreignkey of other table in same database as analogical_values and it's working fine.

class WorldRouter:

    route_app_labels = {'myapp'}
    route_model_list = {'WorldCity'}

    def db_for_read(self, model, **hints):
        if (
            model._meta.app_label in self.route_app_labels and 
            model._meta.object_name in self.route_model_list
        ):
            return 'world_db'
        return None

    def db_for_write(self, model, **hints):
        if (
            model._meta.app_label in self.route_app_labels and 
            model._meta.object_name in self.route_model_list
        ):
            return 'world_db'
        return None
    
    def allow_relation(self, obj1, obj2, **hints):
        if (
            obj1._meta.app_label in self.route_app_labels or
            obj2._meta.app_label in self.route_app_labels
        ):
            return True
        return None

class ExpensesRouter:
    route_app_labels = {'myapp'}
    route_model_list = {'AnalogicalValues'}

    def db_for_read(self, model, **hints):
        if (
            model._meta.app_label in self.route_app_labels and 
            model._meta.object_name in self.route_model_list
        ):
            return 'expenses_db'
        return None

    def db_for_write(self, model, **hints):
        if (
            model._meta.app_label in self.route_app_labels and 
            model._meta.object_name in self.route_model_list
        ):
            return 'expenses_db'
        return None

    def allow_relation(self, obj1, obj2, **hints):
        if (
            obj1._meta.app_label in self.route_app_labels or
            obj2._meta.app_label in self.route_app_labels
        ):
            return True
        return None

In models.py I want to get all results of all columns regarding the string that I write on 'filterData' form:

from . import models
from django.views import generic

class DataView(generic.ListView, generic.FormView):

    (...some code...)

    model = models.AnalogicalValues
    paginate_by = 10

    def get(self, *args, **kwargs):

        (...some code...)

        datalist = self.model.objects.filter(
                    Q(user_id__exact=self.request.user) &
                    (
                        Q(date__icontains=kwargs['filterData'])
                        | Q(description__name__icontains=kwargs['filterData'])
                        | Q(city_id__name__icontains=kwargs['filterData'])
                        | Q(value__icontains=kwargs['filterData'])
                        | Q(comments__icontains=kwargs['filterData'])
                    )
                )
        self.paginate_by = datalist.count()

        (...some code...)

        return render(self.request, self.template_name, {
        'datalist': datalist,
        (...some args...)

    })

When django reaches the self.paginate_by line, I get this error

Environment:

Traceback (most recent call last):
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)

The above exception ((1146, "Table 'EXPENSES.cities' doesn't exist")) was the direct cause of the following exception:
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/django/core/handlers/exception.py", line 47, in inner
    response = get_response(request)
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/django/core/handlers/base.py", line 181, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/django/views/generic/base.py", line 70, in view
    return self.dispatch(request, *args, **kwargs)
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/django/contrib/auth/mixins.py", line 52, in dispatch
    return super().dispatch(request, *args, **kwargs)
  File "/home/bruno/Python/BN_APP/BN_APP/myapp/views.py", line 43, in dispatch
    return super(UserAccessMixin, self).dispatch(self.request, *args, **kwargs)
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/django/contrib/auth/mixins.py", line 85, in dispatch
    return super().dispatch(request, *args, **kwargs)
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/django/views/generic/base.py", line 98, in dispatch
    return handler(request, *args, **kwargs)
  File "/home/bruno/Python/BN_APP/BN_APP/myapp/views.py", line 1256, in post
    return FinancialDataView.get(self, **kwargs)
  File "/home/bruno/Python/BN_APP/BN_APP/myapp/views.py", line 1158, in get
    self.paginate_by = datalist.count()
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/django/db/models/query.py", line 411, in count
    return self.query.get_count(using=self.db)
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/django/db/models/sql/query.py", line 515, in get_count
    number = obj.get_aggregation(using, ['__count'])['__count']
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/django/db/models/sql/query.py", line 500, in get_aggregation
    result = compiler.execute_sql(SINGLE)
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1156, in execute_sql
    cursor.execute(sql, params)
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 98, in execute
    return super().execute(sql, params)
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/bruno/Python/BN_APP/venv/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)

Exception Type: ProgrammingError at /financial_data/
Exception Value: (1146, "Table 'EXPENSES.cities' doesn't exist")

I did some digging and I check that the query that it's built misses the database name for table cities. Can anyone help me out with it please? Thank you

(Edited)

Query that is performed:

USE EXPENSES;
SELECT
    analogical_values.id
  , analogical_values.date
  , analogical_values.description_id
  , analogical_values.transaction_type_id
  , analogical_values.payment_method_id
  , analogical_values.continent_id
  , analogical_values.country_id
  , analogical_values.subcountry_id
  , analogical_values.city_id
  , analogical_values.value
  , analogical_values.comments
  , analogical_values.user_id
  , analogical_values.last_update_db 

FROM
    analogical_values
    INNER JOIN
        description
        ON
            (
                analogical_values. description_id = description.description_id
            )
    INNER JOIN
        transaction_type
        ON
            (
                analogical_values.transaction_type_id = transaction_type.transaction_type_id
            )
    INNER JOIN
        payment_method
        ON
            (
                analogical_values.payment_method_id = payment_method.payment_method_id
            )
    INNER JOIN
        countries 
        ON
            (
                analogical_values.country_id = countries.id
            )
    INNER JOIN
        cities
        ON
            (
                analogical_values.city_id = cities.id
            )
WHERE
    (
        analogical_values.user_id = 2
        AND
        (
            analogical_values.date LIKE '%test%'
            OR description.name LIKE '%test%'
                OR transaction_type.name LIKE '%test%'
                OR payment_method.name LIKE '%test%'
            OR countries.name LIKE '%test%'
                OR cities.name LIKE '%test%' 
                OR analogical_values.value LIKE '%test%'
            OR analogical_values.comments LIKE '%test%'
        )
    )
ORDER BY
    analogical_values.date DESC, analogical_values.id DESC

In the last 2 INNER JOINS, the tables belongs to WORLD database, e.g., WORLD.countries and WORLD.cities

CodePudding user response：

Exception Value: (1146, "Table 'EXPENSES.cities' doesn't exist")

if you read your exception it is easy to see that table does not exists. Probably make migrations first.

CodePudding user response：

I know that it's not the best practice, but I did a filter directly from the model and grab all ids for the Q filter and it worked

if kwargs['filterData']:
    city_list = models.WorldCity.objects.filter(name__icontains=kwargs['filterData']).values()
    city_id = []
    [city_id.append(cur_city.get('id')) for cur_city in city_list]
else:
    city_id = city_list.none()

datalist = self.model.objects.filter(
    Q(user_id__exact=self.request.user) &
    (
        Q(date__icontains=kwargs['filterData'])
        | Q(description__name__icontains=kwargs['filterData'])
        | Q(city_id__id__in=city_id)
        | Q(value__icontains=kwargs['filterData'])
        | Q(comments__icontains=kwargs['filterData'])
    )
)

    self.paginate_by = datalist.count()

Page link：https//www.codepudding.com/net/150154.html

Prev:Create resizable Tkinter frame inside of scrollable canvas
Next:Cannot download any Python Library
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding