Python: MySQLdb: cursor: fetchall: Get all records that match to given query

fecthall()

‘fecthall()’  method returns all the records that match to given query. An exception is raised if the previous call to execute*() did not produce any result set or no call was issued yet.

#!/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")"""
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()

    cursor.execute("SELECT * FROM employee WHERE salary>50000")

    print("Total rows affected by previous query ", cursor.rowcount)
    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)
        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
('Total rows affected by previous query ', 3L)
(1L, 'Krishna', 'Ananda', Decimal('100000'), 'krishna@krishna.com')
(4L, 'Nayan', 'Nayanamma', Decimal('225000'), 'nayan@nayan.com')
(5L, 'Kiran', 'Darsi', Decimal('95000'), 'kiran@kiran.com')

Previous                                                 Next                                                 Home