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...