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

Question

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

dsillman2000 · Accepted Answer · 2021-10-19 13:38:48Z

1

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

answered Oct 19, 2021 at 13:38

dsillman2000

1,0471 gold badge9 silver badges20 bronze badges

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

1 Comment

Starbucks Over a year ago

I tried this, however an error is thrown at line 2 in the first snippet: com.microsoft.sqlserver.jdbc.SQLServerException: Parse error at line: 1, column: 102: Incorrect syntax near 'alias'.

arutar · Accepted Answer · 2021-10-19 13:39:36Z

1

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")

answered Oct 19, 2021 at 13:39

arutar

1,0936 silver badges10 bronze badges

1 Comment

Starbucks Over a year ago

I tried this, however an error was thrown on line 2 in the first code snippet: com.microsoft.sqlserver.jdbc.SQLServerException: Parse error at line: 1, column: 101: Expected but did not find a closing quotation mark after the character string ') alias WHERE 1=0.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related