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 the first of the two lines, we can include the fish and price values as a second argument to execute():

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

To make this program executable, you can preface this code with a shebang line, make the file executable (by changing the permissions on the file), and then call it as you would any other local executable that is not in your execution path. Alternatively, you can call it from the command-line by prefacing it with a call to your local Python interpreter. In either case, don't forget to supply the arguments for sys.argv[]. Here I have run it using the latter method:

 python ./user-defined-data.py angel 7.00

This then appends the data to the database in real time.

mysql> SELECT * FROM menu;
+----+----------------+-------+
| id | name | price |
+----+----------------+-------+
| 1 | tuna | 7.50 | 
| 2 | bass | 6.75 | 
| 3 | salmon | 9.50 | 
| 4 | catfish | 5.00 | 
| 5 | trout | 6.00 | 
| 6 | haddock | 6.50 | 
| 7 | yellowfin tuna | 12.00 | 
| 8 | sole | 7.75 | 
| 9 | angel | 7.00 | 
+----+----------------+-------+
9 rows in set (0.01 sec)

As this is all within the Python API, you are not limited merely to %s, but can use the same string formatting techniques as you would anywhere else in Python.