Retrieving Data from MySQL via Python

In an earlier post Connecting to MySQL with Python, we only returned a single result line from a “SELECT VERSION()” query. We will now take a look at returning multiple rows of data from a MySQL database using Python.

You will need to have Python (including the MySQLdb and sys modules) and MySQL installed. We will also use the same database table (address) from a previous post, Inserting Data into MySQL with Perl.

Here is the SQL statement that we used to create the table:



SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
  `name_first` varchar(30) NOT NULL,
  `name_last` varchar(30) NOT NULL,
  `address_01` varchar(40) NOT NULL,
  `address_02` varchar(40) NOT NULL,
  `address_city` varchar(30) NOT NULL,
  `address_state` varchar(20) NOT NULL,
  `address_postal_code` varchar(12) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS = 1;

Be sure to populate your address table with some dummy data.


insert into `address` values('John','Smith','100 Main Street','Suite 500','Buffalo','NY','14201'),
('Dave','Jones','500 Second Avenue','Suite 100','Atlanta','GA','30303'),
('Tom','Watson','123 Golf Course Lane','Suite 1','Macon','GA','31066'),
('Jack','Nicklaus','400 Laurel Oak Dr','Suite 49','Suwanee','GA','31044');

Next, we will take the script from the earlier post and we simply have to modify our query, the "fetch" line (so it retrieves multiple values), and the print statement.

Fetch a single line:

# fetch a single row using fetchone() method.
row = cursor.fetchone ()

Fetch multiple rows:

# fetch all of the rows from the query
data = cursor.fetchall ()

Here is the python script:


- - - - START SCRIPT - - - - (do not include this line in the script)
#!/usr/bin/python
# view_rows.py - Fetch and display the rows from a MySQL database query

# import the MySQLdb and sys modules
import MySQLdb
import sys

# open a database connection
# be sure to change the host IP address, username, password and database name to match your own
connection = MySQLdb.connect (host = "192.168.1.2", user = "user", passwd = "password, db = "scripting_mysql")

# prepare a cursor object using cursor() method
cursor = connection.cursor ()

# execute the SQL query using execute() method.
cursor.execute ("select name_first, name_last from address")

# fetch all of the rows from the query
data = cursor.fetchall ()

# print the rows
for row in data :
print row[0], row[1]

# close the cursor object
cursor.close ()

# close the connection
connection.close ()

# exit the program
sys.exit()
- - - - STOP SCRIPT - - - - (do not include this line in the script)

Here is what the script looks like when I run it from the command line:

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world's most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
About these ads

2 Responses to Retrieving Data from MySQL via Python

  1. Zak says:

    Why would you not close the connection before doing the print statement?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 34 other followers

%d bloggers like this: