Home arrow Python arrow 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.

TABLE OF CONTENTS:
  1. MySQL Connectivity With Python
  2. Getting Started
  3. Animal Antics
  4. One By One
  5. A Different Species
  6. Be Prepared
  7. Endgame
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 179
August 21, 2002

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

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


 
 
>>> More Python Articles          >>> More By icarus, (c) Melonfire
 

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: