CODEWITHSUNDEEP
pythonmysqldate
user3747506
user3747506

Reputation: 1

Updating DATE field in MySQL database table

Not able to update DATE field in MySql Database.Here is the code i used:

def update_date(self):
    self.cursor.execute("""SELECT  min(date),rec_id FROM mytable1 GROUP BY rec_id;""")
    data=self.cursor.fetchall()
    for row in data:
        open_dt=row[0]
        pr_id=row[1]
        if open_dt:
            open_dt= open_dt.date()
            print open_dt,pr_id
            self.cursor.execute("""UPDATE mytable2 rec_open_date=%s WHERE rec_id=%d;"""%(open_dt,rec_id))
        else:
            pass

I have made all the necessary imports and DB connection is also working. When i run the code it runs with a warning:

Warning: Out of range value for column 'rec_open_date' at row 1
  self.cursor.execute("""UPDATE mytable2 SET rec_open_date=%s WHERE rec_id=%d;""" %(open_dt,rec_id))

Upvotes: 0

Views: 589

Answers (1)

metatoaster
metatoaster

Reputation: 18898

If you refer to the documentation you will see that the execute method calls for a separate data argument, not format the string using %.

Try:

self.cursor.execute("""UPDATE mytable2 rec_open_date=%s WHERE rec_id=%s""",
                    (open_dt, rec_id))

What you did instead was executing this

UPDATE mytable2 rec_open_date=2011-02-03 12:34:56 WHERE rec_id=1;

Which is not a valid statement, but for whatever reason something executed. Also, using % is a security flaw that permits SQL injection attacks.

Upvotes: 1

Related Questions