☰

これらのbash文字列の単語をsqliteテーブルに効率的に挿入できますか？

tag-icon • tag-icon bash shell-script sqlite
これらのbash文字列の単語をsqliteテーブルに効率的に挿入できますか？

テーブルに挿入したい$FNAMES2つのbash変数がありますが、どうすればよいかわかりません。$LNAMESsqlite

これはmweです：

#!/usr/bin/env sh

FNAMES="John Paul George Ringo"
LNAMES="Lennon McCartney Harrison Starr"

sqlite3 people.db "CREATE TABLE people(fname TEXT, lname TEXT);"
sqlite3 people.db "INSERT INTO people(fname, lname) <MAGIC> ;"
sqlite3 people.db -cmd ".mode column" "SELECT * FROM people;"

このスクリプトの出力は次のとおりです。

fname   lname
------  ---------
John    Lennon
Paul    McCartney
George  Harrison
Ringo   Starr

<MAGIC>私が望む結果を得るために置き換えることができるものがあるとします。可能ですか？

答え1

コマンドsqlite3ラインツールはサポートされていません。準備された声明したがって、シェルスクリプトでこれを試みると、エスケープされていない引用符などの問題が発生します。 Shell / Bashで引用された変数と引用されていない変数を処理することはすでに少し苦痛であり、自己引用要件があるSQLデータベースで作業するときに苦しむことはさらに激しくなります。

これはSQLiteライブラリのある言語を使用して行う必要があります。コンパイルされた言語でも解釈された言語でも、最も一般的に使用される言語にはこれらのライブラリがあります。

Perlでこれを行う方法のいくつかの例は次のとおりです。

これらすべての例には、次のものが必要です。データベースインタフェースそしてDBD::SQLiteインストールするライブラリモジュールです。 Linuxディストリビューションを実行している場合は、ほぼ確実にパッケージとして使用できます。たとえば、Debianでは実行しsudo apt install libdbd-sqlite3-perlてインストールします。他のほとんどの言語にも同様のライブラリがあります。

しかし、shやbash（または他のシェル）を使用しないもう1つの理由は、シェルスクリプトには存在するが他の言語には存在しない引用とトークン化および関連問題を処理する痛みを避けるためです（それらの問題と欠点あります）。 ）。

2つの配列があり、次のよう@firstに@lastなります。

#!/usr/bin/perl

use strict;
use DBI;

my $dbfile= './people.db';
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile");

my @first = qw(John Paul George Ringo);
my @last = qw(Lennon McCartney Harrison Starr);

$dbh->do('CREATE TABLE IF NOT EXISTS people(fname TEXT, lname TEXT)');

my $sth = $dbh->prepare('INSERT INTO people (fname, lname) VALUES (?,?)');

for my $i (0..$#first) {
  $sth->execute($first[$i],$last[$i]);
};

ここで重要なのは、準備されたステートメントを使用しているため、名前に引用符などの迷惑な文字を気にする必要がなく、これを処理するために特別な作業を行う必要もないことです。準備されたステートメントを使用すると、すべてが自動的に処理されます。

たとえば、別の名前で保存しsql-arrays.plて実行可能にしますchmod +x sql-arrays.pl。

シェルでレコードが正しく挿入されていることを確認できます。

$ sqlite3 people.db -cmd ".mode column" "SELECT * FROM people;"
fname   lname    
------  ---------
John    Lennon   
Paul    McCartney
George  Harrison 
Ringo   Starr

もちろん、Perlスクリプトを作成してSELECTステートメントを実行し、必要な形式でレコードを出力できます。私はそれを読者に練習として残します。

連想配列（ハッシュとも呼ばれる）を使用%people：

#!/usr/bin/perl

use strict;
use DBI;

my $dbfile= './people.db';
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile");

my %people = (
  'John'   => 'Lennon',
  'Paul'   => 'McCartney',
  'George' => 'Harrison',
  'Ringo'  => 'Starr',
);

$dbh->do("CREATE TABLE IF NOT EXISTS people(fname TEXT, lname TEXT);");

my $sth = $dbh->prepare('INSERT INTO people (fname, lname) VALUES (?,?)');

for my $person (keys %people) {
  $sth->execute($person,$people{$person});
};

注：ハッシュ値は特定の順序で保存されないため、レコードは準ランダムな順序でデータベースに挿入されます。sort keys %peopleを使用する代わりに使用することもできますが、keys %peopleこれにより、指定された順序（John、Paul、George、Ringo）とは異なる順序付き順序（George、John、Paul、Ringo）でレコードが挿入されます。

ほとんどの場合、ハッシュで作業するときの順序は重要ではありません。この場合、半ランダムなハッシュキーを使用するのではなく、別々のインデックス配列を使用して目的の順序を保存して繰り返すのが一般的です。

たとえば@order=qw(John Paul George Ringo);、for my $person (@order) { ... };代わりに後でfor my $person (keys %people) { ... }。

とにかく、データベースのレコードの順序が最初のバージョンと異なることがわかります。

$ rm -f people.db
$ ./sql-hash.pl
$ sqlite3 people.db -cmd ".mode column" "SELECT * FROM people;"
fname   lname    
------  ---------
John    Lennon   
Ringo   Starr    
George  Harrison 
Paul    McCartney

これは非常に人為的な例であることにも注目する価値があります。ハッシュキーは次のようになります。ユニーク、名前はそれから離れています。この例は、サンプルデータの基本的なアイデアを示していますが、実際の使用では、ハッシュキーはUUID、非反復シーケンス、または他の一意の識別子になり、データベースの対応するフィールドは主キーになります。

さらに重要なのは、SELECTクエリを実行するときにDBIモジュールが一致する行を返す方法の1つがハッシュまたはハッシュ参照を使用することで、キーを繰り返してデータを出力および/または処理できることです。

ところで、bash連想配列とインデックス配列もあります。 ksh、zsh、awkも同様です。他のほとんどの言語では、連想配列またはタプルまたは同様です。

フルネームを含む文字列配列を使用してください@people。

#!/usr/bin/perl

use strict;
use DBI;

my $dbfile= './people.db';
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile");

my @people = ('John Lennon', 'Paul McCartney', 'George Harrison', 'Ringo Starr');

$dbh->do("CREATE TABLE IF NOT EXISTS people(fname TEXT, lname TEXT);");

my $sth = $dbh->prepare('INSERT INTO people (fname, lname) VALUES (?,?)');

foreach (@people) {
  my ($first,$last) = split;
  $sth->execute($first,$last);
};

このバージョンでは、各フルネームを名前と姓に分割してデータベースに挿入します。

この形式は、ハードコーディングされた文字列配列を使用するのではなく、テキストファイルから名前のリスト（1行に1つの名前）を読みたい場合に特に便利です。

上記のスクリプト例のいずれも、中間名や役職（MrやDrなど）を持つ人、またはほとんどの英語圏の世界とは異なる命名規則を持つ人を処理できません。十分な努力をすれば、プログラムに合わせて名前を変更することを強制できますが、実際の問題を処理するためにアルゴリズム（およびデータベース構造）を変更する方が簡単です。人々はあまりにも不便かもしれません。特にポップスターたち。

答え2

ここにあなたがしたいことをbash行うスクリプトがあります<MAGIC>

#!/bin/bash
#
FNAMES="John Paul George Ringo Andrew Nicci"
LNAMES="Lennon McCartney Harrison Starr O'Brien Müller"

# Convert strings to space-separated arrays (lists)
# Bad things will happen if you have names with spaces (e.g. "Sarah" + "Maddison Smith")
fnames=($FNAMES)
lnames=($LNAMES)

# Create table
# echo "sqlite3 people.db 'CREATE TABLE people(fname TEXT, lname TEXT);'"
sqlite3 people.db 'CREATE TABLE people(fname TEXT, lname TEXT);'

# Loop across data
# Hope that there are the same number of surnames as forenames
for ((i=0; i<${#fnames[@]}; i++))
do
    # Escape quotes (' becomes ''). Other SQL variants may require a different process
    fname="${fnames[i]//\'/\'\'}"
    lname="${lnames[i]//\'/\'\'}"
    
    # Insert the data
    # echo "sqlite3 people.db \"INSERT INTO people(fname, lname) values ('$fname', '$lname');\""
    sqlite3 people.db "INSERT INTO people(fname, lname) values ('$fname', '$lname');"
done

# Report on the result
# echo "sqlite3 people.db -cmd '.mode column' 'SELECT * FROM people;'"
sqlite3 people.db -cmd '.mode column' 'SELECT * FROM people;'

潜在的なエッジの問題を説明するために、データセットに2つの追加名を使用してこれをテストしましたが、期待どおりに機能します。

John        Lennon
Paul        McCartney
George      Harrison
Ringo       Starr
Andrew      O'Brien
Nicci       Müller

答え3

私は空白で区切られたn個のリストを取得し、それをSQL値セットに変換して元の構文の大部分を保存するMAGICというbash関数を作成しました。私が知っている限り、Sqliteが期待する構文である標準SQLエスケープを使用します。

ゼロより大きいすべての数の列に対して機能します。

# MAGIC  a variadic function. 
# named for a comment in the question.
# takes one or more space-delimited lists and turns them
# into an SQL VALUES clause with SQL99 standard quoting.
# returns true, presents the result on stdout.
MAGIC(){
   local sep1 sep2 argz i
   argz=( "$@" )
   argz[0]="${argz[0]} " # ensure termination
   sep1="VALUES " 
   while [ "${argz[0]}" != "" ]
   do
      sep2=""
      echo -n "$sep1("
      for(( i=0; i<$# ; ++i ))
      do
         v=${argz[$i]%% *}
         echo -n "$sep2'${v//\'/\'\'}'"
         argz[$i]="${argz[i]#* }"
         sep2=','
      done
      echo -n ")"
      sep1=','
   done
}

FNAMES="John Paul George Ringo"
LNAMES="Lennon McCartney Harrison Starr"
    
sqlite3 people.db "CREATE TABLE people(fname TEXT, lname TEXT);"
sqlite3 people.db "INSERT INTO people(fname, lname) $(MAGIC "$FNAMES" "$LNAMES" ) ;"
sqlite3 people.db -cmd ".mode column" "SELECT * FROM people;"

関連情報