Intelligently inserting or updating records

08 Jun 2020

Imagine a scenario where you’ve got game backed by a simple database of names and scores

from utils import connect_to_db

with connect_to_db('test.db') as cursor:
    cursor.execute('''drop table if exists records''')
    cursor.execute('''
        create table records (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT,
            value INTEGER
        )
        '''
    )

And on the first day, Nick has 1, Ben has 2, and Adam has 3

with connect_to_db('test.db') as cursor:
    cursor.execute('''insert into records (name, value) values ('Nick', 1)''')
    cursor.execute('''insert into records (name, value) values ('Ben', 2)''')
    cursor.execute('''insert into records (name, value) values ('Adam', 3)''')

with connect_to_db('test.db') as cursor:
    cursor.execute('select * from records')
    print(cursor.fetchall())

Going forward, we want to write a function that will take a tuple of Name, Score and either:

Update an existing record with the score
Make a new record if it doesn’t exist

And so the trick is to do a quick name lookup before trying to write anything, like so:

def process_record(name, score):
    with connect_to_db('test.db') as cursor:
        cursor.execute('select * from records where name= (?)', (name,))
        row = cursor.fetchone()
        
        if row:
            cursor.execute('''UPDATE records
                              SET value=(?)
                              WHERE name=(?)''',
                           (score, name))
        else:
            cursor.execute('''INSERT INTO records (name, value)
                              VALUES (?, ?)''', (name, score))

Thus, we can process records for multiple days worth of games

process_record('Nick', 3)
process_record('Adam', 4)
process_record('Ben', 5)
process_record('PJ', 10)
process_record('Adam', 11)

And see that Nick, Adam, and Ben all have updated records, as well as the the introduction of PJ to the game.

with connect_to_db('test.db') as cursor:
    cursor.execute('select * from records')
    print(cursor.fetchall())