Can I access sqlite3 using octave?

Question

Is there a way to read and write to sqlite3 from octave?

I'm thinking something along the lines of RODBC in R or the sqlite3 package in python, but for octave.

I looked on octave-forge http://octave.sourceforge.net/packages.php

But could only find the 'database' package, which only supports postgresql.

Details:

• OS: Ubuntu 12.04
• Octave: 3.6.2
• sqlite: 3.7.9

Answer 1

I realise this is an old question, but most answers here seem to miss the point, focusing on whether there exists a bespoke octave package providing a formal interface, rather than whether it is possible to perform sqlite3 queries from within octave at all in the first place.

Therefore I thought I'd provide a practical answer for anyone simply trying to access sqlite3 via octave; it is in fact trivial to do so, I have done so myself many times.

Simply do an appropriate system call to the sqlite3 command (obviously this implies you have an sqlite3 client installed on your system). I find the most convenient way to do so is to use the

sqlite3 database.sqlite < FileContainingQuery > OutputToFile

syntax for calling sqlite3.

Any sqlite3 commands modifying output can be passed together with the query to obtain the output in the desired format.

E.g. here's a toy example plotting a frequency chart from a table which returns appropriate scores and counts in csv format (with headers and runtime stats stripped from the output).

  pkg load io   % required for csv2cell (used to collect results)

% Define database and Query
  Database = '/absolute/path/to/database.sqlite';

  Query = strcat(
  % Options to sqlite3 modifying output format:
    ".timer off            \n",   % Prevents runtime stats printed at end of query
    ".headers off          \n",   % If you just want the output without headers
    ".mode csv             \n",   % Export as csv; use csv2cell to collect results
  % actual query
    "SELECT Scores, Counts \n",
    "FROM Data;            \n"    % (Don't forget the semicolon!)
  );   

% Create temporary files to hold query and results
  QueryFile   = tempname()  ;   QueryFId = fopen( QueryFile, 'w' );
  fprintf( QueryFId, Query );   fclose(  QueryFId);
  ResultsFile = tempname();

% Run query
  Cmd = sprintf( 'sqlite3 "%s" < "%s" > "%s"',  Database, QueryFile, ResultsFile );
  [Status, Output] = system( Cmd );

% Confirm query succeeded and if so collect Results
% in a cell array and clean up temp files.
  if Status != 0,     delete( QueryFile, ResultsFile ); error("Query Failed");
  else,   Results = csv2cell( ResultsFile ); delete( QueryFile, ResultsFile );
  end

% Process Results
  Results  = cell2mat( Results );
  Scores   = Results(:, 1);   Counts  = Results(:, 2);
  BarChart = bar( Scores, Counts, 0.7 ); % ... etc

Et, voilà