Perl DBI

Document Sample
Perl DBI Powered By Docstoc
					             Perl DBI

              Chris Bailey
Bacterial Pathogenesis & Genomics Unit
          cmb036@bham.ac.uk
What is DBI
 • DBI is a data access module for perl.
 • Sits between perl and database server
 • Incorporates database driver & common
   abstraction layer
Using DBI
 #! /usr/bin/perl

 Use DBI;
Creating a database handle
 • 1st thing you need to do in order to get data
 • Requires a data source name
 • Has the form:
   – dbi:DriverName:DatabaseName
   – dbi:DriverName:DatabaseName@hostname:port
   – dbi:DriverName:database=DatabaseName;host=ho
     stname;port=port
Creating a database handle
 • The data source name is then passed as a
   string
 • E.g.
 • $dsn = “dbi:mysql:myco”;
 • $dbh = DBI->connect($dsn,
   $username,$password, \%attr);
Available drivers
 • To get a list of drivers that can be used in
   DBI:
 • @drivers = DBI->available_drivers;
 • E.g.
   – mysql
   – oracle
   – odbc
   – postgresql
Database handle attributes
 • Attributes set as key value pairs:
 • AutoCommit – Automatically commit
                   (transactions)
 • PrintWarn – Automatically print warning
 • PrintError – Print error messages
 • RaiseError – Die on error messages
Testing the connection
 • $dbh will be true if there is a connection,
   false otherwise
 • If there is an error message it will be
   available in $DBI::errstr
 • Assuming success $dbh contains an
   instance of a DBI object which is connected
   to the database set in $dsn
Sending a query
 • If you don’t want access to the result (just a
   return value) can use do
 • $rv = $dbh->do(“DELETE FROM
   cds”);
 • $rv will contain the number of rows
   affected by the operation
Sending a query
 • If you need to return data use the prepare
   method on your database handle
 • $sth = $dbh->prepare($query);
 • This creates a statement handle object
 • Can be used to get information on the query
 • $sth will be undef in the event of a
   problem with the statement
Statement handles
 • What information you can get on your
   statement depends on the database engine
 • Some engines do not pre-process queries
 • Those that do will not all return the same
   information
Statement handles
 • Statement preparation does have
   advantages though (more on that later)
 • Once statement is prepared, you execute it
 • $rv = $sth->execute();
 • $rv contains the number of rows
   affected/returned
 • $rv stores 0 as 0E0, which evaluates as true
 •  $rv is only false if the execute fails
Statement handles
 • Once the statement is executed, you can
   get data from the handle
 • Several methods
   – fetchrow_array
   – fetchrow_arrayref
   – fetchrow_hashref
   – fetchall_arrayref
   – fetchall_hashref
Fetchrow_array
 • Returns the next row in the result set as an
   array:
 • @array = $sth->fetchrow_array()
 • $array[0] = 1st col
 • $array[n] = nth col
Fetchrow_arrayref
 • Returns a reference to an array containing
   the next row in the result set
 • $array_ref = $sth->fetchrow_arrayref
 • $array_ref->[0] = 1st col
 • $array_ref->[n] = nth col
Fetchrow_hashref
 • Returns a reference to a hash containing
   the next row in the result set
 • $hash_ref = $sth->fetchrow_hashref
 • $hash_ref->{name} = name col
 • $hash_ref->{xyz} = xyz col
Fetchall_arrayref
 • Returns a reference to an array containing
   all the rows in the result set
 • $array_ref = $sth->fetchall_arrayref
 • $array_ref->[0]->[0] = 1st row,1st col
 • $array_ref->[n]->[m] = nth row,mth col
Fetchall_arrayref
 • Can define the columns you want to return
 • Maximum number of rows to return
 • Whether to return an array of array refs or
   and array of hash refs
 • Full syntax
 • $sth->fetchall_arrayref($slice,$max_rows)
   – Where $max_rows is an integer
   – $slice is an array ref
Fetchall_arrayref
 • fetchall_arrayref([1])
   – Return 2nd column of every row
   – Access via $array_ref->[row]->[col]
 • fetchall_arrayref([-2,-1],100)
   – Return 2nd to and last column of next 100 rows
   – Access via $array_ref->[row]->[col]
Fetchall_arrayref
 • fetchall_arrayref({})
   – Return all columns, as hash refs
   – Access via $array_ref->[row]->{col_name}
 • fetchall_arrayref({ col1 => 1, col2 => 1})
   – Return columns col1 and col 2, as hash refs
   – Access via $array_ref->[row]->{col_name}
Closing the statement handle
 • $sth->finish gets rid of all data and
   resources needed by the statement handle.
 • Often isn’t needed
 • (finish is automatically called after the last
   row of a select is returned)
More advanced statements
 • DBI can substitute values into prepared
   statements
 • E.g.
 • SELECT * FROM table WHERE name = ?
More advanced statements
 • You can include 1 or more question marks
   in a statement to be prepared
 • Question marks act as placeholders for
   variables you insert later
Placeholders
 • Caveat:
   – If the question marks prevent the database from
     preparing an execution plan then prepare() will
     fail
 • E.g.
   – SELECT name,age FROM ?;
   – SELECT name,? FROM people;
 • Will NOT work
Placeholders
 • NULL values:
 • SELECT * FROM table WHERE col1 = ?
 • Binding undef to ? will not find NULL in col1
 • Have to explicitly ask IS NULL
 • So you have to write
 • … WHERE col1 = ? OR (? IS NULL
   AND col1 IS NULL)
Using placeholders
 $query = “SELECT * FROM table WHERE name=?”;
 $sth = $dbh->prepare($query);
 $sth->execute(“tom”);
 @row=$sth->fetchrow_array();
 $sth->execute(“dave”);
 @row=$sth->fetchrow_array();
 $sth->finish();
Placeholder Advantages
 • Saves have to prepare a statement multiple
   times
 • Will automatically quote strings for you
 • Performance increase
Fetchall_hashref
 • Returns a reference to a hash containing all
   the rows in the result set
 • $hash_ref = $sth->fetchall_hashref(„id‟)
 • $hash_ref->{1}->{name} = row where id
   col = 1, name col
 • $hash_ref->{n}->{xyz} = row where id
   col = n, xyz col
Fetchall_hashref
 • Requires 1 variable to be passed
   – Name of column to use as hash key
   – E.g. $sth->fetchall_hashref(‘accession’)
 • If the data in the column selected is not
   unique, previous rows will be overwriten
Putting it all together
 !# /usr/bin/perl

 use DBI;

 $dsn = “dbi:mysql:colibase”;
 $dbh = DBI->new($dsn, „root‟, „password, {
   raise_error => 0});

 unless ($dbh)
 {
   die “Couldn‟t connect to database:” .
   $DBI::errstr;
 }
Putting it all together
 @accessions = („U00096‟, „BA000007‟,
   „AE005174‟);
 $sth->prepare(“SELECT * FROM genome WHERE
   accession = ?”);

 foreach (@accessions)
 {
      $sth->execute($_);
      ………
Putting it all together
     ………
     print “Data for accession $_\n”;
     while (@rows = $sth->fetchrow_array)
     {
          print “$row[0], $row[2], $row[4]\n”;
     }
 }


 OR …
Putting it all together
     ………
     print “Data for accession $_\n”;
     while ($rows = $sth->fetchrow_arrayref)
     {
          print “$row->[0], $row->[2], ” .
                “$row->[4]\n”;
     }
 }
Tomorrow
• Using the database you created last
  Monday:
• Generate 2 web pages using CGI perl
  module
• 1st page – input search criteria
• 2nd page – output search results