“截断了不正确的DOUBLE值:X_XX"； [英] "Truncated incorrect DOUBLE value: X_XX"
问题描述
Django的ORM并不是为我而点击，但我打算度过整个周末来阅读
Django's ORM just isn't clicking for me, but I intend to spend the weekend reading through the documentation in order to make it click.
与此同时，我需要解决一个我无法解决的问题.
Meanwhile, I have an issue I need to resolve that I haven't been able to.
我第一次来这里是通过提供的答案解决了
The first I had was here, which was resolved with the answer provided:
ValueError :以10为底的int()的无效文字似乎与ForeignKey相关
我已将查询修改为:
# data['product_id] = 78
# returns A_17
product_code = Products.objects.get(id=data['product_id']).code
# this is where the error occurs
print(ProductPositions.objects.filter(product_code__code=product_code))
/mnt/c/dev/current/client/.venv/client/lib/python3.6/site-packages/pymysql/cursors.py:166:警告:(1292，被截断了不正确的DOUBLE值:'A_15 ')
/mnt/c/dev/current/client/.venv/client/lib/python3.6/site-packages/pymysql/cursors.py:166: Warning: (1292, "Truncated incorrect DOUBLE value: 'A_15'")
结果= self._query(查询)
result = self._query(query)
我什至不确定为什么要查看A_15，因为它只应在A_17上进行过滤，所以这是我不明白的一个问题
I am not even sure why it is looking at A_15 because it should only be filtering on A_17, so that is one issue I don't understand
要解释这些表，因为我认为它不是很直观. Products看起来像这样(与该列不相关的列已删除):
To explain these tables because I don't think it is really intuitive. The Products will look something like this (with columns not relevant to this removed):
-- Products table
id code
--------------
77 A_16
78 A_17
81 M_15
ProductPositions中有多个code. code更像是一条产品线，而product_no(我还没有做，是下一步)是该产品线中的产品.最终，我尝试获取描述，但仅在当前过滤A_17时.因此，它看起来像:
There are multiple of the code in the ProductPositions. code is more like a line of products and and product_no (which I haven't got to yet and is the next step) are the products in that line. Ultimately, I am trying to get the description, but only at the point of filtering A_17 currently. So it will look like:
-- ProductPositions table
product_code product_no description
-------------------------------------------------
A_17 ABC123 Widget 1
A_17 DEF456 Widget 2
A_17 GHI789 Widget 3
A_16 ABC123 Widget 1
A_16 DEF456 Widget 2
A_16 GHI789 Widget 3
这些是每个模型:
class Products(models.Model):
id = models.AutoField(primary_key=True)
code = models.CharField(unique=True, max_length=16)
name = models.CharField(max_length=255, blank=True, null=True)
short_name = models.CharField(max_length=128)
class ProductPositions(models.Model):
product_code = models.ForeignKey(Products, db_column='product_code', null=False)
product_no = models.DecimalField(unique=True, max_digits=12, decimal_places=1, primary_key=True)
product_title = models.CharField(max_length=255)
product_description = models.TextField()
def __str__(self):
return self.product_description
class Meta:
db_table = 'Product_Positions'
unique_together = ('product_code', 'product_no')
此外，似乎这可能是数据库生成的错误.但是，当我在./manage.py shell中执行以下操作以查看原始SQL查询的列表时，什么都没有出现(只有一个连接并且未命名):
Also, it seems like it might be an error generated by the database. However, when I do the following in ./manage.py shell to see a list of raw SQL queries, nothing comes up (only one connection and it isn't named):
>>> from django.db import connection
>>> connection.queries
[]
无论如何，最终都试图进入ProductPositions.objects.filter(product_code__code=product_code).filter(product_no=data['product_no'].description，但是在每一步都遇到了问题.
Anyway, ultimately trying to get to ProductPositions.objects.filter(product_code__code=product_code).filter(product_no=data['product_no'].description, but running into issues every step of the way.
要明确指出的问题:正在将字符串传递到varchar字段，但错误会在double中返回.为什么?
And to be clear on the issue: a string is being passed to varchar field, but the error is coming back with double. Why?
推荐答案
好吧，在进行了详尽的调试之后，我终于弄清了问题所在，以便我可以实际看到正在发送的查询.问题在于它是如何加入的，并且默认情况下使用的是Products的pk.
Ok, finally figured out what the issue was after turning verbose debugging on such that I could actually see the queries being sent. The issue was how it was being joined and it was using the pk of Products by default.
ProductPositions.objects.filter(product_code__code=product_code)
基本上被翻译为:
... INNER JOIN.... ON 'ProductPositions'.'product_code' = 'Products'.'id'
我真的不想要.应该是:
Which I really did not want. It should have been:
... INNER JOIN.... ON 'ProductPositions'.'product_code' = 'Products'.'code'
因此解决方案是通过添加to_field来修复ProductPositions模型中的product_code字段.如文档所述:
So the solution was to fix the product_code field in the ProductPositions model by adding to_field. As the documentation says:
对于诸如ForeignKey之类的映射到模型实例的字段，默认值应该是它们引用的字段的值(除非设置了to_field，否则为pk)而不是模型实例.
For fields like ForeignKey that map to model instances, defaults should be the value of the field they reference (pk unless to_field is set) instead of model instances.
https://docs.djangoproject.com/en/dev/ref/models/fields/#django.db.models.ForeignKey.to_field
product_code = models.ForeignKey(Products, to_field='code', db_column='product_code', null=False)
现在正在按预期方式检索数据，而没有错误.
Now data is retrieving as expected and without errors.
这篇关于“截断了不正确的DOUBLE值:X_XX"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
