Power By 专否社区

Changing MySQL checks from 'programside' to the MySQL server itself

Go To StackoverFlow.com

0

So I'm doing a lot of inserts which only I want to insert into a certain table when the name doesn't "exist" in the table yet, i.e. I don't want to have any duplicates. I'm approaching it this way now:

def create_artist(artist_name):
    artistid = has_artist(artist_name)
    if not artistid:
        sql['cursor'].execute("INSERT INTO artists VALUES (NULL, ?)", (artist_name,))
        artistid = has_artist(artist_name)
    return artistid[0]

def has_artist(artist_name):
    sql['cursor'].execute("SELECT id FROM artists WHERE artist_name = ?", (artist_name,))
    return (sql['cursor'].fetchone())

It basically looks up if there is an artist with the same name in the table, if not, it inserts one and else it just returns the lookup. There has to be a better way of doing this, is it possible to move this whole process into a query so I will be able to move this all to SQL?

2012-04-03 20:38
by wvd

2

Look into INSERT IGNORE. This will require you to have a UNIQUE index on your table that will cause the IGNORE to trigger.

INSERT IGNORE INTO artists VALUES (NULL, ?)
2012-04-03 20:43
by scwagner
This seems to work very fine, it's just "INSERT or IGNORE" into SQLITE. Is it possible to also select the row which just was inserted or was already there - wvd 2012-04-03 20:50
Sorry, your title included MySQL, so I was answering it for MySQL, not SQLITE. By adding IGNORE to the example you have, then it would work exactly as you want -- you run the select after the INSERT IGNORE, and you'll get the ID of the row that was either already there or just inserted - scwagner 2012-04-03 20:52
Ah so I'll keep having two inserts, but that's fine. Saves me a lot of code, and sorry for the confusion. Accepted this one. Thank you very much - wvd 2012-04-03 20:53
You can change your code to remove that whole first call to hasartist and if not artistid if you want, and just do the INSERT IGNORE and then call hasartist every time if you don't want to double calls to get the ID. But I suspect a majority of the time you will be getting an existing value, so the way you have it now should be better in the long run.. - scwagner 2012-04-03 20:54
Ads