Re: [O] sqlite im-/export
Oliver Večerník writes: >> I may have missed you spelling this out in a previous email, but can you >> not import Org tables directly into sqlite code blocks? >> >> Evaluate this again after evaluating the second block. >> #+BEGIN_SRC sqlite :csv :db test.sqlite >> SELECT * from t1; >> #+END_SRC > > This works like a charm, thanks again. > > But where is :csv or :db documented? The info manual says to look at worg, > but > there is no `ob-doc-sqlite' documentation link. Are there any more > specific arguments to sqlite? > > There is no `ob-doc-sh' either, but maybe there are no specific arguments > for shell. Sadly many of the languages are under documented. That could be a good thing, in that it generally takes less time to add a new language specific header argument than it does to document one, but it is obviously also a bad thing when header arguments aren't used because no-one knows they exist. In general browsing the source code of the lisp/ob-lang.el files (e.g., lisp/ob-sqlite.el) is the best way to find out what header arguments are used. For example at the top of ob-sqlite you'll find the following. (defvar org-babel-header-args:sqlite '((db. :any) (header. :any) (echo . :any) (bail . :any) (csv . :any) (column. :any) (html . :any) (line . :any) (list . :any) (separator . :any) (nullvalue . :any)) "Sqlite specific header args.") In my opinion the source code of the language-specific files is readable, but of course I'm not a good judge as I wrote most of it. We definitely need more volunteers to add documentation on worg. -- Eric Schulte http://cs.unm.edu/~eschulte
Re: [O] sqlite im-/export
> I may have missed you spelling this out in a previous email, but can you > not import Org tables directly into sqlite code blocks? > > Evaluate this again after evaluating the second block. > #+BEGIN_SRC sqlite :csv :db test.sqlite > SELECT * from t1; > #+END_SRC This works like a charm, thanks again. But where is :csv or :db documented? The info manual says to look at worg, but there is no `ob-doc-sqlite' documentation link. Are there any more specific arguments to sqlite? There is no `ob-doc-sh' either, but maybe there are no specific arguments for shell. -- Best, Oliver
Re: [O] sqlite im-/export
Oliver Večerník writes: >> 2. use ":results drawer", and explicitly formatting the results in >>Org-mode syntax w/awk > > Unfortunately this doesn't work. The output is always printed to the > "#+RESULTS:" section and not piped through awk. But working with > ":results raw" works perfect for me. I can even produce a separator > line in the table now with =print "|-"=. > > The only question left, how to import org tables "on the fly" to sqlite, > to keep everthing in Org mode. This would also be an interesting > candidate for an "ob-doc-sh.org" starter I guess. I may have missed you spelling this out in a previous email, but can you not import Org tables directly into sqlite code blocks? Evaluate this again after evaluating the second block. #+BEGIN_SRC sqlite :csv :db test.sqlite SELECT * from t1; #+END_SRC #+RESULTS: | 1 | apple pie | | 2 | sugar | A manually created Org-mode block. #+name: manual | 3 | rhubarb | | 4 | butter | #+BEGIN_SRC sqlite :csv :var data=manual :db test.sqlite :results silent .separator "," .import $data t1 #+END_SRC -- Eric Schulte http://cs.unm.edu/~eschulte
Re: [O] sqlite im-/export
> 2. use ":results drawer", and explicitly formatting the results in >Org-mode syntax w/awk Unfortunately this doesn't work. The output is always printed to the "#+RESULTS:" section and not piped through awk. But working with ":results raw" works perfect for me. I can even produce a separator line in the table now with =print "|-"=. The only question left, how to import org tables "on the fly" to sqlite, to keep everthing in Org mode. This would also be an interesting candidate for an "ob-doc-sh.org" starter I guess. -- Cheers, Oliver
Re: [O] sqlite im-/export
Eric Schulte writes: > In that case I'd suggest either > > 1. adding :separator support to ob-sh.el, so that you can specify a >different results separator to be passed to >`org-babel-import-elisp-from-file' > > or > > 2. use ":results drawer", and explicitly formatting the results in >Org-mode syntax w/awk I'm faster with your second suggestion. Thanks for your hint and for this wonderful extension to Org mode! -- Cheers, Oliver
Re: [O] sqlite im-/export
Oliver Večerník writes: > Eric Schulte writes: >> What about >> >> #+BEGIN_SRC sqlite :csv :db test.sqlite >> SELECT * from t1; >> #+END_SRC >> >> #+RESULTS: >> | 1 | apple pie | >> | 2 | sugar | > > I needed some calculations and format tweaking with awk which I piped > the results. That is why I choose the shell. In this case this is not > possible. In that case I'd suggest either 1. adding :separator support to ob-sh.el, so that you can specify a different results separator to be passed to `org-babel-import-elisp-from-file' or 2. use ":results drawer", and explicitly formatting the results in Org-mode syntax w/awk Cheers, -- Eric Schulte http://cs.unm.edu/~eschulte
Re: [O] sqlite im-/export
Eric Schulte writes: > What about > > #+BEGIN_SRC sqlite :csv :db test.sqlite > SELECT * from t1; > #+END_SRC > > #+RESULTS: > | 1 | apple pie | > | 2 | sugar | I needed some calculations and format tweaking with awk which I piped the results. That is why I choose the shell. In this case this is not possible. -- Best, Oliver
Re: [O] sqlite im-/export
Oliver Večerník writes: > Hi, > > I've got a small test case which I believe is a bug: > > $ cat t1.sql > PRAGMA foreign_keys=OFF; > BEGIN TRANSACTION; > CREATE TABLE t1(id integer,product text); > INSERT INTO "t1" VALUES(1,'apple pie'); > INSERT INTO "t1" VALUES(2,'sugar'); > COMMIT; > sqlite3 test.sqlite < t1.sql > > #+BEGIN_SRC sh > echo "ID|product" > sqlite3 test.sqlite " > SELECT * from t1; > " > #+END_SRC > > #+RESULTS: > | ID | product | | > | 1 | apple | pie | > | 2 | sugar | | > What about #+BEGIN_SRC sqlite :csv :db test.sqlite SELECT * from t1; #+END_SRC #+RESULTS: | 1 | apple pie | | 2 | sugar | -- Eric Schulte http://cs.unm.edu/~eschulte
[O] sqlite im-/export
Hi, I've got a small test case which I believe is a bug: $ cat t1.sql PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE t1(id integer,product text); INSERT INTO "t1" VALUES(1,'apple pie'); INSERT INTO "t1" VALUES(2,'sugar'); COMMIT; sqlite3 test.sqlite < t1.sql #+BEGIN_SRC sh echo "ID|product" sqlite3 test.sqlite " SELECT * from t1; " #+END_SRC #+RESULTS: | ID | product | | | 1 | apple | pie | | 2 | sugar | | Why are there three columns exported? Tested with Org-mode version 8.0.3 (release_8.0.3-114-gab3f45 @ /home/user/org-mode/lisp/) on GNU Emacs 24.3.50.2 (x86_64-unknown-linux-gnu, GTK+ Version 3.6.4) of 2013-05-13 on myhost? Is it also possible to produce a separator line in the results table? I tried a couple of variations, but nothing worked: echo "|-" echo "-+" echo "|-+|" I have a couple of csv tables now which are imported with a src block like this: #+BEGIN_SRC sqlite :db test.sqlite :exports none :results silent PRAGMA foreign_keys=off; DROP TABLE IF EXISTS t1; CREATE TABLE t1(id INTEGER, product TEXT); .import t1.csv t1 #+END_SRC But it would be really nice to maintain these data in org tables and convert them on fly maybe just in memory. I couldn't figure out how to achieve this. Has anybody a sample? -- Best, Oliver
