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.
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.
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:
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!