Session notes, 27th April 2013 (part 2)

Procedure

  1. Get python program that reads serial port working, see attached program (SerialRead.py)

  2. Install MySQL server on Pi. You will be asked for a root password as part of the install, please user CoderDojo as password

    sudo apt-get install mysql-server

  3. Install MySQL library for Python

    sudo apt-get install python-mysqldb

  4. Log into MySQL and create table

    mysql -u root –p

    CREATE TABLE IF NOT EXISTS RndNums (Id INT PRIMARY KEY AUTO_IMCREMENT, RndNum VARCHAR(2)

  5. Create user (testuser) and set password (test623)

    CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test623';

  6. Grant privileges to testuser on all tables in testdb database

    GRANT ALL ON testdb.* TO 'testuser'@'localhost';

  7. Query new table

    SELECT * FROM RndNums;

  8. Modify Python program to include the following functionality. See program SerialDB.py
    1. database functionality to write values from Serial port to database table.

    2. functionality to write to serial port to turn led on Arduino on/off. If value is greater than 50 and turn led off if value is less than 50.

  9. After the program has run, use a terminal window to start MySQL and query table to check that values have been inserted. See steps 4 & 7 above

SerialRead.py


# SerialRead.py

# Import libraries
import serial
import sys

# X will be used as loop counter
x = 1

# InChar will be used to hold value from serial port
InChar = 0

# Open serial port
ser = serial.Serial('/dev/ttyACM0', 9600)

# Flush contents of serial port
ser.flush()

# Loop while x is less than 10
while x < 10:

	# Read value from serial port
	InChar = ser.readline()
			
	# Print loop counter x and value read from serial port
	print x, InChar

	# Increment x by 1
	x = x + 1

# Close serial port
ser.close

SerialDB.py


# SerialDB.py

# Import libraries
import serial
import time
import MySQLdb as mdb
import sys


# X will be used as loop counter
x = 1

# InChar will be used to hold value from serial port
InChar = 0

# HighLow set to High or Low depending on value in InChar
HighLow = ""


# Open serial port
ser = serial.Serial('/dev/ttyACM0', 9600)

# Flush contents of serial port
ser.flush()

# Open connection to MySQL database, User is testuser, 
# password is test623 and table is test
con = mdb.connect('localhost', 'testuser', 'test623', 'test');

# Create cursor to hold data to be written to database
cur = con.cursor()


# Loop while x is less than 10
while x < 10:

	# Read value from serial port
	InChar = ser.readline()

	# Check if value is greater than 50			
	if int(InChar) > 50:
		# Value > 50, set HighLow to High and
		# write a 1 to serial port
		HighLow = "High"
		ser.write(chr(0x1))
	else:
		# Value < 50, set HighLow to Low and
		# write a 0 to serial port
		HighLow = "Low"
		ser.write(chr(0x0))
		
	# Print loop counter x, Highlow and value
	# read from serial port
	print x, HighLow, InChar


	# Execute SQL statement to insert InChar value into
	# the RNUM column in the RndNums table 
	cur.execute("INSERT INTO RndNums(RNum) VALUES('" + str(InChar) + "')")


	# Increment x by 1
	x = x + 1

# Execute the SQL statement to commit/write data to
# database table. Without a Commit, data is not permanently
# stored in database
cur.execute("Commit;")	

# Close serial port
ser.close