使用pymysql查询出的日期为None问题

Question

问题：使用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)

运行结果：

请教一下这是怎么回事

Answer 1

方案来自 梦想橡皮擦 狂飙组基于 GPT 编写的 “程秘”

---

可以将 execute_sql 返回的结果遍历并处理成合适的数据类型，如下代码示例：

for item in items:
    platform = item[0]
    sku = item[1]
    siteName = item[2]
    siteId = item[3]
    day = item[4]
    hour = item[5]
    amount = float(item[6])
    quantity = int(item[7])
    orderNum = int(item[8])
    skuNum = int(item[9])
    # do something with the data