Assignment operator << for multiline sql statements not working

[Databoy555]

If I understand the documentation correctly in order for me to store the query output to the variable "works" I can use "<<" as done below:

%%sql works << select top 10 itemid from tblitemdata

But this results in an error message (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near '<'. (102) (SQLExecDirectW)") [SQL: u'works <<\nselect top 10 itemid\nfrom tblitemdata']

Single line queries seem to get assigned fine i.e. works = %sql select top 10 itemid from tblitemdata

returns "Done".

Thanks

[younglee23]

Hi Databoy555,

I had the same issue and I think I solved it. The code for this feature is in v0.3.9 which released fairly recently. Unfortunately, updating the package from python or anaconda only updates you to v0.3.8. If you don't want to wait for the release to your preferred package source, you may clone/install directly from the git repository.

Hope this helps!

[younglee23]

BTW, this feature rocks! Thank you @catherinedevlin and @xiaochuanyu for everything. I use this package all the time. And now, with the assignment operator, it just became even more versatile.

[kianho]

+1

I'm encountering the same problem, I'd love to have this feature fixed. Agreed with @younglee23, this package is super useful!

[Databoy555]

@younglee23 . Thanks. I will perform a manual update over the next few days and confirm.

Thanks for the awesome work

[esegal]

I've installed v0.3.9 and I still get this error. Thanks!

[xiaochuanyu]

@esegal can u show the query u doing where this fails as well as the error output? It may help me in debugging this.

[esegal]

cell 1:

# Testing parameters with multiline sql 
%load_ext sql
connection = 'postgresql://username:[email protected]/erezsegal'
from_clause = 'FROM my_table'
where_clause = '1=1'

cell 2:

%%sql $connection
SELECT * $from_clause WHERE $where_clause LIMIT 10;

The error:

(psycopg2.ProgrammingError) syntax error at or near "$"
LINE 1: SELECT * $from_clause WHERE $where_clause LIMIT 10;
                 ^
 [SQL: 'SELECT * $from_clause WHERE $where_clause LIMIT 10;']

[xiaochuanyu]

@esegal This issue pertains to storing result of a query into a variable, it doesn't apply to what you are trying to do. Expanding variables inside cell magic (%%sql) is not supported right now. However, expanding variables should work in all line magic not just %sql. Example: cell 1:

connection = 'postgresql://username:[email protected]/erezsegal'
%sql $connection

cell 2:

from_clause = 'FROM my_table'
where_clause = '1=1'
%sql SELECT * $from_clause WHERE $where_clause LIMIT 10;

[esegal]

You're right, thanks, I was confused because of the duplicate issue thing with #80.

[Vermabhi16]

Is this issue resolved now?

[mengxi-ream]

Is this issue resolved now?

This problem has been resolved in 0.3.9

Just use : before the variable

For multiline SQL

name = 'Countess'

%%sql
select description 
from character 
where charname = :name
# output: [(u'mother to Bertram',)]

You can also use : in single line SQL

name = 'Countess'

%sql select description from character where charname = :name
# output: [(u'mother to Bertram',)]