Run Loop If Value Does Not Exist in Database-CodePudding

#update - I fixed the syntax error moving a ), but it still doesn't work as intended. Now it runs the loop even with a duplicate, which is not the intent.

I'm trying to add a list of quotes to a database, but only if the author doesn't already exist. I'm getting a syntax error here, and no existing posts I could find help with this issue. Would appreciate any help, and hope this helps others.

The first line with the SELECT EXISTS query gives me the error, but not sure if this is the best way to do this anyway. If I made some boneheaded newbie error, I hope it helps someone else avoid this rabbit hole of time wasting in the future.

#Check if author already exists
    if cursor.execute("SELECT EXISTS (SELECT 1 FROM quotebot WHERE author_name=?)", (author,)).fetchone():
        for text, author, title, in all_quotes:
            try: 
                if text is None or title is None and text == "":
                    continue
                # Insert quote into table
                cursor.execute("INSERT INTO quotebot("long-query-that-works"))
                sqliteConnection.commit()
                print('Quote Successfully Added')
              
            # Handle errors
            except sqlite3.Error as error:
                print('Error occured - ', error)
                
    else:
        print("This author is already in the database. Please try a new author.")

CodePudding user response：

Your fetchone() will always return a row. The row will contain the value of EXISTS, either 1 if the quote exists or 0 if it doesn't. So just checking whether fetchone() returns anything will not tell you if there's already a duplicate. The if condition will always succeed and you'll go into the loop.

You should check the contents of the tuple returned by fetchone():

if cursor.execute("SELECT EXISTS (SELECT 1 FROM quotebot WHERE author_name=?)", (author,)).fetchone()[0]:

or remove the EXISTS test and return the row itself:

if cursor.execute("SELECT 1 FROM quotebot WHERE author_name=? LIMIT 1", (author,)).fetchone():