Home arrow Python arrow Page 3 - Database Programming in Python: Accessing MySQL

Accessing MySQL, Step by Step continued - Python

While data storage for applications is no longer considered complex, it is now simplified to the point that flexibility is sacrificed. There is a good compromise, however. In this article, you will learn how to access a a MySQL database with Python.

TABLE OF CONTENTS:
  1. Database Programming in Python: Accessing MySQL
  2. Accessing MySQL, Step By Step
  3. Accessing MySQL, Step by Step continued
  4. Accessing MySQL in the Real World
By: A.P.Rajshekhar
Rating: starstarstarstarstar / 49
February 21, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
  

3. Execution of the SQL statement

The steps enumerated until now have done the job of connecting the application with the database and providing an object that simulates the functionality of cursors. The stage has been set for execution of SQL statements. Any SQL statement supported by MySQL can be executed using the execute() method of the Cursor class. The SQL statement is passed as a string to it. Once the statement is executed successfully, the Cursor object will contain the result set of the retrieved values. For example, to retrieve all the rows of a table named USER_MASTER the statement would be:

cursor.execute(“select * from USER_MASTER”)

Once the above statement is executed, the cursor object would contain all the retrieved. This brings us to the fourth step, fetching of the resultset. Before moving on to the next step, there is one point you must understand. The execute() function accepts and executes any valid SQL statement, including DDL statements such as delete table, alter table, and so on. In the case of DDL statements, there is no fifth step (i.e. iteration over the results fetched).

4. Fetching the resultset

The flexibility of Python comes to the fore in this step also. In the real world, fetching all the rows at once may not be feasible. MySQLdb answers this situation by providing different versions of the fetch() function of Cursor class. The two most commonly used versions are:

  • fetchone(): This fetches one row in the form of a Python tuple. All the data types are mapped to the Python data types except one -- unsigned integer. To avoid any overflow problem, it is mapped to the long. 
  • fetchall(): This fetches all the rows as tuple of tuples. While fetchone() increments the cursor position by one, fetchall() does nothing of that kind. Everything else is similar.

The subtleties will become clear from the following example. To fetch one row at a time and display the result, the block would be:

numrows = int(cursor.rowcount) #get the count of total
rows in the 
#resultset

# get and display one row at a time
for x in range(0,numrows):
        row = cursor.fetchone()
        print row[0], "-->", row[1]

The above result can be achieved by using fetchall() as shown below:

result = cursor.fetchall()

# iterate through resultset
for record in result:
        print record[0] , "-->", record[1]

 

The iteration is through the core Python APIs only. As the returned data structure is tuple, no extra API is required.

That covers all the steps required to access MySQL. What I have discussed up to now is "the approach of tackling a problem of the type database connectivity." However, in real life, reusability plays a more important role than it has in what you have seen so far. Hence in the next section I will be using the steps discussed until now to create a generic class for accessing MySQL.



 
 
>>> More Python Articles          >>> More By A.P.Rajshekhar
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PYTHON ARTICLES

- Python Big Data Company Gets DARPA Funding
- Python 32 Now Available
- Final Alpha for Python 3.2 is Released
- Python 3.1: String Formatting
- Python 3.1: Strings and Quotes
- Python 3.1: Programming Basics and Strings
- Tuples and Other Python Object Types
- The Dictionary Python Object Type
- String and List Python Object Types
- Introducing Python Object Types
- Mobile Programming using PyS60: Advanced UI ...
- Nested Functions in Python
- Python Parameters, Functions and Arguments
- Python Statements and Functions
- Statements and Iterators in Python

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: