How to find user in Sqlite3 Python DB?

Question

Python

Hello.

I have code:

import sqlite3

conn = sqlite3.connect(':memory:')
cur = conn.cursor()

    

cur.execute("""CREATE TABLE IF NOT EXISTS users(
   id TEXT,
   name TEXT,
   family TEXT,
   gender TEXT);
""")
conn.commit()

customers = [
  ('00005', 'Stephanie', 'Stewart', 'female'), ('00006', 'Sincere', 'Sherman', 'female'), ('00007', 'Sidney', 'Horn', 'male'), 
  ('00008', 'Litzy', 'Yates', 'female'), ('00009', 'Jaxon', 'Mills', 'male'), ('00010', 'Paul', 'Richard', 'male'), 
  ('00011', 'Kamari', 'Holden', 'female'), ('00012', 'Gaige', 'Summers', 'female'), ('00013', 'Andrea', 'Snow', 'female'), 
  ('00014', 'Angelica', 'Barnes', 'female'), ('00015', 'Leah', 'Pitts', 'female'), ('00016', 'Dillan', 'Olsen', 'male'), 
  ('00017', 'Joe', 'Walsh', 'male'), ('00018', 'Reagan', 'Cooper', 'male'), ('00019', 'Aubree', 'Hogan', 'female'), 
  ('00020', 'Avery', 'Floyd', 'male'), ('00021', 'Elianna', 'Simmons', 'female'), ('00022', 'Rodney', 'Stout', 'male'), 
  ('00023', 'Elaine', 'Mcintosh', 'female'), ('00024', 'Myla', 'Mckenzie', 'female'), ('00025', 'Alijah', 'Horn', 'female'),
  ('00026', 'Rohan', 'Peterson', 'male'), ('00027', 'Irene', 'Walters', 'female'), ('00028', 'Lilia', 'Sellers', 'female'), 
  ('00029', 'Perla', 'Jefferson', 'female'), ('00030', 'Ashley', 'Klein', 'female')
]


cur.executemany("INSERT INTO users VALUES(?, ?, ?, ?);", customers)
conn.commit()

id = '00011'

cur.execute("SELECT * FROM users;")
all = cur.fetchall()
for x in all:
    if x[0] == str(id):
        a = 'Open'
        print(a)
        #if ......
        break
    else:
        a = 'Close'
        # if ......
        print(a)

I need to find a user with a specific id in a database.
Please tell me how can I do this? Are there other ways that do not check the entire database?

Answer 1

Read the basics of SQL (for example https://proglib.io/p/sql-for-20-minutes )
For information, there is a filter system there. In this case, just

cur.execute("SELECT * FROM users WHERE id = ?", (id, ))
all = cur.fetchone()

all will have an entry with that user if it exists, or None if it doesn't.
or so

cur.execute("SELECT COUNT(*) FROM users WHERE id = ?", (id, ))
cnt = cur.fetchone()[0]

cnt will have the number of records with this user, i.e. 1 if present, 0 if not.