How to create a parameterized query using pandasql?

Question

I would like to create a query that performs as the following query suggests, but have no idea what to substitute for parameterA, parameterB, parameterC, or parameterD as far as syntax goes. It should be simple, but I am not getting it.

I can make it work with hard-coded values, but not variables/parameters.

Data types are as follows:

column1 = float
column2 = category (integer based category)
column3 = datetime

query = """
    SELECT *
    FROM
        df_input
    WHERE column1 >= parameterA
        AND column1 <= parameterB 
        AND column2 = parameterC
        AND column3 > parameterD;"""

I have tried creating variables and putting those in the query, but I am not doing it correctly.

parameterA = input("parameterA Value?")
parameterB = input("parameterB Value?")
parameterC = input("parameterC Value?")
parameterD = input("parameterD Value?")

Inputs are appropriate for the data types.

How do I make the parameters work in the SQL code using pandasql?

Thanks in advance!

N.B. In this instance, I am not querying an external database, only "querying" a dataframe using pandasql so all the engines, connections, and cursors are superfluous. Actually, in some ways connecting to an external system is easier if one is familiar with relational database systems. I am looking for a solution to move away from the RDBMS for data analysis and into pandas and dataframes. Thus, I am working with pandasql since I am familiar with SQL. I am not yet sure if it is too much for the purpose at hand since native pandas filtering maybe more straight-forward and overall simpler, but I am giving it a try.

Does this answer your question? [Pandas read\_sql with parameters](https://stackoverflow.com/questions/24408557/pandas-read-sql-with-parameters) — markalex, Mar 26 '23 at 21:23
markalex thank you for your assistance. In this instance, I am not querying an external database, only "querying" a dataframe so all the engines, connections, and cursors are superfluous. Actually, in some ways connecting to an external system is easier if one is familiar with relational database systems. I am looking for a solution to move away from the RDBMS for data analysis and into pandas and dataframes. Thus, I am giving pandasql a try since I am familiar with SQL. Thank you for your guidance with this. It is very much appreciated. — noobie, Mar 29 '23 at 21:31

score 0 · Answer 1 · answered Mar 26 '23 at 22:31

I think what you are missing from this is to simply make it an f string and you would be golden:

parameterA = input("parameterA Value?")
parameterB = input("parameterB Value?")
parameterC = input("parameterC Value?")
parameterD = input("parameterD Value?")

query = f"""
    SELECT *
    FROM
        df_input
    WHERE column1 >= {parameterA}
        AND column1 <= {parameterB} 
        AND column2 = {parameterC}
        AND column3 > {parameterD}
;
"""

This will, however, only make the query if you are looking to do something with the query as in send it to SQL or something you would need to create an engine and send the query, but that is a totally different question.

Exactly! I expected that it was something simple, but not that simple. Your reply helped me to understand the key to f-strings are the curly brackets. The reference pages for f-strings were convoluted. You made it clear and simple. Thank you for taking the time to reply to this post. Again, Many Thanks! — noobie, Mar 29 '23 at 21:35

How to create a parameterized query using pandasql?

1 Answers1