Shell 脚本 SQLite [英] Shell scripting SQLite

问题描述

如何编写显示 SQLite 结果的 shell 脚本?我编写了一个向 SQLite 数据库添加条目的脚本.现在我想在添加该条目后显示结果.这是我的脚本:

How do I write a shell script that displays SQLite results? I have written a script that adds an entry to the SQLite database. Now I want to display the results after adding that entry. Here's my script:

echo 'insert into myTable (Date, Details, Category, Average) values (datetime('\''now'\'','\''localtime'\''), '\'''$1''\'', '\'''$2''\'', '$3');'|sqlite3 /Users/user/Documents/Test/dbName.db

在此之后，我希望脚本回显/吐出语句的输出:

After this I want the script to echo/spit the output of statements:

select sum(Average) from (select * from myTable where Category = 'category1');
select sum(Average) from (select * from myTable where Category = 'category2');

格式应该是这样的:

Category1 total = <output of first statement>
Category2 total = <output of second statement>

就是这样.我对 SQL 还是很陌生，对 shell 脚本来说不是很好.我也在寻找很好的教程来解释这样的问题.

Thats it. I am quite new to SQL and not that great with shell scripting. I am also looking for good tutorials explaining problems like this.

推荐答案

解决这个问题的一个常用方法是使用一个叫做here document的shell特性，试试这个:

One common way to solve this problem is to use a shell feature called a here document, try this:

 sqlite3 /Users/user/Documents/Test/dbName.dba <<EOS
     insert into myTable (Date, Details, Category, Average) 
               values(datetime('now','localtime'), '$1', '$2', '$3');

     select "Category1 total = " sum(Average) from (
          select * from myTable where Category = 'category1'
     );

     select "Category2 total = " sum(Average) from (
         select * from myTable where Category = 'category2'
     );

 EOS

请注意，EOS 可以是您喜欢的任何字符串(我想到的是 EndOfScript)，但它必须单独出现在文本的最后一行，并且没有尾随空格.

Note that EOS can be any string you like (I think of EndOfScript), but it must be alone on the last line of text with no trailing whitespace.

由于我不使用 sqlite3，您可能需要一些语句来关闭我不知道的批处理.另外，我不确定 '$1' 的东西会起作用，如果 sqlite3 是宽容的，请尝试使用$1"等.此外，您可能需要在 "CategoryN total = " 字符串后添加一个逗号.

As I don't use sqlite3, you may need some statment to close off the batch that I'm not aware of. Also, I'm not certain that the '$1' stuff will work, if sqlite3 is forgiving, try "$1", etc instead. Also, you may need to an a comma after the "CategoryN total = " string.

请注意，此解决方案允许您根据需要创建几乎任意大/长的 sql DML 语句.对于经常发生的事情，并且涉及大表，如果您对我们的系统有权限，您可能希望将 DML 转换为存储过程并调用它.

Note that this solution allows you to create your sql DML statements pretty much as big/long as you want. For stuff that will happen regularly and it ranging over large tables, if you have permissions on our system, you may want your DML to a stored procedure and call that.

我希望这会有所帮助.

(如果这不起作用，请编辑您的帖子以表明您正在使用的 shell、OS/Linux 版本以及您收到的错误消息的最小版本).

(If this doesn't work, please edit your post to indicate shell you are using, OS/Linux Ver and a minimal version of error messages that you are getting).

这篇关于Shell 脚本 SQLite的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！