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
  • Welcome to Code Review! I hope you get some good answers. – Phrancis Mar 29 '16 at 17:11
  • if you only need a certain number of columns, rather than storing the entire table in memory use SELECT col_a, col_b FROM table WHERE (Some conditions)rather than SELECT * FROM table WHERE (Some conditions) – mburke05 Mar 29 '16 at 17:15
  • Even without the threat of SQL injection attacks, why risk having your script break for input with quotes if it's absolutely trivial to fix? – Blender Mar 29 '16 at 19:38
up vote 6 down vote accepted

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.

  • This is what I am looking for except I am getting an error with the row_count....AttributeError: 'sqlite3.Cursor' object has no attribute 'row_count' – deltaskelta Mar 29 '16 at 17:36
  • I could get around it with a try statement, but IDK if that is smart – deltaskelta Mar 29 '16 at 17:37
  • 1
    @deltaskelta: I made a typo. It's rowcount, not row_count. – Sjoerd Job Postmus Mar 29 '16 at 17:38
  • Oh I went through the docs and missed that as well – deltaskelta Mar 29 '16 at 17:39
  • thanks for this answer, I tried to do it all in one step myself before this answer, but failed. Thanks for the code – deltaskelta Mar 29 '16 at 20:21

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})

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.