Skip to content
Related Articles

Related Articles

Python MySQL – Insert record if not exists in table
  • Last Updated : 26 Nov, 2020

Prerequisite: Connect MySQL Database to Python

In this article, we will try to insert records and check if it EXISTS or not. The EXISTS condition in SQL is used to check if the result of a correlated nested query is empty (contains no tuples) or not. It can be used to INSERT, SELECT, UPDATE, or DELETE statement. 

The query we are using the python program is:

INSERT INTO table-name(col1, col2, col3) \
   SELECT * FROM (SELECT val1, val2, val3) as temp \
   WHERE NOT EXISTS \
   (SELECT primary-key FROM table-name WHERE primary-key = inserted-record) LIMIT 1

Suppose we have a database called test and a table named in geeksfoegeeks. Below are the schema and data of the table:



Database

Since each individual has a unique identification number, we will insert records and check the ID_NO of that person. If ID_NO is not already present in the table, the record will be inserted, else the record will be discarded. Let us understand using the below examples:

Example 1: When the record does not exist in the table.

Python3

filter_none

edit
close

play_arrow

link
brightness_4
code

# import required modules
import pymysql
  
  
# establish connection to MySQL
connection = pymysql.connect(
    # specify host name
    
    host="localhost",
      
    # specify username
    user="root",
      
    # enter password for above user
    password="1234",
      
    # default port number for MySQL is 3306
    port=3306,
      
    # specify database name
    db="test"
)
  
  
# make cursor for establish connection
mycursor = connection.cursor()
  
  
# display records before inserting
mycursor.execute("Select * from geeksfoegeeks")
myresult = mycursor.fetchall()
for i in myresult:
    print(i)
  
      
# statement to insert record
mycursor.execute(
    "Insert into geeksfoegeeks(name,address,age,mob_number,ID_NO) \
    select * from( Select 'Thomas','UK',30,1892345670,'IND100') as temp \
    where not exists \
    (Select ID_NO from geeksfoegeeks where ID_NO='IND100') LIMIT 1")
print("After inserting a record....")
  
  
# print records after insertion
mycursor.execute("Select * from geeksfoegeeks")
myresult = mycursor.fetchall()
for i in myresult:
    print(i)
mycursor.execute("Commit")
  
  
# close connection
connection.close()

chevron_right


Output

Python Output after inertion

In the above, output we could see that the record (‘Thomas’,’UK’,30,1892345670,’IND100′) was inserted into MySQL table.

Example 2: When record already Exists

Below are the schema and data of the table geeksdemo in database geek:

Now, we will try to insert a record that already exists. 



Python3

filter_none

edit
close

play_arrow

link
brightness_4
code

# import required modules
import pymysql
  
  
# establish connection to MySQL
connection = pymysql.connect(
    # specify host name
    host="localhost",
     
    # specify username
    user="root",
     
    # enter password for above user
    password="",
      
    # default port number for MySQL is 3306
    port=3306,
      
    # specify database name
    db="geek"
)
  
  
# make cursor for establish connection
mycursor = connection.cursor()
  
  
# display records before inserting
mycursor.execute("Select * from geeksdemo")
myresult = mycursor.fetchall()
for i in myresult:
    print(i)
      
      
# statement to insert record
mycursor.execute(
    "Insert into geeksdemo(id,name,gender,dept) \
    select * from( Select 5,'Thomas','m','information technology') as temp \
    where not exists \
    (Select id from geeksdemo where id=5) LIMIT 1")
print("After inserting a record....")
  
  
# print records after insertion
mycursor.execute("Select * from geeksdemo")
  
  
myresult = mycursor.fetchall()
for i in myresult:
    print(i)
mycursor.execute("Commit")
  
  
# close connection
connection.close()

chevron_right


Output

We observe that record (5,’Thomas’,’m’,’information technology’) was not inserted into the table again. The following image is the output of MySQL database, after running the above python script.

We can see that 0 rows were affected. Therefore, no record was inserted as it already existed in the table with another record.

Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course.

My Personal Notes arrow_drop_up
Recommended Articles
Page :