Perl DBI fetchall_hashref [英] Perl DBI fetchall_hashref

问题描述

考虑下表：

  mysql>从vCountryStatus中选择*; 
 + ------------- + ------------ + ------ + --------- +  - ------- + ----------------- + 
 | CountryName | CountryISO |代码|状态|符号| CurrencyName | 
 + ------------- + ------------ + ------ + --------- +  - ------- + ----------------- + 
 |巴西| BR | 55 | LIVE | BRL |巴西雷亚尔| 
 |法国| FR | 33 |离线| EUR |欧元| 
 |菲律宾| PH | 63 | LIVE | PHP |菲律宾比索| 
 + ------------- + ------------ + ------ + --------- +  - ------- + ----------------- + 
 3行（0.00秒）
  

我想根据此表构造一个散列。为此，我执行以下操作：

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $'$'$ DB $; $！
使用Data :: Dumper;
$ b $ my $ dbh = DBI-> connect（dbi：mysql：database = db，user，password，{RaiseError => 1，AutoCommit => 0，FetchHashKeyName =>NAME_lc}）||死DB打开错误：$ DBI :: errstr;

my $ sth = $ dbh-> prepare（select * from vCountryStatus）;
$ sth->执行;
my $ hash = $ sth-> fetchall_hashref（'countryiso'）;
print Dumper（$ hash）;

以下是生成的输出：

  $ VAR1 = {
'PH'=> {
'符号'=> 'PHP'，
'status'=> 'LIVE'，
'countryname'=> 'Philippines'，
'countryiso'=> 'PH'，
'currencyname'=> 'Philippino Peso'，
'code'=> '63'
}，
'BR'=> {
'符号'=> 'BRL'，
'status'=> 'LIVE'，
'countryname'=> 'Brazil'，
'countryiso'=> 'BR'，
'currencyname'=> 'Brazilian Real'，
'code'=> '55'
}，
'FR'=> {
'符号'=> 'EUR'，
'status'=> 'offline'，
'countryname'=> 'France'，
'countryiso'=> 'FR'，
'currencyname'=> 'Euro'，
'code'=> '33'
} 
}; 
  

现在的问题是：为什么散列值（countryiso）在散列值内重复

我更喜欢以下输出：

  $ VAR1 = {
'PH'=> {
'符号'=> 'PHP'，
'status'=> 'LIVE'，
'countryname'=> 'Philippines'，
'currencyname'=> 'Philippino Peso'，
'code'=> '63'
}，
'BR'=> {
'符号'=> 'BRL'，
'status'=> 'LIVE'，
'countryname'=> 'Brazil'，
'currencyname'=> 'Brazilian Real'，
'code'=> '55'
}，
'FR'=> {
'符号'=> 'EUR'，
'status'=> 'offline'，
'countryname'=> 'France'，
'currencyname'=> 'Euro'，
'code'=> '33'
} 
}; 
  

是否可以使用fetchall_hashref DBI方法？或者，我必须采用传统的方式，循环遍历每一行并快速构建哈希？ 解决方案

不，它不能使用 fetchall_hashref 完成。但你可以迭代散列值并删除键值：

 删除$ _-> {countryiso}获取值％$散列; 
  

Consider the following table:

mysql> select * from vCountryStatus;
+-------------+------------+------+---------+--------+-----------------+
| CountryName | CountryISO | Code | Status  | Symbol | CurrencyName    |
+-------------+------------+------+---------+--------+-----------------+
| Brazil      | BR         |   55 | LIVE    | BRL    | Brazilian Real  |
| France      | FR         |   33 | offline | EUR    | Euro            |
| Philippines | PH         |   63 | LIVE    | PHP    | Philippino Peso |
+-------------+------------+------+---------+--------+-----------------+
3 rows in set (0.00 sec)

I am trying to construct a hash based on this table. For this I do the following:

#!/usr/bin/perl

use DBI;
use Data::Dumper;

my $dbh = DBI->connect("dbi:mysql:database=db", "user", "password", {RaiseError => 1, AutoCommit => 0, FetchHashKeyName => "NAME_lc"}) || die "DB open error: $DBI::errstr";

my $sth = $dbh->prepare("select * from vCountryStatus");
$sth->execute;
my $hash = $sth->fetchall_hashref('countryiso');
print Dumper($hash);

Here is the output this generates:

$VAR1 = {
          'PH' => {
                    'symbol' => 'PHP',
                    'status' => 'LIVE',
                    'countryname' => 'Philippines',
                    'countryiso' => 'PH',
                    'currencyname' => 'Philippino Peso',
                    'code' => '63'
                  },
          'BR' => {
                    'symbol' => 'BRL',
                    'status' => 'LIVE',
                    'countryname' => 'Brazil',
                    'countryiso' => 'BR',
                    'currencyname' => 'Brazilian Real',
                    'code' => '55'
                  },
          'FR' => {
                    'symbol' => 'EUR',
                    'status' => 'offline',
                    'countryname' => 'France',
                    'countryiso' => 'FR',
                    'currencyname' => 'Euro',
                    'code' => '33'
                  }
        };

The question is: why is the key of the hash (countryiso) repeated in the values inside the hash?

What I would prefer is the following output:

$VAR1 = {
          'PH' => {
                    'symbol' => 'PHP',
                    'status' => 'LIVE',
                    'countryname' => 'Philippines',
                    'currencyname' => 'Philippino Peso',
                    'code' => '63'
                  },
          'BR' => {
                    'symbol' => 'BRL',
                    'status' => 'LIVE',
                    'countryname' => 'Brazil',
                    'currencyname' => 'Brazilian Real',
                    'code' => '55'
                  },
          'FR' => {
                    'symbol' => 'EUR',
                    'status' => 'offline',
                    'countryname' => 'France',
                    'currencyname' => 'Euro',
                    'code' => '33'
                  }
        };

Is it possible using fetchall_hashref DBI method? Or do I have to go the traditional way, looping through each row and constructing the hash on the fly?

解决方案

No, it cannot be done using fetchall_hashref. But you can iterate over the hash values and delete the key:

delete $_->{countryiso} for values %$hash;

这篇关于Perl DBI fetchall_hashref的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！