SQL構文でカラム名をハッシュのキーに

Question

PerlをDBIでMySQLに接続しています。

MySQLの“country”テーブルに“name_en”カラムがあり、これをハッシュのキーにして、
下記構文で“name_jp”カラムに文字列を挿入しようとしたのですが、
-----------------------------------------
$sth = $dbh -> prepare ("update country set name_jp = \'$name{name_en}\' where id between 1379 and 1396");
-----------------------------------------
結果は何も入りませんでした。

カラム名をハッシュのキーにするには、どう書けばいいのでしょうか？

宜しくお願いします。

Tacosan · Accepted Answer

そこの文字列は「Perl の文字列」として解釈されるんだから, カラム名として使いたい文字列をハッシュのキーに使っても無意味じゃん.

ちゃんと「name_en が 'USA' で name_jp が 'アメリカ合衆国' というタプル」を作らないと.

hirotn · Answer

例えば以下でしょうか…。
("update country set name_jp = \'" . $name{"name_en"} . "\' where id between 1379 and 1396");

. は文字列結合演算子です。あるいは、

("update country set name_jp = \'$name{'name_en'}\' where id between 1379 and 1396");

Tacosan · Answer

ハッシュのキーは (およそ) \w+ にマッチすれば自動的に文字列とみなされます. だから
$name{name_en}
でも
$name{"name_en"}
でも同じことだし, あるいは
%name = ('USA' => 'アメリカ合衆国')
と
%name = (USA => 'アメリカ合衆国')
も同じ意味だったりする.

とはいえ, そもそもハッシュ %name に name_en ってキーで値は入れてあるの?

Tacosan · Answer

あれ? 「タプル」自体は SQL 方面でも特に珍しい言葉じゃないと思ったんだけど....

いずれにしても
・SQLサーバに渡すコマンド (文字列) を作るまでは Perl の世界
・SQLサーバがコマンドを受け取ってからは SQL の世界
という 2つの世界があることを確認しておいた方がいいんじゃないかな. 念の為ですが, この 2つの世界は魔法でも使わない限り交わることはありません.

国の数がどうであれ, データ構造さえ作ってしまえばループするだけのことでは? まあ, しばしば「データ構造を作る」ところが問題になったりするんだが.

hirotn · Answer

name_en　　　　　name_jp = $name{name_en}
---------------------------------------
USA　　　　　　　　アメリカ合衆国
SPN　　　　　　　　スペイン
JPN　　　　　　　　日本

これを格納したテーブルはあるのですよね？
ならば、Perl上ではハッシュを用いたデータを「別に」用意しなければならないです。

SELECTをexecuteして、
while (my $ary_ref = $sth->fetchrow_arrayref) {
  my ($name_en, sname_jp) = @$ary_ref;
  $name{$name_en}=$name_jp;
}

これでname_enをキーにname_jpを値に取ることのできるハッシュ変数%nameが作れると思います。

_--_1l1_1_ · Answer

質問の内容が理解できません。

1. %nameには何が入っているのか？

2. どういう文字列を期待しているのか？
print "update country set name_jp = \'$name{name_en}\' where id between 1379 and 1396", "
";

-> update country set name_jp = "USA" where id between 1379 and 1396
??

_--_1l1_1_ · Answer

ループではなくて、UPDATE一発でできないかということでしょうかね。

もしDB上にテーブルを作っていいなら、

dummy_table:
name_en name_jp
----------
JPN 日本
SPN スペイン
USA アメリカ合衆国
...

UPDATE target_table A 
　　set A.name_jp = (select B.name_jp from dummy_table B 
　　　　　　　　　　　　　　　　　　　where A.name_en = B.name_en) 
　　where id between 1379 and 1396;

でいけるかなぁ？

UPDATE target_table 一時名
って書き方ができないDBもあったと思うけど

_--_1l1_1_ · Answer

- %nameだとわかりづらいので、%jp_name_ofにした。
- 表示がくずれるので、空白2文字を全角空白にしているていることに注意。

use strict;
use warnings;
use utf8;
use feature 'say';
use DBI;
use Encode;

binmode STDOUT, ':encoding(utf8)';

my $db_name　　= 'sqlite_test.db';
my $table　　　= 'user_info';
my %jp_name_of = (
　　JPN => '日本',
　　USA => 'アメリカ合衆国',
　　SPN => 'スペイン'
);

my $dbh = create_test_db($db_name);
create_test_table( $dbh, $table );
say '-- before';
dump_table( $dbh, $table );

my $update_sql = 'UPDATE ' . $table . ' SET name_jp = ? where id = ?;';
my $update_sth = $dbh->prepare($update_sql);

my $fetch_sql
　　= "SELECT id, name_en FROM $table where id between 1379 and 1396;";
my $fetch_sth = $dbh->prepare($fetch_sql);
$fetch_sth->execute;
while ( my $ref = $fetch_sth->fetch() ) {
　　my $id　　　= $ref->[0];
　　my $name_en = $ref->[1];

$update_sth->bind_param( 1, $jp_name_of{$name_en} );
　　$update_sth->bind_param( 2, $id );
　　$update_sth->execute;
　　$dbh->commit;
}

say '-- after';
dump_table( $dbh, $table );

$dbh->disconnect;
unlink $db_name;

sub create_test_db {
　　my $db_name = shift;

if ( -f $db_name ) {
　　　　unlink $db_name;
　　}

my $dbh = DBI->connect(
　　　　'dbi:SQLite:dbname=' . $db_name,
　　　　'', '',
　　　　{
　　　　　　AutoCommit => 0,
　　　　　　RaiseError => 1
　　　　}
　　) || die "$db_name : $!";

return $dbh;
}

sub create_test_table {
　　my $dbh　 = shift;
　　my $talbe = shift;

my $sql
　　　　= 'CREATE TABLE '
　　　　. $table
　　　　. '(id integer primary key, name_en, name_jp)';
　　$dbh->do($sql);
　　$dbh->commit;

my $sth = $dbh->prepare(
　　　　'INSERT INTO ' . $table . '(id, name_en, name_jp) VALUES (?, ?, ?)' );

while ( my $line = <DATA> ) {
　　　　$line =~ s/\x0D?\x0A?$//;
　　　　my ( $id, $name_en, $name_jp ) = ( '', '', '' );
　　　　if ( $line =~ m/^(\S+)\s+(\S+)$/ ) {
　　　　　　$id　　　= $1;
　　　　　　$name_en = $2;
　　　　}
　　　　elsif ( $line =~ m/^(\S+)\s+(\S+)\s+(\S+)$/ ) {
　　　　　　$id　　　= $1;
　　　　　　$name_en = $2;
　　　　　　$name_jp = encode( 'utf8', $3 );
　　　　}
　　　　else {
　　　　　　die 'Unknown format : ', $line;
　　　　}
　　　　$sth->bind_param( 1, $id );
　　　　$sth->bind_param( 2, $name_en );
　　　　$sth->bind_param( 3, $name_jp );
　　　　$sth->execute;
　　　　$dbh->commit;
　　}
}

sub dump_table {
　　my $dbh　 = shift;
　　my $talbe = shift;

my $sql = "SELECT * FROM $table;";
　　my $sth = $dbh->prepare($sql);
　　$sth->execute;
　　while ( my $ref = $sth->fetch() ) {
　　　　say decode( 'utf8', "@$ref" );
　　}
}

__DATA__
1377　　　RUS　　　　　ロシア
1378　　　GER　　　　　ドイツ
1379　　　USA
1380　　　SPN
1381　　　JPN
1395　　　USA
1396　　　JPN
1397　　　USA　　　　　アメリカ合衆国
1398　　　ITL　　　　　 イタリア

$ perl -w data.pl
-- before
1377 RUS ロシア
1378 GER ドイツ
1379 USA 
1380 SPN 
1381 JPN 
1395 USA 
1396 JPN 
1397 USA アメリカ合衆国
1398 ITL イタリア
-- after
1377 RUS ロシア
1378 GER ドイツ
1379 USA アメリカ合衆国
1380 SPN スペイン
1381 JPN 日本
1395 USA アメリカ合衆国
1396 JPN 日本
1397 USA アメリカ合衆国
1398 ITL イタリア

_--_1l1_1_ · Answer

文字のエンコードが中途半端だったので手なおししました。

文字のエンコードは以下が参考になると思います。
http://d.hatena.ne.jp/tokuhirom/20080408/1207619640
http://blog.livedoor.jp/dankogai/archives/51031595.html

# sqlite_unicode => 1で自動でフラグ付きUTF8に変換するようにした。
# 空白2文字を全角空白にしていることに注意

use strict;
use warnings;
use utf8;
use feature 'say';
use DBI;

binmode STDOUT, ':encoding(utf8)';

my $db_name　　= 'sqlite_test.db';
my $table　　　= 'user_info';
my %jp_name_of = (
　　JPN => '日本',
　　USA => 'アメリカ合衆国',
　　SPN => 'スペイン'
);

my $dbh = create_test_db($db_name);
create_test_table( $dbh, $table );
say '-- before';
dump_table( $dbh, $table );

my $update_sql = 'UPDATE ' . $table . ' SET name_jp = ? where id = ?;';
my $update_sth = $dbh->prepare($update_sql);

my $fetch_sql
　　= "SELECT id, name_en FROM $table where id between 1379 and 1396;";
my $fetch_sth = $dbh->prepare($fetch_sql);
$fetch_sth->execute;
while ( my $ref = $fetch_sth->fetch() ) {
　　my $id　　　= $ref->[0];
　　my $name_en = $ref->[1];

$update_sth->bind_param( 1, $jp_name_of{$name_en} );
　　$update_sth->bind_param( 2, $id );
　　$update_sth->execute;
　　$dbh->commit;
}

say '-- after';
dump_table( $dbh, $table );

$dbh->disconnect;
unlink $db_name;

sub create_test_db {
　　my $db_name = shift;

if ( -f $db_name ) {
　　　　unlink $db_name;
　　}

my $dbh = DBI->connect(
　　　　'dbi:SQLite:dbname=' . $db_name,
　　　　'', '',
　　　　{
　　　　　　AutoCommit　　 => 0,
　　　　　　RaiseError　　 => 1,
　　　　　　sqlite_unicode => 1,
　　　　}
　　) || die "$db_name : $!";

return $dbh;
}

sub create_test_table {
　　my $dbh　 = shift;
　　my $talbe = shift;

my $sql
　　　　= 'CREATE TABLE '
　　　　. $table
　　　　. '(id integer primary key, name_en, name_jp)';
　　$dbh->do($sql);
　　$dbh->commit;

my $sth = $dbh->prepare(
　　　　'INSERT INTO ' . $table . '(id, name_en, name_jp) VALUES (?, ?, ?)' );

while ( my $line = <DATA> ) {
　　　　$line =~ s/\x0D?\x0A?$//;
　　　　my ( $id, $name_en, $name_jp ) = ( '', '', '' );
　　　　if ( $line =~ m/^(\S+)\s+(\S+)$/ ) {
　　　　　　$id　　　= $1;
　　　　　　$name_en = $2;
　　　　}
　　　　elsif ( $line =~ m/^(\S+)\s+(\S+)\s+(\S+)$/ ) {
　　　　　　$id　　　= $1;
　　　　　　$name_en = $2;
　　　　　　$name_jp = $3;
　　　　}
　　　　else {
　　　　　　die 'Unknown format : ', $line;
　　　　}
　　　　$sth->bind_param( 1, $id );
　　　　$sth->bind_param( 2, $name_en );
　　　　$sth->bind_param( 3, $name_jp );
　　　　$sth->execute;
　　　　$dbh->commit;
　　}
}

sub dump_table {
　　my $dbh　 = shift;
　　my $talbe = shift;

my $sql = "SELECT * FROM $table;";
　　my $sth = $dbh->prepare($sql);
　　$sth->execute;
　　while ( my $ref = $sth->fetch() ) {
　　　　say "@$ref";
　　}
}

__END__
1377　　　RUS　　　　　ロシア
1378　　　GER　　　　　ドイツ
1379　　　USA
1380　　　SPN
1381　　　JPN
1395　　　USA
1396　　　JPN
1397　　　USA　　　　　アメリカ合衆国
1398　　　ITL　　　　　 イタリア

SQL構文でカラム名をハッシュのキーに

例えば以下でしょうか…。

この回答への補足

ハッシュのキーは (およそ) \w+ にマッチすれば自動的に文字列とみなされます. だから

この回答への補足

そこの文字列は「Perl の文字列」として解釈されるんだから, カラム名として使いたい文字列をハッシュのキーに使っても無意味じゃん.

あれ? 「タプル」自体は SQL 方面でも特に珍しい言葉じゃないと思ったんだけど....

name_en name_jp = $name{name_en}

この回答への補足

質問の内容が理解できません。

この回答への補足

ループではなくて、UPDATE一発でできないかということでしょうかね。

- %nameだとわかりづらいので、%jp_name_ofにした。

文字のエンコードが中途半端だったので手なおししました。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

name_en　　　　　name_jp = $name{name_en}