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 LazyroarfoeLazyroar. 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
# import required modulesimport pymysql# establish connection to MySQLconnection = 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 connectionmycursor = connection.cursor()# display records before insertingmycursor.execute("Select * from LazyroarfoeLazyroar")myresult = mycursor.fetchall()for i in myresult: print(i) # statement to insert recordmycursor.execute( "Insert into LazyroarfoeLazyroar(name,address,age,mob_number,ID_NO) \ select * from( Select 'Thomas','UK',30,1892345670,'IND100') as temp \ where not exists \ (Select ID_NO from LazyroarfoeLazyroar where ID_NO='IND100') LIMIT 1")print("After inserting a record....")# print records after insertionmycursor.execute("Select * from LazyroarfoeLazyroar")myresult = mycursor.fetchall()for i in myresult: print(i)mycursor.execute("Commit")# close connectionconnection.close() |
Output
Python Output after insertion
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 Lazyroardemo in database geek:
Now, we will try to insert a record that already exists.
Python3
# import required modulesimport pymysql# establish connection to MySQLconnection = 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 connectionmycursor = connection.cursor()# display records before insertingmycursor.execute("Select * from Lazyroardemo")myresult = mycursor.fetchall()for i in myresult: print(i) # statement to insert recordmycursor.execute( "Insert into Lazyroardemo(id,name,gender,dept) \ select * from( Select 5,'Thomas','m','information technology') as temp \ where not exists \ (Select id from Lazyroardemo where id=5) LIMIT 1")print("After inserting a record....")# print records after insertionmycursor.execute("Select * from Lazyroardemo")myresult = mycursor.fetchall()for i in myresult: print(i)mycursor.execute("Commit")# close connectionconnection.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.
