6

With sqlite one can easily populate a table in a database via a org-table like so:

[EDIT](using only one sqlite-scr-block with org-table variable parameter)[/EDIT] 

#+NAME: A | val1 | val2 | |------+------| | 2 | 16 | | 4 | 25 | | 10 | 2 | #+begin_src sqlite :db */path/to/db* :var orgtable=A :colnames yes drop table if exists A; create table A(val1 int, val2 int); .mode csv A .import $orgtable A select * from a; #+end_src #+RESULTS: | val1 | val2 | |------+------| | 2 | 16 | | 4 | 25 | | 10 | 2 |

I want to do this with postgresql. I tried with some COPY commands, but did not find other concrete info, on how to do this with org-variables.

How can I populate a table in postgresql with the contents of an org-mode-table?

EDIT (after @Martín's answer and @NickD's comment): I "merged" the answer and the comment.

1) Export table to tmp-file

#+begin_src emacs-lisp :var orgtable=A (write-region (orgtbl-to-csv orgtable nil) nil "/tmp/test.csv") #+end_src

2) Import to db via localcopy \copy in sql-src-block

#+name: my-query #+header: :engine postgresql #+header: :dbhost localhost #+header: :dbuser USER #+header: :dbpassword PW #+header: :database org-db #+begin_src sql drop table if exists A; create table A(val1 int, val2 int); \copy A(val1,val2) FROM '/tmp/test.csv' DELIMITER ','; #+end_src

This comes quite close… But before I start to improve this, I'd love hints and answers how to do this with variable as input (w/o the detour with 2 src-blocks and a transfer tmp file).

Improve this question
1
  • 1
    There is a \copy command in psql that can import a CSV file. I don't know if there is something to import directly from the variable. Oh, and there is ob-sql that can deal with a SQL database. Commented Feb 24, 2019 at 13:11

2 Answers 2

Reset to default
5

Solution: in sql-scr-block context the variable $orgtable translates to path-string of an auto-generated temporary csv file – wich can then be imported with \copy. 

#+name: my-query-push-table #+header: :var orgtable=A #+begin_src sql :cmdline -q :database org :dbpassword PW :dbuser USER :dbhost localhost :engine postgresql DROP TABLE if exists A; CREATE TABLE A(val1 int, val2 int); \copy A(val1,val2) FROM '$orgtable' DELIMITER ','; SELECT * FROM A; #+end_src #+name: my-query-push-table-result #+RESULTS: my-query-push-table | val1 | val2 | |------+------| | 2 | 16 | | 4 | 25 | | 10 | 22 |

…almost the same solution as in answer from @Martín via a tmp-file, but… org-magic ^^ and using local copy \copy as @NickD suggested. So to have an answer, I posted my solution… feel free to update yours, I'll switch marked solution…

Improve this answer
2
  • Great solution! Way better than both of mine Commented Feb 27, 2019 at 2:04
  • Still learned a lot from your answer :) Nice of org-mode, that variables are assigned according to context (in sh i.e. as plain text, in sql like the automated version of your solution.m1) Commented Feb 27, 2019 at 8:31
2

It is possible, but it's a little hacky (okay, very hacky)

Method 1:

First, you have to save the org-table to a CSV file

#+begin_src emacs-lisp :var orgtable=A (write-region (orgtbl-to-csv orgtable nil) nil "test.csv") #+end_src

Then, you can use the COPY command to import the local CSV file from STDIN with the psql command

#+begin_src sh psql -h dbhost -d mydb -U myuser -c "COPY mytable_a FROM STDIN with delimiter as ','" < test.csv #+end_src

The only caveat is that the table cannot have the headings, because if it does the import will fail trying to insert them.

Method 2:

We can use a python block to format the table rows in order to be inserted with a multirow INSERT INTO mytable_a VALUES ((2,16),...)

#+name: values #+begin_src python :var orgtable=A :results output def format_row(row): return "(" + ",".join(map(str, row)) + ")" def format_rows(rows): return ",".join(map(format_row, rows)) print (format_rows(orgtable)) #+end_src #+RESULTS: values : (2,16),(4,25),(10,2)

Then, we reference the result value of the python block in a SQL block to insert the rows

#+begin_src sql :engine postgresql :var val=values INSERT INTO mytable_a VALUES $val #+end_src
Improve this answer
1
  • do you know, why orgtbl-to-csv does not output the headings? if used interctively w/o params it does… thinking of expanding the hack to also create a table with column names [and as we're shellscripting anyway perhaps interpret type from a part of headings, too] Commented Feb 26, 2019 at 20:27

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.