python - django - 我不能将 Q 过滤器模型与 2 个相关数据库一起使用
问题描述
我在 models.py 中有以下 2 个与 2 个不同数据库相关的模型：
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
它们之间的关系是来自 AnalogicalValues 的 city_id 和来自 WorldCity 的 id，每个模型都映射到 routers.py 中的相应数据库。AnalogicalValues 上的描述字段是与analogical_values 相同的数据库中其他表的外键，它工作正常。
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
在 models.py 中，我想获取关于我在“filterData”表单上写的字符串的所有列的所有结果：
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...)
})
当 django 到达 self.paginate_by 行时，我收到此错误
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")
我进行了一些挖掘，并检查它构建的查询是否缺少表城市的数据库名称。有人可以帮我解决吗？谢谢
（已编辑）
执行的查询：
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
在最后 2 个 INNER JOINS 中，表属于 WORLD 数据库，例如 WORLD.countries 和 WORLD.cities
解决方案
Exception Value: (1146, "Table 'EXPENSES.cities' doesn't exist")
如果您阅读您的异常，很容易看到该表不存在。可能首先进行迁移。
推荐阅读
- java - 如何从布尔变量中获得两个以上的答案？
- ios - 在获取数据期间索引超出部分中的行数范围
- laravel - 如何将两个 Laravel 查询构建器对象合并为一个查询构建器对象
- python - 使用 pathlib，如何将 Path 对象拆分为列表或单个名称的可迭代，与平台无关？
- mule - 无法在任何点工作室运行骡子项目
- angular - Safari 13 协议错误 NSPOSIXErrorDomain: 100 with IISNode
- c# - ASP.NET MVC：从 SQL Server Express 迁移到 Azure
- azure - Azure 虚拟机映像是否维护 SSL 配置？
- ubuntu - gnu 复杂性：命令行参数和构建
- python - 通过 tkinter 中的帧传递变量