ipython-sql same query works with %sql but not with %%sql

Question

I am querying a table using ipython-sql module. I can execute a query with %sql without any problems. However, I just can't do with %%sql.

%%sql 
SELECT DISTINCT Launch_Site AS Distinct_Launch_Site 
FROM spacex

It gives me an error like this:

SELECT DISTINCT Launch_Site AS Distinct_Launch_Site
       ^
SyntaxError: invalid syntax

But when I try like this:

%sql SELECT DISTINCT Launch_Site AS Distinct_Launch_Site FROM spacex

I got what I wanted without any errors raised.

I just don't understand why I can't use %%sql. In addition in some cases it works.

Example:

%%sql 

SELECT SUM(PAYLOAD_MASS_KG_) AS PAYLOAD_MASS_KG 
FROM spacex 
WHERE Customer = 'NASA (CRS)'

This one just works fine !

Answer 1

It really depends on what database are you using in this scenario?

I ran it with both ipython-sql and JupySQL and both seem to work fine with Duckdb. Since each DB behaves differently I'll need more context to help you out. Here's a sample I tried running with the penguins dataset:

JupySQL is a fork of ipython-sql with newer features and active support. Since we're talking open source, there's an active community so you're more than welcome to open an issue ticket in case you're still struggling!