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?

if there is no special method to use variables then use normal string formatig - "... {} ....".format(varMonth) or f-string - f"... {varMonth} ...." — furas
– furas, Commented Aug 25, 2020 at 0:14
How did you get sqldf ? maybe you created function sqldf() but you should create instance of some class - sqldf = SomeClass() - and then you would have sqldf.execute(). — furas
– furas, Commented Aug 25, 2020 at 0:15

furas · Accepted Answer · 2020-08-25 00:55:23Z

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

edited Aug 25, 2020 at 0:55

answered Aug 25, 2020 at 0:44

furas

149k12 gold badges121 silver badges171 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Variables in SQLITE Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related