Perl - Quyền truy cập cơ sở dữ liệu

Chương này hướng dẫn bạn cách truy cập cơ sở dữ liệu bên trong tập lệnh Perl của bạn. Bắt đầu từ Perl 5 đã trở nên rất dễ dàng để viết các ứng dụng cơ sở dữ liệu bằng cách sử dụngDBImô-đun. DBI là viết tắt củaDatabase Independent Interface cho Perl, có nghĩa là DBI cung cấp một lớp trừu tượng giữa mã Perl và cơ sở dữ liệu bên dưới, cho phép bạn chuyển đổi triển khai cơ sở dữ liệu thực sự dễ dàng.

DBI là một mô-đun truy cập cơ sở dữ liệu cho ngôn ngữ lập trình Perl. Nó cung cấp một tập hợp các phương thức, biến và quy ước cung cấp giao diện cơ sở dữ liệu nhất quán, độc lập với cơ sở dữ liệu thực tế đang được sử dụng.

Kiến trúc của một ứng dụng DBI

DBI độc lập với bất kỳ cơ sở dữ liệu nào có sẵn trong phần phụ trợ. Bạn có thể sử dụng DBI cho dù bạn đang làm việc với Oracle, MySQL hay Informix, v.v. Điều này rõ ràng từ sơ đồ lưu trữ sau.

Ở đây DBI chịu trách nhiệm nhận tất cả các lệnh SQL thông qua API, (tức là Giao diện lập trình ứng dụng) và gửi chúng đến trình điều khiển thích hợp để thực thi thực tế. Và cuối cùng, DBI có trách nhiệm lấy kết quả từ trình điều khiển và trả lại cho scritp đang gọi.

Ký hiệu và quy ước

Trong suốt chương này, các ký hiệu sau đây sẽ được sử dụng và bạn cũng nên tuân theo quy ước tương tự.

$dsn    Database source name
$dbh    Database handle object
$sth    Statement handle object
$h      Any of the handle types above ($dbh, $sth, or $drh)
$rc     General Return Code  (boolean: true=ok, false=error)
$rv     General Return Value (typically an integer)
@ary    List of values returned from the database.
$rows   Number of rows processed (if available, else -1)
$fh     A filehandle
undef   NULL values are represented by undefined values in Perl
\%attr  Reference to a hash of attribute values passed to methods

Kết nối cơ sở dữ liệu

Giả sử chúng ta sẽ làm việc với cơ sở dữ liệu MySQL. Trước khi kết nối với cơ sở dữ liệu, hãy đảm bảo những điều sau. Bạn có thể nhận trợ giúp về hướng dẫn MySQL của chúng tôi trong trường hợp bạn không biết về cách tạo cơ sở dữ liệu và bảng trong cơ sở dữ liệu MySQL.

• Bạn đã tạo một cơ sở dữ liệu với tên TESTDB.

• Bạn đã tạo một bảng với tên TEST_TABLE trong TESTDB.

• Bảng này có các trường FIRST_NAME, LAST_NAME, AGE, SEX và INCOME.

• ID người dùng "testuser" và mật khẩu "test123" được đặt để truy cập TESTDB.

• Perl Module DBI được cài đặt đúng cách trên máy của bạn.

• Bạn đã xem qua hướng dẫn MySQL để hiểu Cơ bản về MySQL.

Sau đây là ví dụ về kết nối với cơ sở dữ liệu MySQL "TESTDB" -

#!/usr/bin/perl

use DBI
use strict;

my $driver = "mysql"; 
my $database = "TESTDB";
my $dsn = "DBI:$driver:database=$database";
my $userid = "testuser";
my $password = "test123";

my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;

Nếu kết nối được thiết lập với nguồn dữ liệu thì Xử lý cơ sở dữ liệu sẽ được trả về và lưu vào $ dbh để sử dụng thêm, nếu không thì $ dbh được đặt thành giá trị undef và $ DBI :: errstr trả về một chuỗi lỗi.

INSERT hoạt động

Thao tác INSERT là bắt buộc khi bạn muốn tạo một số bản ghi vào bảng. Ở đây chúng tôi đang sử dụng bảng TEST_TABLE để tạo bản ghi của chúng tôi. Vì vậy, khi kết nối cơ sở dữ liệu của chúng tôi được thiết lập, chúng tôi đã sẵn sàng tạo các bản ghi vào TEST_TABLE. Sau đây là quy trình để tạo một bản ghi vào TEST_TABLE. Bạn có thể tạo bao nhiêu bản ghi mà bạn muốn bằng cách sử dụng cùng một khái niệm.

Việc tạo bản ghi thực hiện theo các bước sau:

• Chuẩn bị câu lệnh SQL với câu lệnh INSERT. Điều này sẽ được thực hiện bằng cách sử dụngprepare() API.

• Thực thi truy vấn SQL để chọn tất cả các kết quả từ cơ sở dữ liệu. Điều này sẽ được thực hiện bằng cách sử dụngexecute() API.

• Phát hành tay cầm Stattement. Điều này sẽ được thực hiện bằng cách sử dụngfinish() API.

• Nếu mọi thứ suôn sẻ thì commit hoạt động này nếu không bạn có thể rollbackhoàn thành giao dịch. Cam kết và Khôi phục được giải thích trong các phần tiếp theo.

my $sth = $dbh->prepare("INSERT INTO TEST_TABLE
                       (FIRST_NAME, LAST_NAME, SEX, AGE, INCOME )
                         values
                       ('john', 'poul', 'M', 30, 13000)");
$sth->execute() or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Sử dụng giá trị ràng buộc

Có thể có trường hợp khi các giá trị cần nhập không được cung cấp trước. Vì vậy, bạn có thể sử dụng các biến ràng buộc sẽ nhận các giá trị cần thiết tại thời điểm chạy. Các mô-đun Perl DBI sử dụng dấu chấm hỏi thay cho giá trị thực và sau đó giá trị thực được chuyển qua API execute () tại thời điểm chạy. Sau đây là ví dụ -

my $first_name = "john";
my $last_name = "poul";
my $sex = "M";
my $income = 13000;
my $age = 30;
my $sth = $dbh->prepare("INSERT INTO TEST_TABLE
                        (FIRST_NAME, LAST_NAME, SEX, AGE, INCOME )
                          values
                        (?,?,?,?)");
$sth->execute($first_name,$last_name,$sex, $age, $income) 
          or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

ĐỌC hoạt động

ĐỌC Hoạt động trên bất kỳ cơ sở dữ liệu nào có nghĩa là lấy một số thông tin hữu ích từ cơ sở dữ liệu, tức là, một hoặc nhiều bản ghi từ một hoặc nhiều bảng. Vì vậy, khi kết nối cơ sở dữ liệu của chúng tôi được thiết lập, chúng tôi đã sẵn sàng thực hiện một truy vấn vào cơ sở dữ liệu này. Sau đây là quy trình để truy vấn tất cả các bản ghi có AGE lớn hơn 20. Việc này sẽ thực hiện bốn bước:

• Chuẩn bị truy vấn SQL SELECT dựa trên các điều kiện bắt buộc. Điều này sẽ được thực hiện bằng cách sử dụngprepare() API.

• Thực thi truy vấn SQL để chọn tất cả các kết quả từ cơ sở dữ liệu. Điều này sẽ được thực hiện bằng cách sử dụngexecute() API.

• Tìm nạp lần lượt tất cả các kết quả và in các kết quả đó. Điều này sẽ được thực hiện bằng cách sử dụng fetchrow_array() API.

• Phát hành tay cầm Stattement. Điều này sẽ được thực hiện bằng cách sử dụngfinish() API.

my $sth = $dbh->prepare("SELECT FIRST_NAME, LAST_NAME
                        FROM TEST_TABLE 
                        WHERE AGE > 20");
$sth->execute() or die $DBI::errstr;
print "Number of rows found :" + $sth->rows;
while (my @row = $sth->fetchrow_array()) {
   my ($first_name, $last_name ) = @row;
   print "First Name = $first_name, Last Name = $last_name\n";
}
$sth->finish();

Sử dụng giá trị ràng buộc

Có thể có một trường hợp khi điều kiện không được đưa ra trước. Vì vậy, bạn có thể sử dụng các biến ràng buộc, các biến này sẽ nhận các giá trị cần thiết tại thời gian chạy. Các mô-đun Perl DBI sử dụng dấu chấm hỏi thay cho giá trị thực và sau đó các giá trị thực được chuyển qua API execute () tại thời điểm chạy. Sau đây là ví dụ -

$age = 20;
my $sth = $dbh->prepare("SELECT FIRST_NAME, LAST_NAME
                        FROM TEST_TABLE
                        WHERE AGE > ?");
$sth->execute( $age ) or die $DBI::errstr;
print "Number of rows found :" + $sth->rows;
while (my @row = $sth->fetchrow_array()) {
   my ($first_name, $last_name ) = @row;
   print "First Name = $first_name, Last Name = $last_name\n";
}
$sth->finish();

CẬP NHẬT hoạt động

CẬP NHẬT Hoạt động trên bất kỳ cơ sở dữ liệu nào có nghĩa là cập nhật một hoặc nhiều bản ghi đã có sẵn trong bảng cơ sở dữ liệu. Sau đây là thủ tục để cập nhật tất cả các bản ghi có SEX là 'M'. Ở đây, chúng tôi sẽ tăng TUỔI của tất cả nam giới thêm một năm. Điều này sẽ thực hiện ba bước -

• Chuẩn bị truy vấn SQL dựa trên các điều kiện bắt buộc. Điều này sẽ được thực hiện bằng cách sử dụngprepare() API.

• Thực thi truy vấn SQL để chọn tất cả các kết quả từ cơ sở dữ liệu. Điều này sẽ được thực hiện bằng cách sử dụngexecute() API.

• Phát hành tay cầm Stattement. Điều này sẽ được thực hiện bằng cách sử dụngfinish() API.

• Nếu mọi thứ suôn sẻ thì commit hoạt động này nếu không bạn có thể rollbackhoàn thành giao dịch. Xem phần tiếp theo để biết các API cam kết và khôi phục.

my $sth = $dbh->prepare("UPDATE TEST_TABLE
                        SET   AGE = AGE + 1 
                        WHERE SEX = 'M'");
$sth->execute() or die $DBI::errstr;
print "Number of rows updated :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Sử dụng giá trị ràng buộc

Có thể có một trường hợp khi điều kiện không được đưa ra trước. Vì vậy, bạn có thể sử dụng các biến ràng buộc, các biến này sẽ nhận các giá trị bắt buộc tại thời điểm chạy. Các mô-đun Perl DBI sử dụng dấu chấm hỏi thay cho giá trị thực và sau đó giá trị thực được chuyển qua API execute () tại thời điểm chạy. Sau đây là ví dụ -

$sex = 'M';
my $sth = $dbh->prepare("UPDATE TEST_TABLE
                        SET   AGE = AGE + 1
                        WHERE SEX = ?");
$sth->execute('$sex') or die $DBI::errstr;
print "Number of rows updated :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Trong một số trường hợp, bạn muốn đặt một giá trị, giá trị này không được cung cấp trước để bạn có thể sử dụng giá trị ràng buộc như sau. Trong ví dụ này, thu nhập của tất cả nam giới sẽ được đặt thành 10000.

$sex = 'M';
$income = 10000;
my $sth = $dbh->prepare("UPDATE TEST_TABLE
                        SET   INCOME = ?
                        WHERE SEX = ?");
$sth->execute( $income, '$sex') or die $DBI::errstr;
print "Number of rows updated :" + $sth->rows;
$sth->finish();

Thao tác DELETE

Thao tác DELETE là bắt buộc khi bạn muốn xóa một số bản ghi khỏi cơ sở dữ liệu của mình. Sau đây là thủ tục để xóa tất cả các bản ghi khỏi TEST_TABLE trong đó AGE bằng 30. Thao tác này sẽ thực hiện theo các bước sau.

• Chuẩn bị truy vấn SQL dựa trên các điều kiện bắt buộc. Điều này sẽ được thực hiện bằng cách sử dụngprepare() API.

• Thực thi truy vấn SQL để xóa các bản ghi được yêu cầu khỏi cơ sở dữ liệu. Điều này sẽ được thực hiện bằng cách sử dụngexecute() API.

• Phát hành tay cầm Stattement. Điều này sẽ được thực hiện bằng cách sử dụngfinish() API.

• Nếu mọi thứ suôn sẻ thì commit hoạt động này nếu không bạn có thể rollback hoàn thành giao dịch.

$age = 30;
my $sth = $dbh->prepare("DELETE FROM TEST_TABLE
                         WHERE AGE = ?");
$sth->execute( $age ) or die $DBI::errstr;
print "Number of rows deleted :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Sử dụng do Statement

Nếu bạn đang thực hiện CẬP NHẬT, CHÈN hoặc XÓA thì không có dữ liệu nào quay lại từ cơ sở dữ liệu, vì vậy, có một bước ngắn để thực hiện thao tác này. Bạn có thể dùngdo câu lệnh để thực hiện bất kỳ lệnh nào như sau.

$dbh->do('DELETE FROM TEST_TABLE WHERE age =30');

dotrả về giá trị true nếu thành công và giá trị false nếu không thành công. Trên thực tế, nếu nó thành công, nó trả về số hàng bị ảnh hưởng. Trong ví dụ này, nó sẽ trả về số hàng đã thực sự bị xóa.

Hoạt động CAM KẾT

Cam kết là hoạt động đưa ra một tín hiệu màu xanh lá cây cho cơ sở dữ liệu để hoàn thành các thay đổi và sau hoạt động này, không có thay đổi nào có thể được hoàn nguyên về vị trí ban đầu của nó.

Đây là một ví dụ đơn giản để gọi commit API.

$dbh->commit or die $dbh->errstr;

Hoạt động ROLLBACK

Nếu bạn không hài lòng với tất cả các thay đổi hoặc gặp lỗi giữa bất kỳ thao tác nào, bạn có thể hoàn nguyên các thay đổi đó để sử dụng rollback API.

Đây là một ví dụ đơn giản để gọi rollback API.

$dbh->rollback or die $dbh->errstr;

Bắt đầu giao dịch

Nhiều cơ sở dữ liệu hỗ trợ giao dịch. Điều này có nghĩa là bạn có thể thực hiện một loạt các truy vấn sẽ sửa đổi cơ sở dữ liệu, nhưng không có thay đổi nào thực sự được thực hiện. Sau đó, ở cuối, bạn đưa ra truy vấn SQL đặc biệtCOMMITvà tất cả các thay đổi được thực hiện đồng thời. Ngoài ra, bạn có thể đưa ra truy vấn ROLLBACK, trong trường hợp này, tất cả các thay đổi sẽ bị loại bỏ và cơ sở dữ liệu vẫn không thay đổi.

Đã cung cấp mô-đun Perl DBI begin_workAPI, cho phép các giao dịch (bằng cách tắt AutoCommit) cho đến lần gọi cam kết hoặc khôi phục tiếp theo. Sau lần cam kết hoặc khôi phục tiếp theo, AutoCommit sẽ tự động được bật lại.

$rc  = $dbh->begin_work  or die $dbh->errstr;

Tùy chọn AutoCommit

Nếu các giao dịch của bạn đơn giản, bạn có thể tránh được rắc rối khi phải đưa ra nhiều cam kết. Khi bạn thực hiện cuộc gọi kết nối, bạn có thể chỉ định mộtAutoCommittùy chọn này sẽ thực hiện thao tác cam kết tự động sau mỗi lần truy vấn thành công. Đây là những gì nó trông như thế này -

my $dbh = DBI->connect($dsn, $userid, $password,
              {AutoCommit => 1}) 
              or die $DBI::errstr;

Ở đây AutoCommit có thể nhận giá trị 1 hoặc 0, trong đó 1 có nghĩa là AutoCommit đang bật và 0 có nghĩa là AutoCommit đang tắt.

Xử lý lỗi tự động

Khi bạn thực hiện cuộc gọi kết nối, bạn có thể chỉ định tùy chọn RaiseErrors để tự động xử lý lỗi cho bạn. Khi xảy ra lỗi, DBI sẽ hủy bỏ chương trình của bạn thay vì trả về mã lỗi. Nếu tất cả những gì bạn muốn là hủy bỏ chương trình do lỗi, điều này có thể thuận tiện. Đây là những gì nó trông như thế này -

my $dbh = DBI->connect($dsn, $userid, $password,
              {RaiseError => 1})
              or die $DBI::errstr;

Ở đây RaiseError có thể nhận giá trị 1 hoặc 0.

Ngắt kết nối cơ sở dữ liệu

Để ngắt kết nối Cơ sở dữ liệu, hãy sử dụng disconnect API như sau -

$rc = $dbh->disconnect  or warn $dbh->errstr;

Đáng buồn là, hành vi giao dịch của phương thức ngắt kết nối là không xác định. Một số hệ thống cơ sở dữ liệu (chẳng hạn như Oracle và Ingres) sẽ tự động cam kết bất kỳ thay đổi nổi bật nào, nhưng những hệ thống khác (chẳng hạn như Informix) sẽ khôi phục bất kỳ thay đổi nào còn tồn tại. Các ứng dụng không sử dụng AutoCommit nên gọi cam kết hoặc khôi phục rõ ràng trước khi gọi ngắt kết nối.

Sử dụng giá trị NULL

Giá trị không xác định, hoặc không xác định, được sử dụng để chỉ ra giá trị NULL. Bạn có thể chèn và cập nhật các cột có giá trị NULL như cách bạn làm với giá trị không phải NULL. Các ví dụ này chèn và cập nhật tuổi cột với giá trị NULL -

$sth = $dbh->prepare(qq {
         INSERT INTO TEST_TABLE (FIRST_NAME, AGE) VALUES (?, ?)
       });
$sth->execute("Joe", undef);

Đây qq{} được sử dụng để trả về một chuỗi được trích dẫn tới prepareAPI. Tuy nhiên, phải cẩn thận khi cố gắng sử dụng giá trị NULL trong mệnh đề WHERE. Hãy xem xét -

SELECT FIRST_NAME FROM TEST_TABLE WHERE age = ?

Việc ràng buộc undef (NULL) với trình giữ chỗ sẽ không chọn các hàng có tuổi NULL! Ít nhất là đối với các công cụ cơ sở dữ liệu phù hợp với tiêu chuẩn SQL. Tham khảo hướng dẫn sử dụng SQL cho công cụ cơ sở dữ liệu của bạn hoặc bất kỳ cuốn sách SQL nào để biết lý do cho việc này. Để chọn rõ ràng NULL, bạn phải nói "WHERE age IS NULL".

Một vấn đề phổ biến là có một đoạn mã xử lý một giá trị có thể được xác định hoặc không xác định (không phải NULL hoặc NULL) trong thời gian chạy. Một kỹ thuật đơn giản là chuẩn bị câu lệnh thích hợp nếu cần và thay thế trình giữ chỗ cho các trường hợp không phải NULL -

$sql_clause = defined $age? "age = ?" : "age IS NULL";
$sth = $dbh->prepare(qq {
         SELECT FIRST_NAME FROM TEST_TABLE WHERE $sql_clause
       });
$sth->execute(defined $age ? $age : ());

Một số chức năng khác của DBI

ready_drivers

@ary = DBI->available_drivers;
@ary = DBI->available_drivers($quiet);

Trả về danh sách tất cả các trình điều khiển có sẵn bằng cách tìm kiếm các mô-đun DBD :: * thông qua các thư mục trong @INC. Theo mặc định, một cảnh báo được đưa ra nếu một số trình điều khiển bị ẩn bởi những người khác cùng tên trong các thư mục trước đó. Việc vượt qua giá trị thực cho $ lặng sẽ ngăn cản cảnh báo.

install_drivers

%drivers = DBI->installed_drivers();

Trả về danh sách tên trình điều khiển và cặp xử lý trình điều khiển cho tất cả các trình điều khiển được 'cài đặt' (đã tải) vào quy trình hiện tại. Tên trình điều khiển không bao gồm tiền tố 'DBD ::'.

nguồn dữ liệu

@ary = DBI->data_sources($driver);

Trả về danh sách các nguồn dữ liệu (cơ sở dữ liệu) có sẵn thông qua trình điều khiển được đặt tên. Nếu $ driver trống hoặc undef, thì giá trị của biến môi trường DBI_DRIVER sẽ được sử dụng.

Trích dẫn

$sql = $dbh->quote($value);
$sql = $dbh->quote($value, $data_type);

Trích dẫn một chuỗi ký tự để sử dụng làm giá trị ký tự trong một câu lệnh SQL, bằng cách thoát bất kỳ ký tự đặc biệt nào (chẳng hạn như dấu ngoặc kép) có trong chuỗi và thêm loại dấu ngoặc kép bên ngoài được yêu cầu.

$sql = sprintf "SELECT foo FROM bar WHERE baz = %s",
                $dbh->quote("Don't");

Đối với hầu hết các loại cơ sở dữ liệu, trích dẫn sẽ trả về 'Không' (bao gồm cả dấu ngoặc kép bên ngoài). Phương thức quote () trả về biểu thức SQL đánh giá thành chuỗi mong muốn là hợp lệ. Ví dụ -

$quoted = $dbh->quote("one\ntwo\0three")

may produce results which will be equivalent to

CONCAT('one', CHAR(12), 'two', CHAR(0), 'three')

Các phương pháp chung cho tất cả các xử lý

sai lầm

$rv = $h->err;
or
$rv = $DBI::err
or
$rv = $h->err

Trả về mã lỗi công cụ cơ sở dữ liệu gốc từ phương thức trình điều khiển cuối cùng được gọi. Mã thường là một số nguyên nhưng bạn không nên cho rằng điều đó. Điều này tương đương với $ DBI :: err hoặc $ h-> err.

errstr

$str = $h->errstr;
or
$str = $DBI::errstr
or
$str = $h->errstr

Trả về thông báo lỗi công cụ cơ sở dữ liệu gốc từ phương thức DBI cuối cùng được gọi. Điều này có các vấn đề về tuổi thọ tương tự như phương pháp "err" được mô tả ở trên. Điều này tương đương với $ DBI :: errstr hoặc $ h-> errstr.

hàng

$rv = $h->rows;
or
$rv = $DBI::rows

Điều này trả về số hàng được thực hiện bởi câu lệnh SQL trước đó và tương đương với $ DBI :: hàng.

dấu vết

$h->trace($trace_settings);

DBI có khả năng cực kỳ hữu ích để tạo thông tin theo dõi thời gian chạy về những gì nó đang làm, có thể tiết kiệm thời gian rất lớn khi cố gắng theo dõi các vấn đề lạ trong chương trình DBI của bạn. Bạn có thể sử dụng các giá trị khác nhau để đặt mức theo dõi. Các giá trị này thay đổi từ 0 đến 4. Giá trị 0 có nghĩa là vô hiệu hóa dấu vết và 4 có nghĩa là tạo dấu vết hoàn chỉnh.

Các câu lệnh nội suy bị cấm

Chúng tôi rất khuyến khích không sử dụng các câu lệnh nội suy như sau:

while ($first_name = <>) {
   my $sth = $dbh->prepare("SELECT * 
                          FROM TEST_TABLE 
                          WHERE FIRST_NAME = '$first_name'");
   $sth->execute();
   # and so on ...
}

Vì vậy, không sử dụng câu lệnh nội suy thay vào đó hãy sử dụng bind value để chuẩn bị câu lệnh SQL động.