MYSQL no duplicate of 2 values

I couldn’t find an answer for this exact question although I am sure the information is on here somewhere.

I have a python script that is adding stock data into a database. For each stock database has ~1000 lines where ‘ticker’ is the stock name and ‘time’ is the date(1 for each day).

When I run my update script I want to replace the most recent line from database in case it changed before the day ended.

Is there a way to make ‘time’ be unique when compared to ‘ticker’?

using this sql I am able to sort and only get the data I want, but it doesn’t remove the excess from the database.

SELECT distinct open, high, low, close, volume, time FROM security WHERE security.ticker='%s' order by time DESC limit 100" % ticker

Answer

You can create a unique index on (ticket, time) (it is rather confusing that something called time represents a date without a time component, while column date has a date and a time… but it seems that’s how your data model is structured).

create unique index on security(ticker, date);

Then, you can use the insert ... on duplicate key syntax in your script. That would look like:

insert into security (ticker, time, date, open, ... )
values (:ticker, :time, :date, :open)
on duplicate key update 
    date = values(date),
    open = values(open),
    ...