Perl Learning Notes (10)-Accessing the database through DBI

The most commonly used package for Perl access to a database is DBI, which can be found in www.cpan.org. Also need to install the corresponding database driver package, such as Dbd::mysql, Dbd::oracle, dbd::sybase or DBD::ODBC.

First, the basic process

In general, database operations consist of a common process of following several steps:

1. Establish a database connection

2. Execute SQL statements by establishing a database connection

3. Get the returned data set after executing SQL

4. Processing records in a data set is generally a cyclical process

5. Finish processing, close database connection, release resources

Here is a piece of code that accesses MySQL in Perl as described above, with this code as an example detailing how dbi is used.

#!/usr/bin/perl -w
use strict;
use DBI;

my $dbh = DBI->connect("DBI:mysql:test:192.168.1.2", ‘root‘, ‘password‘);
my $sth = $dbh->prepare("SELECT * FROM test1");
$sth->execute();

while ( my @row = $sth->fetchrow_array() )
{
       print join(‘\t‘, @row)."\n";
}

$sth->finish();
$dbh->disconnect();

1.1 Connecting the database

My $dbh = Dbi->connect ("dbi:mysql:test:192.168.1.2", ' root ', ' password ');

The use of the DBI methodDBI->connectto establish a connection to a database, if the connection is successful, return a database connection handle, and then execute the SQL and other operations to take the connection handle as an action parameter. In the Connect call, you first provide a database connection string. This connection string is separated by a colon into several parts:

Section	Description
dbi	interface type
mysql	database type
test	Database name
192.168.1.2	database host address

In the connection string in the previous example, DBI indicates that this is a connection string for the DBI interface; MySQL indicates that the database to connect to is a MySQL database (if you are connecting to an Oracle database, this is Oracle), different databases have different connection string definitions, You can refer to the description of the access driver for DBI; test indicates the name of the database connected to the database host; 192.168.1.2 is the IP address of the MySQL server. It is important to note that the database type in the connection string MySQL must be lowercase. If the host name is omitted, localhost is the default. The next two parameters of the Connect method are the user name and password that connect to the database host, which is an essential J

If any errors occur during the connection, the return value of connect will be undef (and Null in the C language is a matter of course). In order to simplify and omit error checking, these errors and return values should be checked when actually doing the project.

1.2 Execute SQL statement

my $sth = $dbh->prepare("SELECT * FROM test1");
$sth->execute();
$dbh->do(“UPDATE test1 SET time=now()”);

Connected to the database, get a database connection handle, you can use this handle to the database operation. To execute an SQL statement, the DBI takes two steps to improve performance. The SQL statement is first submitted to the database through the prepare method, the database allocates the execution resources for the statement, and then the Execute method is called to notify the database to execute the SQL statement. Note that the prepare method is called through the database connection handle, and if successful, returns a handle to the SQL, which is then invoked by the SQL statement handle to execute SQL. In general, execute executes a statement that returns data (for example, a SELECT statement). Conversely, if you perform inserts, UPDATE, DELETE, CREATE table, etc. that do not need to return data, there is a more convenient, fast method$dbh->do(SQL语句), you can save prepare steps. The Do method returns the number of records affected by the SQL.

1.2.1 Tips: Composing sql

A friend who writes large paragraphs of SQL may have a headache with the quotes in SQL, often because of the problem with the quotation marks, the SQL statement mess. Do you remember the QQ that was mentioned in the previous article? Here is the good use of it. Because the string in QQ is the same as the string in the double quotation mark "", the variable is interpreted, and QQ can be wrapped. So it's a good choice to use it to compose SQL, such as an SQL statement like this:

my $res_operator = $dbhandle->prepare( qq{
       SELECT o_customerid, COUNT(*) AS totalMsgNum FROM mm4fcdrs
       WHERE (m_date>‘$begindate‘) AND (m_date<‘enddate‘) 
       GROUP BY o_customerid 
});

1.2.2 Optimizing query execution efficiency through parameters in SQL statements

When executing statements such as a large number of inserts, a SQL statement of the same structure is repeatedly submitted to the database server, in which case the prepare and SQL parameters can be used to optimize execution efficiency:

1. First, use prepare to submit a SQL template to the database server, replacing the part of the value with the parameter placeholder.

2. After using prepare to have the server prepare the execution resources for the SQL, call execute and pass the actual value of the parameter into the method to execute SQL.

3. You can then invoke execute repeatedly, without requiring the server to re-prepare

Suppose you want to execute such a series of SQL

INSERT INTO test1 VALUES (NULL, ‘a’, ‘2005-04-01’)
... ...
INSERT INTO test1 VALUES (NULL, ‘z’, ‘2005-04-01’)

The value of the second field is the letter from A to Z. So you can optimize execution efficiency:

my $sth = $dbh->prepare( qq{
    INSERT INTO test1 VALUES (NULL, ?, ‘2005-04-01’)
} );

for my $value(‘a‘..‘z‘)  {
    $sth->execute($value);
}

The question mark is the previous argument placeholder, which means to tell the server that the resource is ready to execute: This location of SQL will have a value, but it's not known yet, and I'll let you know when it's done. After prepare, use a loop to generate a-Z character to the variable $value, and then pass $value in the Execute method as a parameter, and the server will automatically replace the previous "?" with the value passed in. It is important to note that the number of arguments passed in must be the same as that of the placeholders in SQL.

1.3 Reading Records

The friend who is familiar with ADO must know that there is a DataReader object in it, and the method of reading data in DBI is very similar. Simply put, it is one-way, streaming read data, that is, each time can only read backward one data until no data can be read.

In the example at the beginning of the article, the method is used$sth->fetchrow_array()to read the data. In fact, DBI read data There are several common methods, these methods are similar, the difference is the form of return records.

1.3.1 Fetchrow_array

Returns an array that consists of the values of the fields. The 1th element of the array is the value of the 1th field of the current record.

while ( my @row = $sth->fetchrow_array() )  {
    print "$row[0], $row[1], $row[2]\n";
}

Or so, but notice the order of the fields.

while ( my ($id, $name, $time) = $sth->fetchrow_array() )  {
    print "$id, $name, $time\n";
}

1.3.2 Fetchrow_arrayref

Returns a reference to an array consisting of the values of the field. The samefetchrow_arraydifference is evident in thefetchrow_arrayrefreturn of the array to the reference.

while ( my $row_ref = $sth->fetchrow_arrayref() ) {
    for (my $i = 0; $i < @{$row_ref}; $i++)       {
        print "$row_ref->[$i]\t";
    }
    print "\n";
}

It is important to note that if you want to take the number of fields, you need to take the form of this reference to the array@{$row_ref}. When you get the value of an array element, because $row_ref is a reference, you need to use an->operator.

1.3.3 Fetchrow_hashref

Returns a hash table consisting of a key-value pair such as field name-field value. The key difference is that only this method can get its value through a field name, without having to worry about the field being the first field. The former can only rely on the index to access the value. But the disadvantage is that efficiency is worse than the previous two.

while ( my $record = $sth->fetchrow_hashref() ) {
    for my $field( keys %{$record} ) {
        print "$field: $record->{$field}\t";
    }
    print "\n";
}

Here we need to review the operation of the hash table. The keys operator gets an array of hash keys (key) to$record->{$field}get the value corresponding to the $field in the hash table. Note that this is also a reference, so use the->operator.

Using the above three methods can basically solve the problem. In addition, there are two methodsfetchall_arrayrefandselectall_arrayrefcan get the entire data set directly through SQL, but the use of a little more complex, to refer to the Perl scalar operator, here is not covered. Interested readers can refer to the relevant data of DBI.

Finally, the finishing touches.

1.4 Ending a SQL session

$sth->finish ();

1.5 Disconnecting database connections

$DBH->disconnect ();

http://dbi.perl.org/

Http://www.perl.com/pub/1999/10/DBI.html

Perl Learning Notes (10)-Accessing the database through DBI