python - 仅当组中的最新记录不同时才插入表中(Insert into table only if most recent record from group is different)

Question

I have a MySQL table with the following columns:

(我有一个包含以下各列的MySQL表：)

score_id (int10, auto_increment, primary key); 
game_id (int10); 
away_team_score (int10); 
home_team_score (int10); 
date_time (datetime);

I am scraping a web API (using python) which I am trying to write an array to this database.

(我正在抓取一个Web API（使用python），试图将一个数组写入此数据库。)

However, each time I read this API it provides a list of all events.

(但是，每次阅读此API时，它都会提供所有事件的列表。)

I am trying to write to this database only when there is a difference in either the away_team_score or the home_team_score for each game_id.

(我只尝试在每个game_id的away_team_score或home_team_score中存在差异时才写入此数据库。)

I am able to get the most-recent records using the query from this example ( mySQL GROUP, most recent ).

(我可以使用本示例中的查询（ mySQL GROUP，最新的 ）来获取最新记录。)

However, I am unsure on how to check if the values that I am inserting are the same.

(但是，我不确定如何检查我插入的值是否相同。)

I don't want to use update because I want to keep the scores for historical purposes.

(我不想使用更新，因为我想保留分数用于历史目的。)

Also, if the game_id does not exist, it should insert it.

(另外，如果game_id不存在，则应将其插入。)

My python code I currently have:

(我目前拥有的python代码：)

# Connecting to the mysql database
mydb = mysql.connector.connect(host="examplehost", user="exampleuser", passwd="examplepassword", database="exampledb")
mycursor = mydb.cursor()
# example scores array that I scraped
# It is in the format of game_id, away_team_score, home_team_score, date_time
scores = [[1, 0, 1, '2019-11-30 13:05:00'], [2, 1, 5, '2019-11-30 13:05:00'], [3, 4, 8, '2019-11-30 13:05:00'],
          [4, 6, 1, '2019-11-30 13:05:00'], [5, 0, 2, '2019-11-30 13:05:00']]

# Inserting into database
game_query = "INSERT INTO tbl_scores (game_id, away_team_score, home_team_score, date_time) VALUES (%s, %s, %s, %s)"

mycursor.executemany(game_query, scores)
mydb.commit()

mydb.close()

  ask by cfb_moose translate from so

Answer 1

You need to make use of UPSERT functionality in MySQL.

(您需要在MySQL中使用UPSERT功能。)

Changing your insert query to the following query will only insert when there is new game-id, else it will update the scores:

(将您的插入查询更改为以下查询将仅在有新的游戏ID时插入，否则将更新得分：)

INSERT INTO tbl_scores
    (game_id, score_id, away_team_score, home_team_score, date_time)
VALUES
    (game_id, score_id, away_team_score, home_team_score, date_time)
ON DUPLICATE KEY UPDATE
    game_id = game_id,
    away_team_score = away_team_score,
    home_team_score = home_team_score,
    date_time = date_time;

Details on upsert - https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

(在UPSERT详情- https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html)

Let me know if that helps.

(让我知道是否有帮助。)