Check if a row is already exists in Python SQLite databas

Python / By

Do you want to check if a row already exists in the database? Sometimes you might not want to store one row, multiple times in a database. You need to check if a row already exists in the database, then you simply do not want to insert it.

In this article, You will learn about 3 different ways to check the existence of a row in an SQLite database in Python.

Method No 1: Use fetchall() function to check if a row is already Exist in database

The fetchall() function in python is a built-in function that returns either a list with just one tuple in the list (e.g. [(rowid,),] or an empty list [].

Let us first create the table with SQLite database and insert some data in it. Then we will use the fetchall() function to check if a row already exists in the SQLite table or not.

import sqlite3
# it will create a databse with name sqlite.db
connection= sqlite3.connect('sqlite.db') 
cursor= connection.cursor()
table_query = '''CREATE TABLE  if not Exists Student
               (Name text, Course text, Age real)'''
       
cursor.execute(table_query)   

# student list 

students_data = [
 ['AlixaProDev','CS',19],
 ['Alixawebdev','BBa',21],
  ['AskALixa','Software',22]
]
insert_q = []


# creating the insert query for each student
for std_data in students_data:
    name = std_data[0]
    course = std_data[1]
    age = std_data[2]
    q=f"INSERT INTO Student VALUES ('{name}','{course}','{age}')"
    insert_q.append(q)

# executing the insert queries
for q in insert_q:
    cursor.execute(q)


# you need to commit changes as well
connection.commit()
# you also need to close  the connection
connection.close()

Output of the code:

The database has now 3 rows of data in it. The output of the above code is an SQLite database with 3 rows.

Use the fetchall() function to see if a row already exists or not:

This code uses the fetchall() function in python to check if a row already exists in the SQLite database or not.

for name in ('AlixaProDev','Salamn'): 
    cursor.execute("SELECT rowid FROM Student WHERE Name = ?", (name,))
    db_result=cursor.fetchall()
    if len(db_result)==0:
        print('There is no Student named %s'%name)
    else:
        print('Student %s found with rowids %s'%(name,','.join(map(str, next(zip(*db_result))))))

Example No 1 to check if a row is already in database in python

In this example, we will use the fetchall() function to see if a row is already in the database or not. If a row already exists in the SQLite database we will not insert another row and simply warn the user that there is already a user with that name.

import sqlite3
# it will create a databse with name sqlite.db
connection= sqlite3.connect('sqlite.db') 
cursor= connection.cursor()
table_query = '''CREATE TABLE  if not Exists Student
               (Name text, Course text, Age real)'''
       

cursor.execute(table_query)        
# student list 

students_data = [
 ['AlixaProDev','CS',19],
 ['Alixawebdev','BBa',21],
  ['AskALixa','Software',22],
  ['AskALixa','Software',22]
]
insert_q = []

# creating the insert query for each student
for std_data in students_data:
    name = std_data[0]
    course = std_data[1]
    age = std_data[2]
    q=f"INSERT INTO Student VALUES ('{name}','{course}','{age}')"
    # check if a row is already exist 
    cursor.execute("SELECT rowid FROM Student WHERE Name = ?", (name,))
    db_result=cursor.fetchall()
    if len(db_result)==0:
        cursor.execute(q)
        print('Studnet Data inserted Successfully')
    else:
        print('Student %s found with rowids %s'%(name,','.join(map(str, next(zip(*db_result))))))
  
        
# you need to commit changes as well
connection.commit()
# you also need to close  the connection
connection.close()

Output of the Example

Studnet Data inserted Successfully
Studnet Data inserted Successfully
Studnet Data inserted Successfully
Student AskALixa found with rowids 3

Method No 2: Use fetchone() function to check if a row is already Exist in the database

In Python, the best way to check if a row already exists in the database is to use the fetchone() function. fetchone() function returns None if there is not any rows otherwise it returns a list of rows available in the SQLite database.

Check the following code that will tell if a row already exists or not in the SQLite database. This is the best way in python to find out if a row is already there.

for name in (['AlixaProDev','Salman']): 
    cursor.execute("SELECT rowid FROM Student WHERE Name = ?", (name,))
    data=cursor.fetchone()
    if data is None:
        print('No Student with The Name: %s'%name)
    else:
        print('One Other Student with %s Name Found  rowid %s'%(name,data[0]))

Example No 2 to check if a row is already in database in python

In this example, we will use fetchone() function to see if there is already a row in the database or not. If a row is already available in the database it will not insert that row in the SQLite database. As mentioned fetchone() is a python function to return None if there is not any row available otherwise it will return a list of rows.

import sqlite3
# it will create a databse with name sqlite.db
connection= sqlite3.connect('sqlite.db') 
cursor= connection.cursor()
table_query = '''CREATE TABLE  if not Exists Student
               (Name text, Course text, Age real)'''
       

cursor.execute(table_query)        
# student list 

students_data = [
 ['AlixaProDev','CS',19],
 ['Alixawebdev','BBa',21],
  ['AskALixa','Software',22]
]
insert_q = []

# creating the insert query for each student
for std_data in students_data:
    name = std_data[0]
    course = std_data[1]
    age = std_data[2]
    q=f"INSERT INTO Student VALUES ('{name}','{course}','{age}')"
    # check if a row is already exist 
    cursor.execute("SELECT rowid FROM Student WHERE Name = ?", (name,))
    db_result=cursor.fetchone()
    if db_result is None: 
        cursor.execute(q)
    else:
        print('One Other Student with %s Name Found with rowid %s'%(name,db_result[0]))

# you need to commit changes as well
connection.commit()
# you also need to close  the connection
connection.close()

Output of the Example No 2

Studnet Data inserted Successfully
Studnet Data inserted Successfully
Studnet Data inserted Successfully

Method No 3: Use count() function to check if a row is already Exist in database

In this method, we will use the SQL function count() to see if there is a row already exist or not. This way we can see the duplicate rows. If there is a duplicate row we will not insert that row in the database or show some error to the user.

This is the best way to avoid duplicate rows in the database in Python. The following code will make sure to find the duplicate row before inserting it into database in python.

for name in ('AlixaProDev','Salamn'):  
    cursor.execute("SELECT count(*) FROM Student WHERE name = ?", (name,))
    db_result=cursor.fetchone()[0]
    if db_result==0:
        print('There is no Student named %s'%name)
    else:
        print('Student %s found in %s row(s)'%(name,db_result))

Example No 3 to check if a row is already in database in python

In the following example code, we have used the SQL function, count() to check if there is already a row with the same name or not. It will help us avoid inserting duplicate rows in the database.

Below is the python code that helps us find if there is already a duplicate row available in the database.


import sqlite3
# it will create a databse with name sqlite.db
connection= sqlite3.connect('sqlite.db') 
cursor= connection.cursor()
table_query = '''CREATE TABLE  if not Exists Student
               (Name text, Course text, Age real)'''
       

cursor.execute(table_query)        
# student list 

students_data = [
 ['AlixaProDev','CS',19],
 ['Alixawebdev','BBa',21],
  ['AskALixa','Software',22]
]
insert_q = []

# creating the insert query for each student
for std_data in students_data:
    name = std_data[0]
    course = std_data[1]
    age = std_data[2]
    q=f"INSERT INTO Student VALUES ('{name}','{course}','{age}')"
    # check if a row is already exist 
    cursor.execute("SELECT count(*) FROM Student WHERE name = ?", (name,))
    db_result=cursor.fetchone()[0]
    if db_result ==0: 
        cursor.execute(q)
    else:
         print('Student %s found in %s row(s)'%(name,db_result))
  
# you need to commit changes as well
connection.commit()
# you also need to close  the connection
connection.close()

Output of the Example No 3

Studnet Data inserted Successfully
Studnet Data inserted Successfully
Studnet Data inserted Successfully

Summary and Conclusion

We have discussed three different methods that help us find if there is any duplicate or similar row already available in the database. These three python methods that help us find duplicate row in the database are :

  1. fetchall() function
  2. fetchone() function
  3. count() function

If you still have any questions please feel free to comment below.

Leave a Comment

Scroll to Top