How do I provide inline input to an IPython (notebook) shell command?

Question

I want to put together an IPython notebook with some shell commands and their input. In the bash prompt I can use "here-document" syntax:

bash-3.2$ mysql -u root <<END_IPUT
> use mydb;
> show tables;
> END_INPUT

How do I get the same effect in IPython, and specifically in a jupyter notebook? I know how to execute shell commands as IPython as "line magics" or "cell magics", e.g.:

In [7]:  !! ls -tF
Out[7]:  ['Demo-notebook.ipynb',
          'createdb.sql',
          ...

I've looked at IPython as a system shell, which shows how to enable some syntactic niceties. After the following, I can run system commands without prepending ! or !!

# Turn everything in $PATH into an alias; 
# then enable calling aliases without ! or %
%rehashx      
%autocall 2 

But none of this helps with providing input to these commands inline: The here-document syntax is invalid in IPython, and results in a python SyntaxError. So how do I do it?

Answer 1

The bash cell magic allows you to enter multiple lines of bash. Just start any cell with %%bash:

%%bash
echo "This is bash"
ls

If you're using a heredoc to pipe text into another program, you can also use the script cell magic:

%%script bc
2+3

There may also be other cell magics which address your problem more specifically!

Answer 2

With some more research, and with the help of @ThomasK's tip (see accepted answer), I found a couple of ways to do this.

• One solution is the cell magic %sx. It executes the cell contents as a bash script, and captures and returns the output as a list of lines. Sometimes handy, sometimes not.

  In[1]:  %%sx
          echo Hello, world
          cat -n <<DATA
          this
          and that
          DATA
  
  Out[1]: ['Hello, world', '     1\tthis', '     2\tand that']
  

• Change that to %%bash, as @ThomasK suggested, and the output is printed out, not returned. To capture it for further processing, ipython provides the --out parameter. But then I need to print it myself-- it won't show up by default.

  In[1]:  %%bash --out var
          echo Hello, world
          echo "   Again"
  
  In[2]:  var
  
  Out[2]: 'Hello, world\n   Again\n'
  

• %%bash is actually shorthand for %%script bash, billed as working "like the #! line in a script" (see the help with %%script?). Which it does. So here's how to dispense with the here-document syntax and have any program read the cell contents as input. %%script also accepts the --out parameter.

          %%script --out var mysql -u root -p XYZ
          USE somedb;
          SELECT * FROM users
               WHERE passwd IS NULL\G
  

This is nice enough, but I ended up not using it because my use case is mysql, and eventually I discovered that there's a great third-party ipython-sql extension that I can use instead:

%load_ext sql
%sql mysql+pymysql://user:passwd@localhost/somedb

This presupposes installing the extension, with pip install ipython-sql. For mysql, I also needed pip install pymysql. After the above setup, I just talk to the database like this

In[1]:  %%sql 
        SELECT * FROM users
            WHERE passwd IS NULL

Out[1]: 1 rows affected.
        ...

The returned table is actually drawn as a notebook table with borders, which is kinda nice. And it is made available as a Pandas dataframe (a smart list), which I can grab from _.