Python Database Access
The standard database used for Python is 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 11000, Informix, Interbase, Oracle, Sybase. You must download a separate DB API module for each database that you need to access. For example, if you need to access an Oracle database as well as a MySQL database, then you need to 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.
The API includes:
• Importing the API module.
• Acquiring a connection with the database.
• Issuing SQL statements and stored procedures.
• Closing the connection
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 to install MySQLdb?
Before proceeding, you make sure you have MySQLdb installed on your Tomhine. Just type the following in your Python script and execute it:
#!/usr/bin/python
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, you need to make sure of the followings points given below:
• You have created a database TESTDB.
• You have created a table STAFF in TESTDB.
• This table is having fields FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.
• User ID “abctest” and password “python121” are set to access TESTDB.
• Python module MySQLdb is installed properly on your Tomhine.
• You have gone through MySQL tutorial to understand MySQL Basics.
For Example:
Connecting with MySQL database “TESTDB”:
#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect(“localhost”,“abctest”,“python121”,“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()
Output:
Database version : 5.0.45
Creating Database Table:
Once a database connection is established, you can easily create tables or records into the database using execute method.
Example for creating Database table STAFF:
#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect(“localhost”,“abctest”,“python121”,“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 STAFF”)
# Create table as per requirement sql = “““CREATE TABLE STAFF (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()
INSERT Operation:
INSERT operation is required when you want to create your records into a database table.
Example to create a record into STAFF table:
#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect(“localhost”,“abctest”,“python121”,“TESTDB” )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = “““INSERT INTO STAFF(FIRST_NAME,LAST_NAME, AGE, SEX, INCOME)
VALUES (‘Tom’, ‘David’, 20, ‘M’, 11000)”””
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
Above example can be written as follows to create SQL queries dynamically:
#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect(“localhost”,“abctest”,“python121”,“TESTDB” )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = “INSERT INTO STAFF(FIRST_NAME, \LAST_NAME, AGE, SEX, INCOME) \
VALUES (‘%s’, ‘%s’, ‘%d’, ‘%c’, ‘%d’ )” % \ (‘Tom’, ‘David’, 20, ‘M’, 11000)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
READ Operation:
READ Operation on database 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.
• 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.
• 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.
• rowcount: This is a read-only attribute and returns the number of rows that were affected by an execute() method.
Example to query all the records from STAFF table having salary more than 5000:
#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect(“localhost”,“abctest”,“python121”,“TESTDB” )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = “SELECT * FROM STAFF \
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()
Output:
fname=Tom, lname=David, age=20, sex=M, income=11000
Update Operation:
UPDATE Operation on any database means to update one or more records, which are
already available in the database. Following is the procedure to update all the records
having SEX as ‘M’. Here, we will increase AGE of all the males by one year.
For Example:
#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect(“localhost”,“abctest”,“python121”,“TESTDB” )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to UPDATE required records
sql = “UPDATE STAFF SET AGE = AGE + 1
WHERE SEX = ‘%c’” % (‘M’)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
DELETE Operation:
DELETE operation is required when you want to delete some records from your database. Following is the procedure to delete all the records from STAFF where AGE is more than 20:
For Example:
#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect(“localhost”,“abctest”,“python121”,“TESTDB” )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to DELETE required records
sql = “DELETE FROM STAFF WHERE AGE > ‘%d’” % (20)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
