Writing Administration Scripts | FreeBSD 6 Unleashed

You've reached the next level in our breakneck tour through the world of databases; now that the tables in the sample databases are set up, you can move on past the use of the interactive command line and learn how to automate some of the longer, more painful queries. The MySQL or PostgreSQL command line will always be useful for design and tinkering purposes. However, some tasks that must be done on a regular basis and require long, involved queries demand that you find a less frustrating way to accomplish them.

Perl and Python both provide toolkits with which you can write scripts to interface with the database. These toolkits enable you to issue queries, extract data, process it, and feed data back into the system, all within loops and conditionals and all the other programmatic benefits that those languages offer.

Database Connectivity with Perl

There are available modules for connecting your Perl scripts to either a MySQL or a PostgreSQL database. We'll look at MySQL first.

Perl and MySQL

As a courtesy, the necessary Perl modules for connectivity to MySQL are installed along with the MySQL port. These also are located in /usr/ports/databases/p5-Mysql; the documentation can be read with perldoc Mysql.

The crucial part of these modules is the setup part of any script that intends to connect to a MySQL database. This setup block includes the following lines, which should go right at the top of the script:

use Mysql; $dbh = Mysql->connect(undef, "PicureArchive", "frank", "franks-password");

After you add these lines to the script, you have a database handle to work with: $dbh. A whole suite of operations can be performed on this handle; this chapter doesn't discuss all of themjust the most important operations. You will find the rest in the perldoc Mysql documentation.

Issue a query of any type to the database by using the $dbh->query method and setting it to a variable that acts as a handle to the results:

$queryh = $dbh->query("SELECT Filename,Type FROM Pictures WHERE User_ID='5'");

You can now use the fetchrow method to get the first row of the output. The way this works is that you can keep calling fetchrow over and over (in a loop) until there is no more output; this is how you can apply the same Perl code to each line of output from the database, in whatever order your SQL query specifies:

while (($filename,$filetype) = $queryh->fetchrow) {   print "$filename, $filetype\n"; }

Here is a list of other operators that might be useful:

$dbh->quote quote function>Surround a variable with this operator to quote its contents properly in a query; for example, to ensure that a full name such as "Bob O'Shea" will be properly inserted without the apostrophe causing confusion in the query string, use $dbh->query("INSERT INTO Users (Fullname) VALUES (".$dbh->quote ($fullname).")");.
numrows function>$queryh->numrows This will return the number of rows that the query in $queryh will return.
insertid function>$queryh->insertid If your query was an INSERT and the table had an AUTO_INCREMENT column that generated a new ID, this function will return that ID so you can use it for future queries.

Perl and PostgreSQL

To connect to PostgreSQL in Perl, you will need to install the /usr/ports/databases/p5-Pg port. This module provides much the same functionality as the p5-Mysql module does; you can set up a database connection with the following commands:

use Pg; $conn = Pg::setdbLogin(undef,undef,undef,undef,"PictureArchive","frank","franks-password");

Next, submit a query with the $conn->exec method:

$result = $conn->exec("SELECT Filename,Type FROM Pictures WHERE User_ID='5'");

Then use $result->fetchrow as in the MySQL example you saw previously in this chapter. All the possibilities for the $resulthandle can be found in the perldoc Pg documentation, in the section titled "2. Result."

Note

For both these connectivity packages, the examples describe queries being executed in autocommit mode, meaning that each query is sent to the database as soon as it is formulated (using $dbh->query or $conn->exec), and results are immediately available to fetchrow commands. Each package contains analogous commands that can be used to operate in a mode where queries are not automatically committed. This approach is often very useful for operations that must be atomic (that is, treated as a single instruction that cannot be interrupted by other processes that can potentially change the state of the database between the start and end of the operation). Atomic operations are considered much safer than nonatomic ones, and it's good database coding practice to use them wherever possible. Refer to the perldoc documentation for each package for details.

Database Connectivity with Python

Python's connectivity is built upon the Python Database API Specification 2.0. Apart from the details of the setup lines and how the connection itself is set up, the command syntax for accessing MySQL and PostgreSQL databases is exactly the same. First, build and install either the py-MySQLdb or py-pyPgSQL port, both in /usr/ports/databases, depending on which database you are using.

To set up a connection to a MySQL database, use the following initialization lines:

import MySQLdb db = MySQLdb.connect(database="PictureArchive");

If you're using PostgreSQL, the setup lines will look like this:

from pyPgSQL import PgSQL db = PgSQL.connect(database="PictureArchive");

The preceding lines set up the connection to the database. Next, you can create a cursor, or statement handle, with the db.cursor operator:

cur = db.cursor ()

Now you can begin issuing queries. Python's database API defaults to non-autocommit mode, so you can issue as many queries as you like before committing them, as shown here:

cur.execute ("INSERT INTO Users (Fullname,Age) VALUES ('Joe Smith','22')") cur.execute ("INSERT INTO Users (Fullname,Age) VALUES ('Alice Walker','31')") db.commit()

Perhaps the easiest way to handle multirow responses from queries is to use the fetchall operator, which will retrieve all result rows into a multidimensional array. The following lines show how to use this command:

cur.execute ("SELECT * FROM Users") r = cur.fetchall() r [[Joe Smith, 22], [Alice Walker, 31]]

As with the Perl modules for database connectivity, a great many more operations can be done through these interfaces. Refer to the Python Database API Specification 2.0 site (http://www.python.org/topics/database/DatabaseAPI-2.0.html) for full documentation of these features.

A Perl Script to Populate a Database

In this section, you will look at a simple script written in Perl. This script interfaces with a MySQL database. Its purpose is to read in a set of text files in a directory, each of which contains the information for a particular user in a system. The script will parse each file, which is of the following form:

User's full name User's email address User's age

It will then insert each user into the Users table in the database, using the AUTO_INCREMENT column to create unique ID numbers for each user, and finally will return the number of rows that are in the table after all the operations have completed. The script is shown in Listing 29.1.

Listing 29.1. populateUsers.pl: A Perl Script for Populating the Users Table of a Database

[View full width]

#!/usr/local/bin/perl use Mysql; $dbh = Mysql->connect(undef, "PictureArchive","frank","franks-password"); $filepath = "/usr/local/www/data/users"; opendir (DIR, "$filepath"); @userfiles = sort readdir (DIR); closedir (DIR); foreach $file (@userfiles) {   open (USER,"$filepath/$file");   @userdata = <USER>;   chomp ($_) foreach (@userdata);   close (USER);   $inserth = $dbh->query("INSERT INTO Users(Fullname,Email,Age) VALUES ('$userdata[0]' ,'$userdata[1]','$userdata[2]')");   $num++; } $counth = $dbh->query("SELECT ID FROM Users"); $count = $counth->numrows; print "There are $count users in the table, $num of which are newly added.\n\n";

Note

It's generally considered poor form to directly generate content to insert on the fly; rather, your administration scripts should generate a .sql file (a "recipe" of SQL commands) that you then load into the database.