CREATING PYTHON SCRIPT WITH MYSQL

mysql> SELECT * FROM name;
+----------+----------+
| name | Lastname |
+----------+----------+
| John Paul| Mam |
| Will | Smith |
| Asyong | Taga |
+----------+----------+
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="paul", passwd="jpm1986",
db="db56a")

# create a cursor
cursor = db.cursor()

# execute SQL statement
cursor.execute("SELECT * FROM name")

# get the resultset as a tuple
result = cursor.fetchall()

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


The first step is to import the MySQLdb module, via Python's "import" function.

# import MySQL module
import MySQLdb

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="paul", passwd="jpm1986",
db="db1")

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 name")

# 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]

PYTHON SCRIPT

#!/usr/bin/python

# import MySQL module
import MySQLdb

# connect
db = MySQLdb.connect(host="localhost", user="paul", passwd="jpm1986",
db="db56a")

# create a cursor
cursor = db.cursor()

# execute SQL statement
cursor.execute("SELECT * FROM name")

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

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