Variables in SQLITE Python

I am working in Jupyter Notebooks using pandasql, which uses SQLight syntax. I am trying to select entries from a certain month, depending on a variable. I am planning to create a Python function that will change the value of the variable being used, but right now I am trying to get “Parameterized Queries” to work.

My table has the columns DATE PAYED, PAYEE, AMOUNT

This is what my SQL query looks like:

varMonth= "01"
q = """
    SELECT
        *,
        strftime('%m', "DATE DUE") as "months"
    FROM 
        year2020
    WHERE 
        "months" = "01"    
"""
test2020 = sqldf(q, globals())

The above code successfully selects all entries from January. I want to change the WHERE conditional to compare against the variable varMonth.

I’ve seen there are different ways to pass a value in an SQL query, like ?, :1, :varMonth, %s, %(varMonth)s, +varMonth+ but these don’t seem to work with my code. When tried they produced this error: PandaSQLException: (sqlite3.ProgrammingError) Incorrect number of bindings supplied. The current statement uses 1, and there are 0 supplied. I can put them in quotes without an error but then the variable is treated as a string. I have seen .execute() (1)(2)as a way to pass the query variables, however when I change the last line to sqldf.execute(q, [varMonth]) I received the error AttributeError: 'function' object has no attribute 'execute'.

Any advice on how to use variables in an SQL query on JupyterNotebooks using pandasql?

Answer

It seems that pandasql and pysqldf don’t have method to use variables in query like for normal database – so you have to use string formatting

q = """
    SELECT
        *,
        strftime('%m', "DATE DUE") as "months"
    FROM 
        year2020
    WHERE 
        "months" = "{}"    
""".format(varMonth)

or f-string

q = f"""
    SELECT
        *,
        strftime('%m', "DATE DUE") as "months"
    FROM 
        year2020
    WHERE 
        "months" = "{varMonth}"    
"""

---

EDIT:

I checked source code for both modules and both use pandas.read_sql() which can get params=(...) like for normal database but modules don’t use it.

If you use ?, %s, etc. then you get only error because params is None