Chapter 6 ■ Building low-Cost MysQl data nodes
Notice the line in bold. This command is necessary to ensure the rows are written to the table. More
specifically, since I used a transactional storage engine and my server is set up for transactions, none of the
data is written until I explicitly commit the changes. Your server may be set up differently, but it does not
hurt to add this command here. You could add it inside the loop too, but it is best to push your commits out
to the latest code block, and in this case it is outside the loop.
The file we are reading has only a few rows and is a mockup of the plant-monitoring system example
from Chapter 5. Listing 6-8 shows the file contents. Note that I labeled it plants_data.txt. If you change the
file name, be sure to change the code accordingly.
Listing 6-8. Sample Data
Jerusalem Cherry,deck,2
Moses in the Cradle,patio,2
Peace Lilly,porch,1
Thanksgiving Cactus,porch,1
African Violet,porch,1
To run the script, issue the following command from the folder where you stored the file. Be sure to put
the data file in the same folder first. I show the results of running the script.
$ python ./simple_insert.py
INSERT INTO plant_monitoring.plants (name, location, climate) VALUES ('Jerusalem
Cherry','deck',2);
INSERT INTO plant_monitoring.plants (name, location, climate) VALUES ('Moses in the
Cradle','patio',2);
INSERT INTO plant_monitoring.plants (name, location, climate) VALUES ('Peace
Lilly','porch',1);
INSERT INTO plant_monitoring.plants (name, location, climate) VALUES ('Thanksgiving
Cactus','porch',1);
INSERT INTO plant_monitoring.plants (name, location, climate) VALUES ('African
Violet','porch',1);
Now let’s check our table. If we started with an empty table, we should see the following:mysql> SELECT * FROM plant_monitoring.plants;
+----+---------------------+----------+---------+
| id | name | location | climate |
+----+---------------------+----------+---------+
| 30 | Jerusalem Cherry | deck | outside |
| 31 | Moses in the Cradle | patio | outside |
| 32 | Peace Lilly | porch | inside |
| 33 | Thanksgiving Cactus | porch | inside |
| 34 | African Violet | porch | inside |
+----+---------------------+----------+---------+
5 rows in set (0.00 sec)
You can do much more with the connector than shown here. In fact, you can do just about anything
you want. Typically, I use Python scripts for performing complex operations on my databases and database
servers. For example, I may write a script to set up all of my databases, tables, functions, and so on, so that I
can reload a test or start an experiment on any server I want; I just supply different connection parameters
and run it.