使用复杂的mysql查询python返回的值不正确

Question

我尝试使用python3从mysql数据库获取结果，但在检索值时遇到了一些问题。你知道吗

MySQL表：

Stock       Date          Price
GOOG        2012-05-20    402
GOOG        2012-05-21    432
APPL        2012-05-20    553
APPL        2012-05-21    590
FB          2012-05-20     40
FB          2012-05-21     34

下面是MySQL中的预期结果

Stock       Date          Price Change
GOOG        2012-05-21    30
APPL        2012-05-21    37
FB          2012-05-21    -6

我正在使用以下MySQL来获得上述预期结果中提到的结果：

SELECT t1.stock, 
    t1.date                      AS CurrentDate, 
    oldDate.date                 AS OldDate, 
    ( t1.price - oldDate.price ) AS PriceChange 
FROM   mp_stocktable t1 
    JOIN (SELECT t2.stock, 
                    t2.date              AS date1, 
                    (SELECT Max(t3.date) 
                    FROM   mp_stocktable t3 
                    WHERE  t3.date < t2.date 
                            AND t3.stock = t2.stock 
                    GROUP  BY t3.stock) AS date2 
            FROM   mp_stocktable t2) AS StockDates 
        ON StockDates.stock = t1.stock 
            AND StockDates.date1 = t1.date 
    JOIN mp_stocktable oldDate 
        ON oldDate.stock = t1.stock 
            AND oldDate.date = StockDates.date2     

我有下面的python函数，它用where子句运行上面提到的查询

def checkStockSold(db,stock):
    print("*************checkStockSold*************")
    try:
        curs = db.cursor()
        query = ("
            SELECT t1.stock, 
        t1.date                      AS CurrentDate, 
        oldDate.date                 AS OldDate, 
        ( t1.price - oldDate.price ) AS PriceChange 
    FROM   mp_stocktable t1 
        JOIN (SELECT t2.stock, 
                        t2.date              AS date1, 
                        (SELECT Max(t3.date) 
                        FROM   mp_stocktable t3 
                        WHERE  t3.date < t2.date 
                                AND t3.stock = t2.stock 
                        GROUP  BY t3.stock) AS date2 
                FROM   mp_stocktable t2) AS StockDates 
            ON StockDates.stock = t1.stock 
                AND StockDates.date1 = t1.date 
        JOIN mp_stocktable oldDate 
            ON oldDate.stock = t1.stock 
                AND oldDate.date = StockDates.date2"
                "WHERE t1.stock = %s",(Stock,))
        print(query)                
        curs.execute(query)
        for calcStockBalance in curs:
            pprint.pprint(calcStockBalance)
            return calcStockBalance
    except TypeError as e:
            print(e)   

当我在calcStockBalance上打印或打印时，我得到了以下信息

a bytes-like object is required, not 'tuple'

我做错什么了？你知道吗

我也试过这样的for循环

for stock, currentdate, olddate, calcStockBalance in curs:
    pprint.pprint(stock, currentdate, olddate, calcStockBalance)
    return stock, currentdate, olddate, calcStockBalance

我也有同样的错误

任何指导都将不胜感激。我被困在这一点上。你知道吗

Answer 1

你只有一个小的打字错误。你知道吗

更改此行：

"WHERE t1.stock = %s",(Stock,))

到这行：

"WHERE t1.stock = %s"%(Stock,))

那就行了。你知道吗