Question 136799 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Using a function in a loop in Python

My aim is to get the last price from a histoy table (History_price) and inject it in a parameter table (param_forex).
I have defined the following function:

    mycursor.execute(f"UPDATE `param_forex` SET `Rate` = %s, `Update_Time` = CURRENT_TIME() WHERE `param_forex`.`Ticker` LIKE %s", 
                     (Rate, Ticker))
    mydb.commit()
    mycursor.close

The function works on its own. It simply fills a table with new prices on some particular lines.
Now I try to incorporate it in my loop. It’s actually a double loop because of how mycursor works in mysql.connector

def fillpricetable_assetid() :
        for x in  ['EURUSD','EURJPY','EURGBP']:
            mycursor.execute(f"SELECT `Price` FROM `History_price` WHERE `Ticker` LIKE '{x}' ORDER BY `History_price`.`Time` DESC LIMIT 1")
            for y in mycursor:
                updateForexdb(x.lower(),y[0])

And it does not work anymore…
I get

  File "c:...library_import.py", line 116, in <module>
    fillpricetable_assetid()
  File "c:...library_import.py", line 89, in fillpricetable_assetid
    updateForexdb(x.lower(),y[0])
NameError: name 'updateForexdb' is not defined

How is that possible, when I have just defined it above??

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I have interverted the order and now it works fine.


  2. 0

    Instead of running multiple queries in a loop, why not just join the tables?

    MySQL (any version):

    UPDATE param_forex pf
JOIN History_price hp ON pf.Ticker = hp.Ticker
JOIN (
    SELECT Ticker, MAX(Time) AS max_time
    FROM History_price
    WHERE Ticker IN (?, ?, ?)
    GROUP BY Ticker
) hp_max ON hp.Ticker = hp_max.Ticker AND hp.Time = hp_max.max_time
SET pf.Rate = hp.Price, pf.Update_Time = NOW();

    MySQL (>= 8.0):

    UPDATE param_forex pf
JOIN (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Ticker ORDER BY Time DESC) rn
    FROM History_price
    WHERE Ticker IN (?, ?, ?)
    -- interval within which you know you have latest price
    AND Time > CURRENT_DATE - INTERVAL 3 DAY
) hp ON pf.Ticker = hp.Ticker AND hp.rn = 1
SET pf.Rate = hp.Price, pf.Update_Time = NOW();

    Or, even better, don’t store the redundant xrate values in param_forex and just access directly from History_price. Make sure you have an index on (Ticker, Time).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search