Executing SQL Source Code Blocks in Org Mode
You can execute SQL source code blocks in Org Mode.
Using built-in package ob-sql
Emacs has a built-in way to run SQL source code blocks in Org Mode through the packages sql and ob-sql. In order to evaluate an SQL source code block you must have a properly installed RDBMS. Org mode supports mysql, postgresql, oracle, etc.
You’ll need to activate SQL source code blocks in your init file.
(org-babel-do-load-languages
'org-babel-load-languages
'((sql . t)))
Examples:
Put the header arguments on the same line:
#+begin_src sql :engine mysql :dbhost localhost :dbport 3306 :dbuser root :dbpassword "123456" :database nocobase
SELECT * FROM mytable WHERE id > 500
#+end_src
Put the header arguments on separate lines:
#+name: my-query
#+header: :engine mysql
#+header: :dbhost localhost
#+header: :dbuser root
#+header: :dbpassword "123456"
#+header: :database nocobase
#+begin_src sql
DESC users;
#+end_src
See SQL Source Code Blocks in Org Mode for more.
Using the package ob-sql-mode
The built-in package ob-sql currently doesn’t support sessions, that is, it
makes a connection (session) every time you run the block, which is somewhat
slower than using an existing session. A better approach is to use the package
ob-sql-mode, which supports
sessions.
You can evaluate SQLs in Org mode directly via the package ob-sql-mode, which
is an Org-Babel support for evaluating SQL using sql-mode.
Install the package:
(setq-default dotspacemacs-additional-packages '(ob-sql-mode))
(use-package ob-sql-mode :after (org))
Examples:
* Header 1
Now you can run the following code block with ~org-ctrl-c-ctrl-c~ (=C-c C-c=):
#+begin_src sql-mode :product mysql :session local
DESC users;
#+end_src
** Header 2
:PROPERTIES:
:header-args:sql-mode: :product mysql
:header-args:sql-mode+: :session local
:END:
You can put the header arguments in a drawer.
#+begin_src sql-mode
DESC users;
#+end_src
See (describe-package 'ob-sql-mode) for more details on setup.
Comments