Python: MySQLdb: Hello World Application

In this post, I am going to explain how to get all the rows from given table. Following example going to use simple employee table, which is in database ‘sample’.

Step 1: Create table ‘employee’.

CREATE TABLE employee
(
 id int,
 firstName varchar(30),
 lastName varchar(30),
 salary decimal,
 mailId varchar(30),
 PRIMARY KEY(id)
);

Step 2: Insert some data into employee table.

INSERT INTO employee values(1, "Krishna", "Ananda", 100000.00, "krishna@krishna.com");
INSERT INTO employee values(2, "Arjun", "Dhanunjay", 50000.00, "arjun@arjun.com");
INSERT INTO employee values(3, "Ptr", "Ptr", 25000.00, "ptr@ptr.com");

Step 3: Following python script gets all the records from table employee.

data.py

#!/usr/bin/python

# Import MySQLdb module
import MySQLdb

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 get all records from table employee
sql = "SELECT * FROM employee"

try:
   # 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)

except:
   print("Error: unable to fetch data")

# Close the connection to database
db.close()

$ ./data.py 
(1L, 'Krishna', 'Ananda', Decimal('100000'), 'krishna@krishna.com')
(2L, 'Arjun', 'Dhanunjay', Decimal('50000'), 'arjun@arjun.com')
(3L, 'Ptr', 'Ptr', Decimal('25000'), 'ptr@ptr.com')

db = MySQLdb.connect(host, userName, password, database)

‘connect’ method takes host, useName, password and database details, returns a connection object. Connection object is used to connect to a database.

cursor = db.cursor()

‘db.cursor()’ method return cursor object. By using cursor object, you can fetch the records.

cursor.execute(sql)

‘execute’ method is used to execute a database operation.

results = cursor.fetchall()

Fetch all rows of a query result.

Previous                                                 Next                                                 Home