how to pass a python variable to a multine sql magic

[aborruso]

Hi,
is it possible to have

myvar="white"

And use it in

%%sql
SELECT * from mytable where color LIKE $myvar;

I'm not able to do use it in a multiline magic

[aborruso]

It's a duplicate of #80

[muggle14]

Has this been published into the package now?

[mengxi-ream]

Has this been published into the package 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',)]

[yoonghm]

The following does not work:

t = 'employee'
%sql SELECT * FROM :t

The error messages are:

* sqlite://
(sqlite3.OperationalError) near "?": syntax error
[SQL: SELECT * FROM ?]
[parameters: ('employee',)]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
​

However, this works:

t = 'employee'
%sql SELECT * FROM $t