问题:使用pymysql查询数据,DATE类型的数据都显示None,Hour转换后的数据显示为0,以及部分计算的数据带有Decimal
环境:Python3.10.0,pymysql 1.0.2
代码块:
shopify_item_sql = \
"""
SELECT
'shopify' platform,
osli.sku,
os.site siteName,
os.site_id siteId,
DATE(FROM_UNIXTIME(substr(os.created_at, 1, 10),'%%Y-%%m-%%d %%H:%%i:%%s')) day,
HOUR(FROM_UNIXTIME(substr(os.created_at, 1, 10),'%%Y-%%m-%%d %%H:%%i:%%s')) hour,
sum(os.total_price_usd*(osli.price*osli.quantity/os.total_line_items_price)) amount,
sum(osli.quantity) quantity,
count(distinct os.order_id) orderNum,
sum(osli.quantity) skuNum
FROM sak_rawdata.ord_shopify os RIGHT JOIN sak_rawdata.ord_shopify_line_items osli ON os.id = osli.ord_shopify_id
WHERE osli.sku is not null
and os.id IN ({})
and os.financial_status = 'paid'
AND osli.sku like 'CP33783%%'
GROUP BY osli.sku, os.site_id, DATE(FROM_UNIXTIME(substr(os.created_at, 1, 10),'%%Y-%%m-%%d %%H:%%i:%%s')), HOUR(FROM_UNIXTIME(substr(os.created_at, 1, 10),'%%Y-%%m-%%d %%H:%%i:%%s'));
""".format(",".join(["'%s'" % i.strip() for i in ids]))
items = execute_sql(shopify_item_sql)
print(items)
运行结果:
请教一下这是怎么回事