Variables in SQLITE Python

Question

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 1

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