Python: MySQLdb: Update records

In this post, I am going to explain how to update records.

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 update statement.

cursor.execute("UPDATE employee SET salary = salary + 10000 WHERE salary>50000")

Following is the complete working application.

#!/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("Adding 10000 increment to all employees whose salary > 50000")
    cursor.execute("UPDATE employee SET salary = salary + 10000 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')
Adding 10000 increment to all employees whose salary > 50000
('Total rows affected by previous query ', 3L)
***********************************
('getting all the records from ', 'employee')
(1L, 'Krishna', 'Ananda', Decimal('110000'), 'krishna@krishna.com')
(2L, 'Arjun', 'Dhanunjay', Decimal('50000'), 'arjun@arjun.com')
(3L, 'Ptr', 'Ptr', Decimal('25000'), 'ptr@ptr.com')
(4L, 'Nayan', 'Nayanamma', Decimal('235000'), 'nayan@nayan.com')
(5L, 'Kiran', 'Darsi', Decimal('105000'), 'kiran@kiran.com')

Previous                                                 Next                                                 Home