support multi line statements

[christophlingg]

ipython-sql is a great tool for exploring databases, thanks!

Complex sql statements occur to my quite often, it is convenient to split a statement in multiple lines. I couldn't figure out a way to do that with ipython-sql.

Might be a useful feature for others too.

Cheers

[catherinedevlin]

Hi!

It's a line magic vs. cell magic thing, a characteristic of IPython. For the %sql line magic, the whole SQL statement has to be on one line. But when a cell starts with the %%sql cell magic (two % signs), the SQL can take up as many lines as you need.

I'll consider this a ticket to clarify that in the ipython-sql docs. Thanks!

[christophlingg]

cool, thank you for that hint

[hedata]

you can also use the python linebreaks

%sql SELECT * FROM
TABLE

[dedan]

First I want to say thanks for this great extension.

I also often write longer queries and find it useful to use the %%sql cell magic. However I often don't want to directly see the results. I just want them to be stored to a variable. Thats easy with the line magic, but I don't know how to directly store the result of a cell magic in a variable without doing result = _.

I just read a notebook about other cell magics. Some of them allow to capture the result of the operation in a variable, e.g. the capture cell magic

%%capture capt
from __future__ import print_function
import sys
print('Hello stdout')
print('and stderr', file=sys.stderr)

Would you be interested in including this in this extension?

[catherinedevlin]

Hi, @dedan - I'd definitely like to provide that ability, but I'm not clear on what exactly you're suggesting - do you mean something like

%%sql myvar
SELECT * FROM mytable

... which would capture the results in myvar? That could work, though I'd have to think about how to avoid clashing with the connection identifier. I guess connection identifiers always have forward slashes or @ signs, so any word without either could be interpreted as the name of a capture variable definition.

If you're volunteering to write the patch, it will be accepted. Otherwise I should be able to do it myself - nudge me if you don't see it within a few weeks.

[dedan]

Hey Catherine,

yes, that was exactly what I was suggesting. I'll try to come up with a patch for that later today. I'll come back to you in case I run into problems that I can't solve.

Thanks for the quick reply

On Tue Dec 23 2014 at 15:50:09 Catherine Devlin [email protected] wrote:

Hi, @dedan https://github.com/dedan - I'd definitely like to provide that ability, but I'm not clear on what exactly you're suggesting - do you mean something like

%%sql myvar SELECT * FROM mytable

... which would capture the results in myvar? That could work, though I'd have to think about how to avoid clashing with the connection identifier. I guess connection identifiers always have forward slashes or @ signs, so any word without either could be interpreted as the name of a capture variable definition.

If you're volunteering to write the patch, it will be accepted. Otherwise I should be able to do it myself - nudge me if you don't see it within a few weeks.

— Reply to this email directly or view it on GitHub https://github.com/catherinedevlin/ipython-sql/issues/16#issuecomment-68011730 .

[dedan]

@catherinedevlin I thought about it again and realized that its not se easy to detect that the result should be written into a variable. I found myself sometimes writing something like

%%sql select
    *
from cars;

So the heuristics to just check that the first line does not start with @ or contains // is not sufficient. I also found that in your code you check if the first line starts with [ and ends with ] and I don't understand which kind of information you would capture there.

Do you have any suggestions of how the output capturing could be implemented without breaking existent code?

[catherinedevlin]

Hi, Stephan,

The square brackets are for specifying connection info in DSN format. That part was an independent contribution, I admit that I don't really understand it - I never use DSN myself.

Maybe something like

%%sql --capture myvarname SELECT * FROM blah

hopefully the @magic_arguments decorator would make that fairly easy.

http://ipython.org/ipython-doc/2/api/generated/IPython.core.magic_arguments.html

I admit that I've never actually used it, though.

On Sun, Dec 28, 2014 at 11:18 PM, Stephan Gabler [email protected] wrote:

@catherinedevlin https://github.com/catherinedevlin I thought about it again and realized that its not se easy to detect that the result should be written into a variable. I found myself sometimes writing something like

%%sql select * from cars;

So the heuristics to just check that the first line does not start with @ or contains // is not sufficient. I also found that in your code you check if the first line starts with [ and ends with ] and I don't understand which kind of information you would capture there.

Do you have any suggestions of how the output capturing could be implemented without breaking existent code?

— Reply to this email directly or view it on GitHub https://github.com/catherinedevlin/ipython-sql/issues/16#issuecomment-68231495 .

• Catherine http://catherinedevlin.blogspot.com

[dedan]

Hey Catherine,

that sounds like a good solution. I'll check that out. stephan

On Sun Dec 28 2014 at 21:47:11 Catherine Devlin [email protected] wrote:

Hi, Stephan,

The square brackets are for specifying connection info in DSN format. That part was an independent contribution, I admit that I don't really understand it - I never use DSN myself.

Maybe something like

%%sql --capture myvarname SELECT * FROM blah

hopefully the @magic_arguments decorator would make that fairly easy.

http://ipython.org/ipython-doc/2/api/generated/IPython.core.magic_arguments.html

I admit that I've never actually used it, though.

On Sun, Dec 28, 2014 at 11:18 PM, Stephan Gabler [email protected]

wrote:

@catherinedevlin https://github.com/catherinedevlin I thought about it again and realized that its not se easy to detect that the result should be written into a variable. I found myself sometimes writing something like

%%sql select * from cars;

So the heuristics to just check that the first line does not start with @ or contains // is not sufficient. I also found that in your code you check if the first line starts with [ and ends with ] and I don't understand which kind of information you would capture there.

Do you have any suggestions of how the output capturing could be implemented without breaking existent code?

— Reply to this email directly or view it on GitHub < https://github.com/catherinedevlin/ipython-sql/issues/16#issuecomment-68231495>

.

• Catherine http://catherinedevlin.blogspot.com

— Reply to this email directly or view it on GitHub https://github.com/catherinedevlin/ipython-sql/issues/16#issuecomment-68233898 .

[Gerauddasp]

Hi all,

First of all thank you for this amazing tool! Adding this feature would be really useful. Do you know when it will be added?

Thanks! Geraud.

[eric-burel]

Hi, maybe this issue can be closed, as it is solved by "https://github.com/catherinedevlin/ipython-sql/pull/70"?

%%sql 
r <<
select 1
from whatever
where col like '%foobar%'

This allow having both multiline, and assigning the result to a variable. Thanks for this package it makes working with Jupyter extremely intuitive!

[vvickedvveb]

You may do this in JupyterLab...

res = %sql SELECT \
* \
FROM \
company