-1

I have a dataframe (df) like below

df

MAX_TIMESTAMP   
2020-01-24 03:07:40

I want to pass a value from dataframe column and apply greater than comparison the below query.

My query

cursor.execute("""SELECT * FROM ofs.ord_add oa
        WHERE oa.is_active = 'Y' AND  CREATE_TIMESTAMP > '2020-01-20 08:23:12'""")

data=cursor.fetchall()
columns = [column[0] for column in cursor.description]
status = pd.DataFrame(data,columns=columns)

How can I replace the hardcorded value with a parameter value for datetime coming from a dataframe ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sneha nair
  • 26
  • 9
  • https://www.psycopg.org/docs/usage.html replace the hardcoded value with a %s or %(sth)s and refer to that value with a tuple or dictionary. create_timestamp > %(sth)s;""" {sth:sth}) the link should explain more – sammywemmy Jan 28 '20 at 04:03
  • @sammywemmy, Can you write this as an answer as this will helpful for future readers – pankaj Jan 28 '20 at 04:45

2 Answers2

0

I am referencing this link : psycopg2 - usage

Replace the hardcoded value with a %s or %(sth)s and refer to that value with a tuple or dictionary. 

cursor.execute(
               """SELECT * FROM ofs.ord_add oa
                  WHERE oa.is_active = 'Y' AND  CREATE_TIMESTAMP > %(sth)s;
               """
               {sth:sth}
                )

the link should explain more

sammywemmy
  • 27,093
  • 4
  • 17
  • 31
0
sql_query = "SELECT * FROM ofs.ord_add oa WHERE oa.is_active = 'Y' AND  CREATE_TIMESTAMP > %s"
# Index is the df index of the value 
index = 0

cursor.execute(sql_query , (df["MAX_TIMESTAMP"].values[index]))
ecehttp
  • 66
  • 5
  • Hey, Its failing below error`DatabaseError: ORA-01036: illegal variable name/number` – sneha nair Jan 28 '20 at 05:22
  • Hi, the reason may be that in your df you have numpy datetime64 type but in db dates are in strings. Try to convert your date values into strings (ref: https://stackoverflow.com/questions/28694025/converting-a-datetime-column-back-to-a-string-columns-pandas-python) and maybe be sure to send actual date values in tuple. Update me. – ecehttp Jan 28 '20 at 05:47
  • Still not working.In Db its a `NOT NULL DATE ` date field and dataframe column I made it into datetime format using `pd.datetime' – sneha nair Jan 28 '20 at 06:02
  • So sorry to hear that, I'm suspecting a datetime type mismatch issue here, check this blog https://indradhanush.github.io/blog/dealing-with-datetime-objects-in-python/ I'm hoping this will help. – ecehttp Jan 28 '20 at 06:22