The obvious complement to record retrieval is the insertion of data into a MySQL database. Data insertion is a matter of learning the syntax of the MySQL keyword for the task and applying it through MySQL for Python.
As with retrieval, MySQL functions on the basis of parameter-based invocation and the returning of results in accordance with those parameters. All of this is again based on using MySQL for Python as an intermediary to that process to invoke MySQL, to log in, and to connect to our chosen database.
You will recall that, in Chapter 2, Simple Querying, we needed to validate user input consistently. Malformed input would have caused our program to throw an error without it. That caution goes doubly for insertion. Unqualified user input can corrupt a database and even give the malicious user access to all traffic on the server by granting him or her unwarranted administrative privileges.
In this chapter, we will look at the following:
- Forming an insertion statement in MySQL
- Passing an insertion to MySQL
- User-defined variables in a MySQL insertion
- Passing metadata between databases
- Changing insertion statements dynamically, without user input
Each of these sections will be built into the project at the end of this chapter: Inserting user input into MySQL from the command-line without using the MySQL shell.
As with record retrieval in the previous chapter, inserting data into MySQL through Python relies on understanding data insertion in MySQL itself. You will recall that the requirements of a computing language necessitate the use of as few words as possible to do anything. Ideally, there should be only one word as the Zen of Python reads:
There should be one—and preferably only one—obvious way to do it.
For retrieval, we used the SELECT command. For putting data into the database, we use INSERT. So instead of saying "Put everything on the far table!" or "Stick everything over there!", MySQL needs specification such as:
INSERT INTO far VALUES("everything");
This is perhaps the most basic insertion statement that one can make for MySQL. You can tell from it that the basic syntax of MySQL's INSERT statement is as follows:
INSERT INTO <some table> (<some column names>) VALUES("<some values>");
Now let's take this skeleton of a statement apart and see how MySQL compares to what we might use in English.
It should be clear by now that the use of INSERT is for our benefit as humans. There is nothing special about the word other than the fact that the MySQL programmer used it. It is easier to remember, closer to being standard throughout English, and better reflects the action being called than, say, STICK. As you may know, put is currently used in other programming languages for much the same kind of functionality (for example, fputs in PHP, C, C++). The keyword consequently could have been PAPAYA if the MySQL programmers coded the database system to use that word instead of INSERT (of course, the usability of the system would have taken a sharp drop at that point). All that matters is that we use the word that the system requires in order to do the action that we desire.
It is worth noting that there is one other keyword that can be used for placing data into a MySQL database. REPLACE uses much the same syntax as INSERT.
REPLACE INTO <some table> SET("<some column name>" = "<some value>");
As it is formed on analogy with SELECT, we will not discuss REPLACE much. However, you can read more about it on the MySQL manual page at: http://dev.mysql.com/doc/refman/5.1/en/replace.html
In a lot of ways, the MySQL database handles insertion like a postmaster. It will put mail anywhere you tell it as long as the box exists. So if we are going to tell MySQL to INSERT something, we must tell it where that something must go. To do that we use the complementary keyword INTO. This is the natural complement to the commands INSERT and REPLACE.
If you are new to computer programming, it may still seem reasonable to ask a computer to just do something. But computers are ultimately just machines, exceedingly fast and dumb. They will not reason unless they are explicitly, painstakingly, told how to reason by the programmer. They cannot guess unless told how. In the early days of modern computing, the 1970s and early 1980s, programmers would describe this dynamic of computing with the acronym GIGO—garbage in, garbage out. If you as the programmer don't tell it what to do, it won't know how to do it.
Python helps with this process by offering high-level handles for a lot of common functionality, but there are still limits to that automation and elements of programming for which one must assume responsibility. Where MySQL sticks your data is one of them. The table value is yours to define. If you tell MySQL the correct place to put information, all is well. If it puts it in the wrong place, chances are you are to blame (unless someone is holding a strong magnet next to the CPU at the time). If MySQL does not know what to do with your data, it will throw an error—as we will see in the next chapter.
In this part of the statement, you indicate to MySQL the order in which you will pass the values later in the statement. These are dealt with like variable names and so are not set in quotes, single or double.
The column names that you must address here and in the value section of the statement are determined by the nature of the database. If we use the fish database from the previous chapter, we have the following dataset:
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 | +----+----------------+-------+ 8 rows in set (0.00 sec)
The definitions for this dataset are purposely poor for illustrative reasons.
mysql> describe menu; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(30) | YES | | NULL | | | price | decimal(6,2) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
As such, the only value that is required, 'that cannot be left blank', is the value for id, the primary key. This is already set by the system because it is automatically incremented. Therefore, we can get away with the following statement:
mysql> insert into menu(name) values("shark");
You will notice that we have left off the value for the price column. The effect is that it is now set to a NULL value:
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 | shark | NULL | +----+----------------+-------+ 9 rows in set (0.00 sec)
NULL values in themselves are not bad. All computing is data and code, but both code and data must be controlled by the programmer to affect a desired, controlled result. Otherwise, errors are sure to creep in along with aberrations, and compromises in security and effectiveness will be the result.
Any data on which a program depends for its execution should be required. If this were an enterprise database, you would probably want this hole closed and so would define the table differently. You would want to require a non-NULL value for as many columns as are necessary to ensure the security and serviceability of your database.
There are two keywords that you can use to introduce the data to be inserted at this point in the INSERT statement: VALUE or VALUES. Either one is correct; both can be used with either a single value or multiple values. There needs to be no consonance between the number of values being inserted and the number aspect of the keyword.
VALUES("<some values>", "<some more values>", "<some other values>");
is to MySQL the same as:
VALUE("<some values>", "<some more values>", "<some other values>");
just like the following two phrases of an INSERT statement are the same:
VALUE("<some values>");
VALUES("<some values>");
All this keyword slot does is introduces the values in parentheses.
The values that follow the VALUES keyword must appear in the same order as the column names. Otherwise, MySQL will try to place the data in the wrong location. If you do not verify the integrity of the data passed to MySQL, the data can quickly get out of hand. Consider the effect of this statement on the table menu.
mysql> INSERT INTO menu(name, price) VALUES("13.00", "shark");
Because of the discord between the order of column names and the order of values, this statement tells MySQL to insert the fields name and price with the following values:
name = 13.00price = shark
The problem is that these values are not allowed by the definition of the table:
mysql> describe menu; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(30) | YES | | NULL | | | price | decimal(6,2) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
The field name is supposed to be a thirty character string. The field price is supposed to be a decimal value with up to six numbers to the left of the decimal point and up to two to the right. So what happens when the two are mixed up to the point of utter confusion? Disaster.
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 | shark | NULL | | 10 | 13.00 | 0.00 | +----+----------------+-------+ 10 rows in set (0.00 sec)
We get a fish called 13.00 that costs nothing! The value 13.00 can be a varchar string and is so interpreted by MySQL. However, shark cannot be interpreted as a decimal value in this context.
It is worth noting that the reason shark cannot be a decimal value is because it is not defined as such. By passing it in double quotes, we indicate that it is a value, not a variable name. If, however, we had previously defined a variable shark as a decimal value, then we could use it accordingly.
Such a definition could be done in either MySQL or Python. In Python, we would use a simple variable assignment statement:
shark = 13.00
This would be truncated by Python to 13.00, but it would nonetheless preserve the integrity of the datatype (to insert 13.00, we would need to use a DECIMAL type for the column when we create the table). The second zero could later be reclaimed with a formatting convention.
In MySQL, we would use the SET command. See the MySQL manual Section 8.4, for more: