MySQL and Python

Over the weekend, I decided to write some python code to work with MySQL databases.  I already have experience working with MySQL and PHP and I have a small library of PHP functions to access MySQL.  The goal was to get the same functionality working in python.  Then, I can start storing data from the HESA in MySQL tables.

The first thing I had to do was install a python library.  I did this by typing

sudo apt-get install build-essential python-dev libmysqlclient-dev

at the command prompt.  This library is supposed to allow PIP to install the actual python package. 

Next, I had to install PIP, the app that lets you install python packages.  I did this by typing 

sudo apt-get install python-pip

Once PIP was installed, I used it to install the MySQL python library by typing

sudo pip install MySQL-python

The documentation for MySQL and python is not really that great.  I spent way too much time researching how to do all this.  Once I got this far however, I was able to talk to the MySQL server.

I found a simply python program to connect to a SQL server and display the version of the database.  I was able to use it to query both my local copy of MySQL and my version on the internet.

Next, I converted all of my PHP SQL code to python functions.  I have functions to connect and disconnect from a SQL server.  I also have functions to create insert statements, fix values in insert statement to guard against injection attacks, and execute a SQL statement and return the results.  I was able to successfully test all the functions with my test database.

Now that I have my SQL libraries, I will create a table in my test database to store readings from the HESA.  If only I had the HESA reading stuff...

Measuring Light with a Beaglebone Black

I spent Friday evening at the Milwaukee Makerspace experimenting with a photoresistor on my Beaglebone Black.  I am still trying to learn more about how the BBB works and am going through some learning tutorials on the Adafruit website.  They have one titled Measuring Light with a BeagleBone Black that I was using.

Before I could do anything, I had to enable ADC in the slots file.  The slots file is in the /sys/devices/bone.capemgr.9 folder.  I did not have the correct rights to modify the file.  This was fixed by typing 

sudo chmod 666 slots

to give all users rights to modify the file.

Next, I enabled ADC on the BBB by typing 

echo BB-ADC > slots

while in the folder where the slots file is stored.  I did not have to reboot the BBB.  The change took affect immediately.

I spent a couple of hours playing around with the circuit.  It is extremely simply since it only has a resistor and the photoresistor.  The circuit is a voltage divider.  Basically, the BBB puts a constant 1.8 volts into the circuit.  The resistor keeps some of the voltage from going to ground.  The pin that accepts the incoming voltage on the BBB measures different voltages depending on how much light is coming in to the photoresistor.  The more light that comes in, the more voltage goes to the BBB in pin and not to ground.

I'm not really sure why it works that way but it does.

So, my first experiment with analog readings on the BBB worked.  Next, I would like to figure out how to measure DC volts on the BBB.  That will be a bit tricky because the analog input pins will not take more than 1.8 volts.

Blocking Clash of Clans

My kids play Clash of Clans (CoC) a lot and I'm a bit worried about the addictive affect it has on them.  I want to limit access to CoC on my home network so I started researching how it could be done.

I have a Netgear WNDR3700v4 router that sits between the home network and the internet.  I also use OpenDNS to filter DNS searches.

The simplest way to block CoC with my setup is to block the port that CoC uses -- 9339.  On the Netgear router, click on the Advanced tab.  Then, Security, Block Services.

Click the Add button.  Then, fill out the fields as follows:

Service Type: User Defined
Protocol: TCP
Starting Port: 9339
Ending Port: 9339
Service Type/User Defined: Clash of Clans
Filter Services For: All IP Addresses

I could have only filtered the IP Addresses of my kids devices but that may change and it would be easy for them to get around.

Some web sites say that you can block access to Amazon's AWS servers since Clash of Clans is hosted on AWS.  However, I tried blocking keywords aws.amazon.com, amazonaws.com, amazon.aws.com.  Nothing worked.  I could still play CoC.  I searched the internet for the correct URL to block but I can't find it.

My Netgear router has a log that supposedly tracks every website that the devices go to, but it does not record CoC activity.

Finally, the Netgear router is very limited with security.  You can setup a schedule for blocking ports or sites but you can only have one schedule.  Plus when you block stuff, the scheduling settings apply to all of the things you want to block.  I'd like to be able to block some ports Always, and some ports on the schedule.  You can't do that.

Most home routers are limited like this.  Even the dd-wrt software has limited features.  I guess I will have to buy a commercial router to do what I want.