Check existence of a row then update a column

Question

not too experienced with SQL, wondering if I can make this better... I am trying to check if a row exists and then add one to the frequency column if it does, if it does not exist it should return false...

The table is indexed by the column in the first select statement

I know about the risks of using formatting with public facing code, but this is for processing on a local machine only

    def check_word(self, word_name):
        self.c.execute('SELECT * FROM {tn} WHERE {cn} = """{wn}"""'.format(tn=self.table1, cn=self.column1, wn=word_name))
        exist = self.c.fetchall()
        if exist:
            new_freq = exist[0][2] + 1
            self.c.execute(
            'UPDATE {tn} SET {c2n}={en} WHERE {c1n}="""{word}"""'.format(tn=self.table1, c2n=self.column2,
                                                                         en=new_freq, c1n=self.column1,
                                                                         word=word_name))
            return True
        return False

Answer 1

Obviously, you already know about SQL injection. So let's forget about that. Ideally, please do defend against it, it's easy enough that it's not worth doing it any other way. Code that is not public facing now, might be next week.

Some other things:

You use fetchall instead of fetchone. fetchall has to load all rows into memory (I assume), which is pretty expensive. Don't use it.

Also, you use SELECT *, which needs to load all columns (of the relevant rows) into memory. Again, expensive.

def check_word(self, word_name):
    self.c.execute('SELECT {c2n} FROM {tn} WHERE {c1n} = """{wn}"""'.format(tn=self.table1, c2n=self.column2, c1n=self.column1, word=word_name))
    data = self.c.fetchone()
    if data is not None:
        new_freq = data[0] + 1
        self.c.execute(
            'UPDATE {tn} SET {c2n}={en} WHERE {c1n}="""{word}"""'.format(tn=self.table1, c2n=self.column2,
                                                                     en=new_freq, c1n=self.column1,
                                                                     word=word_name))
        return True
    return False

Also, there is a bit of a trouble with race-conditions here, but that's probably easily solvable using transactions and locking. Or, better, use 1 query (which is also faster!)

def check_word(self, word_name):
    query = 'UPDATE {table_name} SET {counter} = {counter} + 1 WHERE {word_column} = ?'.format(
        table_name=self.table1,
        counter=self.column2,
        word_column=self.column1,
    )
    self.c.execute(query, word_name)
    return self.c.rowcount > 0

(Here, I use the rowcount attribute of a cursor which returns the number of rows changed in the last query: https://docs.python.org/2/library/sqlite3.html#sqlite3.Cursor.rowcount).

I tried to use more sensible names for the columns, and also used string-interpolation for the values that are hopefully not under user control (and can't be done using parametrized queries anyhow), while using paramatrisation for the word_name which is more likely to change.

Answer 2

Use parameterized queries!

There are only very few situations where you would build SQL queries as strings then execute the strings as you are doing, and this is certainly not one of them. What you wrote is, for all intents and purposes, dynamic SQL, which very often should not be used, as it is inefficient and difficult to debug, in addition to potentially opening doors for injection.

Read: The Curse and Blessings of Dynamic SQL

_{An SQL text by Erland Sommarskog, SQL Server MVP. Latest revision: 2015-04-14.}

---

In your case, it appears that you already know what table and column(s) you will need ("The table is indexed by the column in the first select statement"), so just write parameterized statements accordingly. If you need different queries, just write new Python code for those as well. This will keep your calling code much cleaner and overall make your code more maintainable.

self.c.execute('SELECT * FROM YourTable WHERE YourWordColumn = ?', (word_name))
# ...
self.c.execute('UPDATE YourTable SET YourFreqColumn = ? WHERE YourWordColumn = ?', (new_freq, word_name))
    

Alternatively, use named parameters or another paramstyle:

self.c.execute('SELECT * FROM YourTable WHERE YourWordColumn = :word', {"word": word_name})
# ...
self.c.execute('UPDATE YourTable SET YourFreqColumn = :freq WHERE YourWordColumn = :word', {"freq": new_freq, "word": word_name})