Better cell magic by removing some of the syntax limitations

[soerenwolfers]

Below, I'm making suggestions (A)-(E) how I think cell magic could be made even better.

If people generally think these are good ideas, I'd try and make a PR.

First, however, I wanted to see if there are any syntax or compatibility issues I'm missing.

As far as I can tell, each of my suggestions
(1) extends the set of legal cell contents
and all but (B) and (E)
(2) gives the same results on the previous set of legal cell contents
where the only reason that (B) and (E) do not give the same results on previous contents is that nested magic would trip (which I actually use quite frequently)

If I required triple-% for the new functionality, I think all my suggestions would satisfy both (1) and (2).

(A) Just about any cell magic I've ever used would have been better if it was possible to assign output via

df = %%sql
<SOME QUERY>

similar to how it's already possible to do

df = %sql <SOME QUERY>.

More generally, I think it should simply be allowed to start %%-magic anywhere in a cell.

Most cell magic I know instead implement awkward workarounds like

%%sql -o df
<SOME QUERY>

but since every cell magic uses its own syntax and most don't support things like assigning to dictionaries, this is a major hassle.

(B) Extending on the previous item, it'd be great if we could also have code after %%-magic, e.g.,

df = %%sql
<SOME QUERY>
%%

i.e., be able to close a cell magic block by %% (see intro: maybe best to just use triple-%)

(C) Just about any cell magic I've ever used would have been better if it was possible to do

%%sql(connection=a, flag=b)
<SOME QUERY>

and have this be translated to a regular function-call of the function registered as part of the cell-magic registration, the result of which would then do the actual cell-magic.

Most cell magic I know instead implements awkward workarounds like sql -f -conn conn but since every cell magic uses its own syntax and most don't support things like indexing dictionaries, this is a major hassle.

For example, the sql magic above could be implemented by registering

def sql_cell_magic_wrapper(connection: Conn = None, flag: bool = None) -> Func[str, Any]:
    def cell_magic(cell: str) -> Any:
        return sql_client.execute(cell)

with Jupyter; and a simple %%sql without arguments could simply be translated to a call of sql_cell_magic_wrapper without arguments, i.e., sql_cell_magic_wrapper()(<cell>)

(D) Cell magic should be allowed to fall back to line magic:

%%sql <SOME QUERY>

It's annoying to always have to remove/add % just because you're changing your mind whether you need an entire cell or not.

(E) Further to (D), it should be legal to terminate a single-line %%-magic and continue the line non-magically, e.g.

df = (%%sql <SOME QUERY> %%).sum()

(A-E): In combination, it would be possible to do

import pandas as pd
import numpy as np
import my_magic

df = pd.DataFrame({'i': np.arange(13), 'v': np.random.rand(13)})
agg = %%sql(progress_bar=False)
SELECT 
i, v 
FROM df 
WHERE i % 2 == 0
%%
agg.plot(x='i', y='v')

or, if one so prefers,

import pandas as pd
import numpy as np
import my_magic

df = pd.DataFrame({'i': np.arange(13), 'v': np.random.rand(13)})
%%sql(progress_bar=False) SELECT i, v FROM df WHERE i % 2 == 0 %%.plot(x='i', y='v')