SQLite / Python database retrieval and comparison

Question

At the moment, I have a database which contains username, password, etc. I am wishing to look into the database to check if duplicates are in there.

con = lite.connect('userInfo.db')
with con:
    cur = con.cursor()
    cur.execute("SELECT * FROM Users WHERE LOWER(Username) = LOWER(?)", (newID,))
    rows = cur.fetchall()
    if len(rows)!=0:
        return "Duplicate detected"

Here is my code at the moment. newID is a new name and I wish to check if there are any existing entries in the database with the same name.

My question is - is the way I am doing it in my code a good idea? I'm mainly concerned with my approach. Should I be using something other than fetchall() ?

Thank you for your time! :) P.S. This is for a website application.

Answer 1

Here's a way to do exactly what you asked for - find out if a given username already exists:

import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute ("""
              CREATE TABLE users (
                  uid INTEGER PRIMARY KEY AUTOINCREMENT,
                  username TEXT UNIQUE,
                  email TEXT UNIQUE );
             """)

test_users = (
    {'username':"Alice",    'email':"Alice@mail.com"},
    {'username':"Billy",    'email':"Billy@mail.com"},
    {'username':"Charles",  'email':"Charles@mail.com"},
    {'username':"Dick",     'email':"Dick@mail.com"},
    {'username':"Emily",    'email':"Emily@mail.com"},
    {'username':"Faramir",  'email':"Faramir@mail.com"},
)

for user in test_users:
    conn.execute("INSERT INTO users (username, email) VALUES (?,?)",
                 (user['username'],user['email'])
                )

result = conn.execute("SELECT COUNT(*) FROM users WHERE username='Alice'")
number_of_Alices = result.next()[0] # number_of_Alices will be 1

Since all you want is a COUNT this is adequate.

---

Really, though, you shouldn't be enforcing the uniqueness of the usernames yourself. Let the database do that for you by specifying the field to be either UNIQUE or PRIMARY KEY.

If you try to insert "Alice", "alice@wonderland.com" after creating the database like above, this will get you an sqlite3.IntegrityError:

>>> conn.execute("""INSERT INTO users (username, email)
...                     VALUES ("Alice", "alice@wonderland.com");""")
Traceback (most recent call last):
  File "<stdin>", line 2, in <module>
sqlite3.IntegrityError: column username is not unique

To detect this, try to run the INSERT and detect whether it fails.

try:
    conn.execute("""INSERT INTO users (username, email)
                    VALUES ("Alice", "alice@wonderland.com");""")
except sqlite3.IntegrityError:
    print ("Username 'Alice' was already taken.")

Incidentally, be very careful with using the upper/lowercase functions. Does "Главное в новостях".lower() mean what you think it means?

---

Since you mention this is for a webapp, I'll just remind you to store your passwords as salted hashes of the password, using unique salts for each user (never as plain text!), and to guard against SQL injection by using the (?,?,?,?,...) placeholders for SQL queries, not the (%s,%s) % (var1, var2) string interpolation method.

To quote the sqlite3 documentation:

Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack.

Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method. (Other database modules may use a different placeholder, such as %s or :1.) For example:

If you don't do this, then someone could request the username Robert Menzies; DROP TABLE users; with hilarious results.

Answer 2

Why not set the field in SQLite to unique which will prevent duplicate entries in the first place.