Python: MySQLdb: cursor: fetchone(): Fetch next row

‘fetchone()’ method fetch next row of query result set. Return None, when no data is available.

#!/usr/bin/python

# Import MySQLdb module
import MySQLdb

# 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")"""

try:
    print("Dropping table employee")
    dropAndCreateTable(cursor,"employee")


    print("Inserting records")
    cursor.execute(record1)
    cursor.execute(record2)
    cursor.execute(record3)

    print("Commiting data to database")

    db.commit()

    cursor.execute("SELECT * FROM employee")

    row=cursor.fetchone()

    while(row is not None):
        id = row[0]
        firstName = row[1]
        lastName = row[2]
        salary = row[3]
        mailId = row[4]

        print(id, firstName, lastName, salary, mailId)
        row=cursor.fetchone()

    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
(1L, 'Krishna', 'Ananda', Decimal('100000'), 'krishna@krishna.com')
(2L, 'Arjun', 'Dhanunjay', Decimal('50000'), 'arjun@arjun.com')
(3L, 'Ptr', 'Ptr', Decimal('25000'), 'ptr@ptr.com')

Previous                                                 Next                                                 Home