anarres: (Default)
[personal profile] anarres
As part of getting ready for GSoC I've been reading about perl's DBI (database interface) module, which lets you interact with an SQL database through a perl script, rather than typing SQL into a terminal. There's a good little intro to it in Chapter 14 of the freely available Perl Cookbook from O'Reilly. (Free ebooks are one of my favourite things ever).

This script shows the simplest possible example of using DBI:





use strict;

# Import DBI module
use DBI;

# dbh: database handle object - represents the database connection
my $dbh = DBI->connect('DBI:mysql:my_database_name', 'root', 'my_password'),
or die "Couldn't connect to database: " . DBI->errstr;

# Put an SQL command to create table 'my_table' into variable $SQL
my $SQL = 'CREATE TABLE my_table (
my_key INT,
my_num INT)';

# For any SQL statement that doesn't return any data, use 'do':
$dbh->do($SQL);

# Add a row of data to table my_table
my $SQL2 = 'INSERT INTO my_table
(my_key, my_num)
VALUES (2, 628)';

$dbh->do($SQL2);

# Now to get some info out of the database...
my $SQL3 = 'SELECT * FROM my_table';

# $sth: Statement handle object
my $sth = $dbh->prepare($SQL3);

# Execute
$sth->execute();

# This line: @row = $sth->fetchrow_array; fetches just one row, so
# we need to run that line as many times as there are rows
while(my @row = $sth->fetchrow_array ) {
print $row[0];
print "\n";
print $row[1];
print "\n\n";
}

# Some older databases need this line
$sth->finish();

# Disconnect from database
$dbh->disconnect();



Profile

anarres: (Default)
anarres

July 2012

S M T W T F S
1234567
891011121314
151617 18192021
222324 25262728
293031    

Most Popular Tags

Style Credit

Expand Cut Tags

No cut tags
Page generated Sep. 16th, 2014 05:25 pm
Powered by Dreamwidth Studios