MySQL and Perl: How to implement data CSV import and export functions

MySQL and Perl: How to implement the data CSV import and export function

Introduction:
In developing web applications, it is often necessary to export data from a MySQL database to CSV format, or to import CSV files. into the MySQL database. This article will introduce how to use the Perl programming language to combine with the MySQL database to implement the CSV import and export function of data. We will explain the steps and methods of implementation in detail through code examples.

1. CSV export function implementation:
   First, we need to connect to the MySQL database. You can use the interface provided by the DBI module to achieve this purpose. Here is an example of connecting to a MySQL database using Perl code:

use DBI;

my $dbh = DBI->connect("DBI:mysql:database=testdb;host=localhost",
                       "username", "password")
          or die "Could not connect to database: $DBI::errstr";

Next, we need to execute the MySQL query statement and export the results to a CSV file. You can use the SELECT statement to get data from the database and use the Text::CSV module to write the data to a CSV file. The following is a sample code:

use Text::CSV;

my $csv = Text::CSV->new({ binary => 1, eol => "
" });

my $sth = $dbh->prepare("SELECT * FROM table_name");
$sth->execute();

open(my $fh, '>', 'output.csv') or die "Could not open file: $!";
while (my $row = $sth->fetchrow_arrayref) {
    # 将数据写入CSV文件
    $csv->print($fh, $row);
}
close($fh);

In the above code, we use the Text::CSV module to create a CSV object, and execute the SELECT statement through the prepare method to obtain the data into the $sth handle. Then, each row of data is fetched from the handle through the fetchrow_arrayref method, and the data is written to the CSV file using the print method.

2. CSV import function implementation:
   To import CSV files into the MySQL database, you also need to connect to the database. Then, we need to use the LOAD DATA INFILE statement to import the data in the CSV file into the database table. The following is a sample code:

use DBI;

my $dbh = DBI->connect("DBI:mysql:database=testdb;host=localhost",
                       "username", "password")
          or die "Could not connect to database: $DBI::errstr";

my $filename = 'input.csv';
my $sql = "LOAD DATA INFILE ? INTO TABLE table_name FIELDS TERMINATED BY ','";

my $sth = $dbh->prepare($sql);
$sth->execute($filename) or die "Could not execute query: $DBI::errstr";

In the above code, we use the DBI module to connect to the MySQL database, use the CSV file name as the parameter of the LOAD DATA INFILE statement, and execute the statement to import the data into the database table.

Summary:
By combining the Perl programming language with the MySQL database, we can easily implement the CSV import and export function of data. By connecting to the database, executing query statements and using the Text::CSV module to write data to a CSV file, or using the LOAD DATA INFILE statement to import data from the CSV file into a database table, we can easily process and migrate large amounts of data.

Reference link:

1. DBI - https://metacpan.org/pod/DBI
2. Text::CSV - https://metacpan.org/ pod/Text::CSV

The above is the detailed content of MySQL and Perl: How to implement data CSV import and export functions. For more information, please follow other related articles on the PHP Chinese website!