SQL Query in Python - Insert value from Python in SQL Query-CodePudding

I have a SQL query in python that is filtered on Timestamp, however the Timestamp changes based on a value from a dataframe.

query = """(SELECT [ID],[Timestamp],[Value] FROM [table] Where [Timestamp] >= '2021-10-13') alias"""

big_df = spark.read.format("jdbc").option("driver", driver).option("url", url).option("dbtable", query).load()

How can I replace '2021-10-13' below to the inserted value from the python dataframe where the value will change? For example it will be 2021-01-01 in this dataframe.

print(somedf.Timestamp.min())
'2021-01-01'

query = """(SELECT [ID],[Timestamp],[Value] FROM [table] Where [Timestamp] >= somedf.Timestamp.min()) alias"""

Obviously the top line will not work

CodePudding user response：

Looks to me like a great use for string formatting:

query = f"(SELECT [ID],[Timestamp],[Value] FROM [table] Where [Timestamp] >= {somedf.Timestamp.min()} alias)"

Don't forget the lowercase f at the beginning of the string, that indicates that its formatted. Lemme know if this doesn't work for you.

David

CodePudding user response：

you can insert some string like this

query = """(SELECT [ID],[Timestamp],[Value] FROM [table] Where [Timestamp] >= %s) alias""" % somedf.Timestamp.min().strftime("%Y-%d-%m")