SQL executemany() with python and an array and ON DUPLICATE KEY

Question

I am struggling to use ON DUPLICATE KEY with my current code:

def upload_to_database(ticker_collection):
  trend_data = []
  trend_data_table = "trend_data"
  trend_data_columns = "Ticker, Subreddit, Score, Rockets, Date"
  
  trend_data_sql = "INSERT INTO " + trend_data_table +\
                   " (" + trend_data_columns + ") VALUES (%s, %s, %s, %s, %s) " +\
                   "ON DUPLICATE KEY UPDATE " +\
                   "Score = Score + %s, " +\
                   "Rockets = Rockets + %s"

  for ticker in ticker_collection:
    ticker_subreddit = ticker_collection[ticker]['subreddit']
    ticker_score = int(ticker_collection[ticker]['score'])
    ticker_rockets = int(ticker_collection[ticker]['rockets'])
    insert_date = datetime.date(datetime.now(est))
    
    ticker_data = (ticker, ticker_subreddit, ticker_score, ticker_rockets, insert_date, 1, 1)

    trend_data.append(ticker_data)
  
  the_db_cursor.executemany(trend_data_sql, trend_data)
  the_database.commit()
  
  return the_db_cursor.rowcount, "was inserted."

trend_data_sql is my query and towards the end there is Score and Rockets, effectively if an entry with the same Ticker and DateTime (my unique keys) exists I only want to update the score and rockets for that entry.

However, I am trying to do this with executemany() to save on db performance. I am a little stuck on how to incorporate ON DUPLICATE KEY with the for loop and executemany().

Any guidance or advice would be much appreciated.

Table Structure:

CREATE TABLE `trend_data` (
  `Ticker` varchar(255) NOT NULL,
  `Subreddit` varchar(255) NOT NULL,
  `Score` int(11) NOT NULL,
  `Rockets` int(11) NOT NULL,
  `Date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Table Indexes:

ALTER TABLE `trend_data`
  ADD UNIQUE KEY `Unique_Keys` (`Ticker`,`Date`,`Subreddit`) USING BTREE;

ghchoi · Accepted Answer · 2021-02-06 15:23:33Z

1

Please try this:

  trend_data_sql = "INSERT INTO " + trend_data_table +\
                   " (" + trend_data_columns + ") VALUES (%s, %s, %s, %s) " +\
                   "ON DUPLICATE KEY UPDATE " +\ 
                   "Score = Score + %s, " +\
                   "Rockets = Rockets + %s"

with

  for ticker in ticker_collection:
    ...

    value = ...

    ...
    
    ticker_data = (ticker, ticker_subreddit, ticker_score, ticker_rockets, value, value)

    trend_data.append(ticker_data)

It was tested on MariaDB. If not working because you're using MySQL, I think you can try the following. (I could not test because I do not have MySQL though...)

  trend_data_sql = "INSERT INTO " + trend_data_table +\
                   " (" + trend_data_columns + ") VALUES (%(ticker)s, %(reddit)s, %(score)s, %(rocket)s, %(date)s) " +\
                   "ON DUPLICATE KEY UPDATE " +\
                   "Score = Score + %(value)s, " +\
                   "Rockets = Rockets + %(value)s"

  for ticker in ticker_collection:
    ...
        
    ticker_data = {
      'ticker': ticker,
      'reddit': ticker_subreddit, 
      'score': ticker_score, 
      'rocket': ticker_rockets, 
      'date': insert_date, 
      'value': 1
    }    

    trend_data.append(ticker_data)
  
  the_db_cursor.executemany(trend_data_sql, trend_data)
  the_database.commit()
  
  return the_db_cursor.rowcount, "was inserted."


ticker_collection = {
    'a': {'subreddit': 'aa', 'score': 1, 'rockets': 11},
    'b': {'subreddit': 'bb', 'score': 2, 'rockets': 22},
    'c': {'subreddit': 'cc', 'score': 3, 'rockets': 33},
    'd': {'subreddit': 'dd', 'score': 4, 'rockets': 44}
}

print(upload_to_database(ticker_collection))

edited Feb 6, 2021 at 15:23

answered Feb 6, 2021 at 3:28

ghchoi

5,2064 gold badges38 silver badges59 bronze badges

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

29 Comments

ghchoi Over a year ago

@alphadmon What's the variable VALUE in your example? If something else, you should append them to trend_data as well.

ghchoi Over a year ago

@alphadmon I made an edit. Please check it. :)

ghchoi Over a year ago

@alphadmon I checked it again. It works fine. Please replace your trend_data_sql by copy and paste, and modify ticker_data as well. You can replace value with simply 1 to test if it works.

ghchoi Over a year ago

@alphadmon Share me your table schema. And, do you reuse ticker_score and ticker_rockets as values?

ghchoi Over a year ago

@alphadmon I tested with edited code. It works fine. I added my testcase as well. Please check if your data have different properties with mine.

|

Collectives™ on Stack Overflow

SQL executemany() with python and an array and ON DUPLICATE KEY

1 Answer 1

29 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

29 Comments

Your Answer

Sign up or log in

Post as a guest

Related