MySQL Connectivity With Python - Animal Antics (
Page 3 of 7 )
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]
