SQLite3 commands in ruby

Question

Is there a way to pass commands from SQLite into ruby script? For example I have a test.sqlite database created. Now I want to export a query result from that database into .csv file. I can simply do that using command line:

Sqlite3 "H:\\test.sqlite"
.mode csv
.headers on
.once test.csv
select * from cars;

Sqlite3 "H:\\test.sqlite"
.mode csv
.headers on
.once test.csv
select * from cars;

or also using SQLite3:

sqlite3 -header -csv sqlite3 -header -csv H:\test.sqlite< query.sql > test.csv

and it works perfectly fine in CMD but I want to have that coded in my ruby script. I was also trying to pass that into command line using %x but it is not working. Any help?

Answer 1

You could receive the STDIN by using pipes, someone asked that here (Best practices with STDIN in Ruby?)

Or you could also use the sqlite gem(https://rubygems.org/gems/sqlite3/versions/1.3.11) to connect to the database and then run the query from ruby.

You can also use sequel(https://github.com/jeremyevans/sequel) the interface is nicer, to communicate with the sqlite database

Answer 2

Simply system("sqlite3 -header -csv sqlite3 -header -csv H:\test.sqlite< query.sql > test.csv"

I just needed to add 2 slashes in path...

Answer 3

The question is not exactly defined.

Firstly read the documentation for the sqlite3 gem and csv library.

Using this information, you can parse and write csv-files, make queries and get results to/from SQLite3 DB and much more.