* sqlite im-/export
@ 2013-05-16 17:07 Oliver Večerník
2013-05-16 19:11 ` Eric Schulte
0 siblings, 1 reply; 9+ messages in thread
From: Oliver Večerník @ 2013-05-16 17:07 UTC (permalink / raw)
To: emacs-orgmode
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
^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: sqlite im-/export
2013-05-16 17:07 sqlite im-/export Oliver Večerník
@ 2013-05-16 19:11 ` Eric Schulte
2013-05-16 20:03 ` Oliver Večerník
0 siblings, 1 reply; 9+ messages in thread
From: Eric Schulte @ 2013-05-16 19:11 UTC (permalink / raw)
To: Oliver Večerník; +Cc: emacs-orgmode
Oliver Večerník <ov@vecernik.at> 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
^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: sqlite im-/export
2013-05-16 19:11 ` Eric Schulte
@ 2013-05-16 20:03 ` Oliver Večerník
2013-05-16 20:48 ` Eric Schulte
0 siblings, 1 reply; 9+ messages in thread
From: Oliver Večerník @ 2013-05-16 20:03 UTC (permalink / raw)
To: emacs-orgmode
Eric Schulte <schulte.eric@gmail.com> 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
^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: sqlite im-/export
2013-05-16 20:03 ` Oliver Večerník
@ 2013-05-16 20:48 ` Eric Schulte
2013-05-17 4:35 ` Oliver Večerník
2013-05-17 5:34 ` Oliver Večerník
0 siblings, 2 replies; 9+ messages in thread
From: Eric Schulte @ 2013-05-16 20:48 UTC (permalink / raw)
To: Oliver Večerník; +Cc: emacs-orgmode
Oliver Večerník <ov@vecernik.at> writes:
> Eric Schulte <schulte.eric@gmail.com> 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
^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: sqlite im-/export
2013-05-16 20:48 ` Eric Schulte
@ 2013-05-17 4:35 ` Oliver Večerník
2013-05-17 5:34 ` Oliver Večerník
1 sibling, 0 replies; 9+ messages in thread
From: Oliver Večerník @ 2013-05-17 4:35 UTC (permalink / raw)
To: emacs-orgmode
Eric Schulte <schulte.eric@gmail.com> 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
^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: sqlite im-/export
2013-05-16 20:48 ` Eric Schulte
2013-05-17 4:35 ` Oliver Večerník
@ 2013-05-17 5:34 ` Oliver Večerník
2013-05-17 13:54 ` Eric Schulte
1 sibling, 1 reply; 9+ messages in thread
From: Oliver Večerník @ 2013-05-17 5:34 UTC (permalink / raw)
To: emacs-orgmode
> 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
^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: sqlite im-/export
2013-05-17 5:34 ` Oliver Večerník
@ 2013-05-17 13:54 ` Eric Schulte
2013-05-18 6:21 ` Oliver Večerník
0 siblings, 1 reply; 9+ messages in thread
From: Eric Schulte @ 2013-05-17 13:54 UTC (permalink / raw)
To: Oliver Večerník; +Cc: emacs-orgmode
[-- Attachment #1: Type: text/plain, Size: 743 bytes --]
Oliver Večerník <ov@vecernik.at> 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?
[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: example.org --]
[-- Type: text/x-org, Size: 377 bytes --]
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
[-- Attachment #3: Type: text/plain, Size: 46 bytes --]
--
Eric Schulte
http://cs.unm.edu/~eschulte
^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: sqlite im-/export
2013-05-17 13:54 ` Eric Schulte
@ 2013-05-18 6:21 ` Oliver Večerník
2013-05-18 14:10 ` Eric Schulte
0 siblings, 1 reply; 9+ messages in thread
From: Oliver Večerník @ 2013-05-18 6:21 UTC (permalink / raw)
To: emacs-orgmode
> 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
^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: sqlite im-/export
2013-05-18 6:21 ` Oliver Večerník
@ 2013-05-18 14:10 ` Eric Schulte
0 siblings, 0 replies; 9+ messages in thread
From: Eric Schulte @ 2013-05-18 14:10 UTC (permalink / raw)
To: Oliver Večerník; +Cc: emacs-orgmode
Oliver Večerník <ov@vecernik.at> 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
^ permalink raw reply [flat|nested] 9+ messages in thread
end of thread, other threads:[~2013-05-18 14:11 UTC | newest]
Thread overview: 9+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2013-05-16 17:07 sqlite im-/export Oliver Večerník
2013-05-16 19:11 ` Eric Schulte
2013-05-16 20:03 ` Oliver Večerník
2013-05-16 20:48 ` Eric Schulte
2013-05-17 4:35 ` Oliver Večerník
2013-05-17 5:34 ` Oliver Večerník
2013-05-17 13:54 ` Eric Schulte
2013-05-18 6:21 ` Oliver Večerník
2013-05-18 14:10 ` Eric Schulte
Code repositories for project(s) associated with this public inbox
https://git.savannah.gnu.org/cgit/emacs/org-mode.git
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).