Using user-defined variables

Just as in data retrieval, it is inevitable that you will want to utilize user input when inserting data into MySQL. MySQL for Python provides a consistent, Pythonic interface for this.

We use the same string conversion specifier as we did when incorporating user input into our SELECT statements in the previous chapter. Using the fish database, if we assume that the user gives us the name of the fish and the cost, we can code a user-defined INSERT statement as follows:

import MySQLdb, sys

mydb = MySQLdb.connect(host = 'localhost', 
                       user = 'skipper', 
                       passwd = 'secret', 
                       db = 'fish')
cur = mydb.cursor()

fish = sys.argv[1]
price = sys.argv[2]

statement = """INSERT INTO menu(name, price) VALUES(%s, %s)""" %(fish, price)
cur.execute(statement)

An alternative way of rendering the last two lines is to leave the value insertion to the execute() function. Instead of using %(fish, price) at the end of...