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':"[email protected]"},
{'username':"Billy", 'email':"[email protected]"},
{'username':"Charles", 'email':"[email protected]"},
{'username':"Dick", 'email':"[email protected]"},
{'username':"Emily", 'email':"[email protected]"},
{'username':"Faramir", 'email':"[email protected]"},
)
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", "[email protected]" after creating the database like above, this will get you an sqlite3.IntegrityError:
>>> conn.execute("""INSERT INTO users (username, email)
... VALUES ("Alice", "[email protected]");""")
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", "[email protected]");""")
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.
fetchall()withfetchone().