问题

I want to INSERT INTO MySQL or UPDATE MySQL if exist without having to run 2 different queries.

我想要在MySQL中插入或更新数据，如果已存在则不必运行两个不同的查询。

I have checked
https://stackoverflow.com/questions/696190/create-if-an-entry-if-it-doesnt-exist-otherwise-update

我已经查看了https://stackoverflow.com/questions/696190/create-if-an-entry-if-it-doesnt-exist-otherwise-update

INSERT INTO table (a) VALUES (0) ON DUPLICATE KEY UPDATE

INSERT INTO表（a）VALUES（0）ON DUPLICATE KEY UPDATE

but it only works if you check doubles on the key.
The issue is I want to check on another field that is not the key, but yet has so stay unique.
I want to ADD or UPDATE on checking the 'symbol' in my dictionary:

但是它只在检查键上的重复项时起作用。
问题是我想要检查另一个不是键的字段，但它必须保持唯一。
我想要在检查我的字典中的“symbol”时进行添加或更新：

The data I get is (example)

forex = [{'symbol': 'EURUSD', 'price': '1.06763000'}, {'symbol': 'GBPEUR', 'price': '0.90339600'}, {'symbol': 'EURJPYC', 'price': '0.000011200'}]

我得到的数据是（示例）

forex = [{'symbol': 'EURUSD', 'price': '1.06763000'}, {'symbol': 'GBPEUR', 'price': '0.90339600'}, {'symbol': 'EURJPYC', 'price': '0.000011200'}]

The INSERT INTO I want to adapt is:

我想要适应的INSERT INTO是：

mycursor.executemany(f"INSERT INTO `param_forex` (Ticker,Price) 
                    VALUES ( %(symbol)s, %(price)s)", forex)
mydb.commit()

我想要适应的INSERT INTO是：

mycursor.executemany(f"INSERT INTO `param_forex` (Ticker,Price) 
                    VALUES ( %(symbol)s, %(price)s)", forex)
mydb.commit()

I do NOT want to use the REPLACE because of the auto increment field (REPLACE deletes and inserts)
The column I am checking for duplicates is Ticker in the table and symbol in the dictionary.

我不想使用REPLACE，因为它会删除并插入自动递增字段。
我要检查重复项的列是表中的“Ticker”和字典中的“symbol”。

I am stuck. Is there a solution?

我卡住了。有解决方案吗？

INSERT INTO table (a) VALUES (0)  ON DUPLICATE KEY UPDATE

forex = [{'symbol': 'EURUSD', 'price': '1.06763000'}, {'symbol': 'GBPEUR', 'price': '0.90339600'}, {'symbol': 'EURJPYC', 'price': '0.000011200'}]

mycursor.executemany(f"INSERT INTO `param_forex` (Ticker,Price) 
                    VALUES ( %(symbol)s, %(price)s)", forex)
mydb.commit()

答案1

得分: 1

你可以使用INSERT ... ON DUPLICATE KEY UPDATE语句，但如评论中所提到的，你需要在'Ticker'列上拥有一个唯一索引。例如：

forex = [{'symbol': 'EURUSD', 'price': '1.06763000'}, {'symbol': 'GBPEUR', 'price': '0.90339600'}, {'symbol': 'EURJPYC', 'price': '0.000011200'}]

query = "INSERT INTO `param_forex` (Ticker, Price) VALUES (%(symbol)s, %(price)s) ON DUPLICATE KEY UPDATE Price = VALUES(Price)"

mycursor.executemany(query, forex)
mydb.commit()

要使此操作生效，只需修改表以包含唯一键：

ALTER TABLE `param_forex` ADD UNIQUE INDEX `idx_Ticker` (`Ticker`);

forex = [{'symbol': 'EURUSD', 'price': '1.06763000'}, {'symbol': 'GBPEUR', 'price': '0.90339600'}, {'symbol': 'EURJPYC', 'price': '0.000011200'}]

query = "INSERT INTO `param_forex` (Ticker, Price) VALUES (%(symbol)s, %(price)s) ON DUPLICATE KEY UPDATE Price = VALUES(Price)"

mycursor.executemany(query, forex)
mydb.commit()

ALTER TABLE `param_forex` ADD UNIQUE INDEX `idx_Ticker` (`Ticker`);