Connecting to MySQL with Perl

When I was designing web sites, for a long time I wrote my HTML code the “hard” way – by opening a text editor and manually typing in the code (this was before I purchased Adobe DreamWeaver).

During that time of manual HTML writing, I had a project that required forms on a web page, and I needed a place to store the information. After talking with a few tech friends, I decided to use MySQL as my web site database, and Perl as my scripting language.

I had written complex Bourne shell scripts before, but Perl was something entirely new. With a little help from a buddy of mine, after a few hours I was off and running. I was amazed at how easy it was to connect to a MySQL database with Perl.

This example will show you how to use Perl to connect to a MySQL database and simply retrieve the database version using an SQL statement. This script has the same functionality as my previous post Connecting to MySQL with Python.

For this example, we will assume that you have installed Perl and MySQL. You will also need to install the DBI and the DBD::mysql modules in Perl. These modules allow you to connect to the MySQL database.

In this script, we are going to use a text file to store our database connection information, as well as a sub-routine to help retrieve our database connection information and to create the connection string for connecting to the database.

Here is the script. (The first line of the script must be the path of your Perl executable – #!/usr/bin/perl.)

- - - - START SCRIPT - - - - (do not include this line in the script)
#!/usr/bin/perl -w

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the <a href=http://mysql.com/>MySQL</a> database
use DBI;
use DBD::mysql;

use warnings;

#----------------------------------------------------------------------
# open the accessDB file to retrieve the database name, host name, user name and password
open(ACCESS_INFO, "<..\/accessDB") || die "Can't access login credentials";

# assign the values in the accessDB file to the variables
my $database = <ACCESS_INFO>;
my $host = <ACCESS_INFO>;
my $userid = <ACCESS_INFO>;
my $passwd = <ACCESS_INFO>;

# the chomp() function will remove any newline character from the end of a string
chomp ($database, $host, $userid, $passwd);

# close the accessDB file
close(ACCESS_INFO);
#----------------------------------------------------------------------

# invoke the ConnectToMySQL sub-routine to make the database connection
$connection = ConnectToMySql($database);

# set the value of your SQL query
$query = "SELECT VERSION()";

# prepare your statement for connecting to the database
$statement = $connection->prepare($query);

# execute your SQL statement
$statement->execute();

# retrieve the values returned from executing your SQL statement
@data = $statement->fetchrow_array();
	
# print the first (and only) value from the @data array
# we add a \n for a new line (carriage return)
print "$data[0] \n";

# exit the script
exit;

#--- start sub-routine ------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

# assign the values to your connection variable
my $connectionInfo="dbi:mysql:$db;$host";

# make connection to database
my $l_connection = DBI->connect($connectionInfo,$userid,$passwd);

# the value of this connection is returned by the sub-routine
return $l_connection;

}

#--- end sub-routine --------------------------------------------------

- - - - STOP SCRIPT - - - - (do not include this line in the script)

In the sub-routine “ConnectToMySql” above, we are using a text file (named accessDB) to store our MySQL database name, host name, user name and password (we will call this our access file). In the previous post, we hard-coded the database name, host name, user name and password in the script itself. For this script, we will create a text file to store this information, and we will place the text file in the parent directory (from where the script is stored). I have my sample script in the /cgi-bin/blog/ directory, so we would place the access file in the parent directory /cgi-bin .

The purpose of this access file is to hide the connection information, but it also allows you to quickly change the information if you have a need to switch to a different database, host or user name. And you can use this same file for multiple scripts, but I would have separate scripts for each database name.

Here is a sample access file – which contains (in this order), the database name, host name, user name and password. To match the file name in the sub-routine, we are naming the file “accessDB”. This file should be saved as a text file and make sure that it has the correct read permissions (for Unix, the permission is 644).

- - - - START TEXT FILE - - - - (do not include this line in the file)
my_database_name
192.168.1.1
mysql_user
password
- - - - END TEXT FILE - - - - (do not include this line in the file)

Again, place this file in the parent directory of where your script is located. This prevents anyone from seeing the file in case you didn’t protect the directory contents from being listed via a web page.

We use the sub-routine to extract the database name, host name, user name and password from the file after reading the file into the program. You will place the sub-routine at the end of your Perl script.

Now you are ready to run the script, connect to the database, and retrieve the MySQL server version information. Here is what the script looks like when I run it from the command line:

It is a fairly easy script, but it is limited to displaying only one line of output. I will cover multiple lines of output in a future post.

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots   Visit http://2044thebook.com for more information.

Connecting to MySQL with Python

Scripting Python to connect to a MySQL database is fairly simple, even if you don’t have any much experience with Python.

You will need to have Python and MySQL installed. Once you have Python installed, you will need to import the MySQLdb and sys modules. For MySQL, you will simply need a user that has permissions to connect to the database and perform a select statement.

For this example, I am using Python v2.5, MySQL version 5.5.8., with Mac OS X 10.6.8.

Here are the links to download the files and applications necessary for this example script:
MySQL – http://dev.mysql.com/downloads
Python – www.python.org/getit/
MySQLdb module – http://sourceforge.net/projects/mysql-python/

In this post, I am not going to cover installing Python, MySQL or the Python modules, so you will need to do this on your own. There are plenty of other websites and blogs that can assist you with these installations. You will also need to know how to run a Python script on whatever OS you are using.

Once you have everything installed and tested to make sure each application is working properly, you are ready to run this script. This script will simply connect to the MySQL database and fetch the MySQL server version. You will need to substitute your own host IP address, username, password and database name in the script.

Since I am using a unix-based OS, I can simply open up a text editor (or use the vi editor) to write the script. You do not want to save the script in rich-text format, as the script will most likely fail upon execution. You want to save the script as a plain-text file. You will also want to make sure that the line “#!/usr/bin/python” is on the first line of the script.

– – – – START SCRIPT – – – – (do not include this line in the script)
#!/usr/bin/python
# version.py – Fetch and display the MySQL database server version.

# import the MySQLdb and sys modules
import MySQLdb
import sys

# open a database connection
# be sure to change the host IP address, username, password and database name to match your own
connection = MySQLdb.connect (host = “192.168.1.1”, user = “username”, passwd = “password”, db = “database_name”)

# prepare a cursor object using cursor() method
cursor = connection.cursor ()

# execute the SQL query using execute() method.
cursor.execute (“SELECT VERSION()”)

# fetch a single row using fetchone() method.
row = cursor.fetchone ()

# print the row[0]
# (Python starts the first row in an array with the number zero – instead of one)
print “Server version:”, row[0]

# close the cursor object
cursor.close ()

# close the connection
connection.close ()

# exit the program
sys.exit()

– – – – END SCRIPT – – – – (do not include this line in the script)

Here is what the script looks like when I run it from the command line:

It is a fairly easy script, but it is limited to displaying only one line of output. I will cover multiple lines of output in a future post.

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots   Visit http://2044thebook.com for more information.