MySQL Perl DBI fetchall_hashref
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3573370/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Perl DBI fetchall_hashref
提问by emx
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?
问题是：为什么散列的键（countryiso）在散列内部的值中重复？
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?
是否可以使用 fetchall_hashref DBI 方法？还是我必须采用传统方式，遍历每一行并动态构建哈希？
采纳答案by Eugene Yarmash
No, it cannot be done using fetchall_hashref. But you can iterate over the hash values and delete the key:
不，不能使用fetchall_hashref. 但是您可以遍历哈希值并删除键：
delete $_->{countryiso} for values %$hash;
回答by Rui Pedro Bernardino
I had this same problem but was using multiple keys on fetchall_hashref, so I had to go deeper in the hash references. Not exactly rocket science, but here it is:
我遇到了同样的问题，但是在 fetchall_hashref 上使用了多个键，所以我不得不更深入地研究哈希引用。不完全是火箭科学，但它是：
(...)
my @keys=('key1','key2','key3');
my $result_ref=$sth->fetchall_hashref(\@keys);
remove_key_values($result_ref,\@keys);
(...)
sub remove_key_values {
my ($href_values,$aref_keys) = (@_);
foreach my $hk (keys %$href_values) {
foreach my $ak (@$aref_keys) {
if ($ak eq $hk) {
delete $href_values->{$hk};
}
}
if (exists $href_values->{$hk} and ref($href_values->{$hk}) eq 'HASH') {
remove_key_values($href_values->{$hk},$aref_keys);
}
}
}
