Using INSERT with MySQL for Python is just as easy as using SELECT. As we saw in the previous chapter, we pass the command to MySQL using the execute() method of the database cursor object.

We will again use the fish database and the menu table as follows:

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

Once again, we need to set up the database objects in our Python session. If you are using the same Python terminal session as you did for the previous chapter, you may want to go through this process anyway to ensure that all names are set for the examples to come. Alternatively, close the session by pressing Ctrl+D and initiate a new one. Then import MySQL for Python, tend to the database login and create the cursor object as follows:

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

Now we are ready to insert data using Python.

Inserting data through MySQL for Python uses the same method as retrieving it using execute(). You will recall that data retrieval using MySQL for Python follows this formula:

results_variable = cursor_handle.execute('MySQL SELECT statement')

And so one gets a Python statement that looks like the following:

command = cur.execute('SELECT * FROM menu')

The main difference in data insertion is that no values are being returned. Therefore, because we are not retrieving any data, we do not need to assign the value of returning data to a variable. Instead, we pass the insertion command as a stand-alone command.

The basic system call for the insertion command would follow this template:

cursor_handle.execute('MySQL INSERT statement')

Using this template, we can pass the following MySQL INSERT statement:

INSERT INTO menu(name, price) VALUES("shark", "13.00");

Without worrying about validating the integrity of the data for the moment, we insert this statement through MySQL for Python as follows (using the cursor object cur as defined previously):

cur.execute("""INSERT INTO menu(name, price) VALUES("shark", "13.00")""")

Of course, as with the SELECT statement in the previous chapter, this statement can become difficult to control rather quickly because of the number of quotation marks and parentheses. If this proves difficult to follow for you, simply break the statement down by defining the argument for execute(), the actual MySQL statement, in a separate line. As with elsewhere in Python, you can use triple quotes to assign a value verbatim. The preceding call could then be rewritten as follows:

statement = """INSERT INTO menu(name, price) VALUES("shark", "13.00")""" 
cur.execute(statement)

Using triple quotes is also helpful for handling more complex statements as they can bridge multiple lines. This makes it easier to format statements in a way that humans can read more easily. Therefore, to use the ON DUPLICATE KEY UPDATE... example from earlier in this chapter, we can define the statement:

INSERT INTO Combo(ID, Name, CountryCode) VALUES ("4078", "Singapore", "SGP") ON DUPLICATE KEY UPDATE ID=ID+1;

As follows for better readability:

statement = """INSERT INTO Combo(ID, Name, CountryCode) 
                                        VALUES ("4078", "Singapore", "SGP") 
                                        ON DUPLICATE KEY UPDATE ID=ID+1;"""

If this is still too complex for you to follow with ease, it may be advisable to rework the value of statement by employing string formatting techniques as shown later in the chapter under the heading Using user-defined variables.

To pass the INSERT command with any of its optional arguments, simply include them in the statement. For example, where we had the following INSERT...SELECT... command:

INSERT INTO Combo(ID, Name, CountryCode) SELECT ID, Name, CountryCode FROM City WHERE ID < 1000;

One can simply pack all of that into the value of statement:

statement = """INSERT INTO Combo(ID, Name, CountryCode) SELECT ID, Name, CountryCode FROM City WHERE ID < 1000;"""

The DELAYED argument can be passed similarly. The previous statement passed through execute() would look like this:

cur.execute("""INSERT DELAYED INTO Combo(ID, Name, CountryCode) SELECT ID, Name, CountryCode FROM City WHERE ID < 1000;""")

Likewise, we could include the INSERT...ON DUPLICATE KEY UPDATE... argument as follows:

cur.execute("""INSERT INTO Combo(ID, Name, CountryCode) VALUES ("4078", "Singapore", "SGP") ON DUPLICATE KEY UPDATE ID=ID+1""")