Using Python to Query MySQL over the Net
Recently, I became the owner of more than a thousand records of Go (an ancient oriental strategic board game) games played by professional or amateur players. All the games were stored in Smart Game Format (SGF), which is a text-based format designed to keep records of board games for two players. Naturally, I made them available to other players through my web page.
To make searching through the archive easier, I first used an HTML form with only one text input field. The user could enter a string (for example, a player name) that was passed to a Python CGI script which, in turn, invoked good old grep to find the matching files, as shown in Figure 1. But this is a crude way of finding information. For instance, it can be used to find all the games played by a certain player but not to find more complicated things, such as all the games that player played with black stones or all the games won by that player in 1995.
In order to make a better search engine, I had to use a different approach. First, the database should describe the collection of game files. Then, a multiple input field form should be created to allow the user to search for various pieces of information at the same time. Finally, there should be a way to set up communication between the browser and database server, in order to make the result of the database query available to the user as an HTML document. Thus, the whole search would be performed as mapped in Figure 2.
I chose MySQL 3.22.32 as the database and Python 1.5.2 for CGI scripting. I have played with Perl but prefer the feel of Python. The installation procedure for these programs has been explained already in many articles and will not be covered here. The reader should check the web sites in Resources for further details concerning installation. Communication between MySQL and Python is handled by a contributed module, presented below.
Python makes MySQL queries through a special module designed by Joerg Senekowitsch. Of course, several other modules are available on the Net, but MySQLmodule1.4 was easy to install and learn, and it worked very well for me. On some systems (like FreeBSD) it is possible to install this module at the same time as Python. On my Slackware 7.1 Linux, I had to build and install it as a dynamically loadable module. This is a three-step procedure: untar the MySQLmodule archive, compile the shared module and install the module somewhere in Python's library path.
Step one is fairly simple. As root, one would type:
myhost:~# tar xvzf MySQLmodule-1.4.tar.gz
A new directory named MySQLmodule-1.4/ will be created with several files, the most important of which are MySQLmodule.c (the source for the module to be compiled) and README (a file with installation and use information).
There are several tricky things about step two. For instance, one must know precisely where the libraries and include files for MySQL and Python can be found. On my system, MySQL 3.22.32 places the mysqlclient library in /usr/lib/mysql and the mysql.h include file in /usr/include/mysql. Python libraries can be found in /usr/lib/python1.5/config and the include file in /usr/include/python1.5. The command to compile MySQLmodule is:
myhost:~# gcc -shared -I/usr/include/mysql MySQLmodule.so
Another hint: the order of items in the above command line is important and must not be changed! Believe me, this is a hard-learned truth.
Step three consists of copying MySQLmodule.so to a directory where it can be found by Python at runtime. For Python 1.5 this could be /usr/lib/python1.5/lib-dynload, where other shared object files also reside. With Python 2.0 (which I also tested) I would recommend using the directory /usr/lib/python2.0/site-packages/.
Once the module is installed, it should be available from Python. It is a good idea to check this right away with a simple import statement such as:
myhost:~$ python Python 1.5.2 (#1, May 28 2000, 18:04:10) Copyright 1991-1995 Stichting Matematisch Centrum, Amsterdam >>> import MySQL >>>
If Python doesn't complain with an error message, chances are the MySQL module is properly installed and working.
Getting Started with DevOps - Including New Data on IT Performance from Puppet Labs 2015 State of DevOps Report
August 27, 2015
12:00 PM CDT
DevOps represents a profound change from the way most IT departments have traditionally worked: from siloed teams and high-anxiety releases to everyone collaborating on uneventful and more frequent releases of higher-quality code. It doesn't matter how large or small an organization is, or even whether it's historically slow moving or risk averse — there are ways to adopt DevOps sanely, and get measurable results in just weeks.
Free to Linux Journal readers.Register Now!
- Hacking a Safe with Bash
- Django Models and Migrations
- Secure Server Deployments in Hostile Territory, Part II
- Home Automation with Raspberry Pi
- The Controversy Behind Canonical's Intellectual Property Policy
- Huge Package Overhaul for Debian and Ubuntu
- Shashlik - a Tasty New Android Simulator
- KDE Reveals Plasma Mobile
- Embed Linux in Monitoring and Control Systems
- diff -u: What's New in Kernel Development