Insert Record If Not Exists in Table
Query Used:
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 geeksfoegeeks. Below are the schema and data of the table:
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.
Inserting Record When the Record Does Not Exists
In this example, we are inserting the record when the record already exists. We know that each individual has a unique identification number, then we will insert records and check the ID_NO of that person and if ID_NO is not already present in the table then the record will be inserted.
Python3
# 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() |
Output
In the above, output we could see that the record (‘Thomas’,’UK’,30,1892345670,’IND100′) was inserted into MySQL table.
Insert Record When The Record Exists
Below are the schema and data of the table geeksdemo in database geek:
In this example, we will try to insert the record when the record already exists. We know that each individual has a unique identification number, then we will insert records and check the ID_NO of that person and if ID_NO is present in the table then the record will be discarded.
Python3
# 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() |
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.
Python MySQL – Insert record if not exists in table
In this article, we will try to insert records and check if they 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 statements.
Pre-requisite