In this post, I am
going to explain how to delete records from a table.
In brief,
Step 1: Get
connection object.
db =
MySQLdb.connect(host, userName, password, database)
Step 2: Get
cursor instance.
cursor = db.cursor()
Step 3: Execute
the delete statement.
cursor.execute("DELETE
FROM employee WHERE salary>50000")
#!/usr/bin/python # Import MySQLdb module import MySQLdb # Print data from given table def printData(cursor,tableName): print('***********************************') print("getting all the records from ", tableName) sql = "SELECT * FROM "+tableName # Execute the SQL command cursor.execute(sql) # Fetch all the rows results = cursor.fetchall() for row in results: id = row[0] firstName = row[1] lastName = row[2] salary = row[3] mailId = row[4] print(id, firstName, lastName, salary, mailId) # Drop and create table def dropAndCreateTable(cursor,tableName): sql="DROP TABLE IF EXISTS "+tableName cursor.execute(sql) sql = """CREATE TABLE employee ( id int, firstName varchar(30), lastName varchar(30), salary decimal, mailId varchar(30), PRIMARY KEY(id) )""" cursor.execute(sql) # Connection related variables host="localhost" userName="root" password="tiger" database="sample" # Open connection to a database db = MySQLdb.connect(host, userName, password, database) # Get cursor object cursor = db.cursor() # Prepare SQL query to create table record1="""INSERT INTO employee values(1, "Krishna", "Ananda", 100000.00, "krishna@krishna.com")""" record2="""INSERT INTO employee values(2, "Arjun", "Dhanunjay", 50000.00, "arjun@arjun.com")""" record3="""INSERT INTO employee values(3, "Ptr", "Ptr", 25000.00, "ptr@ptr.com")""" record4="""INSERT INTO employee values(4, "Nayan", "Nayanamma", 225000.00, "nayan@nayan.com")""" record5="""INSERT INTO employee values(5, "Kiran", "Darsi", 95000.00, "kiran@kiran.com")""" try: print("Dropping table employee") dropAndCreateTable(cursor,"employee") print("Inserting records") cursor.execute(record1) cursor.execute(record2) cursor.execute(record3) cursor.execute(record4) cursor.execute(record5) print("Commiting data to database") db.commit() printData(cursor,"employee") print("Deleting employees whose salary > 50000") cursor.execute("DELETE FROM employee WHERE salary>50000") print("Total rows affected by previous query ", cursor.rowcount) printData(cursor,"employee") cursor.close() except Exception as e: print("Error while processing query", e) db.rollback() finally: # Close the connection to database db.close()
Output
Dropping table employee Inserting records Commiting data to database *********************************** ('getting all the records from ', 'employee') (1L, 'Krishna', 'Ananda', Decimal('100000'), 'krishna@krishna.com') (2L, 'Arjun', 'Dhanunjay', Decimal('50000'), 'arjun@arjun.com') (3L, 'Ptr', 'Ptr', Decimal('25000'), 'ptr@ptr.com') (4L, 'Nayan', 'Nayanamma', Decimal('225000'), 'nayan@nayan.com') (5L, 'Kiran', 'Darsi', Decimal('95000'), 'kiran@kiran.com') Deleting employees whose salary > 50000 ('Total rows affected by previous query ', 3L) *********************************** ('getting all the records from ', 'employee') (2L, 'Arjun', 'Dhanunjay', Decimal('50000'), 'arjun@arjun.com') (3L, 'Ptr', 'Ptr', Decimal('25000'), 'ptr@ptr.com')
No comments:
Post a Comment