[FIXED] While using %sql with Python, how to remove the quotes of a variable passing from python to %sql?

Issue

I am trying to run a for loop in python which iterate all table names in postgresql database, and return the variable counts for each table. However if I pass a variable using %sql with :, the variable would come with a quotes and made the table name invalid. eg:

tablename = 'film'
film_count = %sql select count(*) from :tablename 

what I want to pass with %sql is

film_count = %sql select count(*) from film

However it became

film_count = %sql select count(*) from 'film'

Then system return syntax error:

(psycopg2.errors.SyntaxError) syntax error at or near "'film'"
LINE 1: select count(*) from 'film'
                             ^

Could anyone help me to understand how to solve this issue? Thank you!

Solution

As shown in the examples in https://pypi.org/project/ipython-sql/ , : will enclose variables with quotes.

Use $ or {} instead

Answered By - pepoluan