HomePython Page 3 - MySQL Connectivity With Python
Animal Antics - Python
Python comes with a bunch of different modules that allow youto add new capabilities to your Python scripts. One of the more usefulones is the MySQLdb module, which allows you to execute SQL queries on aMySQL database through your Python application. This articledemonstrates basic usage of this module with simple examples andillustrations.
With that out of the way, here's a simple example that demonstrates some of the functionality of the DBI. Consider the following database table,
mysql> SELECT * FROM animals;
+---------+----------+
| name | species |
+---------+----------+
| Wallace | Walrus |
| Polly | Parrot |
| Freddie | Frog |
| Tusker | Elephant |
| Sammy | Skunk |
+---------+----------+
5 rows in set (0.01 sec)
and then consider this short Python script, which connects to the database and prints out the data within the table.
#!/usr/bin/python
# import MySQL module
import MySQLdb
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",
db="db56a")
# create a cursor
cursor = db.cursor()
# execute SQL statement
cursor.execute("SELECT * FROM animals")
# get the resultset as a tuple
result = cursor.fetchall()
# iterate through resultset
for record in result:
print record[0] , "-->", record[1]
Most of this is self-explanatory, but let me go through it with you briefly anyway.
The first step is to import the MySQLdb module, via Python's "import" function.
# import MySQL module
import MySQLdb
Once that's done, you can open up a connection to the MySQL database server, by passing the module's connect() method a series of connection parameters - the server name, the database user name and password, and the database name.
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",
db="db56a")
A successful connection returns a Connection object, which you can use to create a cursor.
# create a cursor
cursor = db.cursor()
This cursor is needed to execute an SQL statement, and to retrieve the generated resultset.
# execute SQL statement
cursor.execute("SELECT * FROM animals")
# get the resultset as a tuple
result = cursor.fetchall()
A number of methods are available to retrieve the SQL resultset - the one used here is the fetchall() method, which returns a tuple of tuples, each inner tuple representing a row of the resultset. This tuple can then be iterated over with a regular "for" loop, and its elements printed to the standard output.
# iterate through resultset
for record in result:
print record[0] , "-->", record[1]
