☰

这些bash字符串中的单词可以有效地插入到sqlite表中吗？

tag-icon • tag-icon bash shell-script sqlite
这些bash字符串中的单词可以有效地插入到sqlite表中吗？

我有两个 bash 变量$FNAMES，$LNAMES我想将它们插入到表中，sqlite但我不太确定该怎么做。

这是一个 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 脚本中执行此操作的尝试都会遇到未转义引号等问题。在 shell/bash 中处理带引号和不带引号的变量已经有点痛苦，而且当您使用有自己的引用要求的 SQL 数据库时，这种痛苦只会变得更糟。

像这样的任务应该使用具有 SQLite 库的语言来完成。最常用的语言，无论是编译语言还是解释语言，都具有此类库。

以下是如何在 Perl 中执行此操作的一些示例：

所有这些例子都需要数据库接口和DBD::SQLite要安装的库模块。如果您正在运行任何 Linux 发行版，那么它们几乎肯定会以软件包形式提供。例如，在 Debian 上，运行sudo apt install libdbd-sqlite3-perl以安装它们。大多数其他语言都有类似的库。

顺便说一句，不使用 sh 或 bash（或其他 shell）的另一个原因是避免处理 shell 脚本中存在的引号和分词以及相关问题的痛苦，但其他语言中不存在（它们有自己的问题和怪癖） 。

有两个数组，@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。

您可以检查它是否从 shell 正确插入了记录：

$ 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，但这会按排序顺序插入记录 (George,John,Paul,Ringo)，这与提供的顺序不同 (John,Paul,George,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

我编写了一个名为 MAGIC 的 bash 函数，它采用 n 个空格分隔的列表并将它们转换为一组 SQL 值，从而保留了大部分原始语法。它使用标准的 SQL 转义，据我所知这是 Sqlite 所期望的语法。

它适用于任何大于零的列数。

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

相关内容