Read cell content in an ipython notebook

Question

I have an ipython notebook with mixed markdown and python cells.

And I'd like some of my python cells to read the adjacent markdown cells and process them as input.

An example of the desired situation:

CELL 1 (markdown): SQL Code to execute

CELL 2 (markdown): select * from tbl where x=1

CELL 3 (python) : mysql.query(ipython.previous_cell.content)

(The syntax ipython.previous_cell.content is made up)

Executing "CELL 3" should be equivalent to mysql.query("select * from tbl where x=1")

How can this be done ?

I would start by asking `Can this be done?`. I don't think this is obvious. — cel, Nov 03 '15 at 20:32
It is possible at one way or another, we can always try and interpret the `.ipynb` format ourselves, and read the cell contents — Uri Goren, Nov 05 '15 at 13:14
Writing `sql`,`pig` and `bash` scripts in the same notebooks, without writing them twice, (for the readable `markdown` cell and for the `python` cell that executes them) — Uri Goren, Nov 06 '15 at 18:21
Yes, it can be done and pretty straightforwardly at that. The notebook just needs to have access to the directory that contains it. Are you more interested in running it from the command line, or from the notebook GUI? — alexis, Nov 11 '15 at 07:19
For non-python scripts I'd use raw cells rather than markdown, though. You don't want comments turning into headers, underscores into italics, etc. — alexis, Nov 11 '15 at 07:26

score 28 · Accepted Answer · edited May 29 '16 at 00:41

I think you are trying to attack the problem the wrong way.

First yes, it is possible to get the adjacent markdown cell in really hackish way that would not work in headless notebook execution.

What you want to do is use IPython cell magics, that allow arbitrary syntax as long as the cell starts with 2 percent signs followed by an identifier.

Typically you want SQL cells.

You can refer to the documentation about cells magics or I can show you how to build that :

from IPython.core.magic import  (
    Magics, magics_class, cell_magic, line_magic
)

@magics_class
class StoreSQL(Magics):


    def __init__(self, shell=None,  **kwargs):
        super().__init__(shell=shell, **kwargs)
        self._store = []
        # inject our store in user availlable namespace under __mystore
        # name
        shell.user_ns['__mystore'] = self._store

    @cell_magic
    def sql(self, line, cell):
        """store the cell in the store"""
        self._store.append(cell)

    @line_magic
    def showsql(self, line):
        """show all recorded statements"""
        print(self._store)

    ## use ipython load_ext mechanisme here if distributed
    get_ipython().register_magics(StoreSQL)

Now you can use SQL syntax in your python cells:

%%sql 
select * from foo Where QUX Bar

a second cell:

%%sql
Insert Cheezburger into Can_I_HAZ

check what we executed (the 3 dashes show the input /output delimitation, you do not have to type them):

%showsql
---
['select * from foo Where QUX Bar', 'Insert Cheezburger into Can_I_HAZ']

And what you asked at the beginning in your question:

 mysql.query(__mystore[-1])

This of course does require that you execute the previous cells in the right order, nothing prevent you from using the %%sql syntax to name your cells, e.g if _store is a dict, or better a class where you overwrite __getattr__, to act like __getitem__ to access fields with dot syntax . This is left as an exercise to the reader, or end see of the response:

@cell_magic
def sql(self, line, cell):
    """store the cell in the store"""
    self._store[line.strip()] = cell

you can then use sql cell like

%%sql A1
set foo TO Bar where ID=9

And then in your Python cells

mysql.execute(__mystore.A1)

I would also strongly suggest looking at Catherine Develin SqlMagic for IPython, and this Notebook gist on GitHub that show this all thing live.

In the comment you seem to say you want to add pig, nothing prevent you from having a %%pig magic neither. It is also possible to inject Javascript to enable correct Syntax Highlighting of SQL and PIG, but that's beyond the scope of this question.

I've got no need for this particular solution, but this is great information about IPython magics, thanks! — alexis, Nov 11 '15 at 07:33

Read cell content in an ipython notebook

1 Answers1

Linked