Python Database Access

Share on facebook
Share on twitter
Share on linkedin
Share on twitter
Share on tumblr

In this Python tutorial, let us discuss Python database access, installation and database connection with suitable example programs.

Introduction of Python Database Access

Most of the Python database interfaces adhere to the Python DB-API. You should choose the appropriate 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

Let us take MySQL as an example.

MySQL Database

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

Installation of MySQL DB

For Ubuntu, use the following command 

$ sudo apt-get install python-pip python-dev libmysqlclient-dev
For Fedora, use the following command -
$ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc
For Python command prompt, use the following command -
pip install MySQL-python

You can also check if the MySQL database is installed by using the following code,

#!/usr/bin/python
import MySQLdb

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.

Example code for connecting a database

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

Result

Database version: 5.0.45

Leave a Comment

Your email address will not be published. Required fields are marked *