At some point, you may wish to connect to a MySQL database remotely using a Python script. I wished to be able to do so to do the following:
1. grab coordinates from a samples database to plot points on a map via Cartopy and Matplotlib.
2. insert temperature and humidity readings into my MySQL database from an Arduino project hooked up to a remote machine.
N.B I am using Ubuntu 64bit desktop v14.04, and Python2.7 and I am going to assume that you have access to a MySQL database with remote access enabled.
I will not show you how to setup MySQL in this tutorial, for that see here
I am going to show you how this can be done using the MySQLdb module in python.
Step 1 – Install MySQLdb
To install MySQLdb in Ubuntu, the following should work:
1. Be sure you have pip installed on your machine using this command in terminal:
sudo easy_install pip
2. If you already have pip installed, it’d be a good idea to upgrade it now:
sudo pip install pip --upgrade
3. Build the dependencies for python-mysqldb libraries:
sudo apt-get build-dep python-mysqldb
4. Install the Python MySQL libraries:
sudo pip install MySQL-python
Step 2 – Write your connection script
In the following example, where there is a series of x’s e.g. xxxxx replace with the details relating to your database, and ‘table’ with the name of your target database table:
#import the MySQLdb module import MySQLdb #establish a connection to the database db=MySQLdb.connect(passwd="xxxxx",db="xxxxx",host="xxxxx",port=xxxxx,user="xxxxx") #define your cursor cursor = db.cursor() #define and execute your MySQL query string cursor.execute("SELECT * from table") #grab the rows in the query result and add to a variable rows = cursor.fetchall() #for each item in the rows variable, print item for row in rows: print row #release the database connection db.close()
If you run this script, you should see the whole table be printed to a console window.
Now that you can access a MySQL database using python, you can grab this data and do what you like with it. Stay tuned for part 2.