Linux – sqlite3 and bash, database setup

sqlite3 and bash, database setup… here is a solution to the problem.

sqlite3 and bash, database setup

My bash script automatically feeds some data into the sqlite database. The problem I’m having is that every time I change the database settings, the changes are lost when I run the next command. An example will explain this.

sqlite3 Correlate.db ".mode csv"
sqlite3 Correlate.db ".output Correlated.csv"
sqlite3 Correlate.db "SELECT * FROM ALL_Data;
sqlite3 Correlate.db ".show"

The final result of .show is as follows:

     echo: off
  explain: off
  headers: off
     mode: list
nullvalue: ""
   output: stdout
separator: "|"
    stats: off
    width: 

It seems that I changed the output mode to csv has lost in addition to my output file has no data even though stdout is printing the data I want to see. Does each call reset the database settings? I’m confused!

Solution

I’ll try sending multiple commands to one instance of sqlite3 like this :

<pre class=”lang-sql prettyprint-override”>sqlite3 Correlate.db <<EOF
.mode csv
.output Correlated.csv
SELECT * FROM ALL_Data;
.show
EOF

You can also put these commands in a file and redirect them to sqlite3, for example:

sqlite3 Correlate.db < commandfile

Related Problems and Solutions