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.
Fast/Flexible Linux OS Recovery
On Demand Now
In this live one-hour webinar, learn how to enhance your existing backup strategies for complete disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible full-system recovery solution for UNIX and Linux systems.
Join Linux Journal's Shawn Powers and David Huffman, President/CEO, Storix, Inc.
Free to Linux Journal readers.Register Now!
- Firefox 46.0 Released
- Ubuntu Online Summit
- The Qt Company's Qt Start-Up
- Devuan Beta Release
- May 2016 Issue of Linux Journal
- The US Government and Open-Source Software
- The Death of RoboVM
- Download "Linux Management with Red Hat Satellite: Measuring Business Impact and ROI"
- Open-Source Project Secretly Funded by CIA
- New Container Image Standard Promises More Portable Apps
In modern computer systems, privacy and security are mandatory. However, connections from the outside over public networks automatically imply risks. One easily available solution to avoid eavesdroppers’ attempts is SSH. But, its wide adoption during the past 21 years has made it a target for attackers, so hardening your system properly is a must.
Additionally, in highly regulated markets, you must comply with specific operational requirements, proving that you conform to standards and even that you have included new mandatory authentication methods, such as two-factor authentication. In this ebook, I discuss SSH and how to configure and manage it to guarantee that your network is safe, your data is secure and that you comply with relevant regulations.Get the Guide