“截断不正确的 DOUBLE 值:X_XX"

2022-01-15 00:00:00 python django django-models mariadb mysql

Django 的 ORM 并没有为我点击，但我打算在周末阅读 documentation 以使其点击.

同时，我有一个我无法解决的问题.

我第一次在这里，通过提供的答案解决了:

ValueError: 以 10 为底的 int() 的无效文字似乎与 ForeignKey 相关

我已将查询修改为:

# data['product_id] = 78# 返回 A_17product_code = Products.objects.get(id=data['product_id']).code# 这是错误发生的地方打印(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'")

结果 = self._query(查询)

我什至不确定它为什么要查看 A_15，因为它应该只过滤 A_17，所以这是我不明白的一个问题

解释这些表格是因为我认为它不是很直观.Products 看起来像这样(删除了与此无关的列):

-- 产品表识别码--------------77 A_1678 A_1781 M_15

ProductPositions中有多个code.code 更像是一个产品线，而 product_no (我还没有完成，是下一步)是该线中的产品.最终，我试图获得描述，但目前只是在过滤 A_17 的时候.所以它看起来像:

-- ProductPositions 表product_code product_no 描述-------------------------------------------------A_17 ABC123 小部件 1A_17 DEF456 小部件 2A_17 GHI789 小部件 3A_16 ABC123 小部件 1A_16 DEF456 小部件 2A_16 GHI789 小部件 3

这些是每个的模型:

类产品(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)类 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):返回 self.product_description元类:db_table = 'Product_Positions'unique_together = ('product_code', 'product_no')

此外，它似乎可能是数据库生成的错误.但是，当我在 ./manage.py shell 中执行以下操作以查看原始 SQL 查询列表时，什么都没有出现(只有一个连接并且没有命名):

>>>从 django.db 导入连接>>>连接查询[]

无论如何，最终试图到达 ProductPositions.objects.filter(product_code__code=product_code).filter(product_no=data['product_no'].description，但每一步都遇到问题.

要明确这个问题:一个字符串被传递到 varchar 字段，但错误返回 double.为什么?

解决方案

好的，在打开详细调试之后终于弄清楚了问题所在，这样我就可以实际看到正在发送的查询.问题在于它是如何加入的，它默认使用 Products 的 pk.

ProductPositions.objects.filter(product_code__code=product_c‌ ode)

基本上被翻译成:

... INNER JOIN.... ON 'ProductPositions'.'product_code' = 'Products'.'id'

我真的不想要.应该是:

... INNER JOIN.... ON 'ProductPositions'.'product_code' = 'Products'.'code'

因此解决方案是通过添加 to_field 来修复 ProductPositions 模型中的 product_code 字段.正如文档所说:

<块引用>

对于映射到模型实例的 ForeignKey 等字段，默认值应该是它们引用的字段的值(除非设置了 to_field，否则为pk)而不是模型实例.

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)

现在数据正在按预期检索并且没有错误.

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: invalid literal for int() with base 10 seems to be related to ForeignKey

I have modified my query to:

# 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: Warning: (1292, "Truncated incorrect DOUBLE value: 'A_15'")

result = self._query(query)

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

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

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

These are the models for each:

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')

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
[]

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.

And to be clear on the issue: a string is being passed to varchar field, but the error is coming back with double. Why?

解决方案

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_c‌ode)

Was basically being translated into:

... 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'

So the solution was to fix the product_code field in the ProductPositions model by adding to_field. As the documentation says:

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.

相关文章