So far, I have discussed about connection establishment, create table, insert record, read records (select statement), update record. In this article we are going to learn about delete a record.
Following is the table which I am going to delete a record. the table name is emp_info
| emp_id | emp_name | emp_age |
|---|---|---|
| 1 | Raj | 22 |
| 2 | Rahul | 24 |
| 3 | Vivek | 26 |
| 4 | Ajay | 28 |
| 5 | Suraj | 29 |
In the above table, I want to delete the record whose id is 4
Assuming, you have followed previous tutorials, I am focusing straight to the code.
Delete Record
conn=pymysql.connect(“localhost”,”root”,””,”emp_db”)
cursor=conn.cursor
sql=”delete from emp_info where id=%d”
dataval=(4,)
#Note: If we keep only one value in tuple, then it must end with a comma. So after 4 there is a comma.
try:
cursor.execute(sql,dataval)
conn.commit()
print(“Record deleted”)
except pymysql.Error as pe:
print(pe)
conn.rollback()
finally:
cursor.close()
conn.close()
Output: Record deleted
Because we are making changes in the table by deleting record, we must have to call the function conn.commit(). It makes the changes permanent.
So after deleting the record, the table is like below.
| emp_id | emp_name | emp_age |
|---|---|---|
| 1 | Raj | 22 |
| 2 | Rahul | 24 |
| 3 | Vivek | 26 |
| 5 | Suraj | 29 |
Note: when ever you use insert, delete and update sql command, you must have to run the conn.commit() function.