How to tell if a value exists in a sqlite3 database, python

Question

Here is my code so far:

def signup():
    email = request.form['email']
    username = request.form['user']
    password = request.form['password']
    g.db.execute("INSERT INTO users VALUES (?, ?, ?)", [email, username, password])
    g.db.commit()

I would like it to only insert the values into the database if email and username aren't in the database, but I have no idea where to start.

Scratch'N'Purr · Accepted Answer · 2017-08-08T14:00:31.700

10

All you have to do is make a query before insertion, and do a fetchone. If fetchone returns something, then you know for sure that there is a record already in the DB that has the email OR username:

def signup():
    email = request.form['email']
    username = request.form['user']
    password = request.form['password']

    # Create cursor object
    cur = g.db.cursor()

    # run a select query against the table to see if any record exists
    # that has the email or username
    cur.execute("""SELECT email
                          ,username
                   FROM users
                   WHERE email=?
                       OR username=?""",
                (email, username))

    # Fetch one result from the query because it
    # doesn't matter how many records are returned.
    # If it returns just one result, then you know
    # that a record already exists in the table.
    # If no results are pulled from the query, then
    # fetchone will return None.
    result = cur.fetchone()

    if result:
        # Record already exists
        # Do something that tells the user that email/user handle already exists
    else:
        cur.execute("INSERT INTO users VALUES (?, ?, ?)", (email, username, password))
        g.db.commit()

edited Aug 08 '17 at 14:00

answered Aug 08 '17 at 13:21

Scratch'N'Purr

9,959
2
35
51

Couldn't you do it in one line, like: `g.db.execute("INSERT INTO users VALUES (:email, :username, :password) WHERE users.email <> :email AND users.username <>:username", {"email": email, "username":username, "password": password})` ? – Adonis Aug 08 '17 at 13:26
I am not sure if this what I am asking. If email is in the email column OR user is in the username column I want to to go to the else you put in your code. – Daniel Aug 08 '17 at 13:31
1

@Daniel Then I would modify to: `SELECT email, username FROM users WHERE email=? or username=?`. You specified "`email` and `username`" so I was assuming you wanted to make both checks. I'll make the changes to my answer. – Scratch'N'Purr Aug 08 '17 at 13:32
ok, i thought it woud be something like that but i wanted to check. thanks – Daniel Aug 08 '17 at 13:33
@Scratch'N'Purr it thows up a syntax error for this code: g.db.execute('SELECT, email username FROM users WHERE email=? or username=?',(email, username)) – Daniel Aug 08 '17 at 13:36
1

@Adonis yes, though I would imagine the app would inform the user if a record exists – Scratch'N'Purr Aug 08 '17 at 13:36
1

@Daniel "SELECT email, username...", not "SELECT, email username" :P – Scratch'N'Purr Aug 08 '17 at 13:41
I am a little confused please could you comment the code in full – Daniel Aug 08 '17 at 13:43
@Scratch'N'Purr I am a little confused please could you comment the code in full – Daniel Aug 08 '17 at 13:50
1

@Daniel I have included comments and wrapped my solution in your function. Hope it helps! – Scratch'N'Purr Aug 08 '17 at 13:50
I am really sorry but Now I am getting the error: 'sqlite3.Connection' object has no attribute 'fetchone' – Daniel Aug 08 '17 at 13:57
1

@Daniel Ah, so your `g.db` object isn't a cursor object. You should create a cursor object before using the `execute` method. I'll edit my answer for that then. – Scratch'N'Purr Aug 08 '17 at 13:59
ok, this really is very good of you. – Daniel Aug 08 '17 at 13:59

How to tell if a value exists in a sqlite3 database, python

1 Answers1