Unable to use variables in multiline statements (%%sql)

[CapChrisCap]

Variables can be used normally with the following syntax:
[1] table = 'my_fancy_table'
[2] %sql SELECT * FROM $table
Unfortunately, when I want to create multiline sql statements (like the following) variables are not possible to use:
[1] table = 'my_fancy_table'
[2] %%sql SELECT name, firstname FROM $table
Outputs:
(psycopg2.ProgrammingError) syntax error at or near "$"
LINE 1: SELECT * FROM $table;

Does I have to use a different variables syntax for multiline statements or do multiline statements simply not support variables?

Thanks in advance.

[kindofluke]

This is a duplicate of #79. @catherinedevlin is preparing this as a new feature for the 0.3.9 release

You can upgrade your installation by cloning the repository and pip install from source or just wait until the new release is pushed up.

I upgraded and its working great so thanks to the team for working on it. Also, see news.txt file for more details on 0.3.9

[xiaochuanyu]

I don't think this is a duplicate of #79.
#79 is about storing result of query into variable, not expanding variables inside cell magic which is not supported right now.

[esegal]

As these are different issues I can confirm that v0.3.9 doesn't solve this issue. Hope it will be solved soon!

[choj]

Status on this?

[mengxi-ream]

Status on this?

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',)]

[Lucaszw]

Status on this?

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',)]

Using : solves a similar but not quite the same things as $. : works for parameters but not for extending the query.

IE

part_of_query = "where sign_in_count > 10"

%sql select count(name) from users $part_of_query

works with $ but not for :

[mengxi-ream]

Status on this?

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',)]

Using : solves a similar but not quite the same things as $. : works for parameters but not for extending the query.

IE

part_of_query = "where sign_in_count > 10"

%sql select count(name) from users $part_of_query

works with $ but not for :

Thank you for your intuition!

[michalrudko]

In my case both $ and : don't work.
I am trying to connect to Presto database - without variables the queries run smoothly.

I have the following code:

db_table = "my_schema.my_table"
%%sql 
CREATE TABLE $db_table AS
SELECT x, CAST (y AS INT) as duration
FROM abc.def

DatabaseError: {'message': "line 1:14: mismatched input '$'. Expecting: 'OR', 'ROLE', 'SCHEMA', 'TABLE', 'VIEW'", 'errorCode': 1, 'errorName': 'SYNTAX_ERROR', 'errorType': 'USER_ERROR', 'errorLocation': {'lineNumber': 1, 'columnNumber': 14}, 'failureInfo': {'type': 'com.facebook.presto.sql.parser.ParsingException', 'message': "line 1:14: mismatched input '$'. Expecting: 'OR', 'ROLE', 'SCHEMA', 'TABLE', 'VIEW'"

OR

db_table = "my_schema.my_table"
%%sql 
CREATE TABLE :db_table AS
SELECT x, CAST (y AS INT) as duration
FROM abc.def

DatabaseError: {'message': "line 1:14: mismatched input ''my_schema.my_table''. Expecting: 'OR', 'ROLE', 'SCHEMA', 'TABLE', 'VIEW'", 'errorCode': 1, 'errorName': 'SYNTAX_ERROR', 'errorType': 'USER_ERROR', 'errorLocation': {'lineNumber': 1, 'columnNumber': 14}, 'failureInfo': {'type': 'com.facebook.presto.sql.parser.ParsingException', 'message': "line 1:14: mismatched input ''my_schema.my_table''. Expecting: 'OR', 'ROLE', 'SCHEMA', 'TABLE', 'VIEW'"

How I am supposed to use my python variable inside a query passed to %sql or %%sql?

[Lucaszw]

I made a PR for this, and I think it should resolve your issue @mrjoseph84 .

Try:

db_table = "my_schema.my_table"
%%sql 
CREATE TABLE {db_table} AS
SELECT x, CAST (y AS INT) as duration
FROM abc.def

[michalrudko]

I made a PR for this, and I think it should resolve your issue @mrjoseph84 .

Try:

db_table = "my_schema.my_table"
%%sql 
CREATE TABLE {db_table} AS
SELECT x, CAST (y AS INT) as duration
FROM abc.def

Thanks a lot - this worked for me as expected :)

[Lucaszw]

I made a PR for this, and I think it should resolve your issue @mrjoseph84 .
Try:

db_table = "my_schema.my_table"
%%sql 
CREATE TABLE {db_table} AS
SELECT x, CAST (y AS INT) as duration
FROM abc.def

Thanks a lot - this worked for me as expected :)

Awesome! Glad to hear it :)