1

I am trying to get results from mysql database using python3 and having some issues retrieving the values.

MySQL table:

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

Following is the expected result in MySQL

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

I am using following MySQL to get results mentioned in expected results above:

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     

I have following python function which is running query mentioned above with where clause

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)   

I am getting following when I do pprint or print on calcStockBalance

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

What I am doing wrong here ?

I have also tried the for loop like this

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

I get same Error

Any guidance will be appreciated. I am stuck at this point.

4
  • Please post the full error traceback. Commented Apr 23, 2018 at 17:58
  • That's all I am getting in console "a bytes-like object is required, not 'tuple'" Looks like query does execute gracefully, but the values I am getting are some kind of object Commented Apr 23, 2018 at 18:00
  • On what line is the error? Commented Apr 23, 2018 at 18:01
  • Error comes on line pprint.pprint(calcStockBalance) Commented Apr 23, 2018 at 18:01

1 Answer 1

1

You only have a small typo.

Change this line:

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

To this line:

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

Then it should work.

Sign up to request clarification or add additional context in comments.

1 Comment

thanks, Following worked "WHERE t1.stock = '%s'"%(Stock,))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.