Database Programming in Python: Accessing MySQL - Accessing MySQL, Step By Step (Page 2 of 4 )
Python has provided aspecification for API for database access. This specification is in its second version and is known as DB-API 2.0. Any database access module has to conform to the DB-API 2.0 specification. Almost all the modules for RDBMSs conform to the specification. MySQLdb module is no exception. For accessing MySQL I will use MySQLdb. Four steps must be taken to make it work. They are:
1. Connecting with the database
2. Creation of the cursor
3. Execution of the SQL statement
4. Fetching the result set and iterating over the result set
The fourth step can incorporate any of the following: Create, Retrieve, Update and Delete, or CRUD in short. If the operation is Retrieve or Projection in terms of SQL, a fifth step is required. For all else, only the first four are required, and the fifth is optional. Here are the details:
1. Connecting with the database
A table resides within a database. This is particularly true for MySQL. To create a table, a database must be created first, or at least a database must be present. So to retrieve data from a table, a connection to the database must be established. This is done by using the connect() method. In other words, connect is the constructor of the MySQLdb. The parameters are as follows:
- host is the name of the system where the MySQL server is running. It can be a name or an IP address. If no value is passed, then the default value used is localhost.
- user is the user id, which must be authenticated. In other words, this is the authentic id for using the services of the Server. The default value is the current effective user. Most of the time it is either ‘nobody’ or ‘root’.
- passwd -- It is by using a combination of the user id and a password that MySQL server (or for that matter any server) authenticates a user. The default value is no passwords. That means a null string for this parameter.
- db is the database that must be used once the connection has been established with the server. However, if the database to be used is not selected, the connection established is of no use. There is no default value for this parameter.
- port is the port where the server is running. The default value is 3306.
There are more parameters. But these are the most important and most commonly used parameters. For example, to connect to a database ‘test’ running on a system having the name ‘Demo’ with the user ‘root’ and password ‘adm1n,’ the statement would be:
db= MySQLdb.connect(host=’Demo’, user=’root’ , passwd=’adm1n’, db=’test’)
The connect() returns a connection object. In the above example db contains the connection object. It is this object that will be used in the next step.
2. Creation of the cursor
In the terminology of databases, cursor is that area in the memory where the data fetched from the data tables are kept once the query is executed. In essence it is the scratch area for the database.
MySQL does not support cursors. But it is easy to emulate the functionality of cursors. That’s what the MySQLdb does. To get a cursor, the cursor() method of connection object has to be used. There is only one parameter to this method -- the class that implements cursor behavior. This parameter is optional. If no value is given, then it defaults to the standard Cursor class. If more control is required, then custom Cursor class can be provided. To obtain a cursor object the statement would be:
cursor= db.cursor()
Once the above statement is executed, the cursor variable would have a cursor object.
Next: Accessing MySQL, Step by Step continued >>
More Python Articles
More By A.P.Rajshekhar
/ 20 
