Home arrow Python arrow Page 6 - MySQL Connectivity With Python

Be Prepared - 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
Many database scripts involve preparing a single query (an INSERT, for example) and then executing it again and again with different values. MySQLdb comes with an executemany() method, which simplifies this task and can also reduce performance overhead.

In order to understand how this works, consider the following example, which demonstrates:

#!/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() # dynamically generate SQL statements from list cursor.executemany("INSERT INTO animals (name, species) VALUES (%s, %s)", [ ('Rollo', 'Rat'), ('Dudley', 'Dolphin'), ('Mark', 'Marmoset') ])
In this case, the same query is repeated multiple times, with a different set of values each time. The values for each iteration are provided to the executemany() method as a Python list; each element of the list is a tuple containing the values for that iteration.

Using this technique, it's possible to write a script that asks the user to enter a series of data values, and then inserts them all into the database in one swell foop using the executemany() method. Which is just what I've done below:

#!/usr/bin/python # import MySQL module import MySQLdb # initialize some variables name = "" data = [] # loop and ask for user input while (1): name = raw_input("Please enter a name (EOF to end): ") if name == "EOF": break species = raw_input("Please enter a species: ") # put user input into a tuple tuple = (name, species) # and append to data[] list data.append(tuple) # connect db = MySQLdb.connect(host="localhost", user="joe", passwd="secret", db="db56a") # create a cursor cursor = db.cursor() # dynamically generate SQL statements from data[] list cursor.executemany("INSERT INTO animals (name, species) VALUES (%s, %s)", data)
In this case, a "while" loop is used to continuously throw up user prompts, with each set of values entered by the user being packaged into a tuple and added to the "data" list. Once the user completes entering all the data, an executemany() statement is used, in combination with the various input values, to INSERT the values into the database.

 
 
>>> 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: