☰

이러한 bash 문자열의 단어를 sqlite 테이블에 효율적으로 삽입할 수 있습니까?

tag-icon • tag-icon bash shell-script sqlite
이러한 bash 문자열의 단어를 sqlite 테이블에 효율적으로 삽입할 수 있습니까?

테이블에 삽입하고 싶은 $FNAMESbash 변수가 두 개 있는데 어떻게 해야 할지 잘 모르겠습니다.$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(또는 다른 쉘)를 사용하지 않는 또 다른 이유는 쉘 스크립트에는 존재하지만 다른 언어에는 존재하지 않는 인용문과 토큰화 및 관련 문제를 처리하는 고통을 피하기 위해서입니다(그들만의 문제와 단점이 있습니다). ) .

두 개의 배열이 있으며 다음 @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 모듈이 일치하는 행을 반환하는 방법 중 하나가 해시 또는 해시 참조를 사용하는 것이며 키를 반복하여 데이터를 출력 및/또는 처리할 수 있다는 것입니다.

그런데 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);
};

이 버전은 각 전체 이름을 이름과 성으로 분할하여 데이터베이스에 삽입합니다.

이 형식은 하드코딩된 문자열 배열을 사용하는 대신 텍스트 파일에서 이름 목록(한 줄에 하나의 이름)을 읽으려는 경우에 특히 유용합니다.

위의 예제 스크립트 중 어느 것도 중간 이름이나 직위(예: 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;'

잠재적인 에지 문제를 설명하기 위해 데이터 세트에 두 개의 추가 이름을 사용하여 이것을 테스트했는데 예상대로 작동합니다.

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

답변3

저는 공백으로 구분된 n개의 목록을 가져와 이를 SQL 값 세트로 변환하여 원래 구문의 대부분을 보존하는 MAGIC이라는 bash 함수를 작성했습니다. 내가 아는 한 Sqlite에서 기대하는 구문인 표준 SQL 이스케이프를 사용합니다.

0보다 큰 모든 수의 열에 대해 작동합니다.

# 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;"

관련 정보