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
·
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.
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.
·
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.