My project use Django 2.0 database from sqlite migrate to postgresql 9.6.3, Run normal in sqilet, migrate to postgresql and running error.
我的项目使用Django 2.0数据库从sqlite迁移到postgresql 9.6.3,在sqilet中运行正常,迁移到postgresql并运行错误。
models.py :
Class WechatPay(models.Model):
user = models.CharField('网点', max_length=20, default='None')
user_group = models.CharField('渠道', max_length=20, default='None')
total_fee = models.FloatField('订单金额', default=0, decimal_places=0, max_digits=7)
card_sale = models.FloatField('售卡款', default=0, decimal_places=0, max_digits=7)
card_recharge = models.FloatField('充值款', default=0, decimal_places=0, max_digits=7)
trade_date = models.DateField('交易日期', auto_now_add=True)
views.py :
def group_list(request):
start_time = request.GET.get('start_time', datetime.today().strftime("%Y-%m-%d"))
end_time = request.GET.get('end_time', datetime.today().strftime("%Y-%m-%d"))
object_list = WechatPay.objects.values('user', 'trade_date', 'user_group').filter(
trade_date__gte=start_time).filter(
trade_date__lte=end_time).filter(
status='SUCCESS').annotate(
card_sale=Cast(Sum('card_sale'), DecimalField(decimal_places=2)),
total_fee=Cast(Sum('total_fee'), DecimalField(decimal_places=2)),
card_recharge=Cast(Sum('card_recharge'), DecimalField(decimal_places=2))
).order_by('-trade_date')
summary = WechatPay.objects.filter(
trade_date__gte=start_time).filter(
trade_date__lte=end_time).filter(
status='SUCCESS').aggregate(card_sale_sum=Cast(Sum('card_sale'), DecimalField(decimal_places=2)),
total_fee_sum=Cast(Sum('total_fee'), DecimalField(decimal_places=2)),
card_recharge_sum=Cast(Sum('card_recharge'), DecimalField(decimal_places=2)))
return render(request, 'wechatpay/data-group.html',
{'items': object_list, 'start_time': start_time, 'end_time': end_time,
'summary': summary})
error:
Traceback (most recent call last):
File "D:\workspace\venv\venv-django\lib\site-packages\django\db\backends\utils.py", line 85, in _execute
return self.cursor.execute(sql, params)
psycopg2.DataError: invalid input syntax for integer: "none"
LINE 1: ...LECT SUM("gft_wechat_pay_wechatpay"."card_sale")::numeric(No...
^
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "D:\workspace\venv\venv-django\lib\site-packages\django\core\handlers\exception.py", line 35, in inner
response = get_response(request)
File "D:\workspace\venv\venv-django\lib\site-packages\django\core\handlers\base.py", line 128, in _get_response
response = self.process_exception_by_middleware(e, request)
File "D:\workspace\venv\venv-django\lib\site-packages\django\core\handlers\base.py", line 126, in _get_response
response = wrapped_callback(request, *callback_args, **callback_kwargs)
File "D:\workspace\venv\venv-django\lib\site-packages\django\contrib\auth\decorators.py", line 21, in _wrapped_view
return view_func(request, *args, **kwargs)
File "D:\workspace\weixin_pay\gft_web\gft_wechat_pay\views.py", line 249, in group_list
card_recharge_sum=Cast(Sum('card_recharge'), DecimalField(decimal_places=2)))
File "D:\workspace\venv\venv-django\lib\site-packages\django\db\models\query.py", line 374, in aggregate
return query.get_aggregation(self.db, kwargs)
File "D:\workspace\venv\venv-django\lib\site-packages\django\db\models\sql\query.py", line 476, in get_aggregation
result = compiler.execute_sql(SINGLE)
File "D:\workspace\venv\venv-django\lib\site-packages\django\db\models\sql\compiler.py", line 1063, in execute_sql
cursor.execute(sql, params)
File "D:\workspace\venv\venv-django\lib\site-packages\django\db\backends\utils.py", line 100, in execute
return super().execute(sql, params)
File "D:\workspace\venv\venv-django\lib\site-packages\django\db\backends\utils.py", line 68, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "D:\workspace\venv\venv-django\lib\site-packages\django\db\backends\utils.py", line 77, in _execute_with_wrappers
return executor(sql, params, many, context)
File "D:\workspace\venv\venv-django\lib\site-packages\django\db\backends\utils.py", line 85, in _execute
return self.cursor.execute(sql, params)
File "D:\workspace\venv\venv-django\lib\site-packages\django\db\utils.py", line 89, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "D:\workspace\venv\venv-django\lib\site-packages\django\db\backends\utils.py", line 85, in _execute
return self.cursor.execute(sql, params)
django.db.utils.DataError: invalid input syntax for integer: "none"
LINE 1: ...LECT SUM("gft_wechat_pay_wechatpay"."card_sale")::numeric(No...
queries sql:
SELECT "gft_wechat_pay_wechatpay"."user", "gft_wechat_pay_wechatpay"."trade_date", "gft_wechat_pay_wechatpay"."user_group", SUM("gft_wechat_pay_wechatpay"."card_sale")::numeric(None, 2) AS "card_sale", SUM("gft_wechat_pay_wechatpay"."total_fee")::numeric(None, 2) AS "total_fee", SUM("gft_wechat_pay_wechatpay"."card_recharge")::numeric(None, 2) AS "card_recharge" FROM "gft_wechat_pay_wechatpay" WHERE ("gft_wechat_pay_wechatpay"."trade_date" >= 2018-01-27 AND "gft_wechat_pay_wechatpay"."trade_date" <= 2018-01-27 AND "gft_wechat_pay_wechatpay"."status" = SUCCESS) GROUP BY "gft_wechat_pay_wechatpay"."user", "gft_wechat_pay_wechatpay"."trade_date", "gft_wechat_pay_wechatpay"."user_group" ORDER BY "gft_wechat_pay_wechatpay"."trade_date" DESC
I try change models.py field type, from FloatField to DecimalField,but views save to zero. models.py:
我尝试将models.py字段类型从FloatField更改为DecimalField,但视图保存为零。 models.py:
Class WechatPay(models.Model):
user = models.CharField('网点', max_length=20, default='None')
user_group = models.CharField('渠道', max_length=20, default='None')
total_fee = models.DecimalField('订单金额', default=0, decimal_places=0, max_digits=7)
card_sale = models.DecimalField('售卡款', default=0, decimal_places=0, max_digits=7)
card_recharge = models.DecimalField('充值款', default=0, decimal_places=0, max_digits=7)
trade_date = models.DateField('交易日期', auto_now_add=True)
views.py:
def qrcode(request, cost):
""" Insert data """
trade_log = WechatPay()
total_fee = int(result[0].get('total_fee')) / 100
print(f'receive data {total_fee},date type {type(total_fee)}')
trade_log.total_fee = Decimal(total_fee)
print(trade_log.total_fee)
print(type(trade_log.total_fee))
trade_log.save()
Console:
receive data 0.21,date type <class 'float'>
0.2099999999999999922284388276239042170345783233642578125
<class 'decimal.Decimal'>
But database save "total_fee" field is 0. Please help. Thanks in advance.
但数据库保存“total_fee”字段为0.请帮助。提前致谢。
1 个解决方案
#1
0
One potential issue here could be how you're trying to store NULL values with the database. In Django/Python, you pass in the NULL values using None. This needs to be converted to NULL when you execute your actual SQL statement, which Django should handle under-the-covers. That may be the reason for your initial syntax error above.
这里的一个潜在问题可能是您如何尝试在数据库中存储NULL值。在Django / Python中,使用None传递NULL值。当您执行实际的SQL语句时,需要将其转换为NULL,Django应该在其下进行处理。这可能是您上面的初始语法错误的原因。
If you get past the syntax error, try changing this line:
如果您遇到语法错误,请尝试更改此行:
total_fee = int(result[0].get('total_fee')) / 100
Instead of 100 try using 100.00 and see if that retains your decimal precision when you update the DB.
而不是100尝试使用100.00并查看更新数据库时是否保留小数精度。
If that doesn't work, then look at this line:
如果这不起作用,那么看看这一行:
total_fee = models.DecimalField('订单金额', default=0, decimal_places=0, max_digits=7)
Try changing "default=0" to "default=999". If you run your code and see the value set to 999, then that means no value was passed in and that gives a place to start looking.
尝试将“default = 0”更改为“default = 999”。如果你运行你的代码并看到设置为999的值,那么这意味着没有传入任何值,这给了一个开始寻找的地方。
#1
0
One potential issue here could be how you're trying to store NULL values with the database. In Django/Python, you pass in the NULL values using None. This needs to be converted to NULL when you execute your actual SQL statement, which Django should handle under-the-covers. That may be the reason for your initial syntax error above.
这里的一个潜在问题可能是您如何尝试在数据库中存储NULL值。在Django / Python中,使用None传递NULL值。当您执行实际的SQL语句时,需要将其转换为NULL,Django应该在其下进行处理。这可能是您上面的初始语法错误的原因。
If you get past the syntax error, try changing this line:
如果您遇到语法错误,请尝试更改此行:
total_fee = int(result[0].get('total_fee')) / 100
Instead of 100 try using 100.00 and see if that retains your decimal precision when you update the DB.
而不是100尝试使用100.00并查看更新数据库时是否保留小数精度。
If that doesn't work, then look at this line:
如果这不起作用,那么看看这一行:
total_fee = models.DecimalField('订单金额', default=0, decimal_places=0, max_digits=7)
Try changing "default=0" to "default=999". If you run your code and see the value set to 999, then that means no value was passed in and that gives a place to start looking.
尝试将“default = 0”更改为“default = 999”。如果你运行你的代码并看到设置为999的值,那么这意味着没有传入任何值,这给了一个开始寻找的地方。