replace current_date() from sql with a date in pandas-CodePudding

I have a column with several queries in a dataframe and I would like to replace wherever the CURRENT_DATE() occurs

dict = {'Table Name':'Null', 'Script':'SELECT ID FROM TABLE 1 WHERE CAST(datepartion) BETWEEN '2022-06-01' AND CURRENT_DATE()}
df = pd.DataFrame(dict)

date = '2022-06-29'
date_ = f"CAST('{date}' AS DATE)"

if I use

df['Column'].replace('CURRENT_DATE()', date_, inplace=True, regex=True)

it will return the following as desired with the exception of the parentheses at the end.

CAST('2022-06-29' AS DATE)()

Any help to how to approach the problem. Thanks

CodePudding user response：

() has special meaning in regex, you actually don't need regex

df['Script'] = df['Script'].str.replace('CURRENT_DATE()', date_, regex=False)

print(df)

  Table Name  \
0       Null

                                                                                   Script
0  SELECT ID FROM TABLE 1 WHERE CAST(datepartion) BETWEEN '2022-06-01' AND CURRENT_DATE()

Or escape ()

df['Script'].replace('CURRENT_DATE\(\)', date_, inplace=True, regex=True)