Chapter 6 ■ Building low-Cost MysQl data nodes

Congratulations! You’ve written your first MySQL-enabled Python script. Now that we know the basics,
let’s move on to more powerful scripts using a source file instead of the interactive interpreter.
Most Python scripts (applications) are built using a file named .py and executed from
the command line as follows. We will use this method to execute the following examples. Thus, for each
example, you should open a file and enter the text as shown.

$ python my_script.py

Now let’s see how we can insert some data in a table. In this case, we simply want to read data from a
file and insert it into a table. I’ll let you use your imagination for how you could change the file to reading
sensors. In fact, I will show you how to do this in a later chapter.

■Note refer to Chapter 5 for the table layout. Be sure to empty the table if you performed the examples from

Chapter 5 so you can avoid key violations when running this example.

Open your favorite text editor and enter the code shown in Listing 6-7. Save the file with the name
simple_insert.py.

Listing 6-7. Inserting Data with Connector/Python

import mysql.connector
server = {
'user': 'root',
'password': ,
'host': '127.0.0.1',
'database': 'employees',
'raise_on_warnings': True,
}
cnx = mysql.connector.connect(**server)
cur = cnx.cursor()

read rows from a file for inserting into plant_monitor table

f = open("plants_data.txt")
lines = f.readlines()
f.close()

now insert the data

for line in lines:
cols = line.strip('\n').split(",") # comma-separated row
query = "INSERT INTO plant_monitoring.plants (name, location, climate)" \
" VALUES ('{0}','{1}',{2});".format(cols[0], cols[1], cols[2])
print query
cur.execute(query)
cnx.commit()
cur.close()
cnx.close()

Here we see the same startup code as the previous example only this time we’re reading values from a
file and performing an INSERT SQL statement on each. Take a moment to study the code and how it works.
Note that it uses string substitution.