Python Database Access

Python Database Access

The Python standard for database interfaces is the Python DB-API. Most Python database interfaces adhere to this standard. Python Database API supports a wide range of database servers such as −

·        GadFly

·        MySQL

·        MySQL

·        PostgreSQL

·        Microsoft SQL Server 2000

·        Informix

·        Interbase

·        Oracle

·        Sybase

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

·        Importing the API module.

·        Acquiring a connection with the database.

·        Issuing SQL statements and stored procedures.

·        Closing the connection.

What is MySQL db?

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

How do I Install MySQLdb?

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

#!/usr/bin/python

Import MySQLdb

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

           Traceback (most recent call last):

                       Import MySQLdb

           ImportError: No module named MySQLdb

Database Connection

Before connecting to the MySQL database, check the followings −

·        Create a database TESTDB.

·        Create a table EMPLOYEE in TESTDB.

·        This table has fields such as 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 the machine.

While running this script, the below result would be produced.

           Database version: 5.0.45

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.

INSERT Operation

It is required when we want to create the records into a database table.

READ Operation

Fetch some useful information from the database. Once our database connection is established and then makes a query into this database.

·        fetchone() -  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() - Fetches all the rows in a result set. If some rows have already been extracted from the result set, then it retrieves the remaining rows from the result set.

·        Rowcount -  This is a read-only attribute, returns the number of rows that were affected by an execute() method.

Update Operation

Used to update one or more records that are already available in the database.

DELETE Operation

Used to delete some records from the database.

Performing Transactions

It is a mechanism that ensures data consistency having the following properties:

·        Atomicity - Either a transaction completes or nothing happens at all.

·        Consistency - A transaction must start in a consistent state and leave in a consistent state.

·        Isolation - Intermediate results of a transaction are not visible outside the current transaction.

·        Durability - Once a transaction was committed, the effects are persistent, even after a system failure.

The Python DB API 2.0 provides two methods to either commit or rollback a transaction.

COMMIT Operation

It gives a green signal to database to finalize the changes, and after this operation, no change can be reverted back.

ROLLBACK Operation

Revert back the changes completely by using use rollback() method.

Disconnecting Database

By using the close() method to disconnect the Database connection.

Handling Errors

There are many sources of errors. The DB API defines a number of errors that must exist in each database module. The exceptions are mentioned below.

·        Warning – Used for non-fatal issues.

·        Error – Base class for errors.

·        Interface Error – Used for errors in the database module not for the database itself.

·        Database Error – Used for errors in database.

·        Data Error – Subclass of database error which refers the errors in the data.

·        Operational Error – Refers the loss of connection to the database that are outside of control of the python scripter.

·        Integrity Error – Damage the relational integrity such as uniqueness constraints or foreign keys.

·        Internal Error – Refers to the errors internal to the database module.

·        Programming Error - Refers to the errors such as bad table name.

·        Not Supported Error – Refers that trying to call unsupported functionality.