MySQL Connectivity With Python - Be Prepared (
Page 6 of 7 )
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.
