Python Program - Database - READ Operation

• READ Operation

  READ Operation on any databasse means to fetch some useful information from the database.
  Once our database connection is established, we are ready to make a query into this database.
  We can use either fetchone() method to fetch single record or fetchall method to fetech multiple values from a database table.
  1. fetchone(): This method fetches the next row of a query result set.
     A result set is an object that is returned when a cursor object is used to query a table.
  2. fetchall(): This method fetches all the rows in a result set.
     If some rows have already been extracted from the result set, the fetchall() method retrieves the remaining rows from the result set.
  3. rowcount: This is a read-only attribute and returns the number of rows that were affected by an execute() method.

• Example:

  Following is the procedure to query all the records from EMPLOYEE table having salary more than 1000.
  
  #!/usr/bin/python
  
  import MySQLdb
  
  # Open database connection
  db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
  
  # prepare a cursor object using cursor() method
  cursor = db.cursor()
  
  # Prepare SQL query to INSERT a record into the database.
  sql = "SELECT * FROM EMPLOYEE \
     WHERE INCOME > '%d'" % (1000)
  try:
     # Execute the SQL command
     cursor.execute(sql)
     # Fetch all the rows in a list of lists.
     results = cursor.fetchall()
     for row in results:
       fname = row[0]
       lname = row[1]
       age = row[2]
       sex = row[3]
       income = row[4]
       # Now print fetched result
       print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
       (fname, lname, age, sex, income )
  except:
     print "Error: unable to fecth data"
  # disconnect from server
  db.close()
  
  This will produce following result:
  
  fname=Mac, lname=Mohan, age=20, sex=M, income=2000