Python MySQL Database Access

MySQLdb is simply an interface for connecting to the MySQL database server from python. To install MySQLdb module, download it. Here is the link to download, MySQLdb Download. Now proceed the following:

$ gunzip MySQL-python-1.2.2.tar.gz
$ tar -xvf MySQL-python-1.2.2.tar
$ cd MySQL-python-1.2.2
$ python setup.py build
$ python setup.py install

Create Database Connection in Python

Make sure these things before connecting to the MySQL database:

Make sure that you have created a database named MYDATABASE
Make sure that you have created a table named STUDENT in MYDATABASE
Make sure that this table has fields namely FIRST_NAME, LAST_NAME, AGE, SEX, and FEE
Make sure that the user id is "checkuser" and password is "checkpass123" are set to access the database MYDATABASE
Make sure that the python module MySQLdb is successfully and properly installed on your machine

Here is an example of connecting with MySQL database named MYDATABASE

# Python MySQL Database Access - Example Program 
		
import MySQLdb

db=MySQLdb.connect("localhost","checkuser","checkpass123","MYDATABASE") #this opens database connection

cursor = db.cursor()

cursor.execute("SELECT VERSION()")

data = cursor.fetchone()

print("Database version: %s " % data)

db.close()	# this disconnect from the server

Here is the output produced by the above python script on Linux platform:

Database version : 5.0.45

Create Database Table in Python

Once a connection to the database is established, then we are able to create the tables or records into the database tables. Here is an example of creating a table:

# Python Database Access - Create a Database Table - Example Program

import MySQLdb

db = MySQLdb.connect("localhost","checkuser","checkpass123","MYDATABASE")
cursor = db.cursor()
cursor.execute("DROP TABLE IF EXISTS STUDENT")

sql = """CREATE TABLE STUDENT(
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         FEE FLOAT )"""

cursor.execute(sql)

db.close()

Insert Record in Database Table in Python

Insert statement is used to insert records into the table like this:

# Python MySQL Database Access - Example Program 

import MySQLdb

db = MySQLdb.connect("localhost","checkuser","checkpass123","MYDATABASE")

cursor = db.cursor()

sql = """INSERT INTO STUDENT(FIRST_NAME,
         LAST_NAME, AGE, SEX, FEE)
         VALUES ('Richard', 'Jones', 20, 'M', 2000)"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   db.commit()
except:
   db.rollback()

db.close()

Read Record back from the Database Table in Python

Here is an example program, reads all the records from the table, named STUDENT having fee more than 1000:

# Python MySQL Database Access - Example Program

import MySQLdb

db = MySQLdb.connect("localhost","checkuser","checkpass123","MYDATABASE")

cursor = db.cursor()

sql = "SELECT * FROM STUDENT \
       WHERE FEE > '%d'" % (1000)
try:
   cursor.execute(sql)
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      fee = row[4]
	  
      print("fname=%s,lname=%s,age=%d,sex=%s,fee=%d" % \
             (fname, lname, age, sex, fee))
except:
   print "Error: unable to fecth data"

db.close()

It will produce the following result:

fname=Richard, lname=Jones, age=20, sex=M, fee=2000

Python Online Test

« Previous Tutorial Next Tutorial »