Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Read cell content in an ipython notebook

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 ?

like image 462
Uri Goren Avatar asked Nov 03 '15 20:11

Uri Goren


1 Answers

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.

like image 138
Matt Avatar answered Sep 22 '22 05:09

Matt