Building w/Python! Part 1: Database

The Python standard for database interfaces is the Python DB-API. Most Python database interfaces adhere to this standard.

You can choose the right database for your application. Python Database API supports a wide range of database servers such as −

  • GadFly

  • mSQL

  • MySQL

  • PostgreSQL

  • Microsoft SQL Server 2000

  • Informix

  • Interbase

  • Oracle

  • Sybase

Here is the list of available Python database interfaces: Python Database Interfaces and APIs .You must download a separate DB API module for each database you need to access. For example, if you need to access an Oracle database as well as a MySQL database, you must download both the Oracle and the MySQL database modules.

The DB API provides a minimal standard for working with databases using Python structures and syntax wherever possible. This API includes the following:

  • Importing the API module.

  • Acquiring a connection with the database.

  • Issuing SQL statements and stored procedures.

  • Closing the connection

We would learn all the concepts using MySQL, so let us talk about MySQLdb module.

What is MySQLdb?

MySQLdb is an interface for connecting to a MySQL database server from Python. It implements the Python Database API v2.0 and is built on top of the MySQL C API.

How do I Install MySQLdb?

Before proceeding, you make sure you have MySQLdb installed on your machine. Just type the following in your Python script and execute it:

1. #!/usr/bin/python

2. import MySQLdb

If it produces the following result, then it means MySQLdb module is not installed:

Traceback (most recent call last): File "test.py", line 3, in <module> import MySQLdb ImportError: No module named MySQLdb

To install MySQLdb module, download it from MySQLdb Download page and proceed as follows:

$ 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

Database Connection

Before connecting to a MySQL database, make sure of the followings −

  • You have created a database TESTDB.

  • You have created a table EMPLOYEE in TESTDB.

  • This table has fields FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.

  • User ID "testuser" and password "test123" are set to access TESTDB.

  • Python module MySQLdb is installed properly on your machine.

  • You have gone through MySQL tutorial to understand MySQL Basics.

Example

Following is the example of connecting with MySQL database "TESTDB"

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

# execute SQL query using execute() method.

cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.

data = cursor.fetchone()

print "Database version : %s " % data

# disconnect from server

db.close()

Creating Database Table

Once a database connection is established, we are ready to create tables or records into the database tables using execute method of the created cursor.

Example

Let us create Database table EMPLOYEE:

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

# Drop table if it already exist using execute() method.

cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Create table as per requirement

sql = """CREATE TABLE EMPLOYEE (

FIRST_NAME CHAR(20) NOT NULL,

LAST_NAME CHAR(20),

AGE INT,

SEX CHAR(1),

INCOME FLOAT )"""

cursor.execute(sql)

# disconnect from server

db.close()

#python #programming #database

Recent Posts 
Serach By Tags
No tags yet.