CODEWITHSUNDEEP
pythonmysqlsql
Paul Jackways
Paul Jackways

Reputation: 71

datetime date value not updating into MySQL database in Python correctly (updates the integer 2005 instead)

I am having a weird issue while trying to update a record in my database. Here is the section of the code that doesn't make sense:

        """code preceeds"""

        self.NextMail = self.get_NextMail()
        if self.NextMail != None:

            """debugging"""
            print(self.NextMail)
            print(type(self.NextMail))
            print(record[0]) """this is the primary key for Mailouts table and a 1-1 related students table"""

            self.cur.execute("""UPDATE Mailouts
                                SET NextMail={0}
                                WHERE StudentID={1}
                             """.format(self.NextMail,record[0]))

            """code continues..."""

When I run this, here is what is printed:

2017-01-11
<class 'datetime.date'>
1

As you can see, the date added to the database should be 2017-01-11. The SQL type for that field is DATE.

When I select and output this table, the NextMail field appears as in the image: screenshot

Note that there is no place in my code that ever mentions the year 2005, nor the number 2005. if you go ctrl + f and search 2005, nothing comes up. The database was empty, and the field I was updating was Null before I ran the program.

If I go 

    self.cur.execute("""SELECT * FROM Mailouts LIMIT 1""")
    self.all_data = self.cur.fetchall()
    print(self.all_data)

the record shows the number 2005 in the NextMail field with the type int.

I am using PyQt for GUI I am using the modules sqlite3, datetime, calendar.

Upvotes: 1

Views: 361

Answers (1)

Scratch&#39;N&#39;Purr
Scratch&#39;N&#39;Purr

Reputation: 10399

Try this instead:

self.cur.execute("""UPDATE Mailouts
                    SET NextMail=?
                    WHERE StudentID=?
                 """, (self.NextMail, record[0]))

The string formatter will convert your self.NextMail to a string so when it passes the query to the engine, the engine will end up interpreting it as a mathematical operation (ie. 2017-1-11 = 2005).

Upvotes: 1

Related Questions