Sorry for the cut/paste error in my prior post. The for loop should be written like this:
for userName in users:
if userName == 'support':
print("name is there")
break
else: # This else is associated with the "for", not the "if".
print("user isn't here")But that wouldn't work either since sql returns results as tuples or lists of tuples. To make it work you need to compare tuple to tuple or str to str.
for userName in users:
if userName[0] == 'support': # or userName == ('support',)
print("name is there")
break
else: # This else is associated with the "for", not the "if".
print("user isn't here")The correct way to find out if a particular name is in a particular field is to use a query that has a condition. I don't use sqlite3 all that much, so I wrote a little test program to see how that would work.
import sqlite3 as sql
import pandas as pd
database_file = "test.db"
def make_table(table:str, index:str, df:pd.DataFrame):
""" Make a quick and dirty table for the test """
conn = sql.connect(database_file)
df.to_sql(table, conn, index=False, if_exists="replace")
conn.commit()
conn.close()
def print_table(table:str):
""" Print all the rows in the table """
conn = sql.connect(database_file)
cur = conn.cursor()
print(f"Print table {table}")
for row in cur.execute(f"SELECT * from {table}",): # No fetch required!
print(row)
conn.close()
def print_column(table:str, column:str):
""" Print all the values in a column """
conn = sql.connect(database_file)
print(f"Print table {table}[{column}]")
for row in conn.execute(f"SELECT {column} from {table}",): # Makes a cursor for you automatically!
print(row)
conn.close()
def find(table:str, column:str, value:any):
""" Return all rows where row[column] == value """
conn = sql.connect(database_file)
cur = conn.cursor()
matches = cur.execute(f"SELECT * FROM {table} WHERE {column}=?", (value,)).fetchall()
conn.close()
return matches
make_table("user", "name", pd.DataFrame({"name":["bob", "mike", "admin", "support"], "role":["user", "user", "admin", "admin"]}))
print_table("user")
print_column("user", "name")
print("Finding user[name] == support,", find("user", "name", "support"))Output:
Print table user
('bob', 'user')
('mike', 'user')
('admin', 'admin')
('support', 'admin')
Print table user[name]
('bob',)
('mike',)
('admin',)
('support',)
Finding user[name] == support, [('support', 'admin')]
Of particular interest is the find() function which searches for rows whose column value matches a provided value.
def find(table:str, column:str, value:any):
conn = sql.connect(database_file)
cur = conn.cursor()
matches = cur.execute(f"SELECT * FROM {table} WHERE {column}=?", (value,)).fetchall()
conn.close()
return matchesIf you had a really big table it would take a long time to download all the rows and then check each row to see if a column's value matches your target value. It takes a lot less time to have the highly optimized database server do the search for you.
To apply this to your problem of finding out if you have a particular user in a table:
matches = cursor.execute("SELECT user FROM mysql.user WHERE user=?", ("support",)).fetchone()
if matches:
print('name is there')
else:
print('user isnt here')There are a few other things you might find interesting. Notice that print_column() doesn't make a cursor. As far as I can tell, the only reason for making a cursor is to use fetchone(), fetchmany() or fetchall(). You don't need a cursor to use execute(), and calling execute returns a cursor object. Cursor may provide other benefits, and explicitely making a cursor is certainly the convention, but it doesn't appear necessary. Interesting, but not all that useful.
More useful is that print_table() and print_column() don't use fetchall(). Instead of fetching results, these functions use a lazy iterator provided by Cursor to retrieve values one at a time.
def print_table(table:str):
conn = sql.connect(database_file)
cur = conn.cursor()
print(f"Print table {table}")
for row in cur.execute(f"SELECT * from {table}",): # No need to fetch anything
print(row)
conn.close()This certainly looks more Pythonic, and you don't have to create a big list just to have an something that acts like an iterator.
