If you use various libraries with python, I thought, "You can do a little thing with a little code, and you can make a little script with a little 5 steps, which is convenient." So I just listed python and other commands. I may come up with this, but I will post a 10-step script on an irregular basis.
As ** 7th **, I would like to post a script that outputs csv from the sqlite3 table with bash. </ font>
Although it is a little old data, I will issue a select statement from sqlite3 that stores the csv of the fred exchange rate that I downloaded and display it separated by commas.
【environment】 Linux: debian10 sqlite3: version 3.27.2 bash: 5.0.3
In sqlite3, the method of outputting comma-separated values seems to be that you can specify the delimiter with the option "-separator" in addition to ".mode csv as a command of sqlite3".
Then, I think that the syntax is as follows, but after trying various things, ** I thought it would be better to pipe the external file of [2] to sqlite3. ** **
[1] sqlite3 (-separator,) sqlite file name.sqlite3 "sql statement or command" [2] echo'.read "External file that describes sql"' | sqlite3 -separator, "database file name"
I read and extract the file that describes sql, but most of them are comments. Except for the database definition and the external sql statement indented in multiple lines, the operation of sqllite3 is as simple as one line. </ font> If you want to save the extraction result, just redirect.
The sqlite file corresponding to the database is "dexjpus.sqlite3" in the current directory. As shown below, it is time series data consisting of two columns, date and market price.
sqlite> .schema CREATE TABLE t_dexjpus( "DATE" TEXT, "dexjpus" TEXT ); sqlite>
The script was run from the terminal.
bash,sqlite3
#!/usr/bin/env bash
#####################################
#Script to output csv from sqlite3 table
# sqlite3: version 3.27.2
# bash: 5.0.3
# sqlite3:file name: dexjpus.sqlite3
#sql description file: select_tbl.sql
# sqlite3 (-separator ,)sqlite filename.sqlite3 "sql statement or command"
#csv output: -separator ,
# #
#####################################
#file name
db_file=dexjpus.sqlite3
#echo '.read select_tbl.sql' | sqlite3 -separator , dexjpus.sqlite3
#sqlite command ".Read the external file that describes sql with "read"
#Pipe to sqlite3 to feel separator ",Specify
#Open the file that contains the data
echo '10 cases in ascending order'
echo '.read select_tbl_asc.sql' | sqlite3 -separator , $db_file
echo ''
echo '10 cases in descending order'
echo '.read select_tbl_desc.sql' | sqlite3 -separator , $db_file
The external file may be described in one line, but for the time being, it is described with line breaks. </ font> As "echo'.read select_tbl_asc.sql' | sqlite3 -separator, $ db_file" I read the following sql statement. This data seems to include a period in the market price on the day when there is no transaction.
** Other than that, I searched 10 items in ascending order. ** **
sqlite3
select
*
from t_dexjpus
where dexjpus is not '.'
order by dexjpus asc
limit 10;
** I also read another file of 10 search in descending order. ** **
sqlite3
select
*
from t_dexjpus
order by dexjpus desc
limit 10;
Result of execution
bash
$ sh sqlite3_rev_csv.sh > out.txt
$ less out.txt
10 cases in ascending order
2016-08-18,100.0700
2016-08-23,100.2000
2016-08-17,100.2400
2016-08-19,100.2400
2016-08-22,100.2500
2016-08-16,100.2800
2016-09-26,100.3400
2016-09-27,100.3600
2016-08-24,100.5000
2016-09-28,100.5700
10 cases in descending order
2015-06-05,125.5800
2015-06-08,125.0800
2015-08-11,124.9000
2015-08-05,124.8400
2015-06-01,124.6400
2015-08-06,124.6000
2015-06-04,124.5600
2015-08-10,124.5500
2015-08-18,124.3900
2015-07-30,124.3800
By writing sql statement externally using ".read" of sqlite, it is easy to reuse and it seems to be able to handle complicated sql statement. </ font>
It seems that sqlite3 is easy to connect from languages such as python as well as shell, is easy to use on a file basis, and has abundant functions. Considering the spread of smart devices and IoT in the future </ font>, I think it will become more and more important in the future.
** This is the script that outputs csv from the sqlite3 table in 7.bash. ** **
Recommended Posts