MySQL with JOIN in Python

Question

I have a table (history_price) with 2 columns (asset_id and volume) with a lot of NULL lines that I want to fill.

1- I have the following to identify the NULL

    mycursor.execute(f"SELECT `Ticker`,`Price`,`Quantity` FROM `history_price` WHERE `volume` IS NULL;")

2- I have the following to find asset_id for each ticker

    mycursor.execute(f"SELECT `asset_id`  FROM `param_asset` WHERE `param_asset`.`Ticker` LIKE '{ticker}';")
    for y in mycursor:
        result = y[0]
        return result

3- I have the following to fill asset_id

    mycursor.execute(f"INSERT INTO `history_price` (asset_id) VALUES (%s)",

4- I have the following to calculate the volume

    volume = float(price) * float(quantity)

5-I have the following to insert the volume in the table

    mycursor.execute(f"INSERT INTO `history_price` (volume) VALUES (%s)",

Right now they are running as multiple queries in multiple loops, but I am sure I can just join the tables, calculate and fill in one single neat query... Help please

I don't see how these are related. The two `INSERT` queries create different rows. — Barmar, May 03 '23 at 16:10
Please post your current loop that executes these statements, so we can see how you're passing results from the `select` into the `INSERT` statements. — Barmar, May 03 '23 at 16:11
If you want to fill in NULL values in existing rows you should be using `UPDATE`, not `INSERT`. See https://stackoverflow.com/questions/12394506/mysql-update-table-based-on-another-tables-value — Barmar, May 03 '23 at 16:12
What is the point of having both `asset_id` and `Ticker` as identifiers? As `hp.volume` is just `hp.price * hp.quantity`, perhaps it would be better to add it as a generated column instead of storing it redundantly? — user1191247, May 03 '23 at 17:50
UPDATE `History` SET `History`.`volume` = (`History`.`Price` * History`.`Quantity`) WHERE `History`.`volume` IS NULL was the most efficient way to do it. Thanks — Mat, May 03 '23 at 17:59

score 0 · Accepted Answer · edited May 03 '23 at 18:01

0

UPDATE History 
SET History.volume = (History.Price * History.Quantity)
WHERE History.volume IS NULL

was the most efficient way to do it

edited May 03 '23 at 18:01

Barmar

741,623
53
500
612

answered May 03 '23 at 17:59

Mat

65
6

Where is `param_asset` in this? – Barmar May 03 '23 at 18:01
Why did you change table names? What happened to `history_price`? – Barmar May 03 '23 at 18:02
Sorry , I should not have changed the table nalme for clarity purposes. I wanted to do to many things at once. I have simplified it with 2 different actions. On one end the (History.Price * History.Quantity) for the volume, and the volume * exchange rate for the EUR – Mat May 03 '23 at 18:07

MySQL with JOIN in Python

1 Answers1