时间戳

首页 > 解决方案 > 使用 bash 将值插入 sqlite 数据库

database - 使用 bash 将值插入 sqlite 数据库

问题描述

我在将值插入 sqlite 数据库时遇到问题。我正在使用 bash 脚本来解析 xml 文件，然后将这些值插入到数据库中。我的脚本如下：

#!/bin/bash

create_books_db()
{
sqlite3 books.db <<EOF

create table books (
book_id primary key,
book_url text,
description text,
book_name text
);

EOF
}

plug_values_into_books_db()
{
sqlite3 books.db <<EOF

insert into books (book_id,book_url,description,book_name)

values('$book_id',"$book_url","$description","$book_name");

EOF
}

start of script

create_books_db

line="/home/$USER/star.wars.the.empire.strikes.back.xml"

book_id=$(xmlstarlet sel -t -v //book/id $line);
book_url=$(xmlstarlet sel -t -v //book/book_url $line);
description=$(xmlstarlet sel -t -v //book/description $line);
book_name=$(xmlstarlet sel -t -v //book/name $line);

plug_values_into_books_db

#end of script

这是我正在使用的 xml 文件的示例：

?xml version="1.0"?>
<book>
  <book_url>https://starwars.com/books/</book_url>
  <character_credits>
    <character>
      <character_id>99</character_id>
      <character_name>Darth Vader</character_name>
    </character>
  </character_credits>
  <description>   The Empire Strikes Back (also known as Star Wars: Episode V – The
                  Empire Strikes Back) is a 1980 American epic space-opera film directed
                  by Irvin Kershner. Leigh Brackett and Lawrence Kasdan wrote the
                  screenplay, with George Lucas writing the film's story and serving as
                  executive producer. It was produced by Gary Kurtz for Lucasfilm and
                  stars Mark Hamill, Harrison Ford, Carrie Fisher, Billy Dee Williams,
                  Anthony Daniels, David Prowse, Kenny Baker, Peter Mayhew, and Frank Oz.
                  It is the second installment in the original Star Wars trilogy, the
                  second of the franchise to be produced, and the fifth episode in the
                  &#x201C;Skywalker Saga.&#x201D;</description>
  <book_id>103</book_id>
  <book_name>The Empire Strikes Back</book_name>
</book>

1）当我在值行中尝试这个时：

值($book_id,"$book_url","$description","$book_name");

然后从sqlite中的书籍中选择*，缺少book_id和book_name值

2）在值行中更改为单引号会产生此错误：

错误：第 2 行附近：“s”附近：语法错误

3）转义引号会产生：

错误：第 2 行附近：无法识别的令牌：“\”

我也尝试在 bash 变量周围加上“'”引号，但我不断收到错误

只有第 1) 点上面的作品没有错误，但它似乎省略了 book_id 和 book_name 标签，

我正在学习sqlite，我认为这与在插入之前转义或清理输入有关，但我不知道如何在bash中做到这一点。任何人都可以帮忙吗？

标签： databasebashsqlitecommand-lineinsert

解决方案

用于从 XML 中提取数据的标记名称中有 2 个拼写错误。

  # Was book/id
book_id=$(xmlstarlet sel -t -v //book/book_id $line);
book_url=$(xmlstarlet sel -t -v //book/book_url $line);
description=$(xmlstarlet sel -t -v //book/description $line);
  # Was book/name
book_name=$(xmlstarlet sel -t -v //book/book_name $line);

这应该使插入按预期工作。

建议：为保持一致性，book_id 使用双引号。请注意，双引号必须用于描述，因为它包含单引号，如film's.

values("$book_id","$book_url","$description","$book_name");

推荐阅读